innobackupex fails if SQL_MODE=ANSI_QUOTES

[Baron Schwartz]

This code fails with ANSI quotes:

sub get_slave_open_temp_tables {
   my @lines;
   mysql_send 'SHOW STATUS LIKE "slave_open_temp_tables"\G;';
   file_to_array($mysql_stdout, \@lines);

It causes this error:

innobackupex: Error: mysql child process has died: ERROR 1064 (42000) at line 7: 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 '"slave_open_temp_tables"' at line 1
while waiting for reply to MySQL request: 'SHOW STATUS LIKE "slave_open_temp_tables"\G;' at /usr/bin/innobackupex line 336.

[Stewart Smith (stewart)]

It looks like we should explicitly set SQL_MODE so that we don't run into this if SQL_MODE is set globally?

[Alexey Kopytov (akopytov)]

I don't see a bug here, that's an expected behavior. With SQL_MODE=ANSI_QUOTES, the server treats slave_open_temp_tables as an identifier, so the statement is syntactically identical to "SHOW STATUS LIKE foo", which is wrong syntax.

[Alexey Kopytov (akopytov)]

Sorry, I didn't notice it's an XtraBackup bug and was somehow assuming this to be a server bug report. In innobackupex, we should just not use double quotes.

[Alexey Kopytov (akopytov)]

Does not apply to 2.1, as the corresponding code has been reworked there.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-846