MySQL Bugs: #34346: ctype_latin1_de test fails if test database is recreated

Bug #34346	ctype_latin1_de test fails if test database is recreated
Submitted:	6 Feb 2008 12:04	Modified:	14 Mar 2008 1:48
Reporter:	Rafal Somla	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Rafal Somla	CPU Architecture:	Any

Description:
Test ctype_latin1_de fails if run after dropping and re-creating the test database. The error reported is:

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 '='

How to repeat:
Create a small test script test.test, containing:

DROP DATABASE test;
CREATE DATABASE test;

Then in the test directory run:

./mtr test ctype_latin1_de

The second test will fail. If run alone, it will pass ok.

Suggested fix:
This is probably somehow related to the .opt file residing in database directory, if it is created using CREATE DATABASE but which is not present there after data dir initialization. The test should be fixed to not be sensitive to this.

REFINED PROBLEM DESCRIPTION

Correct execution of ctype_latin1_de test depends on the default character set and collation of test database being set to latin1 and latin1_german2_ci, respectively. To ensure this, test has an .opt file which says:

--default-character-set=latin1 --default-collation=latin1_german2_ci

Normally, when mysql-test-run starts running tests, it creates empty data/test directory - without db.opt file which specifies default character set and collation. 

My guess is that when server needs to determine default collation for test database and can't read it from the db.opt file, then it uses the values given in server startup options. However, if db.opt file exists then the values are taken from there and the startup options are ignored.

When test database is dropped and then re-created, then server initializes a db.opt file. For that reason the options specified in the test's option file are ignored and test fails due to incorrect default character set and collation settings.

SUGGESTED SOLUTION

One way of solving this problem would be to always explicitly set character set and collation for each table created. But using defaults is pretty convenient. These defaults can be set using ALTER DATABASE statement, instead of specifying server startup options and this should work correctly. However, after altering the test database inside test script, it should be restored to default state at the end. This can be done by re-creating the database. I.e.,

DROP DATABASE test;
CREATE DATABASE test;

This should reset character set and collation for the test database to default values.

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/41769

ChangeSet@1.2763, 2008-02-06 13:25:00+01:00, rafal@quant.(none) +3 -0
  Bug #34346 (ctype_latin1_de test fails if test database is recreated):
  
  This patch implements the solution described in the bug report. In short, default 
  character set and collation are set using ALTER DATABASE instead of using server 
  startup options.

Pushed into mysql-6.0-backup tree.

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/42290

ChangeSet@1.2556, 2008-02-14 16:36:49+01:00, rafal@quant.(none) +2 -0
  Fixing problem in 6.0-backup pushbuild.
  
  Test rpl_ndb_ctype_ucs2_def requires non-standard settings for default character 
  set and collation in the test database. This is achieved by specifying these 
  defaults as server options in the rpl_ndb_ctype_ucs2_def-master.opt file.
  
  However, if in some test which runs before rpl_ndb_ctype_ucs2_def the test 
  database is dropped, and created (as is the case when RESTORE is executed) then 
  the default character set and collation are stored in db.opt file inside test/ 
  directory. In that case, even if the server is restarted, defaults will be read 
  from the db.opt file, not from server options. This makes the test to fail.
  
  This is solved by dropping and re-creating test database inside 
  rpl_ndb_ctype_ucs2_def test. This way, when the database is created, the 
  character set and collation are taken from server options as they should.
  
  This issue is similar to that reported in BUG#34346 but the fix is different 
  with the advantage of less changes made to the test case.

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/42334

ChangeSet@1.2561, 2008-02-15 11:02:02+01:00, rafal@quant.(none) +3 -0
  BUG#34346 (ctype_latin1_de test fails if test database is recreated):
  
  This patch reverts the changes introduces by previous one since a different
  solution of the problem was implemented. Now all tests which might re-create
  the test database (e.g., the backup tests) take care of cleaning the test/ dir
  so that it doesn't contain the db.opt file. Thus this test can work in the
  form in which it was originaly designed.

Pushed into 6.0.5-alpha

Test case changes. No changelog entry needed.