Responding on the go, so I apologize in advance for any typos, and for not putting my responses inline.
I've managed dozens of innodb databases using autoinc on hundreds of tables per db for the past five years, and seen lots of problems but always with a reason behind them...
Regarding your snapshots, you expressed concerns that someone changed the data during the export. Since you're using innodb you should always take your export with -single-transaction -master-data to get a consistent non-blocking backup. Since you believe the data to be inconsistent I'm guessing you didn't use these options?
You said your main server is 5.0, what is your slave? There is a bug in replication of LOAD DATA INFILE that looks like what you described, though AFAIK it only affects specific versions - do you use this command, perhaps in a cron or batch job?
Marcus was referring to your replication master. Many folks configure their servers as both master and slave to eachother, as this can make crash recovery easier. However, what I think he was really asking abou is If someone could have inserted a record directly into the slave (this would be more likely if you had master-master repl going), when it read the next insert operation on that table from the master, replication would break- though I have always seen an error reported when this happens. If this is the case, your solution is simple: auto Inc offset and autoinc increment.
If all else fails, use mysqlbinlog to read the master logs at the position that the slave stopped and see if anything looks wrong.
Hope some of that helps,
From: "Jesse" <jc@stripped>
Date: Tue, 15 Jul 2008 07:37:56
Subject: Re: Questions about replication
> In general -- I see dozens of master + slaves here with innodb and
> autoincrements; "never" a problem.
That is GREAT to hear
> ** What is the symptom?
It's been a few months since I attempted this. I got fed up with it. As I
recall, I would take a snap-shot, restored the data on the slave, and
started replication. I would check it ever so often with SHOW SLAVE STATUS
and suddenly, it would say "No" for the "SQL Running" option (I think it
was). I would simply get a mesasge like "Slave SQL thread exiting,
replication stopped in log 'mysql-bin.000007' at position 195" That was it.
I was not able to find out why it stopped.
> ** Are you sure the data in slave is same as in master?
That is kind of what I suspect. I didn't shut down the web site, and I
suspect that while I was trying to take the snap shot that someone got in
and changed a recod. I plan on shutting down the web server portion this
weekend to ensure that no one can get in and making sure that the data is an
exact copy before I begin.
>> 1. MOST of the databases that I have are using the InnoDB
>> storage engine. Is that a problem?
> No problem.
Great, I like the InnoDB, mostly for it's Foreign Key and Trigger capability
>> 2. I have a lot of foreign keys defined in these tables. Is
>> that a problem?
>> 3. I have triggers in a lot of tables, mostly for updating
>> add (or update) date/time fields is that a problem?
Huuum. Then, this one concerns me. Maybe someone else will chime in on this
>> 3. I use AutoIncrement A LOT for a unique ID for each record
>> (a common
>> practice from what I understand).
>No problem if writing to only one master. Do you have a multi-master
When you say "master" are you referring to the replication relationship, or
are you referring to the table relationship? regarding replication, right
now, I have only one master server, and one slave server. Regarding tables,
I do have some tables that related to what you would call two master tables,
I suppose, but, of course, they all have unique ID fields?
>Do you really need row-based? All my experience is with stmt-based.
My experience with non-row-based-replaction hasn't been so sweet at this
point, being that replication stops without a very good explanation (at
least, not one that I can find).
Thanks for your help, you've helped some of my concerns.
MySQL Replication Mailing List
For list archives: http://lists.mysql.com/replication
To unsubscribe: http://lists.mysql.com/replication?unsub=1v@stripped