bzr commit into mysql-5.0-bugteam branch (chad:2684) Bug#35754

[Chad MILLER]

#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;