| Bug #57098 | RBR breaks on changing user password on 5.1 master -> 5.5 slave | ||
|---|---|---|---|
| Submitted: | 29 Sep 2010 12:34 | Modified: | 15 Nov 2010 19:40 |
| Reporter: | Nidhi Shrotriya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.5.6-rc, 5.1 | OS: | Any |
| Assigned to: | Alfranio Tavares Correia Junior | CPU Architecture: | Any |
[5 Oct 2010 23:38]
Alfranio Tavares Correia Junior
The problem happens because the mysql.user's definition in the master is different from the slave. 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, 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
[6 Oct 2010 10:21]
Bugs System
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/120084 3523 Alfranio Correia 2010-10-06 BUG#57098 RBR breaks on changing user password on 5.1 master -> 5.5 slave Backported the patch for BUG#55452.
[10 Oct 2010 20:27]
Alfranio Tavares Correia Junior
Patch queued to 5.1-bugteam --> 5.5-bugteam --> trunk-merge
[10 Oct 2010 21:33]
Alfranio Tavares Correia Junior
This patch enables to replicate the SET PASSWORD from >=5.1.53 to 5.5. See BUG#57357 for information on <5.1.53 to 5.5.
[13 Oct 2010 13:07]
Jon Stephens
Documented bugfix in the 5.5.7 changelog, as follows:
SET PASSWORD caused row-based replication to fail between a MySQL 5.1
master and a MySQL 5.5 slave.
This fix makes it possible to replicate SET PASSWORD correctly from a
master running MySQL 5.1.53 or a later MySQL 5.1 release to a slave
running MySQL 5.5.7 or a later MySQL 5.5 release.
See also BUG#57357 and BUG#55452.
Set NM status, waiting for push to 5.6.
[13 Oct 2010 13:47]
Jon Stephens
Discussed with Alfranio, no new merges expected, closing.
[13 Oct 2010 14:59]
Alfranio Tavares Correia Junior
Note that backport is backward compatible.
[13 Oct 2010 15:02]
Jon Stephens
Disregard previous comment, closed in error. returned to NM status.
[29 Oct 2010 17:10]
Jon Stephens
Already documented in the 5.1.53, 5.57, and 5.6.1 changelogs. Closing.
[9 Nov 2010 19:48]
Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:20]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:39]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[15 Nov 2010 19:40]
Jon Stephens
No new changelogs entries required. Closed.
[18 Nov 2010 15:55]
Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
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 How to repeat: Start 5.1 (5.1.50)master: ---------------------------- -bash-3.2$ bin/mysqld_safe --no-defaults --server-id=1 --socket=/tmp/mysql1.sock --port=3308 --log-bin=mysql-bin --binlog-format=row & Start 5.5 (5.5.6-rc)slave: -------------------------- -bash-3.2$ bin/mysqld_safe --no-defaults --server-id=2 --socket=/tmp/mysql2.sock --port=3309 & Connect to slave: -------------------- -bash-3.2$ bin/mysql --no-defaults --user=root --socket=/tmp/mysql2.sock mysql> CHANGE MASTER TO MASTER_HOST='localhost',MASTER_PORT=3308,MASTER_USER='root',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106; Query OK, 0 rows affected (0.09 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) Connect to master: ------------------ -bash-3.2$ bin/mysql --no-defaults --user=root --socket=/tmp/mysql1.sock Execute: DROP DATABASE IF EXISTS privdb; CREATE DATABASE privdb; USE privdb; GRANT CREATE ROUTINE ON privdb.* TO 'create_rout_db'@'localhost'; SET PASSWORD FOR 'create_rout_db'@'localhost' = PASSWORD('create_rout_db'); On master: ------------ mysql> show binlog events; +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.50-enterprise-commercial-advanced-log, Binlog ver: 4 | | mysql-bin.000001 | 106 | Query | 1 | 201 | DROP DATABASE IF EXISTS privdb | | mysql-bin.000001 | 201 | Query | 1 | 288 | CREATE DATABASE privdb | | mysql-bin.000001 | 288 | Query | 1 | 417 | use `privdb`; GRANT CREATE ROUTINE ON privdb.* TO 'create_rout_db'@'localhost' | | mysql-bin.000001 | 417 | Query | 1 | 487 | BEGIN | | mysql-bin.000001 | 487 | Table_map | 1 | 640 | table_id: 2 (mysql.user) | | mysql-bin.000001 | 640 | Update_rows | 1 | 883 | table_id: 2 flags: STMT_END_F | | mysql-bin.000001 | 883 | Query | 1 | 1037 | use `privdb`; SET PASSWORD FOR 'create_rout_db'@'localhost'='*08792480350CBA057BDE781B9DF183B263934601' | | mysql-bin.000001 | 1037 | Query | 1 | 1108 | COMMIT | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------+ 9 rows in set (0.00 sec) Check status on slave: ----------------------- mysql> show slave status; | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | | Waiting for master to send event | localhost | root | 3308 | 60 | mysql-bin.000001 | 1108 | siv35-relay-bin.000002 | 563 | mysql-bin.000001 | Yes | No | | | | | | | 1677 | Column 32 of table 'mysql.user' cannot be converted from type 'tinyblob' to type 'enum('','ANY','X509','SPECIFIED'' | 0 | 417 | 1410 | None | | 0 | No | | | | | | NULL | No | 0 | | 1677 | Column 32 of table 'mysql.user' cannot be converted from type 'tinyblob' to type 'enum('','ANY','X509','SPECIFIED'' | | 1 | 1 row in set (0.00 sec)