Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
10.0.12
-
None
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.
Thanks for the report.
It looks like a duplicate of
MDEV-8065which, in turn, is another variation ofMDEV-6581– similar story, table gets created but not populated. An interesting specifics here is that with CREATE .. AS SELECT it happens even without binary logging. I have added a note about it toMDEV-6581and link to this bug, which is now being closed as a duplicate. You can track further work on the issue in scope ofMDEV-6581.