Sqlyog Alter Table Bug

[peterlaursen]

I was playing around with MySQL 5.1 and partitioned tables. Even the most simple ALTER TABLE from SQLyog GUI raised an error 1064 (the infamous 'You got a syntax error, check …). See attached!

First I thought it was because I had partitioned tables, but the error also occured when tables were not partitioned.

After 2 hours research (comparison of MySQL 5.0 and 5.1 docs ALTER TABLE syntax, docs on partitioning with 5.1 etc) it came out to be a trivial SQL-related bug with SQLyog. The bug has not had effect before MySQL version 5.1 but now it has! And it IS a bug! The SQL generated by SQLyog is incorrect!

ALTER TABLE syntax for MySQL 5.1 is described here:

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

It is similar for MySQL 5.0 except that 5.1 has partition-related alter_specifications

(I checked 3.23, 4.0 and 4.1 docs as well and it is the same!)

and it says syntax must be:

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] …

and not:

ALTER [IGNORE] TABLE tbl_name, alter_specification [, alter_specification] …

To pin it out:

This WORKS with both MySQL 5.0 and 5.1:

alter table tablename1 add column `n` char(10) NULL after `t`;

This WORKS NOT (and is what SQLyog does) with MySQL 5.1:

alter table tablename1, add column `n` char(10) NULL after `t`;

NOTE the comma that should not be there !!!

MySQL versions before 5.1 has accepted that incorrect comma. MySQL 5.1 does not accept it. And it IS wrong what SQLyog does according to the syntax described in the docs.

[vygi]

confirmed!

I was always wondering why there is this additional comma in the ALTER statement and why MySQL doesn't complaint about it.

[Ritesh]

Bug confirmed and will be fixed in v5.01 development tree by Monday.

[peterlaursen]

fine …

but I think you should release 5.01 ASAP if it is perfectly sure that the lexer-bug is fixed. And I think it is. Even if the docs and minor issues are not quite finished yet (there must then be a 5.02 i 1-2 weeks time – though I know you hate those 'small releases'!) . But the lexer bug is a potential data destroying bug, and that is serious!

Or at least you could make the 5.01 BETA publically available.

[Ritesh]

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

[peterlaursen]

Confirmed fixed 😀

[Author]

Posts