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

Unable to convert Aria Tables to MyISAM after specifying TRANSACTIONAL=0

    XMLWordPrintable

Details

    Description

      TRANSACTIONAL flag is not well managed in ALTER TABLE.

      When an Aria table is created without specifying TRANSACTIONAL, it can be converted to MyISAM with 'ALTER TABLE ENGINE_MyISAM'.

      When an Aria table is created and subsequently altered with 'ALTER TABLE X TRANSACTIONAL=1; ALTER TABLE X TRANSACTIONAL=0' it can no longer be converted to MyISAM at all because of the orphaned TRANSACTIONAL attribute. Instead, the server produces this (incorrect) message:

      ERROR 1478 (HY000): Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1'
      

      To be clear - once the TRANSACTIONAL flag is explicitly started and set back to 0, this message failing the ALTER TABLE and incorrectly claiming TRANSACTIONAL=1 is the result.

      What this means to users is that altering tables from MyISAM to Aria not only carries a 40% performance penalty, but is irreversible after using the TRANSACTIONAL flag.

      The flag is harmless for other storage engines, producing the warning mentioned in MDEV-8733, but they make going back to MyISAM impossible without a full table migration in this case. 10.4 shows a warning instead of an error and completes the operation.

      MariaDB [t34628]> create table taria3(  id int(10) unsigned NOT NULL AUTO_INCREMENT,
          ->   `modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
          ->   `val` int(10) unsigned DEFAULT NULL,
          ->   `blah` varchar(511) DEFAULT NULL,
          ->   PRIMARY KEY (`id`) ) Engine=MyISAM;
      Query OK, 0 rows affected (0.03 sec)
       
      MariaDB [t34628]> alter table taria3 engine=aria, transactional=0;
      Query OK, 0 rows affected (0.07 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [t34628]> alter table taria3 engine=myisam;
      ERROR 1478 (HY000): Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1'
      MariaDB [t34628]> show warnings;
      +-------+------+------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                            |
      +-------+------+------------------------------------------------------------------------------------+
      | Error | 1478 | Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1' |
      +-------+------+------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [t34628]> show create table taria3\G
      *************************** 1. row ***************************
             Table: taria3
      Create Table: CREATE TABLE `taria3` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        `val` int(10) unsigned DEFAULT NULL,
        `blah` varchar(511) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=0
      1 row in set (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              juan.vera Juan
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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