[MDEV-15591] Create index fails on innoDB temporary table Created: 2018-03-16  Updated: 2018-06-21  Resolved: 2018-03-16

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2.3, 10.3.0
Fix Version/s: 10.2.14, 10.3.5

Type: Bug Priority: Major
Reporter: Kenneth Penza Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None
Environment:

MariaDB version 10.2.13-MariaDB-10.2.13+maria~xenial-log running on Ubuntu 16.04

Issue Links:
Problem/Incident
is caused by MDEV-11585 Hard-code the shared InnoDB temporary... Closed
Relates
relates to MDEV-15366 [ERROR] mysqld: The table '#sql-984_1... Closed

 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



 Comments   
Comment by Elena Stepanova [ 2018-03-16 ]

Thanks for the report and test case.
It appears that the problem was recently fixed by this commit:

commit 852c35f571b1b7454aab5768899e2faee8f941d4
 
Author: Marko Mäkelä
 
Date:   Mon Feb 19 14:58:05 2018 +0200
 
 
 
    MDEV-11581 follow-up fix: Correct a condition
 
    
    fsp_fill_free_list(): Correctly determine whether the temporary
 
    tablespace file should be extended in order to respond to a
 
    page allocation request. The inverted condition was noticed
 
    by 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.

Comment by Marko Mäkelä [ 2018-06-21 ]

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.

Generated at Thu Feb 08 08:22:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.