MySQL Forums
Forum List  »  MyISAM

AUTO_INCREMENT messed up for no reason
Posted by: John Doe
Date: May 12, 2010 06:52AM

Please let me know if this should be posted in another forum.


Cliffnotes: auto_increment is behaving in different ways on two different servers with the same database.

Long version:



I run two separate Mysql servers, call them SRV1 and SRV2.

[SRV1]
uname -a: Linux SRV1 2.6.26-2-686 #1 SMP (debian lenny)
dpkg -l | grep mysql:
ii  libdbd-mysql-perl                 4.007-1                  A Perl5 database interface to the MySQL database
ii  libmysqlclient15off               5.0.51a-24+lenny2        MySQL database client library
ii  mysql-client-5.0                  5.0.51a-24+lenny2        MySQL database client binaries
ii  mysql-common                      5.0.51a-24+lenny2        MySQL database common files
ii  mysql-server                      5.0.51a-24+lenny2        MySQL database server (metapackage depending on the latest ver
ii  mysql-server-5.0                  5.0.51a-24+lenny2        MySQL database server binaries



[SRV2]
uname -a: Linux s6 2.6.26-2-686 #1 SMP (debian lenny)
dpkg -l | grep mysql:
ii  libdbd-mysql-perl                 4.007-1+lenny1           A Perl5 database interface to the MySQL database
ii  libmysqlclient15off               5.0.51a-24+lenny3        MySQL database client library
ii  mysql-client-5.0                  5.0.51a-24+lenny3        MySQL database client binaries
ii  mysql-common                      5.0.51a-24+lenny3        MySQL database common files
ii  mysql-server                      5.0.51a-24+lenny3        MySQL database server (metapackage depending on the latest ver
ii  mysql-server-5.0                  5.0.51a-24+lenny3        MySQL database server binaries

As you can see pretty similar setups. Both servers have onedatabase in common, and both are being updated and read from, every day 24/24 by a web site.

There's a particular table (that is obviously on both servers) that I recently discovered has been behaving in differing ways depending on which server it is.


[SRV1]
mysql> explain t;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| uid            | int(20) unsigned     | NO   | PRI | 0       |                |
| entry          | int(20) unsigned     | NO   | PRI | NULL    | auto_increment |
| timestamp      | int(20) unsigned     | NO   | MUL | 0       |                |

... some more data fields that don't appear relevant to the issue................

| del_reason     | tinyint(4)           | NO   |     | 0       |                |
+----------------+----------------------+------+-----+---------+----------------+
17 rows in set (0.00 sec)



[SRV2]
mysql> explain t;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| uid            | int(20) unsigned     | NO   | PRI | 0       |                |
| entry          | int(20) unsigned     | NO   | PRI | NULL    | auto_increment |
| timestamp      | int(20) unsigned     | NO   | MUL | 0       |                |

... some more data fields that don't appear relevant to the issue................

| del_reason     | tinyint(4)           | NO   |     | 0       |                |
+----------------+----------------------+------+-----+---------+----------------+
17 rows in set (0.01 sec)




As you can see both tables are exactly the same from the "explain" point of view.
Basically, "uid" is a USER ID, and "entry" is an ENTRY that the user has made.
I want the ENTRIES to be numbered from 1 to X for each user (as opposed to 1 to X for the whole table).
Something like:
USER_1	ENTRY_1
USER_1	ENTRY_2
USER_1	ENTRY_3

USER_2	ENTRY_1
USER_2	ENTRY_2

USER_X	ENTRY_1
USER_X	ENTRY_2

etc...

I defined PRIMARY KEY(uid, entry) and this was working perfectly by inserting VALUES (xy, '', ...more data...); so far for years.

However, I recently discovered that on SRV1, the ENTRY_X (which is the fiend "entry") wasn't being numbered properly, but rather:
USER_1 ENTRY_1
USER_1 ENTRY_2
USER_2 ENTRY_3
USER_2 ENTRY_4
USER_3 ENTRY_5
...
USER_X ENTRY_2209815   (with the user X having just a couple entries)

Which made me very sad. 8'-(

After digging around I found one difference between the two tables (which are, remember on two different servers), that can be seen in SHOW CREATE TABLE.
[SRV1]

| t | CREATE TABLE `t` (
  `uid` int(20) unsigned NOT NULL default '0',
  `entry` int(20) unsigned NOT NULL auto_increment,
  `timestamp` int(20) unsigned NOT NULL default '0',

...more stuff...

  `del_reason` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`uid`,`id`),
  KEY `timestamp` (`timestamp`),
  KEY `uid` (`uid`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2209815 DEFAULT CHARSET=latin1 |


[SRV2]

| t | CREATE TABLE `t` (
  `uid` int(20) unsigned NOT NULL default '0',
  `entry` int(20) unsigned NOT NULL auto_increment,
  `timestamp` int(20) unsigned NOT NULL default '0',

...more stuff...

  `del_reason` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`uid`,`id`),
  KEY `timestamp` (`timestamp`),
  KEY `uid` (`uid`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |




As you can see the only differing stuff (is the AUTO_INCREMENT=xxx" on SRV1 and this is where "entry" is being numbered improperly.

I have no idea why this started to happen. Both server's have been restored from dumps a few months ago, and then, later, both have been repaired by myisamchk after a crash but I have no idea whether or not this would have caused this. More importantly, I would like to know how to fix it, and how to prevent it from happening again in the future.

I'd be more than happy to provide any additional data if needed.


Thanks in advance for your attention and help.

Options: ReplyQuote


Subject
Views
Written By
Posted
AUTO_INCREMENT messed up for no reason
3710
May 12, 2010 06:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.