Mysql 5.1 And Sqlyog.

[peterlaursen]

Just a few considerations on how SQLyog should adopt to MySQL v 5.1

As I understand it the major things with MySQL 5.1 compared to MySQL 5.0 are

1)

more OBJECTS functionality. Especially regarding triggers and use of cursers inside SP's. This is no problem. The GUI additions to SQLyog 5.0 handles this allready.

2)

Additional cluster/NDB functionality.

I know nothing about it. Does SQLyog work with MySQL clusters / NDB tables at all?

Who knows? Actually it would not be a big deal to set up two old PC's with *nix and a MySQL cluster. Unfortunaltely I don't have two old PC's so somebody else could take over here 🙂

3)

Instance manager. Mainly serves two purposes.

a ) Better management of more MySQL instances running from the same computer.

b ) An ability to change certain configuration parameters while server is running.

I doubt is anything here is SQLyog relevant. Undoubtedly “MySQL Administrator” will integrate some control for this over time. But the documentation on the Instance Manager is very poor right now. So it is too early to tell.

4)

Partitioned tables. This is VERY MUCH SQLyog -relevant.

My considerations:

A ) A partitioned table should be displayed with a slightly changed icon in Object Browser compared to an unpartitioned table.

B ) A partitioned table should have an indication af partitioning model used. if for instance the database my_db displays like;

mytable1

mytable2 (RANGE)

mytable3 (LIST)

mytable4 (HASH)

mytable5 (KEY)

mytable6 (RANGE(sub KEY))

… it just means that mytable1 is unpartitioned, mytable2, -3, -4 and -5 are partitioned using each their partitioning model and (finally) mytable6 is partitioned using the RANGE partitioning model and subpartitioned using the KEY partitioning model.

C ) there should be a “partitions” folder (alongside columns, indexes and triggers) in the TABLE tree.

D ) there should/could be a “subpartitions” folder in a partitions folder.

E ) Basic partition handling methods should be available from pop-up menus and main menu and CREATE TABLE and ALTER TABLE should also somehow give access to partition management.

F ) A special problem is that table diagnostics is not possible on partitioned tables. Diagnostics must be done on each partition at a time. Thus some reorganization of the tools .. table diagnostics menu is needed. And BTW: why is table diagnostics not in table popup-menu? Maybe this would be the easiest and most logical implementation?

… and actually I request at least 4A + 4B + 4F very soon! 😀

[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