List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:July 11 2001 2:00pm
Subject:Re: problem with tables
View as plain text  
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 <mysql-thread79097@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
gerald_clark@stripped

Thread
Referential Integritypak11 Jul
  • Re: Referential IntegrityKen Sommers11 Jul
  • Re: Referential Integritypak11 Jul
    • Re: Referential IntegrityMichael Bacarella11 Jul
    • Re: Referential IntegrityJeremy Zawodny11 Jul
  • Re: Referential IntegrityKen Sommers11 Jul
  • Re: Referential IntegrityKen Sommers11 Jul
    • help me with "select" queryJaime Teng11 Jul
      • RE: help me with "select" queryDon Read11 Jul
  • Re: help me with "select" queryRolf Hopkins11 Jul
  • problem with tablesMolnár Norbert11 Jul
  • Re: problem with tablesGerald Clark11 Jul
  • Re: problem with tablesMolnár Norbert11 Jul
RE: help me with "select" queryDmitri Lubinski11 Jul
RE: Referential IntegrityFred Dinkler11 Jul
  • Re: Referential IntegrityKen Sommers12 Jul
    • Re: Referential Integrityhooker12 Jul
RE: Referential IntegrityFred Dinkler11 Jul