I'm working on a script that sadly I inherited - with no commenting or anything. Argh! For testing purposes I duplicated one of the tables in the database which had an auto-incrementing ID. When the data is saved to the database, though, the ID number just reads "0" -- which is the default for that column. I'm not sure why it's not auto increasing anymore... any thoughts? Thank you!
+4
A:
Are you sure you set the field in the duplicate table to auto-increment? Try running:
ALTER TABLE `duplicate_table` CHANGE `ai_key` `ai_key` INT( key_length ) NOT NULL AUTO_INCREMENT
And see if it is set or not.
Eran Galperin
2009-01-08 00:08:48
+2
A:
Did you create the new table from scratch or as a real copy? If the column is supposed to auto increment it should be a primary (or at least a unique) key, with no default value.
Ant P.
2009-01-08 00:11:20
Hmm, it is unfortunately set to "INDEX" with no auto_increment set. Whereas the original had it set to PRIMARY as well as auto_increment. (Please forgive my newbieness, I'm still learning MySQL and have been thrown into the frying pan!) Thanks - I'm hoping adjusting these settings will fix it. :)
2009-01-08 00:39:32
+1
A:
Just to double check use the sql statement to show the show create table syntax for both tables and compare.
show create table <table>
Zoredache
2009-01-08 00:22:24
A:
It sounds like your column isn't actually auto_increment any more. This has happened to me a couple of times because there was a bug (?) in phpMyAdmin which I used to create backups: it wouldn't add the auto_increment keyword into the CREATE TABLE statements. That was a massive pain in the butt...
nickf
2009-01-08 00:26:07
Oh no! I'm wondering if that's what happened here. I'm sadly using an old version of phpMyAdmin. What did you use to correct the problem? Hopefully you didn't have to recreate the whole table.
2009-01-08 00:43:47
Just had to go through each of the tables which were supposed to have AI fields and add them back in again. You could always upgrade your phpMyAdmin...
nickf
2009-01-08 04:38:18