MySQL Bugs: #56748: SQL Editor creates text drop-outs after opening SQL script file

Bug #56748	SQL Editor creates text drop-outs after opening SQL script file
Submitted:	13 Sep 2010 13:01	Modified:	23 Sep 2010 12:03
Reporter:	Harald Werr	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.27	OS:	Windows (XP Professional SP3)
Assigned to:		CPU Architecture:	Any
Tags:	drop-outs, file content, huge files, regression, sql editor, text mutilation, text truncation

Description:
Used MySQL server: 5.1.50 community

After loading a syntactically correct SQL script file with several thousands of lines to the editor (e.g. from a DB that was previously dumped with the DB dump function of the MySQL Server Administrator), the content of the file - as visible in the editor window - appears to be mutilated: Parts and/or whole sections of consecutive lines have been "dropped", resulting of course in a syntax error when executed, due to the malformed SQL syntax caused by the arbitrary text drop-outs.

The same script file has been loaded and executed successfully in Versions prior to MySQL Workbench 5.2.27.

In the example screenshot attached, a portion of text between the lines 1366 and 1369 got lost, resulting in a syntax error as seen in the SQL Editor window marked with the red "X". For verification reasons, the same SQL file is shown as opened in the editors WordPad and in WinVi (text and hex display, respectively). The missing portion is highlighted in all editors. Coding of the file is UTF-8.

As a file comparison between the original file and a copy from the editor window has shown, the example drop-out is only 1 out of 45(!) text passages which the editor dropped from the original file. The drop-outs are irregularily scattered over the file, although some pattern seems to exist (drop-outs appear about every 1100-1600 lines, and in no instance more than 5 consecutive lines or partial lines are dropped).

This bug seems similar to #56637 "Altering stored procedure removes the end of the code", with the difference that in our case an unaltered file is mutilated right from its opening.

Saving the file again results in a mutilated script file as previously displayed in the MySQL Workbench SQL editor window.

The error can be repeated on various systems (all Windows XP), creating the drop-outs always in exactly the same portions of the file.

How to repeat:
For our company's security policy reasons, the whole example file cannot be published here to reproduce the error.

However, MySQL users are encouraged to produce their own large SQL script files (e.g. by using the DB dumping function on a medium-sized database and to re-create the database by executing the file in the MySQL Workbench SQL Editor) in order to reproduce the error.

Notes:
1. A drop-out my remain undetected if, by chance, the remaining SQL command happens to be again syntactically correct. This appears once in the 45 cases of the example file.

2. Dur to the observed distribution pattern of the drop-outs, a drop-out will probably not appear in script files with less than 1000 lines of code.

Suggested fix:
None. Workaround is to use an older version of the MySQL Workbench.

Comparison of script file content as loaded in SQLEditor and in editors "WordPad" and "WinVi", showing drop-out in MySQL editor

Attachment: MySQL_5_2_27_SQLEditor_Bug.jpg (image/jpeg, text), 423.93 KiB.

Thank you for the bug report. Are you able to provide a test script file private if you wish?. Thanks in advance.

If you can't upload a real file, you can try running some script that will replace all text with XXXX, although you'd have to check if the resulting file can still trigger this bug.

Original anonymized Test DB, dumped with MySQL Administrator

Attachment: MySQLAdmin_Anonymized_DB_Dump_for_Bug_56748.zip (application/octet-stream, text), 473.90 KiB.

Mutilated DB Dump, as created by "Save script as..." of the SQL-Editor

Attachment: MySQLAdmin_Anonymized_DB_Dump_for_Bug_56748_saved.zip (application/octet-stream, text), 473.91 KiB.

To reproduce the bug, I now attached two zipped anonymized SQL script versions of my database, each created by the dump function of MySQL Administrator: The original file with correct syntax, and the mutilated file after loading the original file into the SQL Editor an saving it again.

The erroneous lines have changed in comparison to my original description, since the content of the SQL file has changed by anonymization, especially the lenghts of the lines. I noticed that the error occurs with very long SQL lines (say, more than 10.000 characters). Editing text above the erroneous lines can make the location of the error change (could not reproduce this).

After loading the unzipped original SQL file into the MySQL Editor, the error is indicated in line 284, at an INSERT statement into table "user", around position 5664 of the line, when adding user with ID=64. The 5 characters from position 5665 to 5669 of the original file (,'xxx) are missing. The line has originally 15672 characters, the mutilated line has only 15667 characters.

Some more mutilations happen in the lower section of the script file, but they are more difficult to discover due to the huge length of the line (e.g. line 371 with originally 197068 characters total and 197067 characters in the mutilated version).

The mutilation seems to happen only due to the loading procedure; if the original file content is opened in a different editor and copied into the SQL Editor window via Copy/Paste, the content stays intact and can be executed without errors (this is also a kind of workaround).

Sorry, the first paragraph of my previous comment should say:

"To reproduce the bug, I now attached two zipped anonymized SQL script versions of my database; the original one was created by the dump function of MySQL
Administrator containing correct syntax; the mutilated file (file name ending with "..._saved.sql") was created by loading the original file into the SQL editor and saving it again."

Thank you for the test case. I was able to repeat the bug in 5.2.27 but not in the development version of 5.2.28. After also checking the involved code, this seems to be indeed a variation of bug #56637 and bug #56083 

Thanks once again.

Some questions:

1. If it is only a variation of bug #56083, would that mean that the error would disappear in version 5.2.27 if I removed all German umlauts and other special characters from the database?

2. How does Alfredo's statement that it might only be a variation of bugs #56637 and #56083 match with my observation that text mutilations obviously occur along with the use of very long SQL statements (>10000 characters)?

3. What is implemented differently (better) in development version 5.2.28 that makes the bug disappear? Could I get an installer for version 5.2.28 myself, so that I can prove the disappearance of the bug along with the original database (where 45 errors of that kind had occurred)?

Thank you developers for a brief statement.

Another version of the anonymized DB dump, now without special characters (behaves well)

Attachment: MySQLAdmin_Anonymized_DB_Dump_for_Bug_56748_without_umlauts.zip (application/octet-stream, text), 473.91 KiB.

I verified the assumption of question 1: if the bug is only a variation of bug #56083, the SQL editor should behave well if I removed every suspicious character from the dump.

I edited the original dump, so that it only contains characters satisfying the regular expression

[-A-Z a-z@^0-9,\.:;_+#\*\/'"!?=\n\r\t`\(\)\[\]&%\\<>]

i.e., no umlauts or eszet, enriched vocals like á, è...; especially, all characters are now 8-bit, none is 16-bit long (see attachment above).

After importing in SQL editor, the script can be processed w/o problems and the file saved back to disk is identical to the original file.

I also tested this with the DB dump from my original post (not anonymized). Also processes w/o errors.

When I first forgot to correct one special character in line 39992, it caused mutilations in line 41145 and in line 71222 (the last line of the file). So the current bug is different from bug 56083 in the way that special characters have effects in remote lines, but not in the same line.

This seems to be a general effect: in all my observations the mutilated lines had not necessarily contained special characters, but maybe one of the lines (far) before.

Remark: Everything had worked well in version 5.2.11 OSS Beta (my last installation before 5.2.27), even with special chars. Only the long lines of a dump seemed to be a problem then, too.

Just found: User Chris Wilson describes a similar behavior in his comment on MySQL web site http://wb.mysql.com/?p=750&cpage=1#comment-2118. He states that this effect did not occur till 5.2.25 (he didn't test on 5.2.26).

I can confirm that the reported bug has disappeared with the recently released Version 5.2.28 of MySQL Workbench when tested with the original SQL script of my initial bug report (containing many non-standard characters).

Databases can be reconstructed now from a previous dump produced e.g. by the MySQL Administrator DB Dump function.

Thanks to all developers who worked on the new version!