MySQL Bugs: #39315: STORAGE options incompletely documented

Bug #39315	STORAGE options incompletely documented
Submitted:	8 Sep 2008 14:59	Modified:	16 Sep 2008 11:13
Reporter:	Jon Stephens	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1/NDB 6.2+	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	ALTER TABLE, CREATE TABLE, disk, Memory, ndb, storage, Tablespace

Description:
The "CREATE TABLE Syntax" page

  http://dev.mysql.com/doc/refman/5.1/en/create-table.html

only mentions

   STORAGE {DISK|MEMORY}

as column option and

   TABLESPACE tablespace_name STORAGE DISK

as table option.

Looking at the parser rules in sql/sql_yacc.yy i can see
that DEFAULT is also a valid STORAGE type and that
TABLESPACE and STORAGE are actually independent options
even though STORAGE DISK requires a TABLESPACE setting
(for obvious reasons).

So the column options should actually read

  STORAGE {DISK|MEMORY|DEFAULT}

and the table options should be split into

  |  STORAGE {DISK|MEMORY|DEFAULT}
  |  TABLESPACE tablespace_name

and sorted in alphabetically like the rest of
the table options.

Later in the text the fact that STORAGE DISK
requires a TABLESPACE assignment should be mentioned.

On the "ALTER TABLE Syntax" page STORAGE and TABLESPACE
options are not documented at all, e.g. it doesn't tell
that a cluster tables storage type may be changed using

  ALTER TABLE table_name TABLESPACE tablespace_name STORAGE DISK;

  ALTER TABLE table_name STORAGE DISK TABLESPACE tablespace_name;

  ALTER TABLE table_name STORAGE MEMORY;

or that one can move a disk based table to a different
tablespace using

  ALTER TABLE table_name TABLESPACE tablespace_name; 

How to repeat:
see Description

Suggested fix:
see above

NOTES:

1.  For CREATE TABLE, I've changed table option to:

        TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

    since you can't have STORAGE without TABLESPACE.

2. This is interesting:

mysql> SHOW VARIABLES LIKE '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  | 
| version                 | 5.1.28-ndb-6.3.18   | 
| version_comment         | Source distribution | 
| version_compile_machine | i686                | 
| version_compile_os      | suse-linux-gnu      | 
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 STORAGE DISK ENGINE NDB;
Query OK, 0 rows affected (1.17 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 STORAGE MEMORY;
Query OK, 0 rows affected (3.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /*!50100 STORAGE MEMORY */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 STORAGE DISK;
ERROR 1005 (HY000): Can't create table 'c.#sql-1750_3' (errno: 140)
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /*!50100 STORAGE MEMORY */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

But wait - there's more:

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.84 sec)

mysql> SHOW CREATE TABLE t1\G
ERROR 1146 (42S02): Table 'c.t1' doesn't exist
mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 STORAGE DEFAULT ENGINE NDB;
Query OK, 0 rows affected (1.05 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /*!50100 TABLESPACE ts_1 */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 STORAGE DISK;
ERROR 1005 (HY000): Can't create table 'c.#sql-1750_3' (errno: 140)
mysql> ALTER TABLE t1 STORAGE MEMORY;
Query OK, 0 rows affected (2.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /*!50100 STORAGE MEMORY */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE t1 STORAGE DISK;
ERROR 1005 (HY000): Can't create table 'c.#sql-1750_3' (errno: 140)
mysql> ALTER TABLE t1 STORAGE DEFAULT;
Query OK, 0 rows affected (2.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /*!50100 STORAGE MEMORY */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

So be prepared for some bug reports.

More fun!

mysql> CREATE TABLE t1 (c1 INT STORAGE DISK, c2 INT STORAGE MEMORY, c3 STORAGE DEFAULT) TABLESPACE ts_1 ENGINE NDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'STORAGE
DEFAULT) TABLESPACE ts_1 ENGINE NDB' at line 1
mysql> CREATE TABLE t1 (c1 INT STORAGE DISK, c2 INT STORAGE MEMORY) TABLESPACE ts_1 ENGINE
NDB;
Query OK, 0 rows affected (1.12 sec)

So apparently STORAGE DEFAULT doesn't actually work as a column option.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.