Bug #80070 | allocated_size and file_size differ if create general tablespace outside datadir | ||
---|---|---|---|
Submitted: | 20 Jan 2016 11:25 | Modified: | 7 Feb 2016 12:42 |
Reporter: | Shahriyar Rzayev | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 5.7.10 | OS: | CentOS (7) |
Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[20 Jan 2016 11:25]
Shahriyar Rzayev
[21 Jan 2016 23:49]
MySQL Verification Team
Hi Shahriyar, > General idea for this report to get some > information about general tablespaces. You can read more about InnoDB general tablespaces here: https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html For additional information use either forums.mysql.com or MySQL Support > See the difference between file_size and allocated_size in 2 situation This "18446744073709551615" filesize looks like a bug. What filesystem is /var/lib/mysql_tablespaces/ and how much free space you have there? What does your my.cnf looks like? kind regards Bogdan Kecman
[22 Jan 2016 9:30]
Shahriyar Rzayev
Thanks for link and suggestions :) Well, /var/lib/mysql_tablespaces created on root ('/') path and filesystem is xfs: [root@mysql-57 ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/centos-root 48G 15G 33G 32% / devtmpfs 1,7G 0 1,7G 0% /dev tmpfs 1,7G 0 1,7G 0% /dev/shm tmpfs 1,7G 8,5M 1,7G 1% /run tmpfs 1,7G 0 1,7G 0% /sys/fs/cgroup /dev/sda1 497M 155M 342M 32% /boot tmpfs 343M 0 343M 0% /run/user/0 [root@mysql-57 ~]# mount -l | grep root /dev/mapper/centos-root on / type xfs (rw,relatime,seclabel,attr2,inode64,noquota) my.cnf: [root@mysql-57 ~]# cat /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 max_connections=10000 #log-error=/var/log/mysqld.log log-error=/var/lib/mysql/error.err pid-file=/var/run/mysqld/mysqld.pid # # BINARY LOGGING # server_id = 1 log_bin = /var/lib/mysql/mysql-bin log_bin_index = /var/lib/mysql/mysql-bin expire_logs_days = 14 sync_binlog = 1 binlog_format = row gtid-mode = on enforce-gtid-consistency = true master-info-repository = TABLE relay-log-info-repository = TABLE slave-parallel-workers = 2 binlog-checksum = CRC32 master-verify-checksum = 1 slave-sql-verify-checksum = 1 binlog-rows-query-log_events = 1 log_slave_updates = 1 Just create general tablepsace inside datadir and outside datadir: mysql> create tablespace ts3 add datafile '/home/tablespaces/ts3.ibd' engine=innodb; Query OK, 0 rows affected (0,01 sec) mysql> select * from information_schema.INNODB_SYS_TABLESPACES where name like '%ts3%'; +-------+------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+ | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | +-------+------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+ | 30 | ts3 | 2048 | Any | Any | 16384 | 0 | General | 0 | 18446744073709551615 | 2 | +-------+------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+ 1 row in set (0,00 sec) mysql> drop tablespace ts3; Query OK, 0 rows affected (0,01 sec) mysql> create tablespace ts3 add datafile 'ts3.ibd' engine=innodb; Query OK, 0 rows affected (0,01 sec) mysql> select * from information_schema.INNODB_SYS_TABLESPACES where name like '%ts3%'; +-------+------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | +-------+------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ | 31 | ts3 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 65536 | 32768 | +-------+------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ 1 row in set (0,00 sec)
[22 Jan 2016 19:15]
MySQL Verification Team
Hi, I can certainly verify the bug with weird file_size: mysql [localhost] {msandbox} ((none)) > create tablespace ts3 add datafile '/home/arhimed/sandboxes/msb_5_7_10/data/ts3.ibd' engine=innodb; Query OK, 0 rows affected (0.19 sec) mysql [localhost] {msandbox} ((none)) > create tablespace ts4 add datafile '/tmp/ts4.ibd' engine=innodb; Query OK, 0 rows affected (0.04 sec) mysql [localhost] {msandbox} ((none)) > select * from information_schema.INNODB_SYS_TABLESPACES where name like 'ts%'\G *************************** 1. row *************************** SPACE: 27 NAME: ts3 FLAG: 2048 FILE_FORMAT: Any ROW_FORMAT: Any PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: General FS_BLOCK_SIZE: 4096 FILE_SIZE: 65536 ALLOCATED_SIZE: 65536 *************************** 2. row *************************** SPACE: 28 NAME: ts4 FLAG: 2048 FILE_FORMAT: Any ROW_FORMAT: Any PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: General FS_BLOCK_SIZE: 0 FILE_SIZE: 18446744073709551615 ALLOCATED_SIZE: 2 2 rows in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > thanks Bogdan Kecman
[22 Jan 2016 19:16]
MySQL Verification Team
fyi the 18446744073709551615 == -1 that's where the number comes from :) all best Bogdan Kecman
[31 Jan 2016 9:00]
Shahriyar Rzayev
:) Again -1 , if you create table using data directory option: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/var/lib/mysql_tablespaces/' mysql> select * from information_schema.INNODB_SYS_TABLESPACES where name like '%sbtest%'; +-------+----------------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+ | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | +-------+----------------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+ | 138 | dbtest/sbtest1 | 1057 | Barracuda | Dynamic | 16384 | 0 | Single | 0 | 18446744073709551615 | 2 | +-------+----------------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+ 1 row in set (0,00 sec) Changing Severity to "Serious" because it seems to be related to: http://bugs.mysql.com/bug.php?id=80183 Also I have seen same '18446744073709551615' in error log: 2016-01-26T11:57:40.670681Z 0 [ERROR] InnoDB: Cannot read first page of '/var/lib/mysql_tablespaces/ts6.ibd' I/O error 2016-01-26T11:57:40.670714Z 0 [ERROR] InnoDB: Cannot read first page in datafile: /var/lib/mysql_tablespaces/ts6.ibd, Space ID:18446744073709551615, Flags: 0
[7 Feb 2016 12:42]
Daniel Price
Posted by developer: Fixed as of the upcoming 5.7.12, 5.8.0 release, and here's the changelog entry: INNODB_SYS_TABLESPACES could report incorrect ALLOCATED_SIZE and FILE_SIZE values for a general tablespace created outside of the data directory. The i_s_dict_fill_sys_tablespaces() function generated an incorrect remote file path. Thank you for the bug report.