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: | |
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
[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