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

CREATE FULLTEXT INDEX unnecessarily writes undo log




      In the third part of MDEV-25506, any DDL operations that will delete .ibd files will be rewritten. The design is such that MDL_EXCLUSIVE is supposed to prevent background access to tables that are about to be deleted. For the InnoDB internal tables that implement FULLTEXT INDEX, we tried to jump through some hoops to acquire MDL on the main table name. This will work for the #sql-backup table, but not for the #sql-alter table. (And MDEV-11415 was not really fixed for the internal tables related to fulltext indexes).
      I observed the following crash:

      CURRENT_TEST: innodb_fts.fulltext
      mysqltest: At line 729: query 'CREATE FULLTEXT INDEX idx1 ON t1 (title,body)' failed with wrong errno <Unknown> (2013): 'Lost connection to server during query', instead of ER_DUP_ENTRY (1062)...
      2021-06-03 14:41:15 0x7fcc1923c700  InnoDB: Assertion failure in file /mariadb/10.6/storage/innobase/dict/dict0dict.cc line 1893
      InnoDB: Failing assertion: table->get_ref_count() == 0

      The holder of the reference is about to release the reference as well as MDL_SHARED on test.t1:

      #6  0x00005580aaac4ff5 in dict_sys_t::mutex_lock (this=<optimized out>) at /mariadb/10.6/storage/innobase/dict/dict0dict.cc:1064
      #7  0x00005580aaac4b0a in dict_table_close (table=0x7fca877d0bc8, dict_locked=false, try_drop=false, thd=0x7fcb04001158, mdl=0x7fcb040286b0) at /mariadb/10.6/storage/innobase/dict/dict0dict.cc:325
      #8  0x00005580aa98cf1d in purge_node_t::close_table (this=this@entry=0x5580ae1e9668) at /mariadb/10.6/storage/innobase/include/row0purge.h:229
      #9  0x00005580aa98d422 in purge_node_t::end (this=0x5580ae1e9668) at /mariadb/10.6/storage/innobase/include/row0purge.h:259
      #10 0x00005580aa9856a7 in row_purge_step (thr=thr@entry=0x5580ae1e9478) at /mariadb/10.6/storage/innobase/row/row0purge.cc:1335

      But, the crashing thread is not holding MDL_EXCLUSIVE because it is executing a rollback. Hence, concurrent access from the purge thread was not blocked:

      #5  0x00005580aaa1ad94 in ut_dbg_assertion_failed (expr=0x5580aaed071e "table->get_ref_count() == 0", file=<optimized out>, line=<optimized out>, line@entry=1893) at /mariadb/10.6/storage/innobase/ut/ut0dbg.cc:60
      #6  0x00005580aaac91d9 in dict_sys_t::remove (this=0x5580ab5455c0 <dict_sys>, table=0x7fca877d0bc8, lru=false, keep=false) at /mariadb/10.6/storage/innobase/dict/dict0dict.cc:1893
      #7  0x00005580aaaff4db in trx_t::commit (this=0x7fcc1a4f0290, deleted=std::vector of length 6, capacity 8 = {{m_file = 56, m_psi = 0x7fcc1bde0400}, {m_file = 55, m_psi = 0x7fcc1bde0140}, {m_file = 54, m_psi = 0x7fcc1bddfe80}, {m_file = 53, m_psi = 0x7fcc1bddfbc0}, {m_file = 52, m_psi = 0x7fcc1bddf900}, {m_file = 51, m_psi = 0x7fcc1bddf640}}) at /mariadb/10.6/storage/innobase/dict/drop.cc:241
      #8  0x00005580aa839e15 in commit_unlock_and_unlink (trx=0x7fcc1a4f0290) at /mariadb/10.6/storage/innobase/handler/handler0alter.cc:6106
      #9  rollback_inplace_alter_table (ha_alter_info=<optimized out>, ha_alter_info@entry=0x7fcc19238f80, table=0x7fca84b6e828, prebuilt=0x7fca877c4b88) at /mariadb/10.6/storage/innobase/handler/handler0alter.cc:8742
      #10 0x00005580aa82eb64 in ha_innobase::commit_inplace_alter_table (this=0x7fca84c31420, altered_table=0x7fcc19238610, ha_alter_info=0x7fcc19238f80, commit=false) at /mariadb/10.6/storage/innobase/handler/handler0alter.cc:10771
      #11 0x00005580aa307d8e in mysql_inplace_alter_table (thd=0x7fca84000d48, table_list=0x7fca84016a48, table=0x7fca84b6e828, altered_table=0x7fcc19238610, ha_alter_info=0x7fcc19238f80, target_mdl_request=<optimized out>, ddl_log_state=0x7fcc192385a0, trigger_param=0x7fcc19239c98, alter_ctx=0x7fcc19239040) at /mariadb/10.6/sql/sql_table.cc:7538

      Purge was operating on the table FTS_0000000000000258_CONFIG, and it was holding MDL on test.t1 because table ID 0x258 belongs to that name.

      Later, a different solution was implemented as part of that development: If a transaction would drop any FTS_ tables, using a new primitive purge_sys.stop_FTS() we will ensure that purge will not be running (on any table) until the transaction has been committed.

      Could we fix fts_create_common_tables() so that it never invokes fts_drop_common_tables(), but simply empties the tables if they exist? That would remove the need to drop those common tables. (Note: this suggestion conflicts with the idea below.)

      Even better would be to ensure that DDL operations will never write undo log to the FTS_ tables (extending what was implemented in MDEV-11415). We would still have to protect ALGORITHM=COPY and TRUNCATE TABLE operation by MDL (maybe by having ha_innobase::delete_table() and ha_innobase::truncate() acquire MDL on each to-be-dropped FTS_ table name), because purge may be processing undo log records that had been previously written for the old table.


        Issue Links



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



                Git Integration

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