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

Create index fails on innoDB temporary table

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.3, 10.3.0
    • 10.2.14, 10.3.5
    • 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

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.