Bug #74329 Get rid of db.opt
Submitted: 11 Oct 2014 8:47 Modified: 17 Nov 2014 9:30
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.7.5-labs-dd OS:Any
Assigned to: CPU Architecture:Any
Tags: data dictionary

[11 Oct 2014 8:47] Daniël van Eeden
Description:
The collation for a schema is already stored in mysql.schemata. It looks like the db.opt file is redundant and can be removed.

mysql [localhost] {root} (foo) > \! cat data/foo/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
mysql [localhost] {root} (foo) > select * from mysql.schemata where name='foo'\G
*************************** 1. row ***************************
                  id: 5
          catalog_id: 1
                name: foo
default_collation_id: 8
             created: 0000-00-00 00:00:00
        last_altered: 0000-00-00 00:00:00
1 row in set (0.00 sec)

How to repeat:
CREATE SCHEMA foo;
Now look in the @@datadir/foo

Suggested fix:
Get rid of db.opt
[11 Oct 2014 9:22] Daniël van Eeden
add tag
[13 Oct 2014 5:14] MySQL Verification Team
Hello Daniël,

Thank you for the report and test case.

Thanks,
Umesh
[13 Oct 2014 5:15] MySQL Verification Team
//

mysql> show variables like 'datadir';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /tmp/master/ |
+---------------+--------------+
1 row in set (0.00 sec)

mysql> \! cat /tmp/master/test/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
mysql>
mysql> select * from mysql.schemata where name='test'\G
*************************** 1. row ***************************
                  id: 5
          catalog_id: 1
                name: test
default_collation_id: 8
             created: 0000-00-00 00:00:00
        last_altered: 0000-00-00 00:00:00
1 row in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.5                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.7.5-labs-dd-log            |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-el6                    |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
[17 Nov 2014 9:30] Jon Olav Hauglid
Thanks for the bug report!

This bug does not affect any released version, only a labs release. The bug
was introduced, reported and fixed on a development branch.