bk commit into 5.0 tree (msvensson:1.2497) BUG#31167

[msvensson]

Below is the list of changes that have just been committed into a local
5.0 repository of msvensson. When msvensson does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-09-24 16:58:01+02:00, msvensson@shellback.(none) +1 -0
  Bug#31167 Introduction of @@hostname breaks replication in a ring
   - Avoid using a temporary table as an "if mysql.user" already existed

  scripts/mysql_system_tables_data.sql@stripped, 2007-09-24 16:57:53+02:00, msvensson@shellback.(none) +10 -6
    Don't use a temporary table to store the records that
    maybe should be added to mysql.user. Since a temp table would
    be replicated to a slave the @@hostname might be run on a
    server that might not yet have been upgrade.
    Add a "dummy" table in order to be able to use WHERE as "if" in order
    to not insert any record into mysql.user if it already existed before
    the upgrade

diff -Nrup a/scripts/mysql_system_tables_data.sql b/scripts/mysql_system_tables_data.sql
--- a/scripts/mysql_system_tables_data.sql	2007-04-26 16:31:19 +02:00
+++ b/scripts/mysql_system_tables_data.sql	2007-09-24 16:57:53 +02:00
@@ -13,9 +13,13 @@ DROP TABLE tmp_db;
 
 -- Fill "users" table with default users allowing root access
 -- 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 (@@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 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 user SELECT * FROM tmp_user WHERE @had_user_table=0;
-DROP TABLE tmp_user;
+--  - use a dummy table to be able to add an "if" on each insert
+--  - use INSERT IGNORE since the @@hostname could potentially
+--    be equal to 'localhost' or '127.0.0.1'
+CREATE TEMPORARY TABLE dummy(a int);
+INSERT dummy VALUES(1);
+INSERT user SELECT '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 FROM dummy WHERE @had_user_table = 0;
+INSERT IGNORE user SELECT @@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 dummy WHERE @had_user_table = 0;
+INSERT IGNORE user SELECT '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 FROM dummy WHERE @had_user_table = 0;
+DROP TABLE dummy;
+