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

enforce_storage_engine has an effect on child global temporary tables

    XMLWordPrintable

Details

    • Not for Release Notes
    • Q4/2025 Server Maintenance

    Description

      Using enforce_storage_engine before first DML on a global temporary table results in using that engine during an implicit child table creation

      set sql_mode='';
      		 
      create global temporary table t (t text) engine=myisam;
      		 
      set session enforce_storage_engine=innodb;
      		 
      insert t values ('qwe');

      output

      		 
      Warnings:
      		 
      Note    1266    Using storage engine InnoDB for table 't'

      This is an interesting property, but also may result in various problems, like with MEMORY:

      enforce_storage_engine=memory

      		 
      mysqltest: At line 9: query 'insert t values ('qwe')' failed: ER_TABLE_CANT_HANDLE_BLOB (1163): Storage engine MEMORY doesn't support BLOB/TEXT columns

      This is not expected for the user, as the implicit child table should be transparent for them.
      We also don't provide tools for observing the child table state by the same reason.

      Original description

      With a regular TEMPORARY TABLE:

      SET sql_mode='';
      		 
      CREATE TEMPORARY TABLE t1 (c INT);
      		 
      SET SESSION enforce_storage_engine=MEMORY;
      		 
      BINLOG ' SOgWTg8BAAAAbgAAAHIAAAAAAAQANS42LjMtbTUtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABI6BZOEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAAVAYI8=';
      		 
      BINLOG 'wlZOTxMBAAAAKgAAADwCAAAAACkAAAAAAAEABHRlc3QAAnQxAAIDAwAC wlZOTxcBAAAAJgAAAGICAAAAACkAAAAAAAEAAv/8AgAAAAgAAAA=';

      The outcome on the final BINLOG statement is:

      MDEV-35915-2 CS 12.0.1 5d52514d536e9d141963f956408ac0efb4958a74 (Optimized, Clang 21.1.0-20250811) Build 06/09/2025

      		 
      ERROR 1146 (42S02): Table 'test.t1' doesn't exist

      Though note t1 gets created (as TEMPORARY). With GLOBAL TEMPORARY TABLE:

      SET sql_mode='';
      		 
      CREATE GLOBAL TEMPORARY TABLE t1 (c INT);
      		 
      SET SESSION enforce_storage_engine=MEMORY;
      		 
      BINLOG ' SOgWTg8BAAAAbgAAAHIAAAAAAAQANS42LjMtbTUtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABI6BZOEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAAVAYI8=';
      		 
      BINLOG 'wlZOTxMBAAAAKgAAADwCAAAAACkAAAAAAAEABHRlc3QAAnQxAAIDAwAC wlZOTxcBAAAAJgAAAGICAAAAACkAAAAAAAEAAv/8AgAAAAgAAAA=';
      		 
      SHOW WARNINGS;

      We see instead that the outcome on the final BINLOG statement and the subsequent SHOW WARNINGS is:

      MDEV-35915-2 CS 12.0.1 5d52514d536e9d141963f956408ac0efb4958a74 (Optimized, Clang 21.1.0-20250811) Build 06/09/2025

      		 
      Query OK, 0 rows affected, 1 warning (0.000 sec)
      		 
       
      		 
      12.0.1-opt>SHOW WARNINGS;
      		 
      +-------+------+--------------------------------------------+
      		 
      | Level | Code | Message                                    |
      		 
      +-------+------+--------------------------------------------+
      		 
      | Note  | 1266 | Using storage engine MEMORY for table 't1' |
      		 
      +-------+------+--------------------------------------------+

      Which is not consistent.

      Additionaly, if we instead use t TEXT for the table, with a regular TEMPORARY TABLE:

      SET sql_mode='';
      		 
      CREATE TEMPORARY TABLE t1 (t TEXT);
      		 
      SET SESSION enforce_storage_engine=MEMORY;
      		 
      BINLOG ' SOgWTg8BAAAAbgAAAHIAAAAAAAQANS42LjMtbTUtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABI6BZOEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAAVAYI8=';
      		 
      BINLOG 'wlZOTxMBAAAAKgAAADwCAAAAACkAAAAAAAEABHRlc3QAAnQxAAIDAwAC wlZOTxcBAAAAJgAAAGICAAAAACkAAAAAAAEAAv/8AgAAAAgAAAA=';

      We see:

      MDEV-35915-2 CS 12.0.1 5d52514d536e9d141963f956408ac0efb4958a74 (Optimized, Clang 21.1.0-20250811) Build 06/09/2025

      		 
      ERROR 1146 (42S02): Table 'test.t1' doesn't exist

      Versus with GLOBAL TEMPORARY TABLE:

      SET sql_mode='';
      		 
      CREATE GLOBAL TEMPORARY TABLE t1 (t TEXT);
      		 
      SET SESSION enforce_storage_engine=MEMORY;
      		 
      BINLOG ' SOgWTg8BAAAAbgAAAHIAAAAAAAQANS42LjMtbTUtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABI6BZOEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAAVAYI8=';
      		 
      BINLOG 'wlZOTxMBAAAAKgAAADwCAAAAACkAAAAAAAEABHRlc3QAAnQxAAIDAwAC wlZOTxcBAAAAJgAAAGICAAAAACkAAAAAAAEAAv/8AgAAAAgAAAA=';

      We see instead:

      MDEV-35915-2 CS 12.0.1 5d52514d536e9d141963f956408ac0efb4958a74 (Optimized, Clang 21.1.0-20250811) Build 06/09/2025

      		 
      ERROR 1163 (42000): Storage engine MEMORY doesn't support BLOB/TEXT columns

      It is likely that these inconsistencies in handling BINLOG commands may cause issues with replication.
      Please also clarify how GLOBAL TEMPORARY TABLES should work here. Thank you.

      Attachments

        Issue Links

          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-35915 Implement Global temporary tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-37720 use-after-free on CREATE OR REPLACE GTT under LOCK TABLES and pseudo_slave_mode

          • Critical - Crashes, loss of data, severe memory leak.
          • Confirmed

          Activity

            People

              nikitamalyavin Nikita Malyavin
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: