Bug #29439 mysql_create_db creates "test" database without character set info
Submitted: 29 Jun 2007 6:45 Modified: 16 Nov 2016 6:10
Reporter: Alexander Barkov Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Installing Severity:S3 (Non-critical)
Version:4.1, 5.0, 5.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[29 Jun 2007 6:45] Alexander Barkov
mysql_install_db doesn't work well. It only creates
the "test" database directory and sets its permissions,
but unlike real "CREATE DATABASE" statement it does not 
create the test/db.opt, which should store character set
and collation information.

As a result, the database created using mysql_create_db
and the database created using "CREATE DATABASE test"
may work differently.

How to repeat:
- Add a new test with name "ctype_latin1_da" with these two commands:
  drop database test;
  create database test;
- Record the new test:
  ./mysql-test-run --record ctype_latin1_d
- Run this command:
  ./mysql-test-run --do-test=ctype_latin1

TEST                           RESULT         TIME (ms)

ctype_latin1                   [ pass ]             57
ctype_latin1_da                [ pass ]              2
ctype_latin1_de                [ fail ]

Errors are (from /home/bar/mysql-work/mysql-5.1-new-rpl/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 74: query 'select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0' failed: 1267: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_german2_ci,IMPLICIT) for operation '='
(the last lines may be the most important ones)
Result from queries before failure can be found in /home/bar/mysql-work/mysql-5.1-new-rpl/mysql-test/var/log/ctype_latin1_de.log

- Now run the same command, but skip the new test:
  ./mysql-test-run --do-test=ctype_latin1 --skip-test=ctype_latin1_da

TEST                           RESULT         TIME (ms)

ctype_latin1                   [ pass ]             54
ctype_latin1_da                [ skipped ]
ctype_latin1_de                [ pass ]             50

I.e. "ctype_latin1_de" works fine with the database created
by "mysql-install-db", but it fails with the database created

Suggested fix:
1. Fix mysql-create-db to work exactly the same with "CREATE DATABASE",
   i.e. it must create the db.opt file  and fill it with proper information.

2. Fix ctype_latin1_de to be stable against previous tests.
   It must create its own separate database:

   CREATE DATABASE test_latin1_de;
   USE test_latin1_de;
   .... commands ...
   DROP DATABASE test_latin1_de;
   USE test;
[29 Jun 2007 6:48] Alexander Barkov
Sorry, the CREATE statement in "Suggested fix, part2" should be:

CREATE DATABASE test_latin1_de CHARACTER SET latin1 COLLATE latin1_german2_ci;
[29 Jun 2007 6:52] Alexander Barkov
This bug report should probably be divided into separate reports:

- about mysql_create_db

- about ctype_latin1_de test, which now replies
  on the fact that db.opt file doesn't exists.
[29 Jun 2007 8:10] Sveta Smirnova
Thank you for the report.

Verified as described.
[16 Aug 2010 12:09] Jonathan Perkin
This isn't a packaging bug?
[15 Sep 2011 16:37] MySQL Verification Team
See http://bugs.mysql.com/bug.php?id=62443.
[16 Nov 2016 6:10] Erlend Dahl
mysql_install_db is deprecated as of 5.7.