MySQL Bugs: #34393: all table engines treated like transactional

Bug #34393	all table engines treated like transactional
Submitted:	7 Feb 2008 19:50	Modified:	8 Feb 2008 7:36
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Maria storage engine	Severity:	S2 (Serious)
Version:	5.1.23 maria preview	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
With this specific build both MyISAM tables and non-transactional Maria tables are treated like transactional by the ''strict_trans_tables' SQL_mode.

This

*************************************************************
CREATE TABLE `mysql` (                      
          `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 ;

insert into `mysql` (id,a) values (1,'a');
*************************************************************

... should return an error for transactional tables and a warning (only) for non-transactional tables (because `b` is defined NOT NULL with no default).  But all engines seem to be handled like transactional tables.

How to repeat:
set @@sql_mode = 'strict_trans_tables';

CREATE TABLE `mysql` (                      
          `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 ;

insert into `mysql` (id,a) values (1,'a');

/*
Error Code : 1364
Field 'b' doesn't have a default value
(0 ms taken)*/

CREATE TABLE `maria_nont` (                      
          `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;

insert into `maria_nont` (id,a) values (1,'a');

/*
Error Code : 1364
Field 'b' doesn't have a default value
(0 ms taken)*/

CREATE TABLE `maria_t` (                      
          `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;

insert into `maria_t` (id,a) values (1,'a');

/* now this is OK

Error Code : 1364
Field 'b' doesn't have a default value
(0 ms taken) */

compare this (what is also OK):

set @@sql_mode = '';

CREATE TABLE `mysql2` (                      
          `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 ;

insert into `mysql2` (id,a) values (1,'a');

/*
(1 row(s)affected)
(0 ms taken)
*/

CREATE TABLE `maria_nont2` (                      
          `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;

insert into `maria_nont2` (id,a) values (1,'a');

/*
(1 row(s)affected)
(0 ms taken)*/

CREATE TABLE `maria_t2` (                      
          `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;

insert into `maria_t` (id,a) values (1,'a');

/(1 row(s)affected)
(0 ms taken)*/

Suggested fix:
start reading the manual, next read the manual and finally read the manual again :-)

(asking excuse for a silly reply to a silly question!)

my mistake.  Copied from the wrong connection window!  Please disregard the example above and consider

set @@sql_mode = 'strict_trans_tables';

CREATE TABLE `myisam` (                      
          `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 ;

insert into `myisam` (id,a) values (1,'a');

/*
Error Code : 1364
Field 'b' doesn't have a default value
(0 ms taken)
*/

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

insert into `maria_nont` (id,a) values (1,'a');

/*
(Error Code : 1364
Field 'b' doesn't have a default value
(0 ms taken)*/

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

insert into `maria_t` (id,a) values (1,'a');

/*Error Code : 1364
Field 'b' doesn't have a default value
(0 ms taken)*/

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html:

STRICT_TRANS_TABLES

If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section.