Bug #71103 Wrong syntax is used as example in the manual
Submitted: 6 Dec 2013 12:22 Modified: 9 Dec 2013 15:28
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Tags: Memory, using btree
Triage: Needs Triage: D4 (Minor)

[6 Dec 2013 12:22] Valeriy Kravchuk
Description:
This manual page, http://dev.mysql.com/doc/refman/5.6/en/storage-engine-setting.html, says:

"When you create a new table, you can specify a special-purpose storage engine to use by adding an ENGINE table option to the CREATE TABLE statement:

-- ENGINE=INNODB not needed unless you have set a different default storage engine.
CREATE TABLE t1 (i INT) ENGINE = INNODB;
-- Simple table definitions can be switched from one to another.
CREATE TABLE t2 (i INT) ENGINE = CSV;
-- Some storage engines have their own specific clauses in CREATE TABLE syntax.
CREATE TABLE t3 (i INT) ENGINE = MEMORY USING BTREE;"

Let's try last example on the latest and greatest 5.6.15:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.15-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t3 (i INT) ENGINE = MEMORY USING BTREE;
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 'USING
 BTREE' at line 1

We get syntax error. This way it works surely:

mysql> CREATE TABLE t3 (i INT) ENGINE = MEMORY;
Query OK, 0 rows affected (0.92 sec)

The problem was notedafter a hint from my Facebook friend Federico Razzoli.

How to repeat:
Execute:

CREATE TABLE t3 (i INT) ENGINE = MEMORY USING BTREE;

on different MySQL server versions. Fix examples in the manual accordingly.

Suggested fix:
Test code from the manual pages on each and every release.
[6 Dec 2013 13:45] Peter Laursen
Indeed, HASH or BTREE are specified for index(es individually) and not for the table as such as in example from 
http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html

CREATE TABLE lookup
    (id INT, INDEX USING BTREE (id))
    ENGINE = MEMORY;

(because a MEMORY table may have both HASH and BTREE indexes at the same time)
[6 Dec 2013 14:58] Miguel Solorzano
Thank you for the bug report.
[9 Dec 2013 15:28] Paul Dubois
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.

The USING BTREE clause seems irrelevant to the point being made and confuses the issue.
I removed it.