MySQL Bugs: #59935: Attempting to synchronize (illegal) empty tables generates invalid SQL

Bug #59935	Attempting to synchronize (illegal) empty tables generates invalid SQL
Submitted:	4 Feb 2011 5:06	Modified:	9 Jan 2015 16:35
Reporter:	Nicholas Sherlock	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.31a	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
It is possible to create (illegal) empty tables in a model which, when synchronized with a database, generate unhelpful error messages due to invalid generated SQL.

How to repeat:
1. Create a new model.
2. Double click "add table".
3. Without visiting the "columns" tab, click Database -> Synchronize Model and synchronize with some server that doesn't already have a "mydb".
4. The following SQL is generated to create the table:

CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (

)

Which causes the error message:

ERROR: 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 ')

Suggested fix:
Four possible solutions:

1. Detect tables with no columns and provide a more appropriate error message before synchronization. 

2. Consider tables with no columns to be the same as deleted tables on synchronization.

3. Instead of automatically creating the first column for a table when the "Columns" tab is visited in the UI, create it at the same time as the table is created. This is not a complete solution as it is still possible to delete every column in the table after the table is created.

4. In addition to automatically creating the first column for a table when the "Columns" tab is visited in the UI, also perform that operation before an empty table is synchronized.

Thank you for the problem report. Verified on Windows XP. I think suggested solution 1 is the best option.

solution 1 is - in form of Model validation - one of the additional features in the "SE" Edition  of MySQL Workbench and simply not part of the Community Edition.

We might consider adding some variant of solution #2 though - sounds like an interesting approach. But I'd rather go for simply "ignoring" the table upon synchronization if it doesn't have columns defined as treating it as "to delete" could create an unwanted drop if a populated table with that name is existent in DB.

It is a regression.  I reported this before, and it was claimed to be fixed ½ year ago (but I did not test the fix).

http://bugs.mysql.com/bug.php?id=43786