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
-