| 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 Tavares Correia Junior | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| 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 | ||
[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 Tavares Correia 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.
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.