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

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                crystall Crystal Lemire
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: