Bug #68641 Error creating table with auto_increment
Submitted: 11 Mar 2013 21:52 Modified: 12 Mar 2013 20:53
Reporter: Dave Shaffer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5.16 OS:Windows (7)
Assigned to: CPU Architecture:Any
Tags: auto_increment, SQL Create

[11 Mar 2013 21:52] Dave Shaffer
Description:
Trying to create a table with three rows as primary key and last row has auto_increment. Doing through phpMyAdmin.

SQL query:

--
-- Database: `lhcdata`
--
-- --------------------------------------------------------
--
-- Table structure for table `mafp_training_sessions`
--

 CREATETABLEIFNOTEXISTS`mafp_training_sessions` (

 `ts_org` smallint( 6)NOTNULL ,
 `ts_course_no` int( 11)NOTNULL ,
 `ts_session` tinyint( 4)NOTNULLAUTO_INCREMENT ,
 `ts_display_seq` tinyint( 4)NOTNULL ,
 `ts_description` varchar( 500)NOTNULL ,
 `ts_hours` decimal( 4, 1)NOTNULL DEFAULT'0.0',
 PRIMARYKEY (`ts_org` ,`ts_course_no` ,`ts_session`) 
) ENGINE= InnoDBDEFAULT CHARSET= latin1;

 
MySQL said: 
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key 

How to repeat:
Run the above create
[12 Mar 2013 1:13] Tsubasa Tanaka
I seem auto_increment column must be first in multiple-index, without using MyISAM.
(Search "multiple" in this page.)

http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html

Add the new index for `ts_session` like this.

CREATE TABLE IF NOT EXISTS `mafp_training_sessions` (
 `ts_org` smallint( 6) NOT NULL ,
 `ts_course_no` int( 11) NOT NULL ,
 `ts_session` tinyint( 4) NOT NULL AUTO_INCREMENT ,
 `ts_display_seq` tinyint( 4) NOT NULL ,
 `ts_description` varchar( 500) NOT NULL ,
 `ts_hours` decimal( 4, 1) NOT NULL DEFAULT '0.0',
 PRIMARY KEY (`ts_org` ,`ts_course_no` ,`ts_session`),
 KEY (`ts_session`)
) ENGINE= InnoDB DEFAULT CHARSET= latin1;
[12 Mar 2013 12:04] Dave Shaffer
Thank you that enabled me to ass the auto_increment. I know I did not need to do that before. Is that an "enhancement" with this release?
[12 Mar 2013 16:27] Dave Shaffer
The solution provided above (thank you) does work but no the way it had in the past.

When I add a new session I get the next number. However take this scenero:

I have the following in the database:

org  course  session   (This is the primary key)
---  ------  -------
 1     1       1
 1     1       2
 
I want to add one session for course 2. With this change it adds as:
 1     2       3

In the past, it would have added as
 1     2       1

How can I make the later happen? I have a few tables with auto_increment sequence numbers that I want to function that way. 

Also the danger is that the sequence number would not be sequential if courses are being added at the same time from different sources.

Thanks for your help 
Dave
[12 Mar 2013 19:10] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Additionally version 5.5.16 is old and many bugs were fixed since. Please upgrade to current version 5.5.30, try with it and inform us if problem still exists.
[12 Mar 2013 20:53] Dave Shaffer
I changed the engine to MyISAM and the auto_increment acts as I intended.

Thanks for all your help
Dave