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

[5 Mar 2015 15:12] Sherzod Ruzmetov
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.
[5 Mar 2015 17:24] Miguel Solorzano
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.
[6 Apr 2015 1:00] Bugs System
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".
[25 Apr 2015 19:22] Sherzod Ruzmetov
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
[25 Apr 2015 19:55] Sherzod Ruzmetov
if delimiter "//" is follows a C-style comment synchronization fails in MySQL Workbench

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

[25 Apr 2015 20:06] Sherzod Ruzmetov
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.
[3 Aug 2015 15:00] Miguel Solorzano
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.
[3 Aug 2015 18:20] Sherzod Ruzmetov
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 ";".
[3 Aug 2015 18:20] Sherzod Ruzmetov
sakilla_full.mwb - where this behavior is duplicated

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

[4 Aug 2015 8:33] Umesh Shastry
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.
[28 Mar 2018 16:46] Christine Cole
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.