Mysql 5.1 And Sqlyog.

[peterlaursen]

There is an issue too with 'copy table to other host'. With this example table definition from the MySQL 5.1 docs

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)
);

copy from 5.1.3 to 4.0.26 works fine (the new 'copy table to other host' -code works here!) – however when copying to 5.0.16 it does not work! (no transformation of the 'create statement for the table' is made). See attached.

So the new 'copy table to other host' -code needs another extension to faciliate copy to 5.0 (or 4.1 for that sake) from versions higher tha 5.0. However the charset/collation information should NOT be stripped out in this case!

BTW: the 'create statement for the table' as returned by MySQL 5.1 goes

Code:

CREATE TABLE `employees` (
 `id` int(11) NOT NULL,
 `fname` varchar(30) default NULL,
 `lname` varchar(30) default NULL,
 `hired` date NOT NULL default '1970-01-01',
 `separated` date NOT NULL default '9999-12-31',
 `job_code` int(11) default NULL,
 `store_id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY LIST (store_id)
(PARTITION pNorth VALUES IN (3,5,6,9,17) ENGINE = MyISAM,
PARTITION pEast VALUES IN (1,2,10,11,19,20) ENGINE = MyISAM,
PARTITION pWest VALUES IN (4,12,13,14,18) ENGINE = MyISAM,
PARTITION pCentral VALUES IN (7,8,15,16) ENGINE = MyISAM);

[peterlaursen]

However SQLyog/SJA data sync runs fine between a 5.0.16 and a 5.1.3 table – even if the 5.1.3 table is partitioned! 😀

MySQL 5.0.16 table definition:

Code:

CREATE TABLE `employees` (                      
`id` int(11) NOT NULL auto_increment,            
`fname` varchar(30) default NULL,                
`lname` varchar(30) default NULL,                
`hired` date NOT NULL default '1970-01-01',      
`separated` date NOT NULL default '9999-12-31',  
`job_code` int(11) default NULL,                
`store_id` int(11) NOT NULL,                    
PRIMARY KEY  (`id`,`store_id`)                  
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And mySQL 5.1.3 table definition

Code:

CREATE TABLE `employees` (
`id` int(11) NOT NULL auto_increment,
`fname` varchar(30) default NULL,
`lname` varchar(30) default NULL,
`hired` date NOT NULL default '1970-01-01',
`separated` date NOT NULL default '9999-12-31',
`job_code` int(11) default NULL,`store_id` int(11) NOT NULL default '0',
PRIMARY KEY  (`id`,`store_id`)                                                                            
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY LIST (store_id)
(PARTITION pNorth VALUES IN (3,5,6,9,17) ENGINE = MyISAM,
PARTITION pEast VALUES IN (1,2,10,11,19,20) ENGINE = MyISAM,
PARTITION pWest VALUES IN (4,12,13,14,18) ENGINE = MyISAM,
PARTITION pCentral VALUES IN (7,8,15,16) ENGINE = MyISAM) 

(note that with a LIST type partition the PK must include the column used for partitioning)

[peterlaursen]

And a small issue

(and I don't know if it is a SQLyog or MySQl 5.1 issue)

When copying the 'create statement for the table' from Objects pane there are a lot of blank characters at it end of each line.

this one

`id` int(11) NOT NULL auto_increment,

should be

`id` int(11) NOT NULL auto_increment,

but is

`id` int(11) NOT NULL auto_increment,

But part of the explanation probably is that the partition part of the statement is one l-o-n-g line.

[peterlaursen]

In conclusion:

The 'novelties' of MySQL 5.1 seems to fit fine into the new structure and design of SQLyog 5.x . That makes it quite a lot easer to implement support for MySQL 5.1 then it was with MySQL 5.0.

Actually the adjustments are minor to get SQLyog work smoothless with MySQL 5.1. But of course it will take time to code support for Partition Management (split partition, merge partitions etc) into SQLyog. But even that does not look frightening. The SQL actually is quite simple and only little GUI work is needed that is not identical or very similar to what is there allready 😀

[Ritesh]

We plan to fix all the above issues except the ones related to Partition Table in v5.01.

[Ritesh]

SQLyog v5.01 BETA has been released. More details at: http://www.webyog.com/forums/index.php?act…&st=0#entry8102

No support for partition tables though.

[peterlaursen]

FAQ write-up:

http://www.webyog.com/faq/5_80_en.html

[Author]

Posts