[peterlaursen]

From the changelogs (2 points):

1)

QUOTE

Partition support is not an “engine”, but it was included in the output of SHOW ENGINES. Now it is not. (Bug #14355) The have_partition_engine was renamed to have_partitioning. (Bug #16718)

One should think that thi issue would be fixed now http://www.webyog.com/forums/index.php?sho…mysql+5.1 .But this create statement seems to run forever on 5.1.6

CODE

CREATE TABLE employees (

   id INT NOT NULL,

   fname VARCHAR(30),

   lname VARCHAR(30),

   hired DATE NOT NULL DEFAULT '1970-01-01',

   separated DATE NOT NULL DEFAULT '9999-12-31',

   job_code INT,

   store_id INT

)

PARTITION BY LIST(store_id) (

   PARTITION pNorth VALUES IN (3,5,6,9,17),

   PARTITION pEast VALUES IN (1,2,10,11,19,20),

   PARTITION pWest VALUES IN (4,12,13,14,18),

   PARTITION pCentral VALUES IN (7,8,15,16)

);

So it is difficult to verify … 😡 However it is in accordance (copied from, actually) with the docs! And is the same in SQLyog, Query Browser and Command-line client. Without partitions there are no problems.

Now let's see if this http://bugs.mysql.com/bug.php?id=17331&thanks=4 is also 'not a bug' <_< 2)

QUOTE

ANALYZE TABLE is now supported for partitioned tables. (Bug #13441)

(since I am not able to create a partitioned table (on my windows installation – and I don't want to install this 5.1.6 on LINUX!) I can't verify it to work with SQLyog. Nevertheless I updated the FAQ accordingly!)

[peterlaursen]

Also note that when you have entered the SQL defining the SP in an editor tab, that SQL must be executed to establish the SP with the database. Now you can call() it.

[peterlaursen]

If I get any more insight ….

The important thing is I think that is is HTTP.

looks to me like some protocol error

The http-proctocol converts the data to a stream of octets and must be converted back. So there are chances for some issue wiht the implementation in SQLyog, or with some webserver module (including PHP) or a relay server at the remote network.

it some resemblace with this one:

http://www.webyog.com/forums/index.php?sho…view=getnewpost

But that sort of bits'n'bytes details are not me! Let Ritesh!

[peterlaursen]

I can add that I tested SSH-tunnelling as well.

It looks like this character '>' triggers this error on your system or on this particular server on the combination of the two. Any idea why just this character? 😮

Do you have a chance to test with another server and another client machine as well ? cross-test I mean .. to find out if it is a server issue or client issue?

How do you connect? And what server platform?

When you forget we allways ask you these questions:

http://webyog.com/faq/1_26_en.html 😀

and one question more: what is the returns of this: SELECT @@sql_mode;

[peterlaursen]

I cannot reproduce this. Tested with direct connection and HTTP-tunnel as well on MySQL 5.0.18.

Pic1 shows that there is no error message.

pic2 shows data after import. The r (return) and n (newline) are displayed as squares in the grid, but in BLOB-viever it is parsed correctly.

Looks like a charset issue. What is the default character set for the server? And which MySQL version? If table has another charset (and has to have) than server default then try SET NAMES with the charset you want to use.

Check your HISTORY tab. Do you find anything like:

Code:

/*[04:09:04][   0 ms]*/ show variables like '%character%'
/*[04:09:04][   0 ms]*/ Set character_set_connection=latin1
/*[04:09:04][   0 ms]*/ Set character_set_results=latin1
/*[04:09:04][   0 ms]*/ Set character_set_client=latin1

at the beginning of HISTORY. Those three are identical to SET NAMES

[peterlaursen]

and

Quote:

how i can program a tool under windows to manipulate my data in the mysql db easily?

What language would you prefer to use?

VB for instance uses ODBC to connect. With other languages JDBC may be preferable. For Delphi, C and C++ there a other type of connectors.

But to 'manipulate .. easily' .. hmm. From the little experience that I have (with Powerbuilder /AS-400 ten years ago and more recently PHP and MySQL I would say that coding database manipulations is never 'easy'. The methods for fetching data available with programming languages seem pretty primitive compared to GUI tools like SQLyog. Most often you will need to fetch a row at a time, fill an array with that row and the do ordinary coding with data in that array. And all that in some kind of iterative statement with forms/GUI element control too.

Much depends on how experienced you are as a programmer!

[peterlaursen]

read this:

http://webyog.com/articles/Using_SQLyog_En…L_Databases.pdf

It gives you quite a lot of insight into how it does!

Read about:

1) Timestamp. The sync tool does not use it. Why is explained in the article. And actually we have a pending issue with PK's using a timestamp: http://www.webyog.com/forums/index.php?showtopic=1924

2) How to use the COLUMNS and SQL_WHERE options to effectivize the sync!

Also read this FAQ: http://www.webyog.com/faq/10_61_en.html

[peterlaursen]

no Ritesh!

He is using the Backup 'PowerTool'! But that one has a mail functionality as well!

[peterlaursen]

MySQL 4.x and 5.x should not sync at all!

But it should display an error not attempt to!

http://www.webyog.com/faq/10_68_en.html

@ritesh: check your Eventum! There is such issue pending!

[peterlaursen]

Quote:

Is there a way to encrypt my password in the xml file? This would solve the problem.

I am not sure that I understand! Is it

1) the file as such or

2) the transmitted data

that must be encrypted?

On what platform do you run the SJA? Linux? Windows?

In case of 1) there are mountable encrypted filesystems available for all professional OS's. Also thirdparty 'file system driver' plugins.

in case of 2) well .. where in the transmission chain?

a-) From XML to SJA?

b-) From SJA to MySQL? (here ssh-tunnelling is an option).

c-) Or from SJA to mail-recipients .. this I don't know! Ritesh must be able to tell which authencation methods are supported by the SJA.

It could be a problem with the relay servers running pretty old Linux-variants!

[peterlaursen]

I found these:

http://www.google.com/search?client=opera&…=utf-8&oe=utf-8

http://java.sun.com/products/jndi/tutorial…ty/crammd5.html

.. looks to me like like CRAM-MD5 is only used by LDAP enabled mail servers (and depreciated actually)!

Do you use the e-mail functionality of the Backup Tool? If yes try not to! Can you configure the mailaccount(s) used with the job not to use/demand CRAM-MD5 authentication? Also can you tell from where the demand for CRAM-MD5 comes? The mail-server at an ISP? A corporate mailserver? a mail-client?

[peterlaursen]

And on MySQL 5.1.5

Code:

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                            8        4         2        0        0  

same!

Added: also same on MySQL 3.23.58

[peterlaursen]

1)

I came across a bug report at bugs.mysql.com. there was an issue with GROUP by . Now a TINYINT is not a true boolean. The temp table stores as an integer. This results in 'type loss'. Similar a TIMESTAMP is not a true TIME-variable – it is a char(14) as of 4.0.x. So what hapapens when it is store in a temp table … ?

Just an idea!

2)

I could not help reasearching a little bit into it myself!

Code:

Test 1+2+3:
*********

Source data:

ts                       id  t      
——————-  ——  ——–
2006-02-07 15:34:13      33  Ã¸Ã¸Ã¸    
2006-02-07 15:34:13      34  Ã¸Ã¸      
2006-02-07 15:34:13      35  Ã¸Ã¸Ã¸Ã¸Ã¸Ã¸  
2006-02-07 15:37:00       1  bøllehat
2000-01-01 00:00:00       1  slut    
0000-00-00 00:00:00       1  hakkebøf
2001-11-11 11:11:11       1  bøvl    
2001-11-11 11:11:11       2  griseøre

target data:

ts                       id  t      
——————-  ——  ——–
2006-02-07 15:34:13      33  Ã¸Ã¸Ã¸    
2006-02-07 15:34:13      34  Ã¸Ã¸      
2006-02-07 15:34:13      35  Ã¸Ã¸Ã¸Ã¸Ã¸Ã¸  
2006-02-07 15:37:00       1  bøllehat

(this formatting is as MySQL 4.0.x displays a DATETIME)

Test one:
*********

CREATE TABLE `p1a` (                    
         `ts` timestamp(14) NOT NULL,          
         `id` bigint(20) NOT NULL default '0',  
         `t` varchar(20) default NULL,          
         PRIMARY KEY  (`ts`,`id`)              
       ) TYPE=MyISAM

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                            8        4         2        0        0  

Test two:
*********

CREATE TABLE `p1a` (                    
         `ts` datetime NOT NULL default '0000-00-00 00:00:00',      
         `id` bigint(20) NOT NULL default '0',  
         `t` varchar(20) default NULL,          
         PRIMARY KEY  (`ts`,`id`)              
       ) TYPE=MyISAM

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                            8        4         4        0        0  

test three:
***********

CREATE TABLE `p1a` (                    
         `ts` timestamp(14) NOT NULL,          
         `id` bigint(20) NOT NULL default '0',  
         `t` varchar(20) NOT NULL default '',  
         PRIMARY KEY  (`ts`,`id`,`t`)          
       ) TYPE=MyISAM  

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                            8        4         2        0        0  

test4:
*******

CREATE TABLE `p1a` (            
         `ts` timestamp(14) NOT NULL,  
         `id` bigint(20) default '0',  
         `t` varchar(20) default NULL,  
         PRIMARY KEY  (`ts`)            
       ) TYPE=MyISAM  

(data must now be changed so the TIMESTAMP is unique)

Source data:

           ts      id  t      
————–  ——  ——–
20060207153413      33  Ã¸Ã¸Ã¸    
20060207153414      34  Ã¸Ã¸      
20060207153415      35  Ã¸Ã¸Ã¸Ã¸Ã¸Ã¸  
20060207153700       1  bøllehat
20000101000000       1  slut    
00000000000000       1  hakkebøf
20011111111111       1  bøvl    
20011111111112       2  griseøre

Target data:

           ts      id  t      
————–  ——  ——–
20060207153413      33  Ã¸Ã¸Ã¸    
20060207153414      34  Ã¸Ã¸      
20060207153415      35  Ã¸Ã¸Ã¸Ã¸Ã¸Ã¸  
20060207153700       1  bøllehat

(this formatting is as MySQL 4.0.x displays a TIMESTAMP)

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                            8        4         2        0        0  

Conclusion(s):
**************
1: The issue is not reproduceable with a DATETIME – only a TIMESTAMP.
2: TIMESTAMPs need not be identical to reproduce the problem.

In test4 the data not INSERTED on target are
20011111111111       1  bøvl    
20011111111112       2  griseøre

In test1+2+3 they are
2001-11-11 11:11:11       1  bøvl    
2001-11-11 11:11:11       2  griseøre

All tested on MySQL 4.0.26

[peterlaursen]

@ritesh

I don't think that I will have to remind you that:

1) The fix should be verified on all major MySQL versions – since there are quite a lot code changes with various MySQL's regarding TIMESTAMP handling.

2) It should be checked if it is only a TIMESTAMP that does this. A TIME, a DATE -variable maybe to ??

3) Also if a TIMESTAMP (or a TIME) is used alone as a PK, does that behave the same way?

You may ask me to check some of this. However it would be waste if you did allready … 😉

BTW: does SJA do anything like GROUP BY ?? If yes then MySQL builds temporary table(s) and that could be part of it.

[peterlaursen]

Quote:

Also, with the error on alter table, how can I see the history if there's a modal 'alter table' window up?

Right! The 'Alter Table' pane is 'stealing focus' and must be closed.

[Author]

Posts