From: Date: September 24 2008 3:01pm Subject: bzr commit into mysql-5.0-bugteam branch (chad:2684) Bug#35754 List-Archive: http://lists.mysql.com/commits/54542 X-Bug: 35754 Message-Id: <20080924130137.8728686FFA6@calliope.chad.cornsilk.net> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit #At file:///Users/cmiller/work/mysqlbzr/5.0-bugteam--bug31167/ 2684 Chad MILLER 2008-09-24 Bug#35754: mysql_install_db does not work if no hostname is set Machines with hostname set to "localhost" cause uniqueness errors in the SQL bootstrap data. Now, insert zero lines for cases where the (lowercased) hostname is the same as an already-inserted 'localhost' name. Also, fix a few tests that expect certain local accounts to have a certain host name. modified: mysql-test/r/join.result mysql-test/r/rpl_grant.result mysql-test/t/join.test mysql-test/t/rpl_grant.test scripts/mysql_system_tables_data.sql === modified file 'mysql-test/r/join.result' --- a/mysql-test/r/join.result 2007-05-04 13:47:58 +0000 +++ b/mysql-test/r/join.result 2008-09-24 12:59:56 +0000 @@ -747,11 +747,13 @@ select t1.b from v1a; ERROR 42S22: Unknown column 't1.b' in 'field list' select * from v1a join v1b on t1.b = t2.b; ERROR 42S22: Unknown column 't1.b' in 'on clause' -select * from information_schema.statistics join information_schema.columns -using(table_name,column_name) where table_name='user'; -TABLE_NAME COLUMN_NAME TABLE_CATALOG TABLE_SCHEMA NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT TABLE_CATALOG TABLE_SCHEMA ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT -user Host NULL mysql 0 mysql PRIMARY 1 A NULL NULL NULL BTREE NULL mysql 1 NO char 60 180 NULL NULL utf8 utf8_bin char(60) PRI # -user User NULL mysql 0 mysql PRIMARY 2 A 3 NULL NULL BTREE NULL mysql 2 NO char 16 48 NULL NULL utf8 utf8_bin char(16) PRI # +select +statistics.TABLE_NAME, statistics.COLUMN_NAME, statistics.TABLE_CATALOG, statistics.TABLE_SCHEMA, statistics.NON_UNIQUE, statistics.INDEX_SCHEMA, statistics.INDEX_NAME, statistics.SEQ_IN_INDEX, statistics.COLLATION, statistics.CARDINALITY, statistics.SUB_PART, statistics.PACKED, statistics.NULLABLE, statistics.INDEX_TYPE, statistics.COMMENT, +columns.TABLE_CATALOG, columns.TABLE_SCHEMA, columns.COLUMN_DEFAULT, columns.IS_NULLABLE, columns.DATA_TYPE, columns.CHARACTER_MAXIMUM_LENGTH, columns.CHARACTER_OCTET_LENGTH, columns.NUMERIC_PRECISION, columns.NUMERIC_SCALE, columns.CHARACTER_SET_NAME, columns.COLLATION_NAME, columns.COLUMN_TYPE, columns.COLUMN_KEY, columns.EXTRA, columns.COLUMN_COMMENT +from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user'; +TABLE_NAME COLUMN_NAME TABLE_CATALOG TABLE_SCHEMA NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT TABLE_CATALOG TABLE_SCHEMA COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA COLUMN_COMMENT +user Host NULL mysql 0 mysql PRIMARY 1 A NULL NULL NULL BTREE NULL mysql NO char 60 180 NULL NULL utf8 utf8_bin char(60) PRI +user User NULL mysql 0 mysql PRIMARY 2 A 2 NULL NULL BTREE NULL mysql NO char 16 48 NULL NULL utf8 utf8_bin char(16) PRI drop table t1; drop table t2; drop table t3; === modified file 'mysql-test/r/rpl_grant.result' --- a/mysql-test/r/rpl_grant.result 2008-01-29 11:21:21 +0000 +++ b/mysql-test/r/rpl_grant.result 2008-09-24 12:59:56 +0000 @@ -12,18 +12,18 @@ user host dummy localhost dummy1 localhost dummy2 localhost -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); COUNT(*) -6 +3 **** On Slave **** SELECT user,host FROM mysql.user WHERE user != 'root'; user host dummy localhost dummy1 localhost dummy2 localhost -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); COUNT(*) -6 +3 **** On Master **** DROP USER nonexisting@localhost; ERROR HY000: Operation DROP USER failed for 'nonexisting'@'localhost' @@ -32,15 +32,15 @@ ERROR HY000: Operation DROP USER failed DROP USER dummy1@localhost, dummy2@localhost; SELECT user, host FROM mysql.user WHERE user != 'root'; user host -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); COUNT(*) -3 +0 **** On Slave **** SELECT user,host FROM mysql.user WHERE user != 'root'; user host -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); COUNT(*) -3 +0 SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 === modified file 'mysql-test/t/join.test' --- a/mysql-test/t/join.test 2007-06-15 17:15:22 +0000 +++ b/mysql-test/t/join.test 2008-09-24 12:59:56 +0000 @@ -546,10 +546,12 @@ select * from v1a join v1b on t1.b = t2. # # Bug #17523 natural join and information_schema # -# We mask out the Privileges column because it differs with embedded server ---replace_column 31 # -select * from information_schema.statistics join information_schema.columns - using(table_name,column_name) where table_name='user'; +# Omit columns.PRIVILIGES as it may vary with embedded server. +# Omit columns.ORDINAL_POSITION as it may vary with hostname='localhost'. +select + statistics.TABLE_NAME, statistics.COLUMN_NAME, statistics.TABLE_CATALOG, statistics.TABLE_SCHEMA, statistics.NON_UNIQUE, statistics.INDEX_SCHEMA, statistics.INDEX_NAME, statistics.SEQ_IN_INDEX, statistics.COLLATION, statistics.CARDINALITY, statistics.SUB_PART, statistics.PACKED, statistics.NULLABLE, statistics.INDEX_TYPE, statistics.COMMENT, + columns.TABLE_CATALOG, columns.TABLE_SCHEMA, columns.COLUMN_DEFAULT, columns.IS_NULLABLE, columns.DATA_TYPE, columns.CHARACTER_MAXIMUM_LENGTH, columns.CHARACTER_OCTET_LENGTH, columns.NUMERIC_PRECISION, columns.NUMERIC_SCALE, columns.CHARACTER_SET_NAME, columns.COLLATION_NAME, columns.COLUMN_TYPE, columns.COLUMN_KEY, columns.EXTRA, columns.COLUMN_COMMENT + from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user'; drop table t1; drop table t2; === modified file 'mysql-test/t/rpl_grant.test' --- a/mysql-test/t/rpl_grant.test 2007-06-21 14:55:52 +0000 +++ b/mysql-test/t/rpl_grant.test 2008-09-24 12:59:56 +0000 @@ -10,11 +10,11 @@ CREATE USER dummy@localhost; CREATE USER dummy1@localhost, dummy2@localhost; SELECT user, host FROM mysql.user WHERE user != 'root'; # root host non-determ -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); sync_slave_with_master; --echo **** On Slave **** SELECT user,host FROM mysql.user WHERE user != 'root'; # root host non-determ -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); --echo **** On Master **** connection master; @@ -31,11 +31,11 @@ DROP USER nonexisting@localhost, dummy@l DROP USER dummy1@localhost, dummy2@localhost; SELECT user, host FROM mysql.user WHERE user != 'root'; # root host non-determ -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); sync_slave_with_master; --echo **** On Slave **** SELECT user,host FROM mysql.user WHERE user != 'root'; # root host non-determ -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # === modified file 'scripts/mysql_system_tables_data.sql' --- a/scripts/mysql_system_tables_data.sql 2007-11-30 05:14:43 +0000 +++ b/scripts/mysql_system_tables_data.sql 2008-09-24 12:59:56 +0000 @@ -21,9 +21,9 @@ DROP TABLE tmp_db; -- from local machine if "users" table didn't exist before CREATE TEMPORARY TABLE tmp_user LIKE user; INSERT INTO tmp_user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); -REPLACE INTO tmp_user VALUES (@current_hostname,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); +REPLACE INTO tmp_user SELECT @current_hostname,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0 FROM dual WHERE LOWER( @current_hostname) != 'localhost'; REPLACE INTO tmp_user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); INSERT INTO tmp_user (host,user) VALUES ('localhost',''); -INSERT INTO tmp_user (host,user) VALUES (@current_hostname,''); +INSERT INTO tmp_user (host,user) SELECT @current_hostname,'' FROM dual WHERE LOWER(@current_hostname ) != 'localhost'; INSERT INTO user SELECT * FROM tmp_user WHERE @had_user_table=0; DROP TABLE tmp_user;