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

create temporary table ... select ... from innodb_table doesn't work on read-only instances




      When mysqld has read_only=1 and innodb is used as the default storage engine, execution of "CREATE TEMPORARY TABLE temp_table SELECT * FROM permanent_table LIMIT 10" fails with error "The MySQL server is running with the --read-only option so it cannot execute this statement". Despite this error, the temporary table tt is created, although it is not populated with data.

      The statement works fine if table permanent_table's storage engine is MYISAM. Also, the statement "CREATE TEMPORARY TABLE temp_table LIKE permanent_table; INSERT INTO temp_table SELECT * FROM permanent_table LIMIT 10;" works fine.

      Tracing through the 10.0 branch, when ha_commit_trans is called (sql/handler.cc:1338), ha_check_and_coalesce_trx_read_only returns 1 for rw_ha_count. In ha_check_and_coalesce_trx_read_only (sql/handler.cc:1201), the innodb handler info object has the transaction marked as read_write. When I set a watchpoint on the internal ha_info m_flags to see when they are modified from read-only to read-write, I see that handler::ha_create_partitioning_metadata (sql/handler.cc:4302) is called (presumably for creation of the temporary table), which in turn calls mark_trx_read_write(). In this invocation of mark_trx_read_write (handler.cc:3904), the table_share variable is not yet populated, so the function cannot detect that the newly created table is a temporary table and exempt this transaction from being marked as read-write.


        Issue Links



              Unassigned Unassigned
              crystall Crystal Lemire
              0 Vote for this issue
              3 Start watching this issue



                Git Integration

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