Bug #35754 mysql_install_db does not work if no hostname is set
Submitted: 1 Apr 2008 22:42 Modified: 17 Oct 2008 17:22
Reporter: Matt Lord Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Installing Severity:S2 (Serious)
Version:5.0.58 OS:Any
Assigned to: Chad MILLER
Tags: localhost, mysql_install_db
Triage: Triaged: D3 (Medium) / R1 (None/Negligible) / E1 (None/Negligible)

[1 Apr 2008 22:42] Matt Lord
Description:
mysql_install_db fails if the machine has no hostname or bash# hostname returns
"localhost".

This is because we explicitly install user accounts for "localhost" as well
as creating accounts for whatever /bin/hostname returns.
root@$HOSTNAME, root@127.0.0.1, ''@localhost and ''@$HOSTNAME:

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 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 user SELECT * FROM tmp_user WHERE @had_user_table=0;
DROP TABLE tmp_user;

This causes mysql_install_db to fail with:
Installing MySQL system tables...
ERROR: 1062  Duplicate entry 'localhost-' for key 1

How to repeat:
set /bin/hostname to return "localhost" and install the latest RPM.

Suggested fix:
We should do a check for localhost being the hostname in mysql_install_db:

# When doing a "cross bootstrap" install, no reference to the current
# host should be added to the system tables.  So we filter out any
# lines which contain the current host name.  We also want to filter
# out these lines if no hostname has been set 
if test $cross_bootstrap -eq 1 -a $hostname eq "localhost"
then
  filter_cmd_line="sed -e '/@current_hostname/d'"
else
  filter_cmd_line="cat"
fi
[1 Apr 2008 23:36] Matt Lord
In the how to repeat I stated:
set /bin/hostname to return "localhost" and install the latest RPM.

Of course you can just run mysql_install_db manually from any tar distribution as
well.  This is a bit simpler so I should have stated this originally.
[2 Apr 2008 3:57] Matt Lord
OK, let's try this one more time :).  I apologize, I don't do much bash scripting to speak of so I didn't do the string comparison correctly.  Here's what it should be although I'm sure you already knew:

# When doing a "cross bootstrap" install, no reference to the current
# host should be added to the system tables.  So we filter out any
# lines which contain the current host name.  We also want to filter
# out these lines if no hostname has been set 
if test $cross_bootstrap -eq 1 -o "$hostname" = "localhost"
then
  filter_cmd_line="sed -e '/@current_hostname/d'"
else
  filter_cmd_line="cat"
fi
[23 Sep 2008 8:08] Chad MILLER
I can't reach lists.m.c from Riga network.  Email stuck in local queue.  

cmiller@calliope:~/work/mysqlbzr/5.0-bugteam--bug31167 $ bzr missing --long -v
Using saved parent location: /Users/cmiller/work/mysqlbzr/5.0-bugteam/
You have 1 extra revision(s):
------------------------------------------------------------
revno: 2684
committer: Chad MILLER <chad@mysql.com>
branch nick: 5.0-bugteam--bug31167
timestamp: Tue 2008-09-23 03:42:42 -0400
message:
  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 hostname is the same as an
  already-inserted 'localhost' name.
modified:
  scripts/mysql_system_tables_data.sql
cmiller@calliope:~/work/mysqlbzr/5.0-bugteam--bug31167 $ bzr diff -r-2..
=== modified file 'scripts/mysql_system_tables_data.sql'
--- scripts/mysql_system_tables_data.sql        2007-11-30 05:14:43 +0000
+++ scripts/mysql_system_tables_data.sql        2008-09-23 07:38:13 +0000
@@ -21,9 +21,9 @@
 -- 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 WHERE @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,'' WHERE @current_hostname != 'localhost';
 INSERT INTO user SELECT * FROM tmp_user WHERE @had_user_table=0;
 DROP TABLE tmp_user;
[23 Sep 2008 8:31] Chad MILLER
New change, replacing last:

cmiller@calliope:~/work/mysqlbzr/5.0-bugteam--bug31167 $ bzr diff -r-2..
=== modified file 'scripts/mysql_system_tables_data.sql'
--- scripts/mysql_system_tables_data.sql        2007-11-30 05:14:43 +0000
+++ scripts/mysql_system_tables_data.sql        2008-09-23 08:28:08 +0000
@@ -21,9 +21,9 @@
 -- 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 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,'' WHERE LOWER(@current_hostname) != 'localhost';
 INSERT INTO user SELECT * FROM tmp_user WHERE @had_user_table=0;
 DROP TABLE tmp_user;
[23 Sep 2008 9:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54478

2684 Chad MILLER	2008-09-23
      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 hostname is the same as an
      already-inserted 'localhost' name.
[23 Sep 2008 9:32] Chad MILLER
MySQL doesn't support the syntax  "SELECT exprs WHERE cond".  A FROM-clause is required.  This is another bug.  For this, select "FROM dual" to work around.

=== modified file 'scripts/mysql_system_tables_data.sql'
--- scripts/mysql_system_tables_data.sql        2008-09-23 08:28:29 +0000
+++ scripts/mysql_system_tables_data.sql        2008-09-23 09:28:23 +0000
@@ -21,9 +21,9 @@
 -- 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 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 WHERE LOWER(@current_hostname) != 'localhost';
+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) SELECT @current_hostname,'' WHERE LOWER(@current_hostname) != 'localhost';
+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;
[23 Sep 2008 9:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54480

2684 Chad MILLER	2008-09-23
      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.
[26 Sep 2008 12:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54542

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.
[3 Oct 2008 14:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55269

2696 Chad MILLER	2008-10-03 [merge]
      Merge fix for Bug#35754.
[3 Oct 2008 15:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55276

2761 Chad MILLER	2008-10-03 [merge]
      Merge fix for Bug#35754.
[3 Oct 2008 16:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55277

2845 Chad MILLER	2008-10-03 [merge]
      Merge fix for Bug#35754.
[9 Oct 2008 17:27] Bugs System
Pushed into 5.0.72  (revid:chad@mysql.com-20081003141902-wsds1b5ejsmc8kqz) (version source revid:chad@mysql.com-20081003141902-wsds1b5ejsmc8kqz) (pib:4)
[9 Oct 2008 17:40] Bugs System
Pushed into 5.1.30  (revid:chad@mysql.com-20081003155422-a3cn2qzlkatbzton) (version source revid:mats@sun.com-20081008113713-2vxny72m5w1tywoi) (pib:4)
[15 Oct 2008 15:03] Paul Dubois
This is actually pushed to 5.1.29, not 5.1.30.
[15 Oct 2008 17:07] Paul Dubois
Noted in 5.0.72, 5.1.29 changelogs.

mysql_install_db failed on machines that had the hostname set to
localhost.  

Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:43] Bugs System
Pushed into 6.0.8-alpha  (revid:chad@mysql.com-20081003164123-jhxrvz41zpm7e4jq) (version source revid:chad@mysql.com-20081003164123-jhxrvz41zpm7e4jq) (pib:5)
[17 Oct 2008 17:22] Paul Dubois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:03] Bugs System
Pushed into 5.1.29-ndb-6.2.17  (revid:chad@mysql.com-20081003155422-a3cn2qzlkatbzton) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:22] Bugs System
Pushed into 5.1.29-ndb-6.3.19  (revid:chad@mysql.com-20081003155422-a3cn2qzlkatbzton) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:47] Bugs System
Pushed into 5.1.29-ndb-6.4.0  (revid:chad@mysql.com-20081003155422-a3cn2qzlkatbzton) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)