RE: Potential replication problem with auto increment on multi pr ocessor machine
am 10.06.2003 12:38:36 von Mike Hilton>> Do you have a binary log that shows this?
I don't have a binary log that shows the potential problem I describe in the
bug submission / query, however, re :
>> The auto_increment id that is generated on the master is sent to all
slaves, so they don't (re-)generate them by themselves. At least this is the
case with MyISAM tables.
- I have confirmed that doing an insert on the master into a test table
(mh_test) with an auto increment primary key field :
i.e. create table mh_test(id tinyint(2) NOT NULL AUTO_INCREMENT
PRIMARY KEY, description varchar(30));
insert into mh_test(id, description) values (NULL, 'First row');
- results in the exact same statement being written to the binary log - i.e.
:
mysql[PROFILE_TEST2]@ops-dev5:/var/mysql/PROFILE_TEST2/binlo g>
/usr/local/mysql-4.0.12/bin/mysqlbinlog ops-dev5.003 | grep mh_test
create table mh_test(id tinyint(2) NOT NULL AUTO_INCREMENT
PRIMARY KEY,
insert into mh_test(id, description) values (NULL, 'First
row');
I therefore assume that the schema table on the slave would have to be
created identical to that on the master (with the auto_increment field), for
the replication to populate the id PRIMARY KEY column on the slave via auto
increment.
Mike
-----Original Message-----
From: Fred van Engen [mailto:fred.van.engen@xbn.nl]
Sent: 10 June 2003 11:30
To: Mike Hilton
Cc: bugs@lists.mysql.com
Subject: Re: Potential replication problem with auto increment on multi
processor machine
Hi,
On Tue, Jun 10, 2003 at 10:43:50AM +0100, Mike Hilton wrote:
> Summary: Data integrity (potentially) lost as auto increment field
> used as target of foreign key not replicated predictably on multi
> processor slave
>
> Description:
>
> We have seen a problem on a previous release (32 bit binary MySQL
> 4.0.5) which we are trying to reproduce on 4.0.13. The problem
> occurred where we had a number of [InnoDB] tables (e.g. user_profile)
> with foreign key relationships to another table (e.g. user). The
> foreign key relates to an auto increment generated id field in the
> master (user) table. (Under load) when these tables are replicated,
> the slave auto increment generates the user id value locally, and as
> the slave is a
The auto_increment id that is generated on the master is sent to all slaves,
so they don't (re-)generate them by themselves. At least this is the case
with MyISAM tables.
> multi processor machine, the original order of row creation in the
> master is not preserved when replicated. This leads to the foreign key
There is a single slave thread which processes all replicated queries in
order.
> If user rows id 1,and 2 were to be created + an associated user
> profile row with foeign key relationship :
>
> 1) Potential problem caused by multi processor master :
>
> 1.
>
> 2.
>
> 3.
>
> 4.
>
Changing the query order would be a bug if the order makes a difference. In
this case it does indeed make a difference because the state after 3 could
never have existed in the database. That's the consistency property of ACID
databases if I'm not mistaken.
Do you have a binary log that shows this?
> This could lead to problems, as 3. is written to the binlog before 4.
> owing to race conditions, and when picked up and processed on the
> slave in (binlog written) order, 3. would (incorrectly) create a row
> with auto generated row id 1.
>
But even then, the actual values of the id fields should be correct because
they are sent with the query during replication. That is, unless the SET
INSERT_ID and the INSERT query aren't written atomically to the log, which
could lead to:
SET INSERT_ID (for INSERT 1)
SET INSERT_ID (for INSERT 2)
INSERT (2)
INSERT (1)
That would be a bug again.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: fred.van.engen@xbn.nl Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
BBCi at http://www.bbc.co.uk/
This e-mail (and any attachments) is confidential and may contain
personal views which are not the views of the BBC unless specifically
stated.
If you have received it in error, please delete it from your system, do
not use, copy or disclose the information in any way nor act in
reliance on it and notify the sender immediately. Please note that the
BBC monitors e-mails sent or received. Further communication will
signify your consent to this.
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org