MySQL Bugs: #76176: Synchronize Model gives Error 2014

Bug #76176	Synchronize Model gives Error 2014
Submitted:	5 Mar 2015 15:12	Modified:	28 Mar 2018 16:46
Reporter:	Sherzod Ruzmetov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	6.3.4.0	OS:	Linux (fedora 21)
Assigned to:		CPU Architecture:	Any

Description:
After making changes to the model with table triggers, synchronization fails with error message:

Executing SQL script in server
ERROR: Error 2014: Commands out of sync; you can't run this command now
SQL Code:
        USE `talibro`
SQL script execution finished: statements: 273 succeeded, 1 failed

If I Skip synchronization of triggers synchronization goes through fine.
Also, when I store the synchronization SQL into a file and send it using the command line, it also goes through fine.

How to repeat:
Create a model with table triggers and Choose from the meny "Database -> Synchronize Model". IN the options dialogue make sure nothing is skipped.

Thank you for the bug report. I am not able to repeat with my small own model, are you able to provide a test case model?. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I found the culprit responsible: this is due to C-style comments in my triggers ( /* and */ ). 

When I remove all C-style comments MySQL Workbench can synchronize successfully!

Since trigger codes I'm synchronizing is quite large (few thousand lines) I'm trimming it down right now to the very comment causing this behavior. I'll revert back in few minutes with an SQL script.

Note, that the same script which is causing Error 2014 in Workbench can be sent to /usr/bin/mysql client successfully! So the problem is in the client which is bundled with Mysql Workbench.

I tried to set mysql client through Edit->Preferences->Administrator section to /usr/bin/mysql to no avail. 

Version of mysql client: 14.14 Distrib 5.6.24, for Linux (x86_64) using  EditLine wrapper

The behavior is also consistent in 6.3.3.0

if delimiter "//" is follows a C-style comment synchronization fails in MySQL Workbench

Attachment: alter.sql (application/sql, text), 1.04 KiB.

Phew.... I'm telling you!!

Check out the alter.sql script. If you move the comment "this is the comment" after "//", it synchronizes fine. Otherwise it throws the following error:

Executing SQL script in server
ERROR: Error 2014: Commands out of sync; you can't run this command now
SQL Code:
        DROP TRIGGER IF EXISTS `talibro`.`invoice_BEFORE_DELETE` 

The workaround is: to write all comments inside or above trigger content, NEVER after the trigger.

I also confirm this is not just about C-style comments. Behavior is persistent with  line-comments in the same position ("--").

Since there is a simple workaround perhaps Severity of this bug should be "S3", but this is such a lame bug (honestly, though!) and I spent hours to chase the sucker I left it at S2.

Thank you for the feedback. I couldn't repeat with 6.3.4 if you still get the issue please provide a model file project test case. Thanks.

Hi Miguel,

I'm attaching you the sakilla_full.mwb file where this behavior is persistent. First I Forward  Engineered the model in my local database (Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using  EditLine wrapper). Then I performed synchronize. 

I also discovered another workaround while attemping to duplciate this behavior: just remove ";" from the last END. So make sure, BEGIN/END block doesn't end with a ";".

sakilla_full.mwb - where this behavior is duplicated

Attachment: sakilla_full.mwb (application/vnd.mysql-workbench-model, text), 60.20 KiB.

Thank you for the model file.
Observed this issue with WB 6.3.4 on Win7

Executing SQL script in server
ERROR: Error 2014: Commands out of sync; you can't run this command now
SQL Code:
        USE `sakila`

SQL script execution finished: statements: 69 succeeded, 1 failed

Fetching back view definitions in final form.
7 views were read back.

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 8.0.11 release, and here's the changelog entry:

Models synchronized with existing databases produced an error message if
any of the tables within a model had comments enclosed between /* and */
characters in triggers.

Thank you for the bug report.