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

CREATE TABLE creates extra transaction

    XMLWordPrintable

Details

    Description

      In all MySQL versions, InnoDB starts 2 transactions for CREATE TABLE. Before MariaDB 10.2 (or MySQL 5.7), CREATE TEMPORARY TABLE created a persistent table. Starting with those versions, a persistent transaction is still being started for parsing FOREIGN KEY constraints (which are not allowed for temporary tables).

      I think that we need a patch like this:

      commit e5e3bdd7b2e9949f92fe506be7dae1e96e246890 (HEAD -> 10.2)
      Author: Marko Mäkelä <marko.makela@mariadb.com>
      Date:   Mon Apr 16 03:20:44 2018 +0300
       
          MDEV-1xxxx CREATE TEMPORARY TABLE without persistent transaction
          
          InnoDB does not allow FOREIGN KEY constraints to exist for
          TEMPORARY TABLE. This check was performed in
          dict_create_foreign_constraints_low() even before InnoDB
          in MySQL 5.7 or MariaDB 10.2 introduced a dedicated tablespace
          for temporary tables, and actually stopped creating persistent
          metadata and data for temporary tables.
          
          create_table_info_t::create_table(): Do not create a persistent
          transaction when creating a temporary table.
          
          FIXME: Always parse the foreign key constraints, and for temporary
          tables return an error if FOREIGN KEY is specified. Only if the
          constraints are found valid, allocate a transaction (or better yet,
          store the FOREIGN KEY constraints as part of the same transaction
          with the table metadata creation).
          
          dict_create_foreign_constraints_low(): Remove a duplicated check for
          partitioned tables.
       
      diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc
      index c675be6e19a..823ead89983 100644
      --- a/storage/innobase/dict/dict0dict.cc
      +++ b/storage/innobase/dict/dict0dict.cc
      @@ -4882,23 +4882,6 @@ dict_create_foreign_constraints_low(
       		return(DB_CANNOT_ADD_CONSTRAINT);
       	}
       
      -	/* Don't allow foreign keys on partitioned tables yet. */
      -	ptr1 = dict_scan_to(ptr, "PARTITION");
      -	if (ptr1) {
      -		ptr1 = dict_accept(cs, ptr1, "PARTITION", &success);
      -		if (success && my_isspace(cs, *ptr1)) {
      -			ptr2 = dict_accept(cs, ptr1, "BY", &success);
      -			if (success) {
      -				my_error(ER_FOREIGN_KEY_ON_PARTITIONED,MYF(0));
      -				return(DB_CANNOT_ADD_CONSTRAINT);
      -			}
      -		}
      -	}
      -	if (dict_table_is_partition(table)) {
      -		my_error(ER_FOREIGN_KEY_ON_PARTITIONED,MYF(0));
      -		return(DB_CANNOT_ADD_CONSTRAINT);
      -	}
      -
       	/* Let us create a constraint struct */
       
       	foreign = dict_mem_foreign_create();
      diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
      index 3f707e5e631..77915c095e5 100644
      --- a/storage/innobase/handler/ha_innodb.cc
      +++ b/storage/innobase/handler/ha_innodb.cc
      @@ -12674,8 +12674,6 @@ create_table_info_t::create_table()
       	int		primary_key_no;
       	uint		i;
       	dict_table_t*	innobase_table = NULL;
      -	const char*	stmt;
      -	size_t		stmt_len;
       
       	DBUG_ENTER("create_table");
       
      @@ -12800,9 +12798,9 @@ create_table_info_t::create_table()
       		dict_table_get_all_fts_indexes(innobase_table, fts->indexes);
       	}
       
      -	stmt = innobase_get_stmt_unsafe(m_thd, &stmt_len);
      -
      -	if (stmt) {
      +	size_t stmt_len;
      +	if (const char* stmt = (m_flags2 & DICT_TF2_TEMPORARY)
      +	    ? NULL : innobase_get_stmt_unsafe(m_thd, &stmt_len)) {
       		dberr_t	err = row_table_add_foreign_constraints(
       			m_trx, stmt, stmt_len, m_table_name,
       			m_create_info->options & HA_LEX_CREATE_TMP_TABLE);
      

      Attachments

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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