Description of problem: After upgrading to RHEL5.6 mysqldumps that previously worked without issues can no longer be imported. (The mysqldumps are dumped from a RHEL5.5 mysql package (mysql-server-5.0.77-4.el5_5.4" and have been used for frequent restores since the release of RHEL5.5.) The problem is related to the default option "--extended-insert" which can be turned off with "--skip-opt". With "mysqldump --skip-opt --add-drop-table --add-lock --create-options --disable-keys --lock-tables --quick --complete-insert --hex-blob" the problem is not apparent. The error that appears when trying to import such a dump is : "ERROR at line 7279: Unknown command '\''." Unfortunately the linenumber given by mysql is not very helpful in debugging, as the bundled inserts with "--extended-insert" make the line numbering difficult to use. After downgrading to the previous version of the mysql and mysql-server packages on one of the hosts that had the problem I was immediately able to restore the exact same dumpfile that exhibited the error prior to downgrade. Version-Release number of selected component (if applicable): Name : mysql-server Relocations: (not relocatable) Version : 5.0.77 Vendor: Red Hat, Inc. Release : 4.el5_5.5 Build Date: Fri 03 Dec 2010 06:31:33 PM CET Install Date: Wed 02 Feb 2011 02:12:49 PM CET Build Host: x86-003.build.bos.redhat.com Group : Applications/Databases Source RPM: mysql-5.0.77-4.el5_5.5.src.rpm Size : 23096125 License: GPLv2 with exceptions Signature : DSA/SHA1, Fri 28 Jan 2011 11:35:38 AM CET, Key ID 5326810137017186 Packager : Red Hat, Inc. <http://bugzilla.redhat.com/bugzilla> URL : http://www.mysql.com Summary : The MySQL server and related files. How reproducible: Always reproducible. Steps to Reproduce: 1. Dump the mysql database in question (from a mysql-server-5.0.77-4.el5_5.4 server, I have not attempted to dump and restore from a mysql-server-5.0.77-4.el5_5.5) with mysqldump --hex-blob -h hostname -u username databasename > databasename.sql 2. Restore/import database with : "cat databasename.sql | mysql databasename" 3. Wait until the restore ends with : "ERROR at line 7279: Unknown command '\''. " Actual results: Mysql stops import with "ERROR at line 7279: Unknown command '\''." Expected results: Successful import/restore of the database. Additional info: Just to clarify. The environment consists of three hosts : * master mysql server (RHEL5.5 with mysql-server-5.0.77-4.el5_5.4 rpm) * test server A (RHEL5.6 with mysql-server-5.0.77-4.el5_5.5 rpm) * test server B (RHEL5.6 with mysql-server-5.0.77-4.el5_5.5 rpm) The error is apparent on either test server A or B when running the RHEL5.6 packages. When downgrading test server A to the RHEL5.5 mysql-server and mysql rpms (still staying on RHEL5.5) the problem disappears.
The last paragraph is wrong. It should be : "The error is apparent on either test server A or B when running the RHEL5.6 packages. When downgrading test server A to the RHEL5.5 mysql-server and mysql rpms (still staying on RHEL5.6) the problem disappears."
Mysql dumps can be provided directly to a Red Hat technician via a secure channel upon request. The dumps do contain sensitive data and I can not upload to this public bugtracker.
This looks suspiciously like a regression introduced by the patch for bug #548685. If it is, you can probably reproduce the problem with any sufficiently long blob value --- no need to include sensitive data in the reproducer.
Tom: Would it be possible for you to give me a few hints as to how I can reproduce this? I'm not sure I have a complete understanding of the expected requirements to trigger the bug. Regards -- Denis
Well, that upstream report implied that any blob value longer than mysql's max packet length would create issues. The particular symptom you are describing would likely require having a "\" in the blob data shortly after the limit point, but it would still fail without that, if it's the same/similar issue.
Tom: I'm sorry but I haven't had time to research how to reproduce this without the current dataset. Unless I have a bit more explicit instructions I'm afraid I won't be able to create a failing synthetic dataset in a little while. I'm very interested in getting progress here, but my time is currently very limited and as there is a working workaround (don't upgrade to the new packages) I cannot prioritize this :(
We ran into this same problem last week after updating to mysql-server-5.0.77-4.el5_5.5. Dump and restore on the previous version worked correctly, however the restore on mysql-server-5.0.77-4.el5_5.5 fails with "ERROR at line 963: Unknown command '\''." I have attached an example record (merchandising) which has a text field which contains a perl hash so there are many embedded \n and \'. It is while mysql is importing the records for this table where it fails. The interesting thing is if I add a line return or even a space near the top of file the import will get past this first error and then error out later in the file. The table that it errors out on later in the file also contains a field with a perl hash, which contains \n and \'. If I create a file with just the merchandising record inserts and run the import it succeeds. It seems much like some sort of boundary problem, perhaps where \' is not being passed correctly if it's at the end of the packet. To clarify the insert where it fails in the large import will import correctly on it's own, and the group of inserts for this particular table can also be ran on there own and succeed. We'll give extended insert a try to see if we can dump/restore with this version. A possible test case would probably be to create a table, with a field and put a bunch of \n and \' in that field for each record and see if this issue can be triggered. I'll try to produce such a file, unfortunately I cannot provide this particular database dump that shows the problem. Thanks, -Ron
Created attachment 484256 [details] example merchandising record showing the embedded hash
I was able to create a 20MB test file which exhibits the issue above. Is there a FTP or other method I can use to provide this to the proper person for investigation? Thanks. -Ron
If you don't want to attach it to this bugzilla entry, you can email it to me personally.
I have emailed the test records to Tom with the reproducible case. I also verified in another test case that not using the extended inserts allows the dump and restore to work correctly.
Ron: Thats great. Your efforts are appreciated. Hopefully this can be resolved quickly now that there is a way for Red Hat / Tom to reproduce properly.
Mph. This appears to be an upstream bug. I filed it at http://bugs.mysql.com/bug.php?id=60481 Not sure how long it will take for Red Hat to decide to come out with a patched package (escalating this problem through your support contacts would help move that along). In the meantime, if you don't mind patching your package locally, there's a patch in the upstream bug report that seems to fix Ron's case for me. I'd appreciate it if anyone could confirm that the patch takes care of the symptoms they see.
I'm maybe able to reproduce this issue as well, at least I'm seeing something equivalent at a customer. I'll try whether the patch from the upstream report solves the issue here and if so, I'll escalate via support ticket.
I've solved this issue at our customer by rebuilding the latest MySQL RPM package from RHEL 5 with applying the patch attached to the upstream report of comment #13. Tom, thanks for your work and for the patch! :) As this is a) not supported nor b) the goal of a RHEL subscription, I've now cross-filed this issue as Service Request 00457173.
Excellent, thanks for the cross-check Robert. And the SR --- that should help kick this bug's priority up a notch.
I've just stumbled across this problem as well, with mysql-5.0.77-4.el5_6.6. Unbelievable as that may sound, when I pipe the data via bzcat into mysql, the error occurs. When I first uncompress the same data to disk and pipe it into mysql from there, it works. This is reproducible across many executions of the same commands.
(In reply to comment #30) > I've just stumbled across this problem as well, with mysql-5.0.77-4.el5_6.6. > > Unbelievable as that may sound, when I pipe the data via bzcat into mysql, the > error occurs. When I first uncompress the same data to disk and pipe it into > mysql from there, it works. This is reproducible across many executions of the > same commands. One more thing: the upstream bug report's title "mysql misprocesses lines longer than MAX_BATCH_BUFFER_SIZE" might not be entirely correct. MAX_BATCH_BUFFER_SIZE seems to be hard-coded to 1 GB (1024L * 1024L * 1024L). My entire data file is ~17MB (uncompressed), and the line in question is ~1MB. My max_allowed_packet is 64M.
(In reply to comment #31) > MAX_BATCH_BUFFER_SIZE seems to be hard-coded to 1 GB (1024L * 1024L * 1024L). It is that in recent upstream releases. In our build of 5.0.77 it's 1MB. As I said in the upstream bug report, the ridiculously large size they're using is just making sure that overrun cases are untested, plus exposing the mysql client to out-of-memory failures on long input lines. The case described in this bug report is definitely fixed in mysql-5.0.77-4.el5_6.6. If you have another case that still doesn't work, please file a new report with full instructions to reproduce (what you said in comment #31 is not adequate).
The problem went away after upgrading to mysql-5.0.77-4.el5_6.6. I just found it to be extremely curious that the same data worked from one pipe source but not from another.
(In reply to comment #30) > I've just stumbled across this problem as well, with mysql-5.0.77-4.el5_6.6. Because I hit the same thing at another customer's site today, replying to myself to amend this: With mysql-5.0.77-4.el5_6.6, the problem went away. It occured with 5.0.77-4.el5_5.5 (contrary to what I said above).
Since the problem described in this bug report should be resolved in a recent advisory, it has been closed with a resolution of ERRATA. For information on the advisory, and where to find the updated files, follow the link below. If the solution does not work for you, open a new bug report. http://rhn.redhat.com/errata/RHSA-2013-0121.html