Bug #57357 RBR breaks on changing user password on 5.1 --> 5.5 (BUG#55452 and WL#4300)
Submitted: 10 Oct 2010 21:33 Modified: 15 Oct 2010 9:03
Reporter: Alfranio Junior Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression, replication, runtime, WL#4300

[10 Oct 2010 21:33] Alfranio Junior
Description:
ROW based Replication breaks between 5.1 master and 5.5 slave on 'SET PASSWORD' statement

SET PASSWORD FOR 'create_rout_db'@'localhost' = PASSWORD('create_rout_db');

with error as below:
100929 14:17:17 [ERROR] Slave SQL: Column 32 of table 'mysql.user' cannot be converted
from type 'tinyblob' to type 'enum('','ANY','X509','SPECIFIED'', Error_code: 1677

The problem happens because the mysql.user's definition in the master is different from
the slave (see WL#4300). There is an additional field in the slave's mysql.user:

   ...
   `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
   `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
   `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
+  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
   `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
   `ssl_cipher` blob NOT NULL,
   `x509_issuer` blob NOT NULL,
   ...

And in RBR (see BUG#55452), the SET PASSWORD is replicated in row format and triggers the
following error: "Column 32 of table 'mysql.user' cannot be converted from type 'tinyblob'
to type 'enum('','ANY','X509','SPECIFIED'':

MASTER.Trigger_priv -ok-> SLAVE.Trigger_priv
MASTER.ssl_type     -ok-> SLAVE.tablespave_priv
MASTER.ssl_cipher   -XX-> SLAVE.slave_type

How to repeat:
See BUG#57098.

Suggested fix:
In BUG#57098, we have fixed the problem for >=5.1.53 by backporting the patch for BUG#55452 and making the SET PASSWORD to replicate only in statement format.

In this bug, we suggest to move the new field "Create_tablespace_priv" to the bottom of the table thus enabling to replicate from <5.1.53 to 5.5.
[15 Oct 2010 9:03] Alexander Nozdrin
SET PASSWORD is a DDL statement, so it should be replicated by STMT.
[15 Oct 2010 9:52] Alfranio Junior
SET PASSWORD was replicated in STMT and ROW format due to BUG#55452. This fact along with the new field introduced by WL#4300 lead to the bug reported here.

The issue happens because, in ROW format, replication expects that either the table's definition in the master and slave matches or any additional field is at the bottom of the table. However, WL#4300 introduced a new field at the middle of the table due to the server's assumption that privileges always appear together.

>=5.1.53
SET PASSWORD is a DDL statement and must be replicated in the STMT format. This was fixed in BUG#57098. So replicating from >=5.1.53 will work fine.

<5.1.53
This bug BUG#57357 was filed to move the new field introduced by WL#4300 to the bottom of the table and thus enabling replication between <5.1.53 and 5.5. Note this is required because such versions will still send the SET PASSWORD in ROW format.

However fixing this requires several changes to the parser and functions that handle changes to the privileges as these functions assume that privileges always appear together in a table.