MySQL Bugs: #29439: mysql_create_db creates "test" database without character set info

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:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5.1	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
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
using "CREATE DATABASE".

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;

Sorry, the CREATE statement in "Suggested fix, part2" should be:

CREATE DATABASE test_latin1_de CHARACTER SET latin1 COLLATE latin1_german2_ci;

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.

Thank you for the report.

Verified as described.

This isn't a packaging bug?

See http://bugs.mysql.com/bug.php?id=62443.

mysql_install_db is deprecated as of 5.7.