innobackupex fails if SQL_MODE=ANSI_QUOTES

Bug #945161 reported by Baron Schwartz on 2012-03-02
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona XtraBackup moved to
Alexey Kopytov
Fix Released
Alexey Kopytov
Alexey Kopytov

Bug Description

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.

Related branches

Stewart Smith (stewart) wrote :

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

tags: added: innobackupex
Changed in percona-xtrabackup:
status: New → Triaged
importance: Undecided → Low
Alexey Kopytov (akopytov) wrote :

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.

Changed in percona-xtrabackup:
status: Triaged → Invalid
Alexey Kopytov (akopytov) wrote :

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.

Changed in percona-xtrabackup:
status: Invalid → Triaged
Alexey Kopytov (akopytov) wrote :

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

Percona now uses JIRA for bug reports so this bug report is migrated to:

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers