MySQL Bugs: #70492: Loading binary logs for point-in-time recover produces "Invalid Syntax" errors

Bug #70492	Loading binary logs for point-in-time recover produces "Invalid Syntax" errors
Submitted:	2 Oct 2013 20:16	Modified:	2 Mar 2016 17:18
Reporter:	Roger Hill	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	Cluster 7.2.12	OS:	Linux (CentOS 6.4)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
I am trying to validate the point-in-time recovery capabilities of the MySQL Cluster.  After restoring a backup taken with ndb_mgm backup feature, which restores everything in the backup correctly, I try to roll the database forward to the last committed transaction using the binary logs.  During this process, I receive some errors similar to:  

ERROR 1064 (42000): 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 ''
ejpFUhMBAAAAZgAAAPEthACAABUDAAAAAAEACG5kYl90ZXN0ABRfYWN0X2FjdGl2aXR5X3N0YXR1
c' at line 1

The script that I run that throws these errors is generated using the following:

mysqlbinlog --database=ndb_test --disable-log-bin --start_position=8662106 --stop_position=8674324 rn1-db-1_binary_log.000141 > rrhtest_8662106_8674324_verbose.sql

Since the sql that generated the entry in the binary log completed successfully, it seems that there must be something wrong with the binary logging or the mysqlbinlog program that is causing a problem.

How to repeat:
This repeats each time I restore the database and load the binary logs.

It appears that some of the queries are missing a '/*!*/; at the end of the query string.   When I add these in manually, I do not get the syntax error. This problem occurred in numerous places through the script. 

Example:
This is what was orginally in the script created from the binary log with mysqlbinlog:

BINLOG '
ejpFUhMBAAAAZgAAAPEthACAABUDAAAAAAEACG5kYl90ZXN0ABRfYWN0X2FjdGl2aXR5X3N0YXR1
cwAWCAwHDAwD/gj+CAP+A/4DD/7+DA8ICBDe/d79/jz+PP0C/h7+Hu4Cet8m
ejpFUhMBAAAARgAAADcuhACAAEIDAAAAAAEACG5kYl90ZXN0ABRfYWN0X2FjdGl2aXR5X3RocmVh
ZAAGCAgIDAwPAv8APA==
ejpFUh4BAAAAMgAAAOxChAAAABUDAAAAAAAAAgAWAQDB/JFiAAAAAAAAB0hPTERfVE0=
ejpFUh4BAAAAswAAAGlGhAAAAEIDAAAAAAAAAgAG//DDiAIAAAAAAAoAAAAAAAAAAQAAAAAAAABj
RcEYTxIAAPDEiAIAAAAAAAsAAAAAAAAAAQAAAAAAAABjRcEYTxIAAODDiAIAAAAAAAoAAAAAAAAA
AQAAAAAAAABjRcEYTxIAAGNFwRhPEgAA4MSIAgAAAAAACwAAAAAAAAABAAAAAAAAAGNFwRhPEgAA
Y0XBGE8SAAA=
ejpFUh4BAAAASAAAAMJWhAAAAEIDAAAAAAAAAgAG/+DCiAIAAAAAAAEAAAAAAAAACAAAAAAAAABi
RcEYTxIAAGJFwRhPEgAA
# at 8674324

Adding '/*!*/; as follows resolved the syntax error:
BINLOG '
ejpFUhMBAAAAZgAAAPEthACAABUDAAAAAAEACG5kYl90ZXN0ABRfYWN0X2FjdGl2aXR5X3N0YXR1
cwAWCAwHDAwD/gj+CAP+A/4DD/7+DA8ICBDe/d79/jz+PP0C/h7+Hu4Cet8m
ejpFUhMBAAAARgAAADcuhACAAEIDAAAAAAEACG5kYl90ZXN0ABRfYWN0X2FjdGl2aXR5X3RocmVh
ZAAGCAgIDAwPAv8APA==
ejpFUh4BAAAAMgAAAOxChAAAABUDAAAAAAAAAgAWAQDB/JFiAAAAAAAAB0hPTERfVE0=
ejpFUh4BAAAAswAAAGlGhAAAAEIDAAAAAAAAAgAG//DDiAIAAAAAAAoAAAAAAAAAAQAAAAAAAABj
RcEYTxIAAPDEiAIAAAAAAAsAAAAAAAAAAQAAAAAAAABjRcEYTxIAAODDiAIAAAAAAAoAAAAAAAAA
AQAAAAAAAABjRcEYTxIAAGNFwRhPEgAA4MSIAgAAAAAACwAAAAAAAAABAAAAAAAAAGNFwRhPEgAA
Y0XBGE8SAAA=
ejpFUh4BAAAASAAAAMJWhAAAAEIDAAAAAAAAAgAG/+DCiAIAAAAAAAEAAAAAAAAACAAAAAAAAABi
RcEYTxIAAGJFwRhPEgAA
'/*!*/;
# at 8674324

Hello Roger,

Thank you for the bug report.
Could you please upload binary log rn1-db-1_binary_log.000141? Compress it before uploading and make it private if you prefer.

Also, this appears to me similar to the one reported in Bug #69253 / #65812 

Thanks,
Umesh

Hi,

Thanks for all the details. I will have to recheck but there was a bug IIRC in 7.2.12 fixed in july 2014 (7.2.17 iirc) so the first thing you should do, especially if you are still in the testing phase, is to go to latest 7.2.23 (or even better to latest 7.4).

I'll get back to you when I confirm where the problem is

kind regards
Bogdan Kecman

Hi,

Unfortunately the log files you uploaded to the ftp are no longer available (old SR). I'm testing with latest 7.2/7.4 and I cannot reproduce this issue.

kind regards
Bogdan Kecman