If you are using 'replication', and trying to do bi-directional
replication, you will ( unless you are very careful ) have this problem.
The auto increment id is passed with the rest of the data to the slave.
If you are inserting on both masters and each assigns the same id, the
replication will fail with duplicate autoincrement ids.
Molnár Norbert wrote:
> Hello!
>
> I'm new at this list, but as far as I could search through the archives,
> I didn't find the reason of my problem.
> Here it is:
>
> I manage a list of mailing-list members with approx. 8000 records in
> this table:
> +-----------+--------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+--------------+------+-----+---------+----------------+
> | id | int(11) | | PRI | NULL | auto_increment |
> | email | varchar(100) | | | | |
> | count | int(11) | YES | | 0 | |
> | status | tinyint(1) | | | 1 | |
> | listak_id | int(11) | | | 0 | |
> +-----------+--------------+------+-----+---------+----------------+
> About 7000 are deleted and re-loaded daily, because of a mechanism like
> 'replication'.
> (after delete everey time runs an 'optimize table')
> So the "id" field grows very rapidly.
> Two days ago, it was about 550.000 , and the table suddenly DIED.
> I got messages about 'wrong handler', 'can't read' and so on.
>
> It was the second interesting "happening", because a month ago an other
> table reached a record number of 350 (!not too much!) and at an insert
> command said, that the auto_increment field 'id' is duplicated. It was
> the primary key, so it couldn't execute the insert command.
> Then I dropped the table and reloaded it from a backup. And since then
> it works.
>
> Yesterday I tried to repair the members table (with the 8000 records)
> just like a month ago the other table, and it seemed to work.
> For 5 minutes.
> Then, after an insert command the table died again.
> 'Repair table' didn't work.
> I dropped the whole database. (secondly succeeded a brutal way (rm -rf
> /var/lib/mysql/maillist), because the first try (mysqladmin drop
> maillist) did not work.)
> Re-generated from a backup.
> Tried 'check table' on the members, and it died again.
>
> The solution seems to be:
> I dropped the members table and reloaded it without the 'id' field. So
> it started from 1.
> But how long will it work?
> Will it die when the id reach again the level of 500.000?
>
> Did anyone meet a problem like these? (duplicated primary keys with auto
> increment fields, and short-living-tables :( )
>
> Thanks in advance
>
> Norbert Molnar
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
><[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Gerald L. Clark
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
