Bug #83122 Wrong statement about the location of MySQL system database tablespace files
Submitted: 23 Sep 2016 7:11 Modified: 17 Oct 2016 16:13
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[23 Sep 2016 7:11] Valeriy Kravchuk 
Description:
Manual (http://dev.mysql.com/doc/refman/8.0/en/data-dictionary-transactional-storage.html) says:

"The data dictionary schema stores dictionary data in transactional (InnoDB) tables created in file-per-table tablespaces. The tablespace files (.ibd files) are located in the mysql database directory together with MySQL system database tablespace files."

So, from the above one can conclude that there are system database tablespace files (other than .ibd per table) in the mysql database/directory. This is wrong, they are located in the toplevel directory of mysql database, datadir, by default.

How to repeat:
Check the content of mysql directory:

[openxs@fc23 8.0]$ ls -l data/mysql
total 32496
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 catalogs.ibd
-rw-r-----. 1 openxs openxs   163840 Sep 23 09:46 character_sets.ibd
-rw-r-----. 1 openxs openxs   163840 Sep 23 09:46 collations.ibd
-rw-r-----. 1 openxs openxs 10485760 Sep 14 12:27 columns.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 columns_priv.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 column_stats.ibd
-rw-r-----. 1 openxs openxs   163840 Sep 14 12:27 column_type_elements.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 component.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 db.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 default_roles.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 engine_cost.ibd
-rw-r-----. 1 openxs openxs   196608 Sep 14 12:27 events.ibd
-rw-r-----. 1 openxs openxs   163840 Sep 14 12:27 foreign_key_column_usage.ibd
-rw-r-----. 1 openxs openxs   180224 Sep 14 12:27 foreign_keys.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 func.ibd
-rw-r-----. 1 openxs openxs     7759 Sep 14 12:27 general_log_102.SDI
-rw-r-----. 1 openxs openxs       35 Sep 14 12:28 general_log.CSM
-rw-r-----. 1 openxs openxs        0 Sep 14 12:27 general_log.CSV
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 gtid_executed.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 help_category.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 help_keyword.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 help_relation.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 help_topic.ibd
-rw-r-----. 1 openxs openxs   163840 Sep 14 12:27 index_column_usage.ibd
-rw-r-----. 1 openxs openxs   163840 Sep 14 12:27 indexes.ibd
-rw-r-----. 1 openxs openxs   163840 Sep 14 12:27 index_partitions.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 index_stats.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 innodb_index_stats.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 innodb_table_stats.ibd
-rw-r-----. 1 openxs openxs   163840 Sep 14 12:27 parameters.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 parameter_type_elements.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 plugin.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 procs_priv.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 proxies_priv.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 role_edges.ibd
-rw-r-----. 1 openxs openxs   655360 Sep 14 12:27 routines.ibd
-rw-r-----. 1 openxs openxs   163840 Sep 23 09:46 schemata.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 server_cost.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 servers.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 slave_master_info.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 slave_relay_log_info.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 slave_worker_info.ibd
-rw-r-----. 1 openxs openxs    16062 Sep 14 12:27 slow_log_103.SDI
-rw-r-----. 1 openxs openxs       35 Sep 14 14:10 slow_log.CSM
-rw-r-----. 1 openxs openxs        0 Sep 14 12:27 slow_log.CSV
-rw-r-----. 1 openxs openxs 13631488 Sep 14 12:27 st_spatial_reference_systems.ibd
-rw-r-----. 1 openxs openxs   212992 Sep 14 12:27 table_partitions.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 table_partition_values.ibd
-rw-r-----. 1 openxs openxs   655360 Sep 14 12:27 tables.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 tablespace_files.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 tablespaces.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 tables_priv.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 table_stats.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 time_zone.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 time_zone_leap_second.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 time_zone_name.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 time_zone_transition.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 time_zone_transition_type.ibd
-rw-r-----. 1 openxs openxs   212992 Sep 14 12:27 triggers.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 user.ibd
-rw-r-----. 1 openxs openxs   131072 Sep 14 12:27 version.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 view_routine_usage.ibd
-rw-r-----. 1 openxs openxs   147456 Sep 14 12:27 view_table_usage.ibd

and try to find "MySQL system database tablespace files" there.

I think they are here:

[openxs@fc23 8.0]$ ls -l data
total 122936
-rw-r-----. 1 openxs openxs       56 Sep 14 12:27 auto.cnf
-rw-r-----. 1 openxs openxs    18044 Sep 23 09:46 fc23.err
-rw-r-----. 1 openxs openxs        6 Sep 23 09:46 fc23.pid
-rw-r-----. 1 openxs openxs     1147 Sep 14 14:10 ib_buffer_pool
-rw-r-----. 1 openxs openxs 12582912 Sep 23 09:46 ibdata1
-rw-r-----. 1 openxs openxs 50331648 Sep 23 09:46 ib_logfile0
-rw-r-----. 1 openxs openxs 50331648 Sep 14 12:27 ib_logfile1
-rw-r-----. 1 openxs openxs 12582912 Sep 23 09:46 ibtmp1
drwxr-x---. 2 openxs openxs     4096 Sep 14 12:27 mysql
-rw-rw----. 1 openxs openxs        5 Sep 23 09:17 mysqld_safe.pid
-rw-r-----. 1 openxs openxs      225 Sep 14 12:27 performance_sche_3.SDI
drwxr-x---. 2 openxs openxs     4096 Sep 14 12:27 performance_schema
drwxr-x---. 2 openxs openxs     4096 Sep 14 12:27 sys
-rw-r-----. 1 openxs openxs      210 Sep 14 12:27 sys_4.SDI

That ibdata1 there, no?

Suggested fix:
Please, clarify the location of the system database tablespace files.
[23 Sep 2016 7:13] Valeriy Kravchuk 
Maybe that was about .SDI files we see hereand there? Like this:

[openxs@fc23 8.0]$ cat data/mysql/slow_log_103.SDI | more
{
    "sdi_version": 1,
    "dd_version": 1,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "slow_log",
        "mysql_version_id": 80000,
        "created": 20160914122731,
        "last_altered": 20160914122731,
        "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_recor
d=1;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "start_time",
                "type": 18,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": false,
                "ordinal_position": 1,
                "char_length": 26,
...

Please, clarify.
[23 Sep 2016 7:24] MySQL Verification Team 
Hello Valerii,

Thank you for the report and feedback!

Thanks,
Umesh
[17 Oct 2016 16:13] Daniel Price 
Posted by developer:
 
The statement has been revised as follows:

"The data dictionary schema stores dictionary data in transactional
(InnoDB) tables created in file-per-table tablespaces. Data dictionary
tablespace files are located in the mysql database directory together with
non-dictionary system tables."

Thank you for the bug report.