MySQL Bugs: #47658: how to use auto_increment not well-documented for 'rare' storage Engines

Bug #47658	how to use auto_increment not well-documented for 'rare' storage Engines
Submitted:	26 Sep 2009 12:34	Modified:	24 Jan 2014 16:22
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any recent	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
This is created wiht reference to
http://bugs.mysql.com/bug.php?id=47653

where 
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
.. where both discussed.  InnoDB anne MyISAM are sorted out here. But what about other ENGINES?

How to repeat:
CREATE TABLE `mytab1` (
   `t` VARCHAR(20) NOT NULL,
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`t`,`id`)
 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8;
-- success

CREATE TABLE `mytab2` (
   `t` VARCHAR(20) NOT NULL,
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`t`,`id`)
 ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
 -- Error Code : 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

(I did not check FALCON)

Suggested fix:
For a replication scenario using BLACKHOLE as master it makes sense that BLACKHOLE allows for same construction as MyISAM. I just do not find it documented properly.

Also this works actually

CREATE TABLE `mytab99` (
   `t` VARCHAR(20) NOT NULL,
   `t2` VARCHAR(20) NOT NULL,
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`t`,`t2`,`id`)
 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8;

.. what is sort of inconsistency.  I am not sure, but does not this involve a risk or replication failure (if master is BLACKHOLE and slave something else)?

BDB is also documented at http://dev.mysql.com/doc/refman/5.0/en/create-table.html, for example. Generic case is also documented:

"There can be only one AUTO_INCREMENT  column per table, it must be indexed, and it cannot have a DEFAULT value. "

What other engines are you interested in?

It is not the question what I am interested in personally.

There is a page named "Using AUTO_INCREMENT" with the text "For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index" implicit (and *only* implicit) that indicates that for other engines you cannot. For BLACKHOLE it is obviously wrong. With BLACKHOLE you can you can specify AUTO_INCREMENT on a *any* column in a multiple-column index - obviously because nothing is stored for and index with BLACKHOLE so nothing really matters here (except for replication scenarios). But even if it is obvious for those who know the BLACKHOLE engine it still means that information on http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html is incomplete or wrong.

It is not a big problem. 20 seconds of experimenting will show you. But first of all this is an example of two common problems with the documentation: 1) redundancy 2) fragmentation

A paragraph named "Using AUTO_INCREMENT" should either be complete for all official official engines or it should link/refer properly to engine-specific pages or paragraphs.

The *fragmentation* problem here is that there are (at least) 3 pages one will have to check ot find all details.
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
http://dev.mysql.com/doc/refman/5.0/en/create-table.html

The *redundancy* problem is that information is partly overlapping but not complete in all those pages. Redundancy is a problem because it makes maintenaince very hard (I maintain documentation myself). There is a risk that updates are not done everywhere when updates are required. Lately I came across two examples of redundant information that was not updated:
http://bugs.mysql.com/bug.php?id=47293 (info about where the server reads config file on Windows)
http://bugs.mysql.com/bug.php?id=47308 (last_insert_id/'sql_auto_is_null' behaviour).

I understand it is not something that can be fixed overnight. It is probably a feature request to "remove fragmentation and redundancy from documentation". That was my primary message. I just found an example more. I hope it is positively understood. I do not expect miracles from next week of course.

But in addition to that:

1) what is valid indexing with an auto_increments column with BLACKHOLE engine contradicts implicit meaning of 2 of 3 above listed pages.
2 "only one AUTO_INCREMENT column per table, it must be indexed .. " is incomplete. Except for specific engines ".. it must be indexed and the auto_increment column must be specified as the first column of an index" would be correct in my understanding.

Thank you for the report.

BLACKHOLE part and feature request verified as described.

Will be covered by WL#5117.