| 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.
