Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.3, 10.3.0
-
None
-
MariaDB version 10.2.13-MariaDB-10.2.13+maria~xenial-log running on Ubuntu 16.04
Description
Create index on temporary table fails when table is sufficiently large and innoDB temporary tablespace as a small initial size.
Whilst working on the test case I noted that create index fails when `innodb_temp_data_file_path` is set to 'ibtmp1:12M:autoextend' or 'ibtmp1:16M:autoextend, but succeeds with setting 'ibtmp1:64M:autoextend'. The filesystems have sufficient free to accommodate the growth of the temporary tablespace.
MariaDB should increase the size of the temporary tablespace to accommodate the data and index automatically. An error should be returned only if the filesystem is full or the maximum size of the tablespace has been reached.
Fails:
MariaDB [test]> show variables like 'innodb_temp_data_file_path'\G |
Variable_name: innodb_temp_data_file_path
|
Value: ibtmp1:12M:autoextend
|
MariaDB [test]> drop table if exists t1; |
MariaDB [test]> CREATE TEMPORARY TABLE `t1` ( |
`id` int, |
`dbase64` varchar(30) DEFAULT NULL |
) ENGINE=InnoDB;
|
MariaDB [test]> INSERT INTO t1(id, dbase64) select seq, to_base64(seq) from seq_1_to_1000000; |
MariaDB [test]> create index ix1 on t1(id); |
ERROR 1114 (HY000): The table '#sql-1f4a_a' is fulldone |
Succeeds:
MariaDB [test]> show variables like 'innodb_temp_data_file_path'\G |
Variable_name: innodb_temp_data_file_path
|
Value: ibtmp1:64M:autoextend
|
MariaDB [test]> drop table if exists t1; |
MariaDB [test]> CREATE TEMPORARY TABLE `t1` ( |
`id` int, |
`dbase64` varchar(30) DEFAULT NULL |
) ENGINE=InnoDB;
|
MariaDB [test]> INSERT INTO t1(id, dbase64) select seq, to_base64(seq) from seq_1_to_1000000; |
MariaDB [test]> create index ix1 on t1(id); |
The configuration of MariaDB when it fails:
$ mysqld --print-defaults
|
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --lc_messages_dir=/usr/share/mysql --lc_messages=en_US --skip-external-locking --bind-address=0.0.0.0 --max_connections=100 --connect_timeout=5 --wait_timeout=600 --max_allowed_packet=16M --thread_cache_size=128 --sort_buffer_size=4M --bulk_insert_buffer_size=16M --tmp_table_size=32M --max_heap_table_size=32M --myisam_recover_options=BACKUP --key_buffer_size=128M --table_open_cache=400 --myisam_sort_buffer_size=512M --concurrent_insert=2 --read_buffer_size=2M --read_rnd_buffer_size=1M --query_cache_limit=128K --query_cache_size=64M --log_warnings=2 --slow_query_log_file=/var/log/mysql/mariadb-slow.log --long_query_time=10 --log_slow_verbosity=query_plan --log_bin=/var/log/mysql/mariadb-bin --log_bin_index=/var/log/mysql/mariadb-bin.index --expire_logs_days=10 --max_binlog_size=100M --sql_mode=ONLY_FULL_GROUP_BY --default_storage_engine=InnoDB --innodb_buffer_pool_size=256M --innodb_log_buffer_size=8M --innodb_file_per_table=1 --innodb_open_files=400 --innodb_io_capacity=400 --innodb_flush_method=O_DIRECT --histogram_size=128 |
Attachments
Issue Links
- is caused by
-
MDEV-11585 Hard-code the shared InnoDB temporary tablespace ID
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
This bug was a regression of MDEV-11585, which simplified some code related to InnoDB temporary tables in MariaDB 10.2. The commit message of the fix referred to the wrong MDEV.
Thanks for the report and test case.
It appears that the problem was recently fixed by this commit:
commit 852c35f571b1b7454aab5768899e2faee8f941d4Author: Marko MäkeläDate: Mon Feb 19 14:58:05 2018 +0200MDEV-11581 follow-up fix: Correct a conditionfsp_fill_free_list(): Correctly determine whether the temporarytablespace file should be extended in order to respond to apage allocation request. The inverted condition was noticedby Thiru when he analyzed MDEV-13013.The fix will be in the upcoming 10.2.14 release.
If you still experience the problem after upgrade, please comment here.