MySQL Bugs: #24158: SET PASSWORD in binary log fails under ANSI

Bug #24158	SET PASSWORD in binary log fails under ANSI_QUOTES
Submitted:	10 Nov 2006 0:22	Modified:	7 Feb 2007 11:50
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.28, 5.1	OS:	Linux (Linux)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
The arguments of the SET PASSWORD statement are written to the binary log using double-quotes.

The resulting statement cannot be replicated when the ANSI_QUOTES SQL Mode is enabled.

How to repeat:
SET PASSWORD = password('test');

# mysqlbinlog ./data/*bin.0* | grep 'SET PASSWORD' | tail -n 1

SET sql_mode = '';
SHOW VARIABLES LIKE 'sql_mode';
SET PASSWORD FOR "root"@"localhost"="*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29";
SET sql_mode = 'ANSI_QUOTES';
SHOW VARIABLES LIKE 'sql_mode';
SET PASSWORD FOR "root"@"localhost"="*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29";

mysql 5.0.28-enterprise-gpl-log (root) [test]> SET PASSWORD FOR "root"@"localhost"="*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29";
Query OK, 0 rows affected (0.00 sec)

mysql 5.0.28-enterprise-gpl-log (root) [test]> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql 5.0.28-enterprise-gpl-log (root) [test]> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| sql_mode      | ANSI_QUOTES |
+---------------+-------------+
1 row in set (0.00 sec)

mysql 5.0.28-enterprise-gpl-log (root) [test]> SET PASSWORD FOR "root"@"localhost"="*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29";
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 '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29"' at line 1

Suggested fix:
All strings written to the binary log should be quoted using single-quotes.

Versions 4.1 and 5.1 are affected as well.

Won't fix in 4.1, only 5.0 and higher will be fixed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16186

ChangeSet@1.2310, 2006-11-30 11:08:23+04:00, bar@mysql.com +3 -0
  Bug#24158 SET PASSWORD in binary log fails under ANSI_QUOTES
  Problem: ``SET PASSWORD FOR foo@localhost'' was written into
  binary log using double quites: ``SET PASSWORD FOR "foo"@"localhost"...''.
  If sql_mode was set to ANSI_QUOTES, parser on slave considered
  "foo" and "localhost" as identifiers instead of strigns constants,
  so it failed to parse, generated syntax error and slave then stopped.
  Fix: changing binary log entries to use single quotes:
  ``SET PASSWORD FOR 'foo'@'localhost'...'' not to depend on ANSI_QUOTES.

Pushed into 5.0.32-rpl
Pushed into 5.1.14-rpl

Pushed into 5.0.36, 5.1.15

A note has been added to the 5.1.15 and 5.0.36 changelogs.