From: Martin Skold Date: August 26 2011 10:36am Subject: bzr push into mysql-5.1-telco-7.0 branch (Martin.Skold:4285 to 4287) List-Archive: http://lists.mysql.com/commits/140805 Message-Id: <20110826103631.B2AA19F8D75@quadfish> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 4287 Martin Skold 2011-08-26 ndb dist priv - Added explicit commit/rollback before writing to binlog If transaction was aborted nothing is written to binlog Added new test cases with lock conflict in mysql.user table causing transaction to abort (after timeout) modified: mysql-test/suite/ndb/r/ndb_dist_priv.result mysql-test/suite/ndb/t/ndb_dist_priv.test mysql-test/suite/rpl_ndb/r/rpl_ndb_dist_priv.result mysql-test/suite/rpl_ndb/t/rpl_ndb_dist_priv.test sql/sql_acl.cc 4286 Martin Skold 2011-08-26 bug#11938564 NDB DIST PRIV FAILS TO REPLICATE REVOKE UPDATE: Always use explicit database name first then the one in context when calling ha_binlog_log_query modified: sql/sql_acl.cc 4285 Martin Skold 2011-04-19 bug#11938564 NDB DIST PRIV FAILS TO REPLICATE REVOKE UPDATE: Updated test cases modified: mysql-test/suite/ndb/r/ndb_dist_priv.result mysql-test/suite/ndb/t/ndb_dist_priv.test mysql-test/suite/rpl_ndb/r/rpl_ndb_dist_priv.result mysql-test/suite/rpl_ndb/t/rpl_ndb_dist_priv.test === modified file 'mysql-test/suite/ndb/r/ndb_dist_priv.result' --- a/mysql-test/suite/ndb/r/ndb_dist_priv.result 2011-04-19 12:09:29 +0000 +++ b/mysql-test/suite/ndb/r/ndb_dist_priv.result 2011-08-26 10:13:41 +0000 @@ -109,6 +109,16 @@ connect(127.0.0.1,billy1,,test,MASTER_PO ERROR 28000: Access denied for user 'billy1'@'localhost' (using password: NO) connect(127.0.0.1,billy1,wrongpass,test,MASTER_PORT1,MASTER_SOCKET); ERROR 28000: Access denied for user 'billy1'@'localhost' (using password: YES) +BEGIN; +UPDATE mysql.user SET Password = '' +WHERE User = 'billy1'; +SET PASSWORD FOR 'billy1'@'127.0.0.1' = PASSWORD('newpass'); +ERROR 42000: Can't find any matching row in the user table +SHOW WARNINGS; +Level Code Message +Error 1297 Got temporary error 266 'Time-out in NDB, probably caused by deadlock' from NDB +Error 1133 Can't find any matching row in the user table +ROLLBACK; DROP USER 'billy1'@'127.0.0.1'; GRANT ALL ON *.* TO 'billy2'@'127.0.0.1'; SELECT USER(); === modified file 'mysql-test/suite/ndb/t/ndb_dist_priv.test' --- a/mysql-test/suite/ndb/t/ndb_dist_priv.test 2011-04-19 12:09:29 +0000 +++ b/mysql-test/suite/ndb/t/ndb_dist_priv.test 2011-08-26 10:13:41 +0000 @@ -103,6 +103,22 @@ connect (server2_should_fail,127.0.0.1,b --error ER_ACCESS_DENIED_ERROR connect (server2_should_fail,127.0.0.1,billy1,wrongpass,test,$MASTER_MYPORT1,); +# +# Testing failed DDL transaction +# +connection server1; +BEGIN; +UPDATE mysql.user SET Password = '' +WHERE User = 'billy1'; + +connection server2; +--error ER_PASSWORD_NO_MATCH +SET PASSWORD FOR 'billy1'@'127.0.0.1' = PASSWORD('newpass'); +SHOW WARNINGS; + +connection server1; +ROLLBACK; + connection server2; DROP USER 'billy1'@'127.0.0.1'; @@ -121,8 +137,6 @@ SELECT * FROM t1 order by pk; connection server2; DROP USER 'billy2'@'127.0.0.1'; - - --echo === making backup of new users === connection server1; === modified file 'mysql-test/suite/rpl_ndb/r/rpl_ndb_dist_priv.result' --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_dist_priv.result 2011-04-19 12:09:29 +0000 +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_dist_priv.result 2011-08-26 10:13:41 +0000 @@ -110,33 +110,15 @@ DROP USER 'newuser'@'localhost'; == Showing binlog server1 == show binlog events from ; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; GRANT UPDATE ON t1 TO 'user'@'localhost' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; SET PASSWORD FOR 'user'@'localhost'='*D8DECEC305209EEFEC43008E1D420E1AA06B19E0' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; RENAME USER 'user'@'localhost' TO 'newuser'@'localhost' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.t1 TO 'user2'@'localhost' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.t1 TO 'user'@'localhost' -master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # REVOKE SELECT ON test.t1 FROM 'user'@'localhost' master-bin.000001 # Query # # CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass' master-bin.000001 # Query # # REVOKE UPDATE ON t1 FROM 'newuser'@'localhost' @@ -157,25 +139,13 @@ master-bin.000001 # Query # # GRANT SELE master-bin.000001 # Query # # grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user'@'localhost' master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; REVOKE SELECT ON test.t1 FROM 'user'@'localhost' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass' -master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; REVOKE UPDATE ON t1 FROM 'newuser'@'localhost' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; GRANT SELECT (a) ON test.t1 TO 'user'@'localhost' -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; DROP USER 'newuser'@'localhost' -master-bin.000001 # Query # # COMMIT select distinct User,Password from mysql.user order by User; User Password root @@ -194,6 +164,36 @@ user3 *6C8989366EAF75BB670AD8EA7A7FC1176 select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv user +BEGIN; +UPDATE mysql.user SET Password = '' +WHERE User = 'user2'; +SET PASSWORD FOR 'user2'@'localhost' = PASSWORD('newpass'); +ERROR 42000: Can't find any matching row in the user table +SHOW WARNINGS; +Level Code Message +Error 1297 Got temporary error 266 'Time-out in NDB, probably caused by deadlock' from NDB +Error 1133 Can't find any matching row in the user table +== Showing binlog server2 == +show binlog events from ; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass' +master-bin.000001 # Query # # GRANT UPDATE ON t1 TO 'user'@'localhost' +master-bin.000001 # Query # # SET PASSWORD FOR 'user'@'localhost'='*D8DECEC305209EEFEC43008E1D420E1AA06B19E0' +master-bin.000001 # Query # # RENAME USER 'user'@'localhost' TO 'newuser'@'localhost' +master-bin.000001 # Query # # GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2' +master-bin.000001 # Query # # CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass' +master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user2'@'localhost' +master-bin.000001 # Query # # grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION +master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user'@'localhost' +master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES +master-bin.000001 # Query # # use `test`; REVOKE SELECT ON test.t1 FROM 'user'@'localhost' +master-bin.000001 # Query # # use `test`; CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass' +master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES +master-bin.000001 # Query # # use `test`; REVOKE UPDATE ON t1 FROM 'newuser'@'localhost' +master-bin.000001 # Query # # use `test`; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost' +master-bin.000001 # Query # # use `test`; GRANT SELECT (a) ON test.t1 TO 'user'@'localhost' +master-bin.000001 # Query # # use `test`; DROP USER 'newuser'@'localhost' +ROLLBACK; === making backup of new users === call mysql.mysql_cluster_backup_privileges(); ==== clean up ==== === modified file 'mysql-test/suite/rpl_ndb/t/rpl_ndb_dist_priv.test' --- a/mysql-test/suite/rpl_ndb/t/rpl_ndb_dist_priv.test 2011-04-19 12:09:29 +0000 +++ b/mysql-test/suite/rpl_ndb/t/rpl_ndb_dist_priv.test 2011-08-26 10:13:41 +0000 @@ -115,6 +115,27 @@ sync_slave_with_master; select distinct User,Password from mysql.user order by User; select User,Table_priv from mysql.tables_priv ORDER BY User; +# +# Testing that failed DDL transaction does not write into binlog +# +connection server1; +BEGIN; +UPDATE mysql.user SET Password = '' +WHERE User = 'user2'; + +connection server2; +--error ER_PASSWORD_NO_MATCH +SET PASSWORD FOR 'user2'@'localhost' = PASSWORD('newpass'); +SHOW WARNINGS; +echo == Showing binlog server2 ==; +let $binlog_start= $binlog_start_server2; +--source include/show_binlog_events.inc + + +connection server1; +ROLLBACK; + + --echo === making backup of new users === connection server1; === modified file 'sql/sql_acl.cc' --- a/sql/sql_acl.cc 2011-04-19 12:06:58 +0000 +++ b/sql/sql_acl.cc 2011-08-26 10:13:41 +0000 @@ -1654,7 +1654,23 @@ bool change_password(THD *thd, const cha acl_cache->clear(1); // Clear locked hostname cache VOID(pthread_mutex_unlock(&acl_cache->lock)); result= 0; + +#ifndef MCP_WL5482 +#ifdef USING_TRANSACTIONS + if (thd->transaction.stmt.ha_list) + { + int error; + DBUG_PRINT("info", ("%u: Committing/aborting DDL transaction here", __LINE__)); + if ((error= ha_autocommit_or_rollback(thd, thd->is_error()))) + { + result= -1; + } + } +#endif + if ((!result) && mysql_bin_log.is_open()) +#else if (mysql_bin_log.is_open()) +#endif { query_length= sprintf(buff, "SET PASSWORD FOR '%-.120s'@'%-.120s'='%-.120s'", acl_user->user ? acl_user->user : "", @@ -1665,9 +1681,14 @@ bool change_password(THD *thd, const cha FALSE, FALSE, 0); } end: + +#ifndef MCP_WL5482 + int failed= result; +#endif + close_thread_tables(thd); #ifndef MCP_WL5482 - if (result == 0) + if (!failed) { query_length= sprintf(buff, "SET PASSWORD FOR '%-.120s'@'%-.120s'='%-.120s'", acl_user->user ? acl_user->user : "", @@ -3307,7 +3328,21 @@ int mysql_table_grant(THD *thd, TABLE_LI pthread_mutex_unlock(&acl_cache->lock); #ifndef MCP_WL5482 - const bool save_result= result; + bool failed= result; +#endif + +#ifndef MCP_WL5482 +#ifdef USING_TRANSACTIONS + if (thd->transaction.stmt.ha_list) + { + int error; + DBUG_PRINT("info", ("%u: Committing/aborting DDL transaction here", __LINE__)); + if ((error= ha_autocommit_or_rollback(thd, thd->is_error()))) + { + failed= result= -1; + } + } +#endif #endif if (!result) /* success */ @@ -3324,13 +3359,15 @@ int mysql_table_grant(THD *thd, TABLE_LI thd->lex->restore_backup_query_tables_list(&backup); #ifndef MCP_WL5482 - if (!save_result) /* Same condition as 'write_bin_log' */ + if (!failed) /* Same condition as 'write_bin_log' */ { char * db= table_list->get_db_name(); + char *lex_db= thd->lex->select_lex.db; + ha_binlog_log_query(thd, 0, LOGCOM_GRANT, thd->query(), thd->query_length(), - (db)?db:"mysql", ""); + (db)?db:((lex_db)?lex_db:"mysql"), ""); } #endif @@ -3512,7 +3549,6 @@ bool mysql_routine_grant(THD *thd, TABLE DBUG_RETURN(result); } - bool mysql_grant(THD *thd, const char *db, List &list, ulong rights, bool revoke_grant) { @@ -3629,7 +3665,21 @@ bool mysql_grant(THD *thd, const char *d VOID(pthread_mutex_unlock(&acl_cache->lock)); #ifndef MCP_WL5482 - const int save_result = result; + int failed= result; +#endif + +#ifndef MCP_WL5482 +#ifdef USING_TRANSACTIONS + if (thd->transaction.stmt.ha_list) + { + int error; + DBUG_PRINT("info", ("%u: Committing/aborting DDL transaction here", __LINE__)); + if ((error= ha_autocommit_or_rollback(thd, thd->is_error()))) + { + result= failed= -1; + } + } +#endif #endif if (!result) @@ -3641,12 +3691,13 @@ bool mysql_grant(THD *thd, const char *d close_thread_tables(thd); #ifndef MCP_WL5482 - if (!save_result) /* Same condition as 'write_bin_log' */ + if (!failed) /* Same condition as 'write_bin_log' */ { char *lex_db= thd->lex->select_lex.db; + ha_binlog_log_query(thd, 0, LOGCOM_GRANT, thd->query(), thd->query_length(), - (lex_db)?lex_db:((db)?db:"mysql"), ""); + (db)?db:((lex_db)?lex_db:"mysql"), ""); } #endif @@ -5863,6 +5914,20 @@ bool mysql_create_user(THD *thd, List transaction.stmt.ha_list) + { + int error; + DBUG_PRINT("info", ("%u: Committing/aborting DDL transaction here", __LINE__)); + if ((error= ha_autocommit_or_rollback(thd, thd->is_error()))) + { + result= some_users_created= FALSE; + } + } +#endif +#endif + if (some_users_created) result |= write_bin_log(thd, FALSE, thd->query(), thd->query_length()); @@ -5952,6 +6017,20 @@ bool mysql_drop_user(THD *thd, List transaction.stmt.ha_list) + { + int error; + DBUG_PRINT("info", ("%u: Committing/aborting DDL transaction here", __LINE__)); + if ((error= ha_autocommit_or_rollback(thd, thd->is_error()))) + { + result= some_users_deleted= FALSE; + } + } +#endif +#endif + if (some_users_deleted) result |= write_bin_log(thd, FALSE, thd->query(), thd->query_length()); @@ -6053,6 +6132,20 @@ bool mysql_rename_user(THD *thd, List transaction.stmt.ha_list) + { + int error; + DBUG_PRINT("info", ("%u: Committing/aborting DDL transaction here", __LINE__)); + if ((error= ha_autocommit_or_rollback(thd, thd->is_error()))) + { + result= some_users_renamed= FALSE; + } + } +#endif +#endif + if (some_users_renamed && mysql_bin_log.is_open()) result |= write_bin_log(thd, FALSE, thd->query(), thd->query_length()); @@ -6257,6 +6350,24 @@ bool mysql_revoke_all(THD *thd, List transaction.stmt.ha_list) + { + int error; + DBUG_PRINT("info", ("%u: Committing/aborting DDL transaction here", __LINE__)); + if ((error= ha_autocommit_or_rollback(thd, thd->is_error()))) + { + failed= result= -1; + } + } +#endif +#endif + result= result | write_bin_log(thd, FALSE, thd->query(), thd->query_length()); @@ -6264,11 +6375,13 @@ bool mysql_revoke_all(THD *thd, List lex->select_lex.db; + ha_binlog_log_query(thd, 0, LOGCOM_REVOKE, thd->query(), thd->query_length(), - (lex_db)?lex_db:((db)?db:"mysql"), ""); + (db)?db:((lex_db)?lex_db:"mysql"), ""); } #endif No bundle (reason: useless for push emails).