Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15591

Create index fails on innoDB temporary table

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.3, 10.3.0
    • Fix Version/s: 10.2.14, 10.3.5
    • Labels:
      None
    • Environment:
      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

            Activity

              People

              • Assignee:
                marko Marko Mäkelä
                Reporter:
                kpenza Kenneth Penza
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: