MySQL Bugs: #45829: CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW

Bug #45829	CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, does nothing
Submitted:	29 Jun 2009 15:58	Modified:	1 Sep 2009 18:18
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1-bzr	OS:	Any
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
In bzr revision sp1r-monty@mysql.com/narttu.mysql.fi-20071011150740-05766 ,
new keywords for CREATE TABLE have been added:
CREATE TABLE ... TRANSACTIONAL=0|1 PAGE_CHECKSUM=0|1
The intention of TRANSACTIONAL=0|1 is to tell the engine to create a transactional or non-transactional table (for example, a non-transactional table does not have crash recovery). The intention of PAGE_CHECKSUM=0|1 is to tell the engine to maintain a checksum for each page (data or index page).
Those keywords are not documented in the manual.
The supplied information (0 or 1) is stored by CREATE TABLE in the frm file.
If such information was supplied, it is reproduced in the output of SHOW CREATE TABLE.
Given that MySQL releases of 5.1 don't have any engine which actually can disable/enable transactionality or page checksums (for example InnoDB is always transactional, MyISAM is always not), the presence of those keywords in 5.1 has no benefit. It has the drawback of letting the user intuitively believe that she/he can influence transactionality or page checksums of a table, which is wrong.

How to repeat:
Server version: 5.1.36-valgrind-max-debug-log Source distribution

mysql> drop table if exists t; create table t (a int) engine=myisam page_checksum=1 transactional=1;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t; create table t (a int) engine=innodb page_checksum=0 transactional=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

No warnings. So we see that creating a myisam table with transactions and page checksums is accepted (though internally myisam doesn't support this), and creating an innodb table without transactions and page checksums is accepted (though internally innodb doesn't support this).

Suggested fix:
Undocumented keywords which do nothing should be removed from the syntax.
The reserved place (byte) in the frm should stay reserved, for upgrade compatibility reasons (we cannot reuse this byte for some other purpose or we wouldn't be able to correctly read old 5.1 frm files).
The keywords should not be printed by SHOW CREATE TABLE.
Note that if one previously managed to know about those undocumented keywords and used them to create a table, then does a mysqldump of the table, and injects the output into a MySQL server where the keywords have been removed from the syntax, then that will fail with a syntax error. The user will have to manually remove keywords (a "sed" job would do) from the output, which is fine, as undocumented features have no guarantee from us. Anyway as the keywords are undocumented this is an unlikely scenario.
The removal should ideally happen in 5.1; the longer we let keywords in 5.1, the more people can discover and use them, the more we have to support them in the future. The removal should anyway be propagated to Azalea.
When we have an engine which allows turning transactionality and page checksum on/off, we can add the keywords again (that's the case of Maria in 6.0). We will then have to solve the problem of: how do we properly warn the user when she/he uses the keywords on an engine which doesn't support them? In 6.0, a warning is raised for TRANSACTIONAL= if the engine doesn't support COMMIT, which is incomplete as:

Server version: 6.0.12-alpha-valgrind-max-debug-log-perfschema Source distribution

mysql> drop table if exists t; create table t (a int) engine=myisam transactional=0;

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

mysql> drop table if exists t; create table t (a int) engine=myisam transactional=1;

Warning (Code 1478): Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1'
mysql> drop table if exists t; create table t (a int) engine=innodb transactional=1;

mysql> drop table if exists t; create table t (a int) engine=innodb transactional=0;

Which means:
- TRANSACTIONAL=0 should be fully ok for myisam (as this engine is not transactional) but gives a warning
- TRANSACTIONAL=0 should not be allowed for innodb (as innodb does not allow turning off transactions) but it does not give a warning.
And there are never warnings for PAGE_CHECKSUM:
mysql> drop table if exists t; create table t (a int) engine=myisam page_checksum=1;
gives no warnings even though myisam does not feature page checksums.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/78122

3007 Guilhem Bichot	2009-07-07
      Bug#45829 "CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, does nothing":
      those keywords do nothing in 5.1 (they were meant for 6.0) so they are removed from the syntax.
     @ mysql-test/r/create.result
        test that syntax is not accepted
     @ mysql-test/t/create.test
        test that syntax is not accepted
     @ sql/handler.cc
        remove ROW_FORMAT=PAGE
     @ sql/handler.h
        Mark unused objects, but I don't remove them by fear of breaking any plugin which includes this file
        (see also table.h)
     @ sql/lex.h
        removing syntax
     @ sql/sql_show.cc
        removing output of noise keywords in SHOW CREATE TABLE and INFORMATION_SCHEMA.TABLES
     @ sql/sql_table.cc
        removing TRANSACTIONAL
     @ sql/sql_yacc.yy
        removing syntax
     @ sql/table.cc
        removing TRANSACTIONAL, PAGE_CHECKSUM. Their place in the frm file is not reclaimed,
        for compatibility with older 5.1.
     @ sql/table.h
        Mark unused objects, but I don't remove them by fear of breaking any plugin which includes this file
        (and there are several engines which use the content TABLE_SHARE and thus rely on a certain binary
        layout of this structure).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/79560

3046 Guilhem Bichot	2009-07-29
      Bug#45829 "CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, does nothing":
      those keywords do nothing in 5.1 (they are meant for future versions, for example featuring the Maria engine)
      so they are here removed from the syntax. Adding those keywords to future versions when needed is:
      - WL#5034 "Add TRANSACTIONA=0|1 and PAGE_CHECKSUM=0|1 clauses to CREATE TABLE"
      - WL#5037 "New ROW_FORMAT value for CREATE TABLE: PAGE"
     @ mysql-test/r/create.result
        test that syntax is not accepted
     @ mysql-test/t/create.test
        test that syntax is not accepted
     @ sql/handler.cc
        remove ROW_FORMAT=PAGE
     @ sql/handler.h
        Mark unused objects, but I don't remove them by fear of breaking any plugin which includes this file
        (see also table.h)
     @ sql/lex.h
        removing syntax
     @ sql/sql_show.cc
        removing output of noise keywords in SHOW CREATE TABLE and INFORMATION_SCHEMA.TABLES
     @ sql/sql_table.cc
        removing TRANSACTIONAL
     @ sql/sql_yacc.yy
        removing syntax
     @ sql/table.cc
        removing TRANSACTIONAL, PAGE_CHECKSUM. Their place in the frm file is not reclaimed,
        for compatibility with older 5.1.
     @ sql/table.h
        Mark unused objects, but I don't remove them by fear of breaking any plugin which includes this file
        (and there are several engines which use the content TABLE_SHARE and thus rely on a certain binary
        layout of this structure).

queued to 5.1-bugteam and mysql-pe.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:guilhem@mysql.com-20090729102507-p89mxntsxlry54wc) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/80660

3064 Konstantin Osipov	2009-08-12
      A follow up patch for Bug#45829 "CREATE TABLE TRANSACTIONAL 
      PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, does nothing"
      Remove unused code that would lead to warnings when compiling
      sql_yacc.yy.
     @ sql/handler.h
        Remove unused defines.
     @ sql/sql_yacc.yy
        Remove unused grammar.
     @ sql/table.h
        Remove unused TABLE members.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/80672

3065 Konstantin Osipov	2009-08-12
      A follow up patch for the follow up patch for Bug#45829 
      "CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, 
      does nothing".
      
      Put back stubs for members of structures that are shared between
      sql/ and pluggable storage engines. to not break ABI unnecessarily.
      To be NULL-merged into 5.4, where we do break the ABI already.

Noted in 5.1.38, 5.4.4 changelogs.

The undocumented TRANSACTIONAL and PAGE_CHECKSUM keywords were 
removed from the grammar.

Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:mattias.jonsson@sun.com-20090812165333-tmbq81se96tosyq6) (merge vers: 5.1.38) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

This fix now has been pushed into 5.4.2.