MySQL Bugs: #34395: MyISAM confused with Maria

Bug #34395	MyISAM confused with Maria
Submitted:	7 Feb 2008 20:09	Modified:	26 Feb 2008 15:36
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Maria storage engine	Severity:	S3 (Non-critical)
Version:	5.1.23 Maria preview	OS:	Any
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
With this build MyISAM tables cna be defined with the parameter transactional = 0|1.

Such properties for the Maria engine should not apply to MyISAM!

How this in every respect affects the table behaviour I don't know!

How to repeat:
CREATE TABLE `myisam_nontr` (                      
          `id` bigint(20) NOT NULL AUTO_INCREMENT,  
          `a` varchar(50) NOT NULL,                 
          `b` varchar(50) NOT NULL,                 
          PRIMARY KEY (`id`)                        
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1 transactional = 0;

show create table `myisam_nontr`;

/*
CREATE TABLE `myisam_nontr` (                           
                `id` bigint(20) NOT NULL AUTO_INCREMENT,              
                `a` varchar(50) NOT NULL,                             
                `b` varchar(50) NOT NULL,                             
                PRIMARY KEY (`id`)                                    
              ) ENGINE=MyISAM DEFAULT CHARSET=latin1 TRANSACTIONAL=0
*/

CREATE TABLE `myisam_tr` (                      
          `id` bigint(20) NOT NULL AUTO_INCREMENT,  
          `a` varchar(50) NOT NULL,                 
          `b` varchar(50) NOT NULL,                 
          PRIMARY KEY (`id`)                        
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1 transactional = 1;

show create table `myisam_tr`;

/*
CREATE TABLE `myisam_tr` (                              
             `id` bigint(20) NOT NULL AUTO_INCREMENT,              
             `a` varchar(50) NOT NULL,                             
             `b` varchar(50) NOT NULL,                             
             PRIMARY KEY (`id`)                                    
           ) ENGINE=MyISAM DEFAULT CHARSET=latin1 TRANSACTIONAL=1
*/

Suggested fix:
This build is of course first of all intended for previewing the maria ENGINE.  'Side-effects' on MyISAM is not the most important thing here.  I know and agree.

But I still believe that this shows a structural issue with the code.  
Code review recommended!

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

I fixed it by giving a warning if you use TRANSACTIONAL=1 for a table engine that doesn't support it.

We give a warning instead of an error as we want to allow one to ALTER a table freely from one engine to another and back without loosing context. This is useful for testing, for trying out different engines and for setups where you have different engines for a master and a slave.

Fix will be in 5.1.24-Maria

That sounds OK to me!

Just one comment: once Maria will ship with servers along with (other) transactional engines (InnoDB, Falcon) also "... transactional = 0" should (for consistency) return a warning for those!

We need to preserve the keywords used for an engine to allow you to, in most cases, alter a table back and forth between table engines.

The keyword 'transactional=1' for MyISAM is a hint that is has the right to ignore. However if you do an alter table from MyISAM to Maria, then the Maria table will be transactional if you used the transactional=1 hint.

This is critical in a master-slave setup where you use MyISAM (or some other non transactional engine) on the slave.  In this case you don't want to get error on the slave because you use some not supported keyword, like transactional=1, for the master.

For example, on master
CREATE TABLE t1 (a int) engine=maria transactional=1;
on slave, if Maria is not enabled and MyISAM is default engine, this is executed as:
CREATE TABLE t1 (a int) engine=myisam transactional=1

This is a not that uncommon setup and we have to support it.

You also should be able to do:

CREATE TABLE t1 (a int) engine=maria transactional=1;
ALTER TABLE t1 engine=myisam;
ALTER TABLE t1 engine=maria;

without getting any errors.

Yes, I understand!  I did not express disagreement in my last post!

But then also

CREATE TABLE t1 (a int) engine=maria transactional=0;
ALTER TABLE t1 engine=falcon; -- or innodb
ALTER TABLE t1 engine=maria;

should be functional (with a warning, however) once Maria is shipped with those other transactional engines.

A note has been added to the 5.1.24-maria changelog: 

When creating tables not using the MARIA engine, it would be possible to create a table using the TRANSACTIONAL option, even though this option is not supported by non-MARIA tables. A warning will now be produced when using these options, but the option will still be recorded within the options for the table to allow for correct modification during ALTER TABLE operations.

Remains Mr Laursen's request that a warning is issued if the user types TRANSACTIONAL=0 for a table which does not support disabling transactions.
It is a bit complicated to do (storage engines don't tell the SQL layer if they can disable transactions or not). We'll have to think more about it.

Bug #36104 was marked as duplicated of this one.