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

Assertion `!btr_search_own_any(RW_LOCK_S)' failed in btr_search_drop_page_hash_index

    XMLWordPrintable

    Details

      Description

      Note: The test case contains only a few statements: CREATE, INSERT, ALTER, SELECT (and DROP for cleanup), and it only inserts one row. But the value is long, that's why the test case looks big. The data is taken from the help table, I've located the record but couldn't reduce the value much.

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c TEXT, FULLTEXT KEY (c)) ENGINE=InnoDB;
      INSERT INTO t1 (c) VALUES ("
      alter_specification:
       table_option ...
       | ADD [COLUMN] [IF NOT EXISTS] col_name column_definition
       [FIRST | AFTER col_name ]
       | ADD [COLUMN] [IF NOT EXISTS] (col_name
      column_definition,...)
       | ADD {INDEX|KEY} [IF NOT EXISTS] [index_name]
       [index_type] (index_col_name,...) [index_option] ...
       | ADD [CONSTRAINT [symbol]] PRIMARY KEY
       [index_type] (index_col_name,...) [index_option] ...
       | ADD [CONSTRAINT [symbol]]
       UNIQUE [INDEX|KEY] [index_name]
       [index_type] (index_col_name,...) [index_option] ...
       | ADD FULLTEXT [INDEX|KEY] [index_name]
       (index_col_name,...) [index_option] ...
       | ADD SPATIAL [INDEX|KEY] [index_name]
       (index_col_name,...) [index_option] ...
       | ADD [CONSTRAINT [symbol]]
       FOREIGN KEY [IF NOT EXISTS] [index_name]
      (index_col_name,...)
       reference_definition
       | ADD PERIOD FOR SYSTEM_TIME (start_column_name,
      end_column_name)
       | ALTER [COLUMN] col_name SET DEFAULT literal
      |(expression)
       | ALTER [COLUMN] col_name DROP DEFAULT
       | CHANGE [COLUMN] [IF EXISTS] old_col_name new_col_name
      column_definition
       [FIRST|AFTER col_name]
       | MODIFY [COLUMN] [IF EXISTS] col_name column_definition
       [FIRST | AFTER col_name]
       | DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE]
       | DROP PRIMARY KEY
       | DROP {INDEX|KEY} [IF EXISTS] index_name
       | DROP FOREIGN KEY [IF EXISTS] fk_symbol
       | DROP CONSTRAINT [IF EXISTS] constraint_name
       | DISABLE KEYS
       | ENABLE KEYS
       | RENAME [TO] new_tbl_name
       | ORDER BY col_name [, col_name] ...
       | CONVERT TO CHARACTER SET charset_name [COLLATE
      collation_name]
       | [DEFAULT] CHARACTER SET [=] charset_name
       | [DEFAULT] COLLATE [=] collation_name
       | DISCARD TABLESPACE
       | IMPORT TABLESPACE
       | ALGORITHM [=] {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT}
       | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
       | FORCE
       | partition_options
       | ADD PARTITION (partition_definition)
       | DROP PARTITION partition_names
       | COALESCE PARTITION number
       | REORGANIZE PARTITION [partition_names INTO
      (partition_definitions)]
       | ANALYZE PARTITION partition_names
       | CHECK PARTITION partition_names
       | OPTIMIZE PARTITION partition_names
       | REBUILD PARTITION partition_names
       | REPAIR PARTITION partition_names
       | EXCHANGE PARTITION partition_name WITH TABLE tbl_name
       | REMOVE PARTITIONING
       | ADD SYSTEM VERSIONING
       | DROP SYSTEM VERSIONING
       
      index_col_name:
       col_name [(length)] [ASC | DESC]
       
      index_type:
       USING {BTREE | HASH | RTREE}
       
      index_option:
       KEY_BLOCK_SIZE [=] value
       | index_type
       | WITH PARSER parser_name
       | COMMENT 'string'
       | CLUSTERING={YES| NO}
       
      table_options:
       table_option [[,] table_option] ...
      In MariaDB 10.0.2 and later, IF EXISTS and IF NOT EXISTS
      clauses have been added for the following:
       
      ADD COLUMN [IF NOT EXISTS]
      ADD INDEX [IF NOT EXISTS]
      ADD FOREIGN KEY [IF NOT EXISTS]
      ADD PARTITION [IF NOT EXISTS]
      CREATE INDEX [IF NOT EXISTS]
       
      DROP COLUMN [IF EXISTS]
      DROP INDEX [IF EXISTS]
      DROP FOREIGN KEY [IF EXISTS]
      DROP PARTITION [IF EXISTS]
      CHANGE COLUMN [IF EXISTS]
      MODIFY COLUMN [IF EXISTS]
      DROP INDEX [IF EXISTS]
      When IF EXISTS and IF NOT EXISTS are used in clauses,
      queries will not
      report errors when the condition is triggered for that
      clause. A warning with
      the same message text will be issued and the ALTER will move
      on to the next
      clause in the statement (or end if finished).
       
      This was done in MDEV-318.
       
      Description
      ----------- 
      ALTER TABLE enables you to change the structure of an
      existing table.
      For example, you can add or delete columns, create or
      destroy indexes,
      change the type of existing columns, or rename columns or
      the table
      itself. You can also change the comment for the table and
      the storage engine of the
      table.
       
      If another connection is using the table, a metadata lock is
      active, and this statement will wait until the lock is
      released. This is also true for non-transactional tables.
       
      When adding a UNIQUE index on a column (or a set of columns)
      which have duplicated values, an error will be produced and
      the statement will be stopped. To suppress the error and
      force the creation of UNIQUE indexes, discarding duplicates,
      the IGNORE option can be specified. This can be useful if a
      column (or a set of columns) should be UNIQUE but it
      contains duplicate values; however, this technique provides
      no control on which rows are preserved and which are
      deleted. Also, note that IGNORE is accepted but ignored in
      ALTER TABLE ... EXCHANGE PARTITION statements.
       
      This statement can also be used to rename a table. For
      details see RENAME TABLE.
       
      When an index is created, the storage engine may use a
      configurable buffer in the process. Incrementing the buffer
      speeds up the index creation. Aria and MyISAM allocate a
      buffer whose size is defined by aria_sort_buffer_size or
      myisam_sort_buffer_size, also used for REPAIR TABLE.
      InnoDB/XtraDB allocates three buffers whose size is defined
      by innodb_sort_buffer_size.
       
      Privileges
       
      Executing the ALTER TABLE statement generally requires at
      least the ALTER privilege for the table or the database..
       
      If you are renaming a table, then it also requires the DROP,
      CREATE and INSERT privileges for the table or the database
      as well.
       
      Online DDL
       
      In MariaDB 10.0 and later, online DDL is supported with the
      ALGORITHM and LOCK clauses.
       
      See InnoDB Online DDL Overview for more information on
      online DDL with InnoDB.
       
      ALTER ONLINE TABLE
       
      ALTER ONLINE TABLE has also worked for partitioned tables
      since MariaDB 10.0.11.
       
      Online ALTER TABLE is available by executing the following:
       
      ALTER ONLINE TABLE ...;
       
      This statement has the following semantics:
       
      In MariaDB 10.0.12 and later, this statement is equivalent
      to the following:
       
      ALTER TABLE ... LOCK=NONE;
       
      See the LOCK alter specification for more information.
       
      In MariaDB 10.0.11, this statement is equivalent to the
      following:
       
      ALTER TABLE ... ALGORITHM=INPLACE;
       
      See the ALGORITHM alter specification for more information.
       
      MariaDB until 10.0.10
       
      In MariaDB 10.0.10 and before, this statement ensures that
      the ALTER TABLE statement does not make a copy of the table.
       
      WAIT/NOWAIT
       
      Set the lock wait timeout. See WAIT and NOWAIT.
       
      Column Definitions
       
      See CREATE TABLE: Column Definitions for information about
      column definitions.
       
      Index Definitions
       
      See CREATE TABLE: Index Definitions for information about
      index definitions.
       
      The CREATE INDEX and DROP INDEX statements can also be used
      to add or remove an index.
       
      Character Sets and Collations
       
      CONVERT TO CHARACTER SET charset_name [COLLATE
      collation_name]
      [DEFAULT] CHARACTER SET [=] charset_name
      [DEFAULT] COLLATE [=] collation_name
      See Setting Character Sets and Collations for details on
      setting the character sets and collations.
       
      Alter Specifications
       
      Table Options
       
      See CREATE TABLE: Table Options for information about table
      options.
       
      ADD COLUMN
       
      ... ADD COLUMN [IF NOT EXISTS] (col_name
      column_definition,...)
      Adds a column to the table. The syntax is the same as in
      CREATE TABLE.
      If you are using IF NOT_EXISTS the column will not be added
      if it was not there already. This is very useful when doing
      scripts to modify tables.
       
      The FIRST and AFTER clauses affect the physical order of
      columns in the datafile. Use FIRST to add a column in the
      first (leftmost) position, or AFTER followed by a column
      name to add the new column in any other position. Note that,
      nowadays, the physical position of a column is usually
      irrelevant.
       
      See also Instant ADD COLUMN for InnoDB.
       
      DROP COLUMN
       
      ... DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]
      Drops the column from the table.
      If you are using IF EXISTS you will not get an error if the
      column didn't exist.
      If the column is part of any index, the column will be
      dropped from them, except if you add a new column with
      identical name at the same time. The index will be dropped
      if all columns from the index were dropped.
      If the column was used in a view or trigger, you will get an
      error next time the view or trigger is accessed.
       
      Dropping a column that is part of a multi-column UNIQUE
      constraint is not permitted. For example:
       
      CREATE TABLE a (
       a int,
       b int,
       primary key (a,b)
      );
       
      ALTER TABLE x DROP COLUMN a;
      [42000][1072] Key column 'A' doesn't exist in table
       
      The reason is that dropping column a would result in the new
      constraint that all values in column b be unique. In order
      to drop the column, an explicit DROP PRIMARY KEY and ADD
      PRIMARY KEY would be required. Up until MariaDB 10.2.7, the
      column was dropped and the additional constraint applied,
      resulting in the following structure:
       
      ALTER TABLE x DROP COLUMN a;
      Query OK, 0 rows affected (0.46 sec)
       
      DESC x;
      +-------+---------+------+-----+---------+-------+
      | Field | Type | Null | Key | Default | Extra |
      +-------+---------+------+-----+---------+-------+
      | b | int(11) | NO | PRI | NULL | |
      +-------+---------+------+-----+---------+-------+
       
      MariaDB 10.4.0 supports instant DROP COLUMN. DROP COLUMN of
      an indexed column would imply DROP INDEX (and in the case of
      a non-UNIQUE multi-column index, possibly ADD INDEX). These
      will not be allowed with ALGORITHM=INSTANT, but unlike
      before, they can be allowed with ALGORITHM=NOCOPY
       
      RESTRICT and CASCADE are allowed to make porting from other
      database systems easier. In MariaDB, they do nothing.
       
      MODIFY COLUMN
       
      Allows you to modify the type of a column. The column will
      be at the same place as the original column and all indexes
      on the column will be kept. Note that when modifying column,
      you should specify all attributes for the new column.
       
      CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY
      KEY((a));
      ALTER TABLE t1 MODIFY a BIGINT UNSIGNED AUTO_INCREMENT;
       
      CHANGE COLUMN
       
      Works like MODIFY COLUMN except that you can also change the
      name of the column. The column will be at the same place as
      the original column and all index on the column will be
      kept.
       
      CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY
      KEY(a));
      ALTER TABLE t1 CHANGE a b BIGINT UNSIGNED AUTO_INCREMENT;
       
      ALTER COLUMN
       
      This lets you change column options.
       
      CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, b
      varchar(50), PRIMARY KEY(a));
      ALTER TABLE t1 ALTER b SET DEFAULT 'hello';
       
      ADD PRIMARY KEY
       
      Add a primary key.
       
      For PRIMARY KEY indexes, you can specify a name for the
      index, but it is silently ignored, and the name of the index
      is always PRIMARY.
       
      See Getting Started with Indexes: Primary Key for more
      information.
       
      DROP PRIMARY KEY
       
      Drop a primary key.
       
      For PRIMARY KEY indexes, you can specify a name for the
      index, but it is silently ignored, and the name of the index
      is always PRIMARY.
       
      See Getting Started with Indexes: Primary Key for more
      information.
       
      ADD FOREIGN KEY
       
      Add a foreign key.
       
      For FOREIGN KEY indexes, a reference definition must be
      provided.
       
      For FOREIGN KEY indexes, you can specify a name for the
      constraint, using the CONSTRAINT keyword. That name will be
      used in error messages.
       
      First, you have to specify the name of the target (parent)
      table and a column or a column list which must be indexed
      and whose values must match to the foreign key's values.
      The MATCH clause is accepted to improve the compatibility
      with other DBMS's, but has no meaning in MariaDB. The ON
      DELETE and ON UPDATE clauses specify what must be done when
      a DELETE (or a REPLACE) statements attempts to delete a
      referenced row from the parent table, and when an UPDATE
      statement attempts to modify the referenced foreign key
      columns in a parent table row, respectively. The following
      options are allowed:
      RESTRICT: The delete/update operation is not performed. The
      statement terminates with a 1451 error (SQLSTATE '2300').
      NO ACTION: Synonym for RESTRICT.
      CASCADE: The delete/update operation is performed in both
      tables.
      SET NULL: The update or delete goes ahead in the parent
      table, and the corresponding foreign key fields in the child
      table are set to NULL. (They must not be defined as NOT NULL
      for this to succeed).
       
      MariaDB until 5.3
      SET DEFAULT: This option is currently implemented only for
      the PBXT storage engine, which is disabled by default and no
      longer maintained. It sets the child table's foreign key
      fields to their DEFAULT values when the referenced parent
      table key entries are updated or deleted.
       
      If either clause is omitted, the default behavior for the
      omitted clause is RESTRICT.
       
      See Foreign Keys for more information.
       
      DROP FOREIGN KEY
       
      Drop a foreign key.
       
      See Foreign Keys for more information.
       
      ADD INDEX
       
      Add a plain index.
       
      Plain indexes are regular indexes that are not unique, and
      are not acting as a primary key or a foreign key. They are
      also not the \"specialized\" FULLTEXT or SPATIAL indexes.
       
      See Getting Started with Indexes: Plain Indexes for more
      information.
       
      DROP INDEX
       
      Drop a plain index.
       
      Plain indexes are regular indexes that are not unique, and
      are not acting as a primary key or a foreign key. They are
      also not the \"specialized\" FULLTEXT or SPATIAL indexes.
       
      See Getting Started with Indexes: Plain Indexes for more
      information.
       
      ADD UNIQUE INDEX
       
      Add a unique index.
       
      The UNIQUE keyword means that the index will not accept
      duplicated values, except for NULLs. An error will raise if
      you try to insert duplicate values in a UNIQUE index.
       
      For UNIQUE indexes, you can specify a name for the
      constraint, using the CONSTRAINT keyword. That name will be
      used in error messages.
       
      See Getting Started with Indexes: Unique Index for more
      information.
       
      DROP UNIQUE INDEX
       
      Drop a unique index.
       
      The UNIQUE keyword means that the index will not accept
      duplicated values, except for NULLs. An error will raise if
      you try to insert duplicate values in a UNIQUE index.
       
      For UNIQUE indexes, you can specify a name for the
      constraint, using the CONSTRAINT keyword. That name will be
      used in error messages.
       
      See Getting Started with Indexes: Unique Index for more
      information.
       
      ADD FULLTEXT INDEX
       
      Add a FULLTEXT index.
       
      See Full-Text Indexes for more information.
       
      DROP FULLTEXT INDEX
       
      Drop a FULLTEXT index.
       
      See Full-Text Indexes for more information.
       
      ADD SPATIAL INDEX
       
      Add a SPATIAL index.
       
      See SPATIAL INDEX for more information.
       
      DROP SPATIAL INDEX
       
      Drop a SPATIAL index.
       
      See SPATIAL INDEX for more information.
       
      ENABLE/ DISABLE KEYS
       
      DISABLE KEYS will disable all non unique keys for the table
      for storage engines that support this (at least MyISAM and
      Aria). This can be used to speed up inserts into empty
      tables.
       
      ENABLE KEYS will enable all disabled keys.
       
      RENAME TO
       
      Renames the table. See also RENAME TABLE.
       
      ADD CONSTRAINT
       
      Modifies the table adding a constraint on a particular
      column or columns.
       
      MariaDB 10.2.1 introduced new ways to define a constraint.
       
      Note: Before MariaDB 10.2.1, constraint expressions were
      accepted in syntax, but ignored.
       
      ALTER TABLE table_name 
      ADD CONSTRAINT [constraint_name] CHECK(expression);
      Before a row is inserted or updated, all constraints are
      evaluated in the order they are defined. If any constraint
      fails, then the row will not be updated. One can use most
      deterministic functions in a constraint, including UDF's.
       
      CREATE TABLE account_ledger (
       id INT PRIMARY KEY AUTO_INCREMENT,
       transaction_name VARCHAR(100),
       credit_account VARCHAR(100),
       credit_amount INT,
       debit_account VARCHAR(100),
       debit_amount INT);
       
      ALTER TABLE account_ledger 
      ADD CONSTRAINT is_balanced 
       CHECK((debit_amount + credit_amount) = 0);
       
      The constraint_name is optional. If you don't provide one
      in the ALTER TABLE statement, MariaDB auto-generates a name
      for you. This is done so that you can remove it later using
      DROP CONSTRAINT clause.
       
      You can disable all constraint expression checks by setting
      the variable check_constraint_checks to OFF. You may find
      this useful when loading a table that violates some
      constraints that you want to later find and fix in SQL.
       
      To view constraints on a table, query
      information_schema.TABLE_CONSTRAINTS:
       
      SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE 
      FROM information_schema.TABLE_CONSTRAINTS
      WHERE TABLE_NAME = 'account_ledger';
       
      +-----------------+----------------+-----------------+
      | CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
      +-----------------+----------------+-----------------+
      | is_balanced | account_ledger | CHECK |
      +-----------------+----------------+-----------------+
       
      DROP CONSTRAINT
       
      DROP CONSTRAINT for UNIQUE and FOREIGN KEY constraints was
      introduced in MariaDB 10.2.22 and MariaDB 10.3.13.
       
      DROP CONSTRAINT for CHECK constraints was introduced in
      MariaDB 10.2.1
       
      Modifies the table, removing the given constraint.
       
      ALTER TABLE table_name
      DROP CONSTRAINT constraint_name;
       
      When you add a constraint to a table, whether through a
      CREATE TABLE or ALTER TABLE...ADD CONSTRAINT statement, you
      can either set a constraint_name yourself, or allow MariaDB
      to auto-generate one for you. To view constraints on a
      table, query information_schema.TABLE_CONSTRAINTS. For
      instance,
       
      CREATE TABLE t (
       a INT,
       b INT,
       c INT,
       CONSTRAINT CHECK(a > b),
       CONSTRAINT check_equals CHECK(a = c)); 
       
      SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE 
      FROM information_schema.TABLE_CONSTRAINTS
      WHERE TABLE_NAME = 't';
       
      +-----------------+----------------+-----------------+
      | CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
      +-----------------+----------------+-----------------+
      | check_equals | t | CHECK |
      | CONSTRAINT_1 | t | CHECK |
      +-----------------+----------------+-----------------+
       
      To remove a constraint from the table, issue an ALTER
      TABLE...DROP CONSTRAINT statement. For example,
       
      ALTER TABLE t DROP CONSTRAINT is_unique;
       
      ADD SYSTEM VERSIONING
       
      System-versioned tables was added in MariaDB 10.3.4.
       
      Add system versioning.
       
      DROP SYSTEM VERSIONING
       
      System-versioned tables was added in MariaDB 10.3.4.
       
      Drop system versioning.
       
      ADD PERIOD FOR SYSTEM_TIME
       
      System-versioned tables was added in MariaDB 10.3.4.
       
      FORCE
       
      ALTER TABLE ... FORCE can force MariaDB to re-build the
      table.
       
      In MariaDB 5.5 and before, this could only be done by
      setting the ENGINE table option to its old value. For
      example, for an InnoDB table, one could execute the
      following:
       
      ALTER TABLE tab_name ENGINE = InnoDB;
       
      In MariaDB 10.0 and later, the FORCE option can be used
      instead. For example, :
       
      ALTER TABLE tab_name FORCE;
       
      With InnoDB, the table rebuild will only reclaim unused
      space (i.e. the space previously used for deleted rows) if
      the innodb_file_per_table system variable is set to ON. If
      the system variable is OFF, then the space will not be
      reclaimed, but it will be-re-used for new data that's later
      added.
       
      EXCHANGE PARTITION
       
      ALTER TABLE ... EXCHANGE PARTITION was introduced in MariaDB
      10.0.4
       
      This is used to exchange the tablespace files between a
      partition and another table.
       
      See copying InnoDB's transportable tablespaces for more
      information.
       
      DISCARD TABLESPACE
       
      This is used to discard an InnoDB table's tablespace.
       
      See copying InnoDB's transportable tablespaces for more
      information.
       
      IMPORT TABLESPACE
       
      This is used to import an InnoDB table's tablespace. The
      tablespace should have been copied from its original server
      after executing FLUSH TABLES FOR EXPORT.
       
      See copying InnoDB's transportable tablespaces for more
      information.
       
      ALTER TABLE ... IMPORT only applies to InnoDB tables. Most
      other popular storage engines, such as Aria and MyISAM, will
      recognize their data files as soon as they've been placed
      in the proper directory under the datadir, and no special
      DDL is required to import them.
       
      ALGORITHM
       
      In MariaDB 5.5 and before, ALTER TABLE operations required
      making a temporary copy of the table, which can be slow for
      large tables.
       
      In MariaDB 10.0 and later, the ALTER TABLE statement
      supports the ALGORITHM clause. This clause is one of the
      clauses that is used to implement online DDL. ALTER TABLE
      supports several different algorithms. An algorithm can be
      explicitly chosen for an ALTER TABLE operation by setting
      the ALGORITHM clause. The supported values are:
      ALGORITHM=DEFAULT - This implies the default behavior for
      the specific statement, such as if no ALGORITHM clause is
      specified.
      ALGORITHM=COPY
      ALGORITHM=INPLACE
      ALGORITHM=NOCOPY - This was added in MariaDB 10.3.7.
      ALGORITHM=INSTANT - This was added in MariaDB 10.3.7.
       
      See InnoDB Online DDL Overview: ALGORITHM for information on
      how the ALGORITHM clause affects InnoDB.
       
      ALGORITHM=DEFAULT
       
      The default behavior, which occurs if ALGORITHM=DEFAULT is
      specified, or if ALGORITHM is not specified at all, usually
      only makes a copy if the operation doesn't support being
      done in-place at all. In this case, the most efficient
      available algorithm will usually be used.
       
      However, in MariaDB 10.3.6 and before, if the value of the
      old_alter_table system variable is set to ON, then the
      default behavior is to perform ALTER TABLE operations by
      making a copy of the table using the old algorithm.
       
      In MariaDB 10.3.7 and later, the old_alter_table system
      variable is deprecated. Instead, the alter_algorithm system
      variable defines the default algorithm for ALTER TABLE
      operations.
       
      ALGORITHM=COPY
       
      ALGORITHM=COPY was introduced in MariaDB 10.0 as the name
      for the original ALTER TABLE algorithm.
       
      When ALGORITHM=COPY is set, MariaDB essentially does the
      following operations:
       
      -- Create a temporary table with the new definition
      CREATE TEMPORARY TABLE tmp_tab (
      ...
      );
       
      -- Copy the data from the original table
      INSERT INTO tmp_tab
       SELECT * FROM original_tab;
       
      -- Drop the original table
      DROP TABLE original_tab;
       
      -- Rename the temporary table, so that it replaces the
      original one
      RENAME TABLE tmp_tab TO original_tab;
       
      This algorithm is very inefficient, but it is generic, so it
      works for all storage engines.
       
      If ALGORITHM=COPY is specified, then the copy algorithm will
      be used even if it is not necessary. This can result in a
      lengthy table copy. If multiple ALTER TABLE operations are
      required that each require the table to be rebuilt, then it
      is best to specify all operations in a single ALTER TABLE
      statement, so that the table is only rebuilt once.
       
      ALGORITHM=INPLACE
       
      ALGORITHM=INPLACE was introduced in MariaDB 10.0.
       
      ALGORITHM=COPY can be incredibly slow, because the whole
      table has to be copied and rebuilt. ALGORITHM=INPLACE was
      introduced as a way to avoid this by performing operations
      in-place and avoiding the table copy and rebuild, when
      possible.
       
      When ALGORITHM=INPLACE is set, the underlying storage engine
      uses optimizations to perform the operation while avoiding
      the table copy and rebuild. However, INPLACE is a bit of a
      misnomer, since some operations may still require the table
      to be rebuilt for some storage engines. Regardless, several
      operations can be performed without a full copy of the table
      for some storage engines.
       
      A more accurate name would have been ALGORITHM=ENGINE, where
      ENGINE refers to an \"engine-specific\" algorithm.
       
      If an ALTER TABLE operation supports ALGORITHM=INPLACE, then
      it can be performed using optimizations by the underlying
      storage engine, but it may rebuilt.
       
      See InnoDB Online DDL Operations with ALGORITHM=INPLACE for
      more.
       
      ALGORITHM=NOCOPY
       
      ALGORITHM=NOCOPY was introduced in MariaDB 10.3.7.
       
      ALGORITHM=INPLACE can sometimes be surprisingly slow in
      instances where it has to rebuild the clustered index,
      because when the clustered index has to be rebuilt, the
      whole table has to be rebuilt. ALGORITHM=NOCOPY was
      introduced as a way to avoid this. 
       
      If an ALTER TABLE operation supports ALGORITHM=NOCOPY, then
      it can be performed without rebuilding the clustered index.
       
      If ALGORITHM=NOCOPY is specified for an ALTER TABLE
      operation that does not support ALGORITHM=NOCOPY, then an
      error will be raised. In this case, raising an error is
      preferable, if the alternative is for the operation to
      rebuild the clustered index, and perform unexpectedly
      slowly.
       
      See InnoDB Online DDL Operations with ALGORITHM=NOCOPY for
      more.
       
      ALGORITHM=INSTANT
       
      ALGORITHM=INSTANT was introduced in MariaDB 10.3.7.
       
      ALGORITHM=INPLACE can sometimes be surprisingly slow in
      instances where it has to modify data files.
      ALGORITHM=INSTANT was introduced as a way to avoid this.
       
      If an ALTER TABLE operation supports ALGORITHM=INSTANT, then
      it can be performed without modifying any data files.
       
      If ALGORITHM=INSTANT is specified for an ALTER TABLE
      operation that does not support ALGORITHM=INSTANT, then an
      error will be raised. In this case, raising an error is
      preferable, if the alternative is for the operation to
      modify data files, and perform unexpectedly slowly.
       
      See InnoDB Online DDL Operations with ALGORITHM=INSTANT for
      more.
       
      LOCK
       
      In MariaDB 10.0 and later, the ALTER TABLE statement
      supports the LOCK clause. This clause is one of the clauses
      that is used to implement online DDL. ALTER TABLE supports
      several different locking strategies. A locking strategy can
      be explicitly chosen for an ALTER TABLE operation by setting
      the LOCK clause. The supported values are:
      DEFAULT: Acquire the least restrictive lock on the table
      that is supported for the specific operation. Permit the
      maximum amount of concurrency that is supported for the
      specific operation.
      NONE: Acquire no lock on the table. Permit all concurrent
      DML. If this locking strategy is not permitted for an
      operation, then an error is raised.
      SHARED: Acquire a read lock on the table. Permit read-only
      concurrent DML. If this locking strategy is not permitted
      for an operation, then an error is raised.
      EXCLUSIVE: Acquire a write lock on the table. Do not permit
      concurrent DML.
       
      Different storage engines support different locking
      strategies for different operations. If a specific locking
      strategy is chosen for an ALTER TABLE operation, and that
      table's storage engine does not support that locking
      strategy for that specific operation, then an error will be
      raised.
       
      If the LOCK clause is not explicitly set, then the operation
      uses LOCK=DEFAULT.
       
      ALTER ONLINE TABLE is equivalent to LOCK=NONE. Therefore,
      the ALTER ONLINE TABLE statement can be used to ensure that
      your ALTER TABLE operation allows all concurrent DML.
       
      See InnoDB Online DDL Overview: LOCK for information on how
      the LOCK clause affects InnoDB.
       
      Progress Reporting
       
      MariaDB provides progress reporting for ALTER TABLE
      statement for clients
      that support the new progress reporting protocol. For
      example, if you were using the mysql client, then the
      progress report might look like this::
       
      ALTER TABLE test ENGINE=Aria;
      Stage: 1 of 2 'copy to tmp table' 46% of stage
       
      The progress report is also shown in the output of the SHOW
      PROCESSLIST statement and in the contents of the
      information_schema.PROCESSLIST table.
       
      See Progress Reporting for more information.
       
      Aborting ALTER TABLE Operations
       
      If an ALTER TABLE operation is being performed and the
      connection is killed, the changes will be rolled back in a
      controlled manner. The rollback can be a slow operation as
      the time it takes is relative to how far the operation has
      progressed.
       
      Aborting ALTER TABLE ... ALGORITHM=COPY was made faster by
      removing excessive undo logging (MDEV-11415). This
      significantly shortens the time it takes to abort a running
      ALTER TABLE operation.
       
      Examples
      -------- 
      Adding a new column:
       
      ALTER TABLE t1 ADD x INT;
       
      Dropping a column:
       
      ALTER TABLE t1 DROP x;
       
      Modifying the type of a column:
       
      ALTER TABLE t1 MODIFY x bigint unsigned;
       
      Changing the name and type of a column:
       
      ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;
      ");
       
      ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
      SELECT * FROM t1 WHERE MATCH(c) AGAINST('constraint' WITH QUERY EXPANSION);
       
      # Cleanup
      DROP TABLE t1;
      

      10.3 51e9381d

      mysqld: /data/src/10.3/storage/innobase/btr/btr0sea.cc:1096: void btr_search_drop_page_hash_index(buf_block_t*): Assertion `!btr_search_own_any(RW_LOCK_S)' failed.
      200314  0:10:39 [ERROR] mysqld got signal 6 ;
       
      #7  0x00007f513c036f12 in __GI___assert_fail (assertion=0x5596e352bfd8 "!btr_search_own_any(RW_LOCK_S)", file=0x5596e352bf00 "/data/src/10.3/storage/innobase/btr/btr0sea.cc", line=1096, function=0x5596e352e320 <btr_search_drop_page_hash_index(buf_block_t*)::__PRETTY_FUNCTION__> "void btr_search_drop_page_hash_index(buf_block_t*)") at assert.c:101
      #8  0x00005596e2e4c7a9 in btr_search_drop_page_hash_index (block=0x7f511fa8eb40) at /data/src/10.3/storage/innobase/btr/btr0sea.cc:1096
      #9  0x00005596e2e9a455 in buf_LRU_free_page (bpage=0x7f511fa8eb40, zip=false) at /data/src/10.3/storage/innobase/buf/buf0lru.cc:1775
      #10 0x00005596e2e69f74 in buf_block_try_discard_uncompressed (page_id=...) at /data/src/10.3/storage/innobase/buf/buf0buf.cc:3809
      #11 0x00005596e2e6a4b3 in buf_page_get_zip (page_id=..., page_size=...) at /data/src/10.3/storage/innobase/buf/buf0buf.cc:3889
      #12 0x00005596e2e38728 in btr_copy_zblob_prefix (buf=0x7f50d41600b0 "", len=27220, page_size=..., space_id=17, page_no=6, offset=12) at /data/src/10.3/storage/innobase/btr/btr0cur.cc:8062
      #13 0x00005596e2e38d19 in btr_copy_externally_stored_field_prefix_low (buf=0x7f50d41600b0 "", len=27220, page_size=..., space_id=17, page_no=6, offset=12) at /data/src/10.3/storage/innobase/btr/btr0cur.cc:8186
      #14 0x00005596e2e39058 in btr_copy_externally_stored_field (len=0x7f513887b268, data=0x7f51205b8095 "", page_size=..., local_len=0, heap=0x7f50d4010f20) at /data/src/10.3/storage/innobase/btr/btr0cur.cc:8296
      #15 0x00005596e2e39178 in btr_rec_copy_externally_stored_field (rec=0x7f51205b8080 "", offsets=0x7f513887b310, page_size=..., no=3, len=0x7f513887b268, heap=0x7f50d4010f20) at /data/src/10.3/storage/innobase/btr/btr0cur.cc:8352
      #16 0x00005596e2d37632 in row_sel_fetch_columns (index=0x7f50d40d2c10, rec=0x7f51205b8080 "", offsets=0x7f513887b310, column=0x7f50d40d8878) at /data/src/10.3/storage/innobase/row/row0sel.cc:530
      #17 0x00005596e2d39e75 in row_sel_try_search_shortcut (node=0x7f50d40d8958, plan=0x7f50d40d8d58, mtr=0x7f513887b8b0) at /data/src/10.3/storage/innobase/row/row0sel.cc:1539
      #18 0x00005596e2d3a29e in row_sel (node=0x7f50d40d8958, thr=0x7f50d40d9a38) at /data/src/10.3/storage/innobase/row/row0sel.cc:1650
      #19 0x00005596e2d3bbdf in row_sel_step (thr=0x7f50d40d9a38) at /data/src/10.3/storage/innobase/row/row0sel.cc:2341
      #20 0x00005596e2c8fbc3 in que_thr_step (thr=0x7f50d40d9a38) at /data/src/10.3/storage/innobase/que/que0que.cc:1013
      #21 0x00005596e2c90009 in que_run_threads_low (thr=0x7f50d40d9a38) at /data/src/10.3/storage/innobase/que/que0que.cc:1099
      #22 0x00005596e2c90259 in que_run_threads (thr=0x7f50d40d9a38) at /data/src/10.3/storage/innobase/que/que0que.cc:1139
      #23 0x00005596e2f7141d in fts_eval_sql (trx=0x7f5138b252a0, graph=0x7f50d40d9978) at /data/src/10.3/storage/innobase/fts/fts0sql.cc:218
      #24 0x00005596e2f53766 in fts_doc_fetch_by_doc_id (get_doc=0x0, doc_id=1, index_to_use=0x7f50d40d25b0, option=1, callback=0x5596e2f51b9e <fts_query_expansion_fetch_doc(void*, void*)>, arg=0x7f513887bfe0) at /data/src/10.3/storage/innobase/fts/fts0fts.cc:3827
      #25 0x00005596e2f6eddd in fts_expand_query (index=0x7f50d40d25b0, query=0x7f513887c0d0) at /data/src/10.3/storage/innobase/fts/fts0que.cc:4292
      #26 0x00005596e2f6e49c in fts_query (trx=0x7f5138b230f0, index=0x7f50d40d25b0, flags=6, query_str=0x7f50d4013020 "constraint", query_len=10, result=0x7f513887c3e0) at /data/src/10.3/storage/innobase/fts/fts0que.cc:4078
      #27 0x00005596e2b66289 in ha_innobase::ft_init_ext (this=0x7f50d40b4988, flags=6, keynr=1, key=0x7f50d4013060) at /data/src/10.3/storage/innobase/handler/ha_innodb.cc:9939
      #28 0x00005596e29caa66 in Item_func_match::init_search (this=0x7f50d40130e8, thd=0x7f50d4000af0, no_order=false) at /data/src/10.3/sql/item_func.cc:6008
      #29 0x00005596e2591f74 in init_ftfuncs (thd=0x7f50d4000af0, select_lex=0x7f50d4005140, no_order=false) at /data/src/10.3/sql/sql_base.cc:8791
      #30 0x00005596e26623e3 in JOIN::optimize_stage2 (this=0x7f50d4013320) at /data/src/10.3/sql/sql_select.cc:2458
      #31 0x00005596e266075e in JOIN::optimize_inner (this=0x7f50d4013320) at /data/src/10.3/sql/sql_select.cc:1968
      #32 0x00005596e265eb91 in JOIN::optimize (this=0x7f50d4013320) at /data/src/10.3/sql/sql_select.cc:1488
      #33 0x00005596e2668c18 in mysql_select (thd=0x7f50d4000af0, tables=0x7f50d4012870, wild_num=1, fields=..., conds=0x7f50d40130e8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f50d40132f8, unit=0x7f50d40049b8, select_lex=0x7f50d4005140) at /data/src/10.3/sql/sql_select.cc:4283
      #34 0x00005596e265a358 in handle_select (thd=0x7f50d4000af0, lex=0x7f50d40048f8, result=0x7f50d40132f8, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:370
      #35 0x00005596e2621d3f in execute_sqlcom_select (thd=0x7f50d4000af0, all_tables=0x7f50d4012870) at /data/src/10.3/sql/sql_parse.cc:6293
      #36 0x00005596e2618763 in mysql_execute_command (thd=0x7f50d4000af0) at /data/src/10.3/sql/sql_parse.cc:3820
      #37 0x00005596e2626037 in mysql_parse (thd=0x7f50d4000af0, rawbuf=0x7f50d4012608 "SELECT * FROM t1 WHERE MATCH(c) AGAINST('constraint' WITH QUERY EXPANSION)", length=74, parser_state=0x7f513887f630, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7817
      #38 0x00005596e2612bc3 in dispatch_command (command=COM_QUERY, thd=0x7f50d4000af0, packet=0x7f50d402f001 "SELECT * FROM t1 WHERE MATCH(c) AGAINST('constraint' WITH QUERY EXPANSION)", packet_length=74, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1856
      #39 0x00005596e261150b in do_command (thd=0x7f50d4000af0) at /data/src/10.3/sql/sql_parse.cc:1401
      #40 0x00005596e2789084 in do_handle_one_connection (connect=0x5596e6790a20) at /data/src/10.3/sql/sql_connect.cc:1403
      #41 0x00005596e2788de6 in handle_one_connection (arg=0x5596e6790a20) at /data/src/10.3/sql/sql_connect.cc:1308
      #42 0x00007f513dfbf4a4 in start_thread (arg=0x7f5138880700) at pthread_create.c:456
      #43 0x00007f513c0f3d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97
      

      Reproducible on 10.3-10.5.
      Not reproducible on 10.2.
      No obvious problem on a non-debug build.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: