| Bug #69315 | Filtering of system databases doesn't work consistent. | ||
|---|---|---|---|
| Submitted: | 24 May 2013 22:10 | Modified: | 12 Jun 2013 16:17 |
| Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.7.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[24 May 2013 22:14]
Daniël van Eeden
The ignore-db-dir option was intended to hide directories like 'lost+found', '.zfs' and '.snapshot'. Those directories do not contain any tables. So a solution might be to only hide databases/directories if they do not have any tables in them.
[25 May 2013 8:08]
MySQL Verification Team
Hello Daniel, Thank you for the report. Verified as described. Thanks, Umesh
[12 Jun 2013 16:17]
Erlend Dahl
[7 Jun 2013 2:30] Georgi Kodinov http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_ign ore_db_dirs says : "A comma-separated list of names that are not considered as database directories in the data directory. The value is set from any instances of --ignore-db-dir given at server startup. This variable was added in MySQL 5.6.3." Note the word "directories". It's not "databases", it's "database directories". This option was designed to help with the cases when the data dir is on a path that does already have some subdirectories that can't be removed : e.g. a volume directory in linux will always have .lost+found etc. So the behavior you're observing is perfectly correct. Performance schema is an actual storage engine, so all of its tables are real tables (with .FRM etc) in a real database (a directory inside data dir). Whereas I_S is a virtual database implemented through hooks inside the query execution code. So it doesn't have any files or databases in data dir.
Description: When the --ignore_db_dir option is used to filter information_schema, mysql and/or performance_schema some statements start to behave strangely. Filtering performance_schema and mysql works, but it doesn't work for information_schema Queries against information_schema tables only shows rows if there is an explicit where with the full schema name. How to repeat: mysql [localhost] {msandbox} ((none)) > select schema_name from information_schema.schemata where schema_name like 'm%'; Empty set (0.00 sec) mysql [localhost] {msandbox} ((none)) > select schema_name from information_schema.schemata where schema_name='mysql'; +-------------+ | schema_name | +-------------+ | mysql | +-------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > select @@ignore_db_dirs; +--------------------------------------------------+ | @@ignore_db_dirs | +--------------------------------------------------+ | performance_schema,information_schema,mysql,test | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > select schema_name from information_schema.schemata where schema_name in('information_schema','mysql'); +--------------------+ | schema_name | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.01 sec) Suggested fix: The mysql schema is only shown if you explicitly ask for it. I think it should always be shown or never be show, it should not depend on the where statement.