Description:
1) I have a table that is being created with this script:
DROP TABLE IF EXISTS `artists`;
CREATE TABLE `artists` (
`artist_id` int(11) NOT NULL auto_increment,
`artist` varchar(100) default NULL,
`firstLetter` char(1) NOT NULL default '#',
`mask` VARCHAR(100),
`regex` VARCHAR(255),
PRIMARY KEY (`artist_id`),
KEY `artist` (`artist`(20)),
KEY `firstletter` (`firstLetter`)
) TYPE=MyISAM;
ALTER TABLE artists auto_increment=0;
INSERT INTO artists VALUES("0","Unknown","U", "Unknown", "^Unknown$");
UPDATE artists SET artist_id=0;
It is important for me to have the first record with the primary key == 0
Then I add some records to the table and want to change the auto_increment:
INSERT INTO artists SET artist = "XXX";
INSERT INTO artists SET artist = "YYY";
ALTER TABLE artists auto_increment=99999;
INSERT INTO artists SET artist = "ZZZ";
I find that the value of the primary key in the first record was changed:
99999,Unknown,U,Unknown,^Unknown$
2,XXX,#,NULL,NULL
3,YYY,#,NULL,NULL
100000,ZZZ,#,NULL,NULL
It was expected that Unknown has the value of 0;
How to repeat:
simply run this in MySQL-Front or something:
DROP TABLE IF EXISTS `artists`;
CREATE TABLE `artists` (
`artist_id` int(11) NOT NULL auto_increment,
`artist` varchar(100) default NULL,
`firstLetter` char(1) NOT NULL default '#',
`mask` VARCHAR(100),
`regex` VARCHAR(255),
PRIMARY KEY (`artist_id`),
KEY `artist` (`artist`(20)),
KEY `firstletter` (`firstLetter`)
) TYPE=MyISAM;
ALTER TABLE artists auto_increment=0;
INSERT INTO artists VALUES("0","Unknown","U", "Unknown", "^Unknown$");
UPDATE artists SET artist_id=0;
INSERT INTO artists SET artist = "XXX";
INSERT INTO artists SET artist = "YYY";
ALTER TABLE artists auto_increment=99999;
INSERT INTO artists SET artist = "ZZZ";
SELECT * FROM artists
You will have:
99999,Unknown,U,Unknown,^Unknown$
2,XXX,#,NULL,NULL
3,YYY,#,NULL,NULL
100000,ZZZ,#,NULL,NULL
Then Run this
simply run this in MySQL-Front or something:
DROP TABLE IF EXISTS `artists`;
CREATE TABLE `artists` (
`artist_id` int(11) NOT NULL auto_increment,
`artist` varchar(100) default NULL,
`firstLetter` char(1) NOT NULL default '#',
`mask` VARCHAR(100),
`regex` VARCHAR(255),
PRIMARY KEY (`artist_id`),
KEY `artist` (`artist`(20)),
KEY `firstletter` (`firstLetter`)
) TYPE=MyISAM;
ALTER TABLE artists auto_increment=0;
INSERT INTO artists VALUES("0","Unknown","U", "Unknown", "^Unknown$");
UPDATE artists SET artist_id=0;
INSERT INTO artists SET artist = "XXX";
INSERT INTO artists SET artist = "YYY";
SELECT * FROM artists
artist_id,artist,firstLetter,mask,regex
0,Unknown,U,Unknown,^Unknown$
2,XXX,#,NULL,NULL
3,YYY,#,NULL,NULL
Suggested fix:
Check what happens when the 'auto_increment = something' command is run.
Description: 1) I have a table that is being created with this script: DROP TABLE IF EXISTS `artists`; CREATE TABLE `artists` ( `artist_id` int(11) NOT NULL auto_increment, `artist` varchar(100) default NULL, `firstLetter` char(1) NOT NULL default '#', `mask` VARCHAR(100), `regex` VARCHAR(255), PRIMARY KEY (`artist_id`), KEY `artist` (`artist`(20)), KEY `firstletter` (`firstLetter`) ) TYPE=MyISAM; ALTER TABLE artists auto_increment=0; INSERT INTO artists VALUES("0","Unknown","U", "Unknown", "^Unknown$"); UPDATE artists SET artist_id=0; It is important for me to have the first record with the primary key == 0 Then I add some records to the table and want to change the auto_increment: INSERT INTO artists SET artist = "XXX"; INSERT INTO artists SET artist = "YYY"; ALTER TABLE artists auto_increment=99999; INSERT INTO artists SET artist = "ZZZ"; I find that the value of the primary key in the first record was changed: 99999,Unknown,U,Unknown,^Unknown$ 2,XXX,#,NULL,NULL 3,YYY,#,NULL,NULL 100000,ZZZ,#,NULL,NULL It was expected that Unknown has the value of 0; How to repeat: simply run this in MySQL-Front or something: DROP TABLE IF EXISTS `artists`; CREATE TABLE `artists` ( `artist_id` int(11) NOT NULL auto_increment, `artist` varchar(100) default NULL, `firstLetter` char(1) NOT NULL default '#', `mask` VARCHAR(100), `regex` VARCHAR(255), PRIMARY KEY (`artist_id`), KEY `artist` (`artist`(20)), KEY `firstletter` (`firstLetter`) ) TYPE=MyISAM; ALTER TABLE artists auto_increment=0; INSERT INTO artists VALUES("0","Unknown","U", "Unknown", "^Unknown$"); UPDATE artists SET artist_id=0; INSERT INTO artists SET artist = "XXX"; INSERT INTO artists SET artist = "YYY"; ALTER TABLE artists auto_increment=99999; INSERT INTO artists SET artist = "ZZZ"; SELECT * FROM artists You will have: 99999,Unknown,U,Unknown,^Unknown$ 2,XXX,#,NULL,NULL 3,YYY,#,NULL,NULL 100000,ZZZ,#,NULL,NULL Then Run this simply run this in MySQL-Front or something: DROP TABLE IF EXISTS `artists`; CREATE TABLE `artists` ( `artist_id` int(11) NOT NULL auto_increment, `artist` varchar(100) default NULL, `firstLetter` char(1) NOT NULL default '#', `mask` VARCHAR(100), `regex` VARCHAR(255), PRIMARY KEY (`artist_id`), KEY `artist` (`artist`(20)), KEY `firstletter` (`firstLetter`) ) TYPE=MyISAM; ALTER TABLE artists auto_increment=0; INSERT INTO artists VALUES("0","Unknown","U", "Unknown", "^Unknown$"); UPDATE artists SET artist_id=0; INSERT INTO artists SET artist = "XXX"; INSERT INTO artists SET artist = "YYY"; SELECT * FROM artists artist_id,artist,firstLetter,mask,regex 0,Unknown,U,Unknown,^Unknown$ 2,XXX,#,NULL,NULL 3,YYY,#,NULL,NULL Suggested fix: Check what happens when the 'auto_increment = something' command is run.