Bug #4327 create table syntax change between 4.0.16 and 4.0.20

• Submitted: 29 Jun 2004 17:23
• Modified: 29 Jun 2004 18:06
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.20
• OS: Linux (RedHat Linux (MySQL rpms))
• Assigned to: Dean Ellis
• CPU Architecture: Any

[29 Jun 2004 17:23] [ name withheld ]

Description:
A trailing comma at the end of the last item in a create table statement was valid in 4.0.16 but is now a syntax error in 4.0.20. When a 4.0.20 slave is replicating from a 4.0.16 master this leads to a case where replication will stop because the replicated create table fails on the slave and the subsequent inserts into that table are now trying to use a non-existant table.

How to repeat:
create table dealCategories_staging (
id int not null auto_increment primary key,
dealID int(4),
baseCategoryID int(4),
cpc decimal(7,2),
cpm decimal(7,2) default -1,
rank int default 0,
dateCreated datetime,
lastModified timestamp(14),
KEY idxDealID (dealID),
KEY idxCatID (baseCategoryID),
KEY idxDealCat (dealID, baseCategoryID),
);

works on 4.0.16 but not on 4.0.20. On 4.0.20 this results in an error in the error log: ERROR: 1064  You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 13

Suggested fix:
While I understand that this may not be valid syntax the fact that it used to work  means that it's a bit of a backwards compatability break. 

If this is resolved as a WONTFIX, can I at least request that the error log include the full failing statement or possibly binlog/relaylog filename and position on the slave?

Thanks

[29 Jun 2004 18:06] Dean Ellis

That it accepted a trailing comma was a bug, which as you observe was corrected ( http://bugs.mysql.com/bug.php?id=3481 ).

As the statement fails there is no binary/relay log position for the statement, per se.  If the statement fails on the slave, the slave's error log and slave status should identify the position.