From: Date: October 3 2008 4:20pm Subject: bzr commit into mysql-5.0-bugteam branch (chad:2696) Bug#35754 List-Archive: http://lists.mysql.com/commits/55269 X-Bug: 35754 Message-Id: <20081003142009.CB7F48821C8@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/ 2696 Chad MILLER 2008-10-03 [merge] Merge fix for Bug#35754. 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;