MySQL Bugs: #68641: Error creating table with auto

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

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

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;

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?

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

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.

I changed the engine to MyISAM and the auto_increment acts as I intended.

Thanks for all your help
Dave