From: Jonas Oreland Date: September 1 2011 12:37pm Subject: bzr push into mysql-5.5-cluster branch (jonas.oreland:3450 to 3453) List-Archive: http://lists.mysql.com/commits/140880 Message-Id: <20110901123710.3B70391B344@perch.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3453 Jonas Oreland 2011-09-01 ndb - wl5482 - user tables in ndb added: mysql-test/suite/ndb/r/ndb_dist_priv.result mysql-test/suite/ndb/t/have_ndb_dist_priv.inc 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/ndb_dist_priv_util.h storage/ndb/tools/HOWTO_distribute_privileges.txt storage/ndb/tools/ndb_dist_priv.sql modified: sql/ha_ndbcluster.cc sql/ha_ndbcluster_binlog.cc sql/ha_ndbcluster_binlog.h storage/ndb/tools/restore/restore_main.cpp 3452 Jonas Oreland 2011-09-01 wl6004-distribution - add calls to SE for adding user/grant updates into binlog of other mysql-servers modified: sql/handler.h sql/sql_acl.cc 3451 Jonas Oreland 2011-09-01 wl6004-transaction - add support for user tables in transactional SE modified: sql/sql_acl.cc 3450 Jonas Oreland 2011-09-01 ndb - disable mysql_embedded, fails in tag:mysql-5.5.15 too modified: mysql-test/t/disabled.def === added file 'mysql-test/suite/ndb/r/ndb_dist_priv.result' --- a/mysql-test/suite/ndb/r/ndb_dist_priv.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/r/ndb_dist_priv.result 2011-09-01 12:36:37 +0000 @@ -0,0 +1,143 @@ +call mysql.mysql_cluster_move_privileges(); +select mysql.mysql_cluster_privileges_are_distributed(); +mysql.mysql_cluster_privileges_are_distributed() +1 +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT) engine = ndb; +INSERT INTO t1 VALUES(0,0),(1,1),(2,2); +CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass'; +GRANT UPDATE ON t1 TO 'user'@'localhost'; +SET PASSWORD FOR 'user'@'localhost'= PASSWORD('newpass'); +RENAME USER 'user'@'localhost' TO 'newuser'@'localhost'; +GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2'; +CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass'; +GRANT SELECT ON test.t1 TO 'user2'@'localhost'; +select distinct User,Password from mysql.user order by User; +User Password +newuser *1E9649BB3F345563008E37641B407AFF50E5835C +root +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user2 Select +grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user2 Select +GRANT SELECT ON test.t1 TO 'user'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user Select +user2 Select +FLUSH PRIVILEGES; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user Select +user2 Select +REVOKE SELECT ON test.t1 FROM 'user'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user2 Select +CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass'; +select distinct User,Password from mysql.user order by User; +User Password +newuser *1E9649BB3F345563008E37641B407AFF50E5835C +root +user +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user2 Select +REVOKE UPDATE ON t1 FROM 'newuser'@'localhost'; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +user2 Select +GRANT SELECT (a) ON test.t1 TO 'user'@'localhost'; +select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; +User Table_name Column_name +user t1 a +FLUSH PRIVILEGES; +select distinct User,Password from mysql.user order by User; +User Password +newuser *1E9649BB3F345563008E37641B407AFF50E5835C +root +user +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +user +user2 Select +select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; +User Table_name Column_name +user t1 a +REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +user +select distinct User,Password from mysql.user order by User; +User Password +newuser *1E9649BB3F345563008E37641B407AFF50E5835C +root +user +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +user +connect(127.0.0.1,magnus,,test,MASTER_PORT,MASTER_SOCKET); +ERROR 28000: Access denied for user 'magnus'@'localhost' (using password: NO) +connect(127.0.0.1,magnus,,test,MASTER_PORT1,MASTER_SOCKET); +ERROR 28000: Access denied for user 'magnus'@'localhost' (using password: NO) +CREATE USER 'billy1'@'127.0.0.1' IDENTIFIED by 'mypass'; +SELECT USER(); +USER() +billy1@localhost +SELECT * FROM t1 order by pk; +pk a +0 0 +1 1 +2 2 +connect(127.0.0.1,billy1,,test,MASTER_PORT1,MASTER_SOCKET); +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 +Warning 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(); +USER() +billy2@localhost +SELECT * FROM t1 order by pk; +pk a +0 0 +1 1 +2 2 +DROP USER 'billy2'@'127.0.0.1'; +=== making backup of new users === +call mysql.mysql_cluster_backup_privileges(); +==== clean up ==== +DROP USER 'newuser'@'localhost'; +DROP USER 'user2'@'localhost'; +DROP USER 'user3'@'localhost'; +DROP TABLE t1; +call mysql.mysql_cluster_backup_privileges(); +call mysql.mysql_cluster_backup_privileges(); +call mysql.mysql_cluster_restore_local_privileges(); +call mysql.mysql_cluster_restore_local_privileges(); === added file 'mysql-test/suite/ndb/t/have_ndb_dist_priv.inc' --- a/mysql-test/suite/ndb/t/have_ndb_dist_priv.inc 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/have_ndb_dist_priv.inc 2011-09-01 12:36:37 +0000 @@ -0,0 +1,39 @@ +--perl +use strict; + +use File::Basename; +use IO::File; +use lib "lib/"; +use My::Find; + +# +# Look for ndb_dist_pric.sql, if not found: skip test. +# + +# +# Set up paths +# +my $vardir = $ENV{MYSQLTEST_VARDIR} or die "Need MYSQLTEST_VARDIR"; +my $mysql_test_dir = $ENV{MYSQL_TEST_DIR} or die "Need MYSQL_TEST_DIR"; +my $basedir = dirname($mysql_test_dir); + +# +# Check if the needed tests are available +# +my $sql_file = my_find_file($basedir, + ["storage/ndb/tools", "share/mysql/"], + "ndb_dist_priv.sql", NOT_REQUIRED); + +my $F = IO::File->new("$vardir/tmp/have_ndb_dist_priv_result.inc", "w") or die; +if ($sql_file) { + print $F "--let \$NDB_DIST_PRIV_SQL= $sql_file\n"; +} +else +{ + print $F "skip Could not find ndb_dist_priv.sql;\n"; +} +$F->close(); + +EOF + +--source $MYSQLTEST_VARDIR/tmp/have_ndb_dist_priv_result.inc === added file 'mysql-test/suite/ndb/t/ndb_dist_priv.test' --- a/mysql-test/suite/ndb/t/ndb_dist_priv.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/ndb_dist_priv.test 2011-09-01 12:36:37 +0000 @@ -0,0 +1,223 @@ +--source include/not_embedded.inc +--source include/have_multi_ndb.inc + +--source have_ndb_dist_priv.inc + +let $load_sql_file = $NDB_DIST_PRIV_SQL; + +connection server1; +--disable_query_log +--disable_result_log +--exec $MYSQL < $load_sql_file +call mysql.mysql_cluster_backup_privileges(); + --enable_result_log + --enable_query_log + +connection server2; +--disable_query_log +--disable_result_log +let $MYSQL2 = $EXE_MYSQL --defaults-file=$MYSQLTEST_VARDIR/my.cnf; +let $MYSQL2 = $MYSQL2 --defaults-group-suffix=.2.1; +--exec $MYSQL2 < $load_sql_file +call mysql.mysql_cluster_backup_privileges(); + --enable_result_log + --enable_query_log + +connection server1; +call mysql.mysql_cluster_move_privileges(); +select mysql.mysql_cluster_privileges_are_distributed(); + +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT) engine = ndb; +INSERT INTO t1 VALUES(0,0),(1,1),(2,2); +CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass'; +GRANT UPDATE ON t1 TO 'user'@'localhost'; +SET PASSWORD FOR 'user'@'localhost'= PASSWORD('newpass'); +RENAME USER 'user'@'localhost' TO 'newuser'@'localhost'; +GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2'; +CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass'; +GRANT SELECT ON test.t1 TO 'user2'@'localhost'; +select distinct User,Password from mysql.user order by User; +select User,Table_priv from mysql.tables_priv ORDER BY User; +grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION; +select User,Table_priv from mysql.tables_priv ORDER BY User; +GRANT SELECT ON test.t1 TO 'user'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; + +connection server2; +FLUSH PRIVILEGES; +select User,Table_priv from mysql.tables_priv ORDER BY User; +REVOKE SELECT ON test.t1 FROM 'user'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass'; +select distinct User,Password from mysql.user order by User; +select User,Table_priv from mysql.tables_priv ORDER BY User; +REVOKE UPDATE ON t1 FROM 'newuser'@'localhost'; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +GRANT SELECT (a) ON test.t1 TO 'user'@'localhost'; +select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; + +connection server1; +FLUSH PRIVILEGES; +select distinct User,Password from mysql.user order by User; +select User,Table_priv from mysql.tables_priv ORDER BY User; +select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; + +connection server2; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; + +connection server1; +select distinct User,Password from mysql.user order by User; +select User,Table_priv from mysql.tables_priv ORDER BY User; + +# +# Test connecting with a user that does not exist +# +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error ER_ACCESS_DENIED_ERROR +connect (server1_should_fail,127.0.0.1,magnus,,test,$MASTER_MYPORT,); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT1 MASTER_PORT1 +--error ER_ACCESS_DENIED_ERROR +connect (server2_should_fail,127.0.0.1,magnus,,test,$MASTER_MYPORT1,); + +# +# Create a new user with CREATE USER and check that it's possible +# to connect as that user on second mysqld +# +connection server1; +CREATE USER 'billy1'@'127.0.0.1' IDENTIFIED by 'mypass'; + +connect (server2_as_billy1,127.0.0.1,billy1,mypass,test,$MASTER_MYPORT1,); +connection server2_as_billy1; +SELECT USER(); +SELECT * FROM t1 order by pk; + +# Test connecting with blank password +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT1 MASTER_PORT1 +--error ER_ACCESS_DENIED_ERROR +connect (server2_should_fail,127.0.0.1,billy1,,test,$MASTER_MYPORT1,); + +# Test connecting with wrong password +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT1 MASTER_PORT1 +--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'; + +# +# Create a new user with GRANT ALL and check that it's possible +# to connect as that user on second mysqld +# +connection server1; +GRANT ALL ON *.* TO 'billy2'@'127.0.0.1'; + +connect (server2_as_billy2,127.0.0.1,billy2,,test,$MASTER_MYPORT1,); +connection server2_as_billy2; +SELECT USER(); +SELECT * FROM t1 order by pk; + +connection server2; +DROP USER 'billy2'@'127.0.0.1'; + +--echo === making backup of new users === + +connection server1; +call mysql.mysql_cluster_backup_privileges(); + +--echo ==== clean up ==== +connection server1; +DROP USER 'newuser'@'localhost'; +DROP USER 'user2'@'localhost'; +DROP USER 'user3'@'localhost'; + +DROP TABLE t1; + +# Restore local privileges +connection server1; +call mysql.mysql_cluster_backup_privileges(); +connection server2; +call mysql.mysql_cluster_backup_privileges(); +connection server1; +call mysql.mysql_cluster_restore_local_privileges(); +connection server2; +call mysql.mysql_cluster_restore_local_privileges(); + + +--disable_query_log +--disable_result_log + +connection server1; + +# Drop the local backup tables +drop table mysql.user_backup; +drop table mysql.db_backup; +drop table mysql.tables_priv_backup; +drop table mysql.columns_priv_backup; +drop table mysql.procs_priv_backup; +drop table mysql.host_backup; + +# Drop the distributed backup tables +drop table mysql.ndb_user_backup; +drop table mysql.ndb_db_backup; +drop table mysql.ndb_tables_priv_backup; +drop table mysql.ndb_columns_priv_backup; +drop table mysql.ndb_procs_priv_backup; +drop table mysql.ndb_host_backup; + +# Drop the function and sprocs +drop function mysql.mysql_cluster_privileges_are_distributed; +drop procedure mysql.mysql_cluster_backup_privileges; +drop procedure mysql.mysql_cluster_move_grant_tables; +drop procedure mysql.mysql_cluster_restore_local_privileges; +drop procedure mysql.mysql_cluster_restore_privileges; +drop procedure mysql.mysql_cluster_restore_privileges_from_local; +drop procedure mysql.mysql_cluster_move_privileges; + +connection server2; + +# Drop the local backup tables +drop table mysql.user_backup; +drop table mysql.db_backup; +drop table mysql.tables_priv_backup; +drop table mysql.columns_priv_backup; +drop table mysql.procs_priv_backup; +drop table mysql.host_backup; + +# The distributed backup tables should already have been dropped +--error ER_NO_SUCH_TABLE +select * from mysql.ndb_user_backup; + +# Drop the function and sprocs +drop function mysql.mysql_cluster_privileges_are_distributed; +drop procedure mysql.mysql_cluster_backup_privileges; +drop procedure mysql.mysql_cluster_move_grant_tables; +drop procedure mysql.mysql_cluster_restore_local_privileges; +drop procedure mysql.mysql_cluster_restore_privileges; +drop procedure mysql.mysql_cluster_restore_privileges_from_local; +drop procedure mysql.mysql_cluster_move_privileges; + +# Force restart since other tests depend on the _exact_ +# order of rows in for example mysql.user +--source include/force_restart.inc + +--enable_result_log +--enable_query_log + === added file 'mysql-test/suite/rpl_ndb/r/rpl_ndb_dist_priv.result' --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_dist_priv.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_dist_priv.result 2011-09-01 12:36:37 +0000 @@ -0,0 +1,205 @@ +include/master-slave.inc +[connection master] +call mysql.mysql_cluster_move_privileges(); +select mysql.mysql_cluster_privileges_are_distributed(); +mysql.mysql_cluster_privileges_are_distributed() +1 +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT) engine = ndb; +CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass'; +GRANT UPDATE ON t1 TO 'user'@'localhost'; +SET PASSWORD FOR 'user'@'localhost'= PASSWORD('newpass'); +RENAME USER 'user'@'localhost' TO 'newuser'@'localhost'; +GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2'; +CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass'; +GRANT SELECT ON test.t1 TO 'user2'@'localhost'; +select distinct User,Password from mysql.user order by User; +User Password +newuser *1E9649BB3F345563008E37641B407AFF50E5835C +root +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user2 Select +grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user2 Select +GRANT SELECT ON test.t1 TO 'user'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user Select +user2 Select +FLUSH PRIVILEGES; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user Select +user2 Select +REVOKE SELECT ON test.t1 FROM 'user'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user2 Select +CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass'; +select distinct User,Password from mysql.user order by User; +User Password +newuser *1E9649BB3F345563008E37641B407AFF50E5835C +root +user +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user2 Select +select distinct User,Password from mysql.user order by User; +User Password +newuser *1E9649BB3F345563008E37641B407AFF50E5835C +root +user +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +newuser Update +user2 Select +FLUSH PRIVILEGES; +REVOKE UPDATE ON t1 FROM 'newuser'@'localhost'; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +GRANT SELECT (a) ON test.t1 TO 'user'@'localhost'; +select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; +User Table_name Column_name +user t1 a +FLUSH PRIVILEGES; +select distinct User,Password from mysql.user order by User; +User Password +newuser *1E9649BB3F345563008E37641B407AFF50E5835C +root +user +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +user +select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; +User Table_name Column_name +user t1 a +select distinct User,Password from mysql.user order by User; +User Password +newuser *1E9649BB3F345563008E37641B407AFF50E5835C +root +user +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +user +select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; +User Table_name Column_name +user t1 a +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 # # use `test`; CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass' +master-bin.000001 # Query # # use `test`; GRANT UPDATE ON t1 TO 'user'@'localhost' +master-bin.000001 # Query # # use `test`; SET PASSWORD FOR 'user'@'localhost'='*D8DECEC305209EEFEC43008E1D420E1AA06B19E0' +master-bin.000001 # Query # # use `test`; RENAME USER 'user'@'localhost' TO 'newuser'@'localhost' +master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2' +master-bin.000001 # Query # # use `test`; CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass' +master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.t1 TO 'user2'@'localhost' +master-bin.000001 # Query # # use `test`; grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION +master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.t1 TO 'user'@'localhost' +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' +master-bin.000001 # Query # # REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost' +master-bin.000001 # Query # # GRANT SELECT (a) ON test.t1 TO 'user'@'localhost' +master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES +master-bin.000001 # Query # # DROP USER 'newuser'@'localhost' +== 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' +select distinct User,Password from mysql.user order by User; +User Password +root +user +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +select User,Table_priv from mysql.tables_priv ORDER BY User; +User Table_priv +user +select distinct User,Password from mysql.user order by User; +User Password +root +user +user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 +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 +Warning 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 ==== +DROP USER 'user2'@'localhost'; +DROP USER 'user3'@'localhost'; +DROP TABLE t1; +STOP SLAVE; +call mysql.mysql_cluster_backup_privileges(); +call mysql.mysql_cluster_backup_privileges(); +call mysql.mysql_cluster_backup_privileges(); +call mysql.mysql_cluster_restore_local_privileges(); +call mysql.mysql_cluster_restore_local_privileges(); +call mysql.mysql_cluster_restore_local_privileges(); === added file 'mysql-test/suite/rpl_ndb/t/rpl_ndb_dist_priv.test' --- a/mysql-test/suite/rpl_ndb/t/rpl_ndb_dist_priv.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/rpl_ndb/t/rpl_ndb_dist_priv.test 2011-09-01 12:36:37 +0000 @@ -0,0 +1,256 @@ +-- source include/have_ndb.inc +-- source include/master-slave.inc +-- source include/have_binlog_format_mixed_or_row.inc +-- source include/have_multi_ndb.inc + +--source suite/ndb/t/have_ndb_dist_priv.inc + +let $load_sql_file = $NDB_DIST_PRIV_SQL; + +connection server1; +--disable_query_log +--disable_result_log +--exec $MYSQL < $load_sql_file +call mysql.mysql_cluster_backup_privileges(); + --enable_result_log + --enable_query_log + +connection server2; +--disable_query_log +--disable_result_log +let $MYSQL2 = $EXE_MYSQL --defaults-file=$MYSQLTEST_VARDIR/my.cnf; +let $MYSQL2 = $MYSQL2 --defaults-group-suffix=.2.1; +--exec $MYSQL2 < $load_sql_file +call mysql.mysql_cluster_backup_privileges(); + --enable_result_log + --enable_query_log + +connection server1; +call mysql.mysql_cluster_move_privileges(); +select mysql.mysql_cluster_privileges_are_distributed(); + +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT) engine = ndb; + +# Save the current binlog position to use as start when +# showing CREATE/DROP USER and GRANTs are written as statements +# in binlog +connection server1; +let $binlog_start_server1= query_get_value(SHOW MASTER STATUS, Position, 1); +connection server2; +let $binlog_start_server2= query_get_value(SHOW MASTER STATUS, Position, 1); +connection server1; + +CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass'; +GRANT UPDATE ON t1 TO 'user'@'localhost'; +SET PASSWORD FOR 'user'@'localhost'= PASSWORD('newpass'); +RENAME USER 'user'@'localhost' TO 'newuser'@'localhost'; +GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2'; +CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass'; +GRANT SELECT ON test.t1 TO 'user2'@'localhost'; +select distinct User,Password from mysql.user order by User; +select User,Table_priv from mysql.tables_priv ORDER BY User; +grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION; +select User,Table_priv from mysql.tables_priv ORDER BY User; +GRANT SELECT ON test.t1 TO 'user'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; + +connection server2; +FLUSH PRIVILEGES; +select User,Table_priv from mysql.tables_priv ORDER BY User; +REVOKE SELECT ON test.t1 FROM 'user'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass'; +select distinct User,Password from mysql.user order by User; +select User,Table_priv from mysql.tables_priv ORDER BY User; + +connection master; +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; + +connection server2; +FLUSH PRIVILEGES; +REVOKE UPDATE ON t1 FROM 'newuser'@'localhost'; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost'; +select User,Table_priv from mysql.tables_priv ORDER BY User; +GRANT SELECT (a) ON test.t1 TO 'user'@'localhost'; +select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; + +connection server1; +FLUSH PRIVILEGES; +select distinct User,Password from mysql.user order by User; +select User,Table_priv from mysql.tables_priv ORDER BY User; +select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; + +connection master; +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; +select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; + +connection server2; +DROP USER 'newuser'@'localhost'; + +# Show that all the above CREATE/DROP USER and GRANT's +# have been written as statements to binlog +# on both server1 and server2 +connection server1; +echo == Showing binlog server1 ==; +let $binlog_start= $binlog_start_server1; +--source include/show_binlog_events.inc +connection server2; +echo == Showing binlog server2 ==; +let $binlog_start= $binlog_start_server2; +--source include/show_binlog_events.inc + +connection server1; +select distinct User,Password from mysql.user order by User; +select User,Table_priv from mysql.tables_priv ORDER BY User; + +connection master; +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; +call mysql.mysql_cluster_backup_privileges(); + +--echo ==== clean up ==== +connection server1; +DROP USER 'user2'@'localhost'; +DROP USER 'user3'@'localhost'; + +DROP TABLE t1; + +sync_slave_with_master; + +STOP SLAVE; + +# Restore local privileges +connection server1; +call mysql.mysql_cluster_backup_privileges(); +connection server2; +call mysql.mysql_cluster_backup_privileges(); +connection slave; +call mysql.mysql_cluster_backup_privileges(); +connection server1; +call mysql.mysql_cluster_restore_local_privileges(); +connection server2; +call mysql.mysql_cluster_restore_local_privileges(); +connection slave; +call mysql.mysql_cluster_restore_local_privileges(); + + +--disable_query_log +--disable_result_log + +connection server1; + +# Drop the local backup tables +drop table mysql.user_backup; +drop table mysql.db_backup; +drop table mysql.tables_priv_backup; +drop table mysql.columns_priv_backup; +drop table mysql.procs_priv_backup; +drop table mysql.host_backup; + +# Drop the distributed backup tables +drop table mysql.ndb_user_backup; +drop table mysql.ndb_db_backup; +drop table mysql.ndb_tables_priv_backup; +drop table mysql.ndb_columns_priv_backup; +drop table mysql.ndb_procs_priv_backup; +drop table mysql.ndb_host_backup; + +# Drop the function and sprocs +drop function mysql.mysql_cluster_privileges_are_distributed; +drop procedure mysql.mysql_cluster_backup_privileges; +drop procedure mysql.mysql_cluster_move_grant_tables; +drop procedure mysql.mysql_cluster_restore_local_privileges; +drop procedure mysql.mysql_cluster_restore_privileges; +drop procedure mysql.mysql_cluster_restore_privileges_from_local; +drop procedure mysql.mysql_cluster_move_privileges; + +connection server2; + +# Drop the local backup tables +drop table mysql.user_backup; +drop table mysql.db_backup; +drop table mysql.tables_priv_backup; +drop table mysql.columns_priv_backup; +drop table mysql.procs_priv_backup; +drop table mysql.host_backup; + +# The distributed backup tables should already have been dropped +--error ER_NO_SUCH_TABLE +select * from mysql.ndb_user_backup; + +# Drop the function and sprocs +drop function mysql.mysql_cluster_privileges_are_distributed; +drop procedure mysql.mysql_cluster_backup_privileges; +drop procedure mysql.mysql_cluster_move_grant_tables; +drop procedure mysql.mysql_cluster_restore_local_privileges; +drop procedure mysql.mysql_cluster_restore_privileges; +drop procedure mysql.mysql_cluster_restore_privileges_from_local; +drop procedure mysql.mysql_cluster_move_privileges; + +connection slave; + +# Drop the local backup tables +drop table mysql.user_backup; +drop table mysql.db_backup; +drop table mysql.tables_priv_backup; +drop table mysql.columns_priv_backup; +drop table mysql.procs_priv_backup; +drop table mysql.host_backup; + +# Drop the distributed backup tables(slave is stopped they +# should still be there) +drop table mysql.ndb_user_backup; +drop table mysql.ndb_db_backup; +drop table mysql.ndb_tables_priv_backup; +drop table mysql.ndb_columns_priv_backup; +drop table mysql.ndb_procs_priv_backup; +drop table mysql.ndb_host_backup; + +# Drop the function and sprocs +drop function mysql.mysql_cluster_privileges_are_distributed; +drop procedure mysql.mysql_cluster_backup_privileges; +drop procedure mysql.mysql_cluster_move_grant_tables; +drop procedure mysql.mysql_cluster_restore_local_privileges; +drop procedure mysql.mysql_cluster_restore_privileges; +drop procedure mysql.mysql_cluster_restore_privileges_from_local; +drop procedure mysql.mysql_cluster_move_privileges; + +# Force restart since other tests depend on the _exact_ +# order of rows in for example mysql.user +--source include/force_restart.inc + +--enable_query_log +--enable_result_log === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2011-08-31 10:39:08 +0000 +++ b/sql/ha_ndbcluster.cc 2011-09-01 12:36:37 +0000 @@ -46,6 +46,7 @@ #include "ndb_global_schema_lock.h" #include "ndb_global_schema_lock_guard.h" #include "abstract_query_plan.h" +#include "ndb_dist_priv_util.h" #include "ha_ndb_index_stat.h" #include @@ -10109,7 +10110,9 @@ int ha_ndbcluster::rename_table(const ch ndb_rep_event_name(&event_name, new_dbname, new_tabname, get_binlog_full(share)); - if (!ndbcluster_create_event(thd, ndb, ndbtab, event_name.c_ptr(), share, + if (!Ndb_dist_priv_util::is_distributed_priv_table(new_dbname, + new_tabname) && + !ndbcluster_create_event(thd, ndb, ndbtab, event_name.c_ptr(), share, share && ndb_binlog_running ? 2 : 1/* push warning */)) { if (opt_ndb_extra_logging) @@ -11094,7 +11097,8 @@ int ndbcluster_discover(handlerton *hton } } #ifdef HAVE_NDB_BINLOG - if (ndbcluster_check_if_local_table(db, name)) + if (ndbcluster_check_if_local_table(db, name) && + !Ndb_dist_priv_util::is_distributed_priv_table(db, name)) { DBUG_PRINT("info", ("ndbcluster_discover: Skipping locally defined table '%s.%s'", db, name)); === modified file 'sql/ha_ndbcluster_binlog.cc' --- a/sql/ha_ndbcluster_binlog.cc 2011-08-31 10:39:08 +0000 +++ b/sql/ha_ndbcluster_binlog.cc 2011-09-01 12:36:37 +0000 @@ -53,6 +53,8 @@ bool ndb_log_empty_epochs(void); */ #include "ha_ndbcluster_tables.h" +#include "ndb_dist_priv_util.h" + /* Timeout for syncing schema events between mysql servers, and between mysql server and the binlog @@ -395,6 +397,12 @@ int ndbcluster_binlog_init_share(THD *th strcmp(share->table_name, NDB_APPLY_TABLE) == 0) do_event_op= 1; + if (Ndb_dist_priv_util::is_distributed_priv_table(share->db, + share->table_name)) + { + do_event_op= 0; + } + { int i, no_nodes= g_ndb_cluster_connection->no_db_nodes(); share->subscriber_bitmap= (MY_BITMAP*) @@ -626,6 +634,44 @@ ndbcluster_binlog_index_purge_file(THD * DBUG_RETURN(error); } + +// Determine if privilege tables are distributed, ie. stored in NDB +static bool +priv_tables_are_in_ndb(THD *thd) +{ + bool distributed= false; + Ndb_dist_priv_util dist_priv; + DBUG_ENTER("ndbcluster_distributed_privileges"); + + Ndb *ndb= check_ndb_in_thd(thd); + if (!ndb) + DBUG_RETURN(false); // MAGNUS, error message? + + if (ndb->setDatabaseName(dist_priv.database()) != 0) + DBUG_RETURN(false); + + const char* table_name; + while((table_name= dist_priv.iter_next_table())) + { + DBUG_PRINT("info", ("table_name: %s", table_name)); + Ndb_table_guard ndbtab_g(ndb->getDictionary(), table_name); + const NDBTAB *ndbtab= ndbtab_g.get_table(); + if (ndbtab) + { + distributed= true; + } + else if (distributed) + { + sql_print_error("NDB: Inconsistency detected in distributed " + "privilege tables. Table '%s.%s' is not distributed", + dist_priv.database(), table_name); + DBUG_RETURN(false); + } + } + DBUG_RETURN(distributed); +} + + static void ndbcluster_binlog_log_query(handlerton *hton, THD *thd, enum_binlog_command binlog_command, const char *query, uint query_length, @@ -676,6 +722,46 @@ ndbcluster_binlog_log_query(handlerton * type= SOT_DROP_DB; DBUG_ASSERT(FALSE); break; + case LOGCOM_CREATE_USER: + type= SOT_CREATE_USER; + if (priv_tables_are_in_ndb(thd)) + { + DBUG_PRINT("info", ("Privilege tables have been distributed, logging statement")); + log= 1; + } + break; + case LOGCOM_DROP_USER: + type= SOT_DROP_USER; + if (priv_tables_are_in_ndb(thd)) + { + DBUG_PRINT("info", ("Privilege tables have been distributed, logging statement")); + log= 1; + } + break; + case LOGCOM_RENAME_USER: + type= SOT_RENAME_USER; + if (priv_tables_are_in_ndb(thd)) + { + DBUG_PRINT("info", ("Privilege tables have been distributed, logging statement")); + log= 1; + } + break; + case LOGCOM_GRANT: + type= SOT_GRANT; + if (priv_tables_are_in_ndb(thd)) + { + DBUG_PRINT("info", ("Privilege tables have been distributed, logging statement")); + log= 1; + } + break; + case LOGCOM_REVOKE: + type= SOT_REVOKE; + if (priv_tables_are_in_ndb(thd)) + { + DBUG_PRINT("info", ("Privilege tables have been distributed, logging statement")); + log= 1; + } + break; } if (log) { @@ -1681,6 +1767,16 @@ get_schema_type_name(uint type) return "ONLINE_ALTER_TABLE_PREPARE"; case SOT_ONLINE_ALTER_TABLE_COMMIT: return "ONLINE_ALTER_TABLE_COMMIT"; + case SOT_CREATE_USER: + return "CREATE_USER"; + case SOT_DROP_USER: + return "DROP_USER"; + case SOT_RENAME_USER: + return "RENAME_USER"; + case SOT_GRANT: + return "GRANT"; + case SOT_REVOKE: + return "REVOKE"; } return ""; } @@ -1782,6 +1878,21 @@ int ndbcluster_log_schema_op(THD *thd, case SOT_TRUNCATE_TABLE: type_str= "truncate table"; break; + case SOT_CREATE_USER: + type_str= "create user"; + break; + case SOT_DROP_USER: + type_str= "drop user"; + break; + case SOT_RENAME_USER: + type_str= "rename user"; + break; + case SOT_GRANT: + type_str= "grant/revoke"; + break; + case SOT_REVOKE: + type_str= "revoke all"; + break; default: abort(); /* should not happen, programming error */ } @@ -2515,6 +2626,26 @@ ndb_binlog_thread_handle_schema_event(TH log_query= 1; break; } + case SOT_CREATE_USER: + case SOT_DROP_USER: + case SOT_RENAME_USER: + case SOT_GRANT: + case SOT_REVOKE: + { + if (opt_ndb_extra_logging > 9) + sql_print_information("Got dist_priv event: %s, " + "flushing privileges", + get_schema_type_name(schema_type)); + + thd_ndb_options.set(TNO_NO_LOCK_SCHEMA_OP); + const int no_print_error[1]= {0}; + char *cmd= (char *) "flush privileges"; + run_query(thd, cmd, + cmd + strlen(cmd), + no_print_error); + log_query= 1; + break; + } case SOT_TABLESPACE: case SOT_LOGFILE_GROUP: log_query= 1; @@ -2825,7 +2956,9 @@ ndb_binlog_thread_handle_schema_event_po } thd_ndb_options.set(TNO_NO_LOCK_SCHEMA_OP); - if (ndbcluster_check_if_local_table(schema->db, schema->name)) + if (ndbcluster_check_if_local_table(schema->db, schema->name) && + !Ndb_dist_priv_util::is_distributed_priv_table(schema->db, + schema->name)) { sql_print_error("NDB Binlog: Skipping locally defined table '%s.%s' " "from binlog schema event '%s' from node %d.", @@ -4714,6 +4847,15 @@ int ndbcluster_create_binlog_setup(THD * DBUG_RETURN(0); // replication already setup, or should not } + if (Ndb_dist_priv_util::is_distributed_priv_table(db, table_name)) + { + // The distributed privilege tables are distributed by writing + // the CREATE USER, GRANT, REVOKE etc. to ndb_schema -> no need + // to listen to events from this table + DBUG_PRINT("info", ("Skipping binlogging of table %s/%s", db, table_name)); + do_event_op= 0; + } + if (!ndb_schema_share && strcmp(share->db, NDB_REP_DB) == 0 && strcmp(share->table_name, NDB_SCHEMA_TABLE) == 0) @@ -5024,6 +5166,11 @@ ndbcluster_create_event_ops(THD *thd, ND DBUG_RETURN(0); } + // Don't allow event ops to be created on distributed priv tables + // they are distributed via ndb_schema + assert(!Ndb_dist_priv_util::is_distributed_priv_table(share->db, + share->table_name)); + Ndb_event_data *event_data= share->event_data; int do_ndb_schema_share= 0, do_ndb_apply_status_share= 0; #ifdef HAVE_NDB_BINLOG === modified file 'sql/ha_ndbcluster_binlog.h' --- a/sql/ha_ndbcluster_binlog.h 2011-08-31 10:39:08 +0000 +++ b/sql/ha_ndbcluster_binlog.h 2011-09-01 12:36:37 +0000 @@ -82,7 +82,12 @@ enum SCHEMA_OP_TYPE SOT_TRUNCATE_TABLE= 11, SOT_RENAME_TABLE_PREPARE= 12, SOT_ONLINE_ALTER_TABLE_PREPARE= 13, - SOT_ONLINE_ALTER_TABLE_COMMIT= 14 + SOT_ONLINE_ALTER_TABLE_COMMIT= 14, + SOT_CREATE_USER= 15, + SOT_DROP_USER= 16, + SOT_RENAME_USER= 17, + SOT_GRANT= 18, + SOT_REVOKE= 19 }; const uint max_ndb_nodes= 256; /* multiple of 32 */ === modified file 'sql/handler.h' --- a/sql/handler.h 2011-08-31 10:39:08 +0000 +++ b/sql/handler.h 2011-09-01 12:10:36 +0000 @@ -431,6 +431,13 @@ enum enum_binlog_command { LOGCOM_CREATE_DB, LOGCOM_ALTER_DB, LOGCOM_DROP_DB +#ifndef MCP_WL6004_DISTRIBUTION + ,LOGCOM_CREATE_USER + ,LOGCOM_DROP_USER + ,LOGCOM_RENAME_USER + ,LOGCOM_GRANT + ,LOGCOM_REVOKE +#endif }; /* struct to hold information about the table that should be created */ === added file 'sql/ndb_dist_priv_util.h' --- a/sql/ndb_dist_priv_util.h 1970-01-01 00:00:00 +0000 +++ b/sql/ndb_dist_priv_util.h 2011-09-01 12:36:37 +0000 @@ -0,0 +1,71 @@ +/* + Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA +*/ + +#ifndef NDB_DIST_PRIV_UTIL_H +#define NDB_DIST_PRIV_UTIL_H + +class Ndb_dist_priv_util { + size_t m_iter_curr_table; +public: + Ndb_dist_priv_util() + { + iter_reset(); + } + + const char* database() const { return "mysql"; } + + // Iterator for distributed priv tables name + const char* iter_next_table() + { + static const char* tables[] = + { "user", "db", "tables_priv", "columns_priv", "procs_priv", "host", + "proxies_priv" }; + + if (m_iter_curr_table >= (sizeof(tables) / sizeof(tables[0]))) + return NULL; + m_iter_curr_table++; + return tables[m_iter_curr_table-1]; + } + + // Reset iterator to start at first table name + void iter_reset() { m_iter_curr_table = 0; } + + // Determine if a given table name is in the list + // of distributed priv tables + static + bool + is_distributed_priv_table(const char *db, const char *table) + { + Ndb_dist_priv_util dist_priv; + if (strcmp(db, dist_priv.database())) + { + return false; // Ignore tables not in dist_priv database + } + const char* priv_table_name; + while((priv_table_name= dist_priv.iter_next_table())) + { + if (strcmp(table, priv_table_name) == 0) + { + return true; + } + } + return false; + } + +}; + +#endif === modified file 'sql/sql_acl.cc' --- a/sql/sql_acl.cc 2011-08-31 10:39:08 +0000 +++ b/sql/sql_acl.cc 2011-09-01 12:10:36 +0000 @@ -50,6 +50,10 @@ #include "hostname.h" #include "sql_db.h" +#ifndef MCP_WL6004_DISTRIBUTION +#include "handler.h" +#endif + bool mysql_user_table_is_in_short_password_format= false; static const @@ -1183,6 +1187,14 @@ my_bool acl_reload(THD *thd) if (old_initialized) mysql_mutex_unlock(&acl_cache->lock); end: +#ifndef MCP_WL6004_TRANS + if (!thd->transaction.stmt.is_empty()) + { + DBUG_PRINT("info", ("%u: Committing read transaction", __LINE__)); + trans_commit_stmt(thd); + assert(thd->transaction.stmt.is_empty()); + } +#endif close_mysql_tables(thd); DBUG_RETURN(return_val); } @@ -1834,6 +1846,9 @@ bool change_password(THD *thd, const cha bool save_binlog_row_based; uint new_password_len= (uint) strlen(new_password); bool result= 1; +#ifndef MCP_WL6004_DISTRIBUTION + bool do_distribute= 0; +#endif DBUG_ENTER("change_password"); DBUG_PRINT("enter",("host: '%s' user: '%s' new_password: '%s'", host,user,new_password)); @@ -1905,7 +1920,47 @@ bool change_password(THD *thd, const cha acl_cache->clear(1); // Clear locked hostname cache mysql_mutex_unlock(&acl_cache->lock); result= 0; - if (mysql_bin_log.is_open()) + +#ifndef MCP_WL6004_TRANS + if (!thd->transaction.stmt.is_empty()) + { + if (!result) + { + int err= trans_commit_stmt(thd); + if (err == 0) + { + DBUG_PRINT("info", ("%u: Commit DDL transaction ok", __LINE__)); + } + else + { + result= 1; + do_distribute= 0; + DBUG_PRINT("info", ("%u: Commit DDL transaction failed: %d", + __LINE__, err)); + } + } + else + { + DBUG_PRINT("info", ("%u: Aborting DDL transaction", __LINE__)); + do_distribute= 0; + trans_rollback_stmt(thd); + } + assert(thd->transaction.stmt.is_empty()); + } +#endif + +#ifndef MCP_WL6004_DISTRIBUTION + if (result) + { + do_distribute= 0; + } + else + { + do_distribute= 1; + } +#endif + + if (!result && mysql_bin_log.is_open()) { query_length= sprintf(buff, "SET PASSWORD FOR '%-.120s'@'%-.120s'='%-.120s'", acl_user->user ? acl_user->user : "", @@ -1916,7 +1971,35 @@ bool change_password(THD *thd, const cha FALSE, FALSE, FALSE, 0); } end: - close_mysql_tables(thd); + +#ifndef MCP_WL6004_TRANS + if (!thd->transaction.stmt.is_empty()) + { + /** + * We get here if something went wrong, + * check that result != 0 + */ + assert(result != 0); + DBUG_PRINT("info", ("%u: Aborting DDL transaction", __LINE__)); + trans_rollback_stmt(thd); + assert(thd->transaction.stmt.is_empty()); + } +#endif + +#ifndef MCP_WL6004_DISTRIBUTION + if (do_distribute) + { + query_length= sprintf(buff, "SET PASSWORD FOR '%-.120s'@'%-.120s'='%-.120s'", + acl_user->user ? acl_user->user : "", + acl_user->host.hostname ? acl_user->host.hostname : "", + new_password); + ha_binlog_log_query(thd, 0, LOGCOM_GRANT, + buff, query_length, + "mysql", ""); + } +#endif + + close_thread_tables(thd); /* Restore the state of binlog format */ DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row()); @@ -3758,6 +3841,36 @@ int mysql_table_grant(THD *thd, TABLE_LI thd->mem_root= old_root; mysql_mutex_unlock(&acl_cache->lock); +#ifndef MCP_WL6004_TRANS + if (!thd->transaction.stmt.is_empty()) + { + if (!result) + { + int err= trans_commit_stmt(thd); + if (err == 0) + { + DBUG_PRINT("info", ("%u: Commit DDL transaction ok", __LINE__)); + } + else + { + result= 1; + DBUG_PRINT("info", ("%u: Commit DDL transaction failed: %d", + __LINE__, err)); + } + } + else + { + DBUG_PRINT("info", ("%u: Aborting DDL transaction", __LINE__)); + trans_rollback_stmt(thd); + } + assert(thd->transaction.stmt.is_empty()); + } +#endif + +#ifndef MCP_WL6004_DISTRIBUTION + bool do_distribute= !result; +#endif + if (!result) /* success */ { result= write_bin_log(thd, TRUE, thd->query(), thd->query_length()); @@ -3765,6 +3878,17 @@ int mysql_table_grant(THD *thd, TABLE_LI mysql_rwlock_unlock(&LOCK_grant); +#ifndef MCP_WL6004_DISTRIBUTION + if (do_distribute) + { + 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:((lex_db)?lex_db:"mysql"), ""); + } +#endif + if (!result) /* success */ my_ok(thd); @@ -4094,6 +4218,36 @@ bool mysql_grant(THD *thd, const char *d } mysql_mutex_unlock(&acl_cache->lock); +#ifndef MCP_WL6004_TRANS + if (!thd->transaction.stmt.is_empty()) + { + if (!result) + { + int err= trans_commit_stmt(thd); + if (err == 0) + { + DBUG_PRINT("info", ("%u: Commit DDL transaction ok", __LINE__)); + } + else + { + result= 1; + DBUG_PRINT("info", ("%u: Commit DDL transaction failed: %d", + __LINE__, err)); + } + } + else + { + DBUG_PRINT("info", ("%u: Aborting DDL transaction", __LINE__)); + trans_rollback_stmt(thd); + } + assert(thd->transaction.stmt.is_empty()); + } +#endif + +#ifndef MCP_WL6004_DISTRIBUTION + bool do_distribute= !result; +#endif + if (!result) { result= write_bin_log(thd, TRUE, thd->query(), thd->query_length()); @@ -4101,6 +4255,16 @@ bool mysql_grant(THD *thd, const char *d mysql_rwlock_unlock(&LOCK_grant); +#ifndef MCP_WL6004_DISTRIBUTION + if (do_distribute) + { + char *lex_db= thd->lex->select_lex.db; + ha_binlog_log_query(thd, 0, LOGCOM_GRANT, + thd->query(), thd->query_length(), + (db)?db:((lex_db)?lex_db:"mysql"), ""); + } +#endif + if (!result) my_ok(thd); /* Restore the state of binlog format */ @@ -4382,6 +4546,14 @@ static my_bool grant_reload_procs_priv(T } mysql_rwlock_unlock(&LOCK_grant); +#ifndef MCP_WL6004_TRANS + if (!thd->transaction.stmt.is_empty()) + { + DBUG_PRINT("info", ("%u: Committing read transaction", __LINE__)); + trans_commit_stmt(thd); + assert(thd->transaction.stmt.is_empty()); + } +#endif close_mysql_tables(thd); DBUG_RETURN(return_val); } @@ -4453,6 +4625,14 @@ my_bool grant_reload(THD *thd) free_root(&old_mem,MYF(0)); } mysql_rwlock_unlock(&LOCK_grant); +#ifndef MCP_WL6004_TRANS + if (!thd->transaction.stmt.is_empty()) + { + DBUG_PRINT("info", ("%u: Committing read transaction", __LINE__)); + trans_commit_stmt(thd); + assert(thd->transaction.stmt.is_empty()); + } +#endif close_mysql_tables(thd); /* @@ -6460,10 +6640,39 @@ bool mysql_create_user(THD *thd, List transaction.stmt.is_empty()) + { + int err= trans_commit_stmt(thd); + if (err == 0) + { + DBUG_PRINT("info", ("%u: Commit DDL ok", __LINE__)); + } + else + { + result= 1; + some_users_created= FALSE; + DBUG_PRINT("info", + ("%u: Commit DDL transaction failed: %d", __LINE__, err)); + } + assert(thd->transaction.stmt.is_empty()); + } +#endif + if (some_users_created) result |= write_bin_log(thd, FALSE, thd->query(), thd->query_length()); mysql_rwlock_unlock(&LOCK_grant); + +#ifndef MCP_WL6004_DISTRIBUTION + if (some_users_created) + { + ha_binlog_log_query(thd, 0, LOGCOM_CREATE_USER, + thd->query(), thd->query_length(), + "mysql", ""); + } +#endif + /* Restore the state of binlog format */ DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row()); if (save_binlog_row_based) @@ -6544,10 +6753,47 @@ bool mysql_drop_user(THD *thd, List transaction.stmt.is_empty()) + { + if (!result) + { + int err= trans_commit_stmt(thd); + if (err == 0) + { + DBUG_PRINT("info", ("%u: Commit DDL transaction ok", __LINE__)); + } + else + { + result= 1; + some_users_deleted= FALSE; + DBUG_PRINT("info", ("%u: Commit DDL transaction failed: %d", + __LINE__, err)); + } + } + else + { + DBUG_PRINT("info", ("%u: Aborting DDL transaction", __LINE__)); + trans_rollback_stmt(thd); + } + assert(thd->transaction.stmt.is_empty()); + } +#endif + if (some_users_deleted) result |= write_bin_log(thd, FALSE, thd->query(), thd->query_length()); mysql_rwlock_unlock(&LOCK_grant); + +#ifndef MCP_WL6004_DISTRIBUTION + if (some_users_deleted) + { + ha_binlog_log_query(thd, 0, LOGCOM_DROP_USER, + thd->query(), thd->query_length(), + "mysql", ""); + } +#endif + thd->variables.sql_mode= old_sql_mode; /* Restore the state of binlog format */ DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row()); @@ -6640,10 +6886,48 @@ bool mysql_rename_user(THD *thd, List transaction.stmt.is_empty()) + { + if (!result) + { + int err= trans_commit_stmt(thd); + if (err == 0) + { + DBUG_PRINT("info", ("%u: Commit DDL transaction ok", __LINE__)); + } + else + { + result= 1; + some_users_renamed= FALSE; + DBUG_PRINT("info", ("%u: Commit DDL transaction failed: %d", + __LINE__, err)); + } + } + else + { + DBUG_PRINT("info", ("%u: Aborting DDL transaction", __LINE__)); + trans_rollback_stmt(thd); + some_users_renamed= FALSE; + } + assert(thd->transaction.stmt.is_empty()); + } +#endif + if (some_users_renamed && mysql_bin_log.is_open()) result |= write_bin_log(thd, FALSE, thd->query(), thd->query_length()); mysql_rwlock_unlock(&LOCK_grant); + +#ifndef MCP_WL6004_DISTRIBUTION + if (some_users_renamed) + { + ha_binlog_log_query(thd, 0, LOGCOM_RENAME_USER, + thd->query(), thd->query_length(), + "mysql", ""); + } +#endif + /* Restore the state of binlog format */ DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row()); if (save_binlog_row_based) @@ -6671,6 +6955,10 @@ bool mysql_revoke_all(THD *thd, List db; +#endif continue; } result= -1; // Something went wrong @@ -6837,10 +7128,47 @@ bool mysql_revoke_all(THD *thd, List transaction.stmt.is_empty()) + { + if (!result) + { + int err= trans_commit_stmt(thd); + if (err == 0) + { + DBUG_PRINT("info", ("%u: Commit DDL transaction ok", __LINE__)); + } + else + { + result= 1; + DBUG_PRINT("info", ("%u: Commit DDL transaction failed: %d", + __LINE__, err)); + } + } + else + { + DBUG_PRINT("info", ("%u: Aborting DDL transaction", __LINE__)); + trans_rollback_stmt(thd); + } + assert(thd->transaction.stmt.is_empty()); + } +#endif + result= result | write_bin_log(thd, FALSE, thd->query(), thd->query_length()); mysql_rwlock_unlock(&LOCK_grant); + +#ifndef MCP_WL6004_DISTRIBUTION + if (true) // statment is always written to binlog?? + { + char *lex_db= thd->lex->select_lex.db; + ha_binlog_log_query(thd, 0, LOGCOM_REVOKE, + thd->query(), thd->query_length(), + (db)?db:((lex_db)?lex_db:"mysql"), ""); + } +#endif + /* Restore the state of binlog format */ DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row()); if (save_binlog_row_based) === added file 'storage/ndb/tools/HOWTO_distribute_privileges.txt' --- a/storage/ndb/tools/HOWTO_distribute_privileges.txt 1970-01-01 00:00:00 +0000 +++ b/storage/ndb/tools/HOWTO_distribute_privileges.txt 2011-09-01 12:36:37 +0000 @@ -0,0 +1,72 @@ +HOWTO distribute user privileges in a MySQL Cluster. + +This text is a short description how to distribute MySQL +privilege tables to all connected MySQL servers belonging to +a MySQL Cluster. This enables adding and removing users to +the system as well as defining grants for various privileges +in a more or less transparent manner. Flushing of grants +might be needed to make changes visible on already opened +connections. + +The procedure is based on moving the privilege tables from local +storage (MyISAM) to distributed storage (ndbcluster). To +simplify the procedure a number of store procedures have been +defined to more or less automate the procedure. +The procedures can be created by running the script ndb_dist_priv.sql +(which you find in storage/ndb/tools/ or share/mysql/ depending +on your distribution of the mysql files) through "mysql" +Example: +"mysql --user root < storage/ndb/tool/ndb_dist_priv.sql" + +IMPORTANT: Prior to performing the actual distribution it is recommended +to backup the current privilege tables, using for example mysqldump: +"mysqldump --user root mysql user db tables_priv columns_priv procs_priv" + +The actual procedure can be initiated by running the stored procedure +mysql_cluster_move_privilege_tables once on any connected MySQL server: +"call mysql.mysql_cluster_move_privileges();" + +NOTE: This procedure will create two sets of backups of the privilege tables +as well, one local and one distributed. These are used to revert the tables +if anything goes wrong during the distribution process. + +To verify that the procedure was successful one can do the following query: +"select mysql.mysql_cluster_privileges_are_distributed();" +which should return 1 if procedure was successful. + +NOTE: If a MySQL server is disconnected from the cluster while the privilege +tables are being moved the local privilege tables will need to be dropped and +refreshed when the MySQL server has reconnected: +use mysql; +select mysql_cluster_privileges_are_distributed(); +0 +drop table if exists user db tables_priv columns_priv procs_priv; +show tables; +select mysql_cluster_privileges_are_distributed(); +1 + +In case cluster being restarted with resetting of all tables (--inital) +the privilege tables will be lost after the they have been moved. If no +MySQL server is available to revert the tables (either from the local backup +tables or from a mysqldump file). If a new MYSQL server needs to be connected +to the cluster to perform restoring the privilege tables it will need to be +started with the flag --skip-grant-tables. After the local tables have been +restored they can be distributed again by again calling: +"call mysql.mysql_cluster_move_privileges();" +After that the MySQL server should be restarted without the --skip-grant-tables +flag. + +Once privilige tables have been stored in ndb they will be part of +ndb backups. However, if one want to restore these tables from a backup +one has to give an new option flag --restore-privilege-tables to ndb_restore. + +Known limitations: +All changes to privilege tables are automatically distributed when the +privilege tables have been moved to ndb. However, since privilege data +is cached per connection, a remote change will not be vissible until one +do a: +"flush privileges;" + +If for some reason want to revert distribution of privilege tables back +to local tables this has to be done on each mysqld by running: +"call mysql.mysql_cluster_restore_local_privileges();" === added file 'storage/ndb/tools/ndb_dist_priv.sql' --- a/storage/ndb/tools/ndb_dist_priv.sql 1970-01-01 00:00:00 +0000 +++ b/storage/ndb/tools/ndb_dist_priv.sql 2011-09-01 12:36:37 +0000 @@ -0,0 +1,313 @@ +-- Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. +-- +-- This program is free software; you can redistribute it and/or modify +-- it under the terms of the GNU General Public License as published by +-- the Free Software Foundation; version 2 of the License. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program; if not, write to the Free Software +-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +delimiter | + +drop function if exists mysql.mysql_cluster_privileges_are_distributed| +drop procedure if exists mysql.mysql_cluster_backup_privileges| +drop procedure if exists mysql.mysql_cluster_move_grant_tables| +drop procedure if exists mysql.mysql_cluster_restore_privileges_from_local| +drop procedure if exists mysql.mysql_cluster_restore_privileges| +drop procedure if exists mysql.mysql_cluster_restore_local_privileges| +drop procedure if exists mysql.mysql_cluster_move_privileges| + +create function mysql.mysql_cluster_privileges_are_distributed() +returns bool +reads sql data +begin + declare distributed_user bool default 0; + declare distributed_db bool default 0; + declare distributed_tables_priv bool default 0; + declare distributed_columns_priv bool default 0; + declare distributed_procs_priv bool default 0; + declare distributed_host bool default 0; + declare distributed_proxies_priv bool default 0; + + select IF(COUNT(engine) > 0, engine = 'ndbcluster', 0) + into distributed_user + from information_schema.tables + where table_schema = "mysql" and table_name = "user"; + select IF(COUNT(engine) > 0, engine = 'ndbcluster', 0) + into distributed_db + from information_schema.tables + where table_schema = "mysql" and table_name = "db"; + select IF(COUNT(engine) > 0, engine = 'ndbcluster', 0) + into distributed_tables_priv + from information_schema.tables + where table_schema = "mysql" and table_name = "tables_priv"; + select IF(COUNT(engine) > 0, engine = 'ndbcluster', 0) + into distributed_columns_priv + from information_schema.tables + where table_schema = "mysql" and table_name = "columns_priv"; + select IF(COUNT(engine) > 0, engine = 'ndbcluster', 0) + into distributed_procs_priv + from information_schema.tables + where table_schema = "mysql" and table_name = "procs_priv"; + select IF(COUNT(engine) > 0, engine = 'ndbcluster', 0) + into distributed_host + from information_schema.tables + where table_schema = "mysql" and table_name = "host"; + select IF(COUNT(engine) > 0, engine = 'ndbcluster', 0) + into distributed_proxies_priv + from information_schema.tables + where table_schema = "mysql" and table_name = "proxies_priv"; + + if distributed_user = 1 and + distributed_db = 1 and + distributed_tables_priv = 1 and + distributed_columns_priv = 1 and + distributed_procs_priv = 1 and + distributed_host = 1 and + distributed_proxies_priv = 1 + then + return 1; + else + return 0; + end if; +end| + +create procedure mysql.mysql_cluster_backup_privileges() +begin + declare distributed_privileges bool default 0; + declare first_backup bool default 1; + declare first_distributed_backup bool default 1; + select mysql_cluster_privileges_are_distributed() + into distributed_privileges; + select 0 into first_backup + from information_schema.tables + where table_schema = "mysql" and table_name = "user_backup"; + select 0 into first_distributed_backup + from information_schema.tables + where table_schema = "mysql" and table_name = "ndb_user_backup"; + if first_backup = 1 then + create table if not exists mysql.user_backup + like mysql.user; + create table if not exists mysql.db_backup + like mysql.db; + create table if not exists mysql.tables_priv_backup + like mysql.tables_priv; + create table if not exists mysql.columns_priv_backup + like mysql.columns_priv; + create table if not exists mysql.procs_priv_backup + like mysql.procs_priv; + create table if not exists mysql.host_backup + like mysql.host; + create table if not exists mysql.proxies_priv_backup + like mysql.proxies_priv; + if distributed_privileges = 1 then + alter table mysql.user_backup engine = myisam; + alter table mysql.db_backup engine = myisam; + alter table mysql.tables_priv_backup engine = myisam; + alter table mysql.columns_priv_backup engine = myisam; + alter table mysql.procs_priv_backup engine = myisam; + alter table mysql.host_backup engine = myisam; + alter table mysql.proxies_priv_backup engine = myisam; + end if; + else + truncate mysql.user_backup; + truncate mysql.db_backup; + truncate mysql.tables_priv_backup; + truncate mysql.columns_priv_backup; + truncate mysql.procs_priv_backup; + truncate mysql.host_backup; + truncate mysql.proxies_priv_backup; + end if; + if first_distributed_backup = 1 then + create table if not exists mysql.ndb_user_backup + like mysql.user; + create table if not exists mysql.ndb_db_backup + like mysql.db; + create table if not exists mysql.ndb_tables_priv_backup + like mysql.tables_priv; + create table if not exists mysql.ndb_columns_priv_backup + like mysql.columns_priv; + create table if not exists mysql.ndb_procs_priv_backup + like mysql.procs_priv; + create table if not exists mysql.ndb_host_backup + like mysql.host; + create table if not exists mysql.ndb_proxies_priv_backup + like mysql.proxies_priv; + + if distributed_privileges = 0 then + alter table mysql.ndb_user_backup engine = ndbcluster; + alter table mysql.ndb_db_backup engine = ndbcluster; + alter table mysql.ndb_tables_priv_backup engine = ndbcluster; + alter table mysql.ndb_columns_priv_backup engine = ndbcluster; + alter table mysql.ndb_procs_priv_backup engine = ndbcluster; + alter table mysql.ndb_host_backup engine = ndbcluster; + alter table mysql.ndb_proxies_priv_backup engine = ndbcluster; + end if; + else + truncate mysql.ndb_user_backup; + truncate mysql.ndb_db_backup; + truncate mysql.ndb_tables_priv_backup; + truncate mysql.ndb_columns_priv_backup; + truncate mysql.ndb_procs_priv_backup; + truncate mysql.ndb_host_backup; + truncate mysql.ndb_proxies_priv_backup; + end if; + insert into mysql.user_backup select * from mysql.user; + insert into mysql.db_backup select * from mysql.db; + insert into mysql.tables_priv_backup select * from mysql.tables_priv; + insert into mysql.columns_priv_backup select * from mysql.columns_priv; + insert into mysql.procs_priv_backup select * from mysql.procs_priv; + insert into mysql.host_backup select * from mysql.host; + insert into mysql.proxies_priv_backup select * from mysql.proxies_priv; + + insert into mysql.ndb_user_backup select * from mysql.user; + insert into mysql.ndb_db_backup select * from mysql.db; + insert into mysql.ndb_tables_priv_backup select * from mysql.tables_priv; + insert into mysql.ndb_columns_priv_backup select * from mysql.columns_priv; + insert into mysql.ndb_procs_priv_backup select * from mysql.procs_priv; + insert into mysql.ndb_host_backup select * from mysql.host; + insert into mysql.ndb_proxies_priv_backup select * from mysql.proxies_priv; +end| + +create procedure mysql.mysql_cluster_restore_privileges_from_local() +begin + declare local_backup bool default 0; + select 1 into local_backup + from information_schema.tables + where table_schema = "mysql" and table_name = "user_backup"; + if local_backup = 1 then + create table if not exists mysql.user + like mysql.user_backup; + create table if not exists mysql.db + like mysql.db_backup; + create table if not exists mysql.tables_priv + like mysql.tables_priv_backup; + create table if not exists mysql.columns_priv + like mysql.columns_priv_backup; + create table if not exists mysql.procs_priv + like mysql.procs_priv_backup; + create table if not exists mysql.host + like mysql.host_backup; + create table if not exists mysql.proxies_priv + like mysql.proxies_priv_backup; + delete from mysql.user; + insert into mysql.user select * from mysql.user_backup; + delete from mysql.db; + insert into mysql.db select * from mysql.db_backup; + delete from mysql.tables_priv; + insert into mysql.tables_priv select * from mysql.tables_priv_backup; + delete from mysql.columns_priv; + insert into mysql.columns_priv select * from mysql.columns_priv_backup; + delete from mysql.procs_priv; + insert into mysql.procs_priv select * from mysql.procs_priv_backup; + delete from mysql.host; + insert into mysql.host select * from mysql.host_backup; + delete from mysql.proxies_priv; + insert into mysql.proxies_priv select * from mysql.proxies_priv_backup; + end if; +end| + +create procedure mysql.mysql_cluster_restore_privileges() +begin + declare distributed_backup bool default 0; + select 1 into distributed_backup + from information_schema.tables + where table_schema = "mysql" and table_name = "ndb_user_backup"; + if distributed_backup = 1 then + flush tables; + create table if not exists mysql.user + like mysql.ndb_user_backup; + create table if not exists mysql.db + like mysql.ndb_db_backup; + create table if not exists mysql.tables_priv + like mysql.ndb_tables_priv_backup; + create table if not exists mysql.columns_priv + like mysql.ndb_columns_priv_backup; + create table if not exists mysql.procs_priv + like mysql.ndb_procs_priv_backup; + create table if not exists mysql.host + like mysql.ndb_host_backup; + create table if not exists mysql.proxies_priv + like mysql.ndb_proxies_priv_backup; + delete from mysql.user; + insert into mysql.user + select * from mysql.ndb_user_backup; + delete from mysql.db; + insert into mysql.db + select * from mysql.ndb_db_backup; + delete from mysql.tables_priv; + insert into mysql.tables_priv + select * from mysql.ndb_tables_priv_backup; + delete from mysql.columns_priv; + insert into mysql.columns_priv + select * from mysql.ndb_columns_priv_backup; + delete from mysql.procs_priv; + insert into mysql.procs_priv + select * from mysql.ndb_procs_priv_backup; + delete from mysql.host; + insert into mysql.host + select * from mysql.ndb_host_backup; + delete from mysql.proxies_priv; + insert into mysql.proxies_priv + select * from mysql.ndb_proxies_priv_backup; + else + call mysql_cluster_restore_privileges_from_local(); + end if; +end| + +create procedure mysql.mysql_cluster_restore_local_privileges() +begin + declare distributed_privileges bool default 0; + select mysql.mysql_cluster_privileges_are_distributed() + into distributed_privileges; + if distributed_privileges = 1 then + begin + drop table mysql.user; + drop table mysql.db; + drop table mysql.tables_priv; + drop table mysql.columns_priv; + drop table mysql.procs_priv; + drop table mysql.host; + drop table mysql.proxies_priv; + end; + end if; + call mysql_cluster_restore_privileges_from_local(); +end| + +create procedure mysql.mysql_cluster_move_grant_tables() +begin + declare distributed_privileges bool default 0; + declare revert bool default 0; + select mysql.mysql_cluster_privileges_are_distributed() + into distributed_privileges; + if distributed_privileges = 0 then + begin + declare exit handler for sqlexception set revert = 1; + alter table mysql.user engine = ndb; + alter table mysql.db engine = ndb; + alter table mysql.tables_priv engine = ndb; + alter table mysql.columns_priv engine = ndb; + alter table mysql.procs_priv engine = ndb; + alter table mysql.host engine = ndb; + alter table mysql.proxies_priv engine = ndb; + end; + end if; + if revert = 1 then + call mysql_cluster_restore_privileges(); + end if; +end| + +create procedure mysql.mysql_cluster_move_privileges() +begin + call mysql_cluster_backup_privileges(); + call mysql_cluster_move_grant_tables(); +end| + +delimiter ; + === modified file 'storage/ndb/tools/restore/restore_main.cpp' --- a/storage/ndb/tools/restore/restore_main.cpp 2011-07-05 12:46:07 +0000 +++ b/storage/ndb/tools/restore/restore_main.cpp 2011-09-01 12:36:37 +0000 @@ -124,6 +124,7 @@ static const char *opt_include_tables= N static const char *opt_exclude_databases= NULL; static const char *opt_include_databases= NULL; static const char *opt_rewrite_database= NULL; +static bool opt_restore_privilege_tables = false; static struct my_option my_long_options[] = { @@ -266,6 +267,11 @@ static struct my_option my_long_options[ "Example: db1.t1,db3.t1", (uchar**) &opt_exclude_tables, (uchar**) &opt_exclude_tables, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0 }, + { "restore-privilege-tables", NDB_OPT_NOSHORT, + "Restore privilege tables (after they have been moved to ndb)", + (uchar**) &opt_restore_privilege_tables, + (uchar**) &opt_restore_privilege_tables, 0, + GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0 }, { "exclude-missing-columns", NDB_OPT_NOSHORT, "Ignore columns present in backup but not in database", (uchar**) &ga_exclude_missing_columns, @@ -495,18 +501,19 @@ processTableList(const char* str, Vector { // Process tables list like db1.t1,db2.t1 and exits when // it finds problems. + Vector tmp; unsigned int i; /* Split passed string on comma into 2 BaseStrings in the vector */ - BaseString(str).split(lst,","); - for (i=0; i < lst.size(); i++) + BaseString(str).split(tmp,","); + for (i=0; i < tmp.size(); i++) { BaseString internalName; - if (makeInternalTableName(lst[i], internalName)) + if (makeInternalTableName(tmp[i], internalName)) { - info << "`" << lst[i] << "` is not a valid tablename!" << endl; + info << "`" << tmp[i] << "` is not a valid tablename!" << endl; exit(NDBT_ProgramExit(NDBT_WRONGARGS)); } - lst[i].assign(internalName); + lst.push_back(internalName); } } @@ -524,6 +531,24 @@ makeExternalTableName(const BaseString & return externalName; } +#include "../../../../sql/ndb_dist_priv_util.h" + +// Exclude privilege tables unless explicitely included +void +exclude_privilege_tables() +{ + const char* table_name; + Ndb_dist_priv_util dist_priv; + while((table_name= dist_priv.iter_next_table())) + { + BaseString priv_tab; + priv_tab.assfmt("%s.%s", dist_priv.database(), table_name); + g_exclude_tables.push_back(priv_tab); + save_include_exclude(OPT_EXCLUDE_TABLES, (char *)priv_tab.c_str()); + } +} + + bool readArguments(int *pargc, char*** pargv) { @@ -698,7 +723,35 @@ o verify nodegroup mapping if (g_tables.size() > 0) info << endl; } - + + if (ga_restore) + { + // Exclude privilege tables unless explicitely included + if (!opt_restore_privilege_tables) + exclude_privilege_tables(); + + // Move over old style arguments to include/exclude lists + if (g_databases.size() > 0) + { + BaseString tab_prefix, tab; + tab_prefix.append(g_databases[0].c_str()); + tab_prefix.append("."); + if (g_tables.size() == 0) + { + g_include_databases.push_back(g_databases[0]); + save_include_exclude(OPT_INCLUDE_DATABASES, + (char *)g_databases[0].c_str()); + } + for (unsigned i= 0; i < g_tables.size(); i++) + { + tab.assign(tab_prefix); + tab.append(g_tables[i]); + g_include_tables.push_back(tab); + save_include_exclude(OPT_INCLUDE_TABLES, (char *)tab.c_str()); + } + } + } + if (opt_include_databases) { tmp = BaseString(opt_include_databases); @@ -758,7 +811,7 @@ o verify nodegroup mapping } info << endl; } - + /* the below formatting follows the formatting from mysqldump do not change unless to adopt to changes in mysqldump @@ -978,7 +1031,7 @@ static bool check_include_exclude(BaseSt } } } - + return do_include; } @@ -1012,7 +1065,7 @@ checkDbAndTableName(const TableS* table) return false; // If new options are given, ignore the old format - if (opt_include_tables || opt_exclude_tables || + if (opt_include_tables || g_exclude_tables.size() > 0 || opt_include_databases || opt_exclude_databases ) { return (checkDoRestore(table)); } No bundle (reason: useless for push emails).