RE: Potential replication problem with auto increment on multi pr ocessor machine

I will continue further testing of this to see if I can reproduce the
problem on the latest release.

Thanks,

Mike

-----Original Message-----
From: Fred van Engen [mailto:fred.van.engen@xbn.nl]
Sent: 10 June 2003 11:48
To: Mike Hilton
Cc: bugs@lists.mysql.com
Subject: Re: Potential replication problem with auto increment on multi pr
ocessor machine


Mike,

On Tue, Jun 10, 2003 at 11:38:36AM +0100, Mike Hilton wrote:
> >> 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');
>

True, but there should be a line like below before the insert:

SET INSERT_ID = 661721;

This is used in the slave for auto-generated id's.


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

Re: Potential replication problem with auto increment on multi pr ocessor machine

Mike,

On Tue, Jun 10, 2003 at 11:56:13AM +0100, Mike Hilton wrote:
> I will continue further testing of this to see if I can reproduce the
> problem on the latest release.
>

You might be interested in this:

D.3.5 Changes in release 4.0.10 (29 Jan 2003)

* Fixed bug in logging to binary log (which affects replication) a query that inserts a NULL in an auto_increment field and also uses LAST_INSERT_ID().

This is mentioned in the mailing list archives as well.


Regards,

Fred.


> -----Original Message-----
> From: Fred van Engen [mailto:fred.van.engen@xbn.nl]
> Sent: 10 June 2003 11:48
> To: Mike Hilton
> Cc: bugs@lists.mysql.com
> Subject: Re: Potential replication problem with auto increment on multi pr
> ocessor machine
>
>
> Mike,
>
> On Tue, Jun 10, 2003 at 11:38:36AM +0100, Mike Hilton wrote:
> > >> 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');
> >
>
> True, but there should be a line like below before the insert:
>
> SET INSERT_ID = 661721;
>
> This is used in the slave for auto-generated id's.
>
>
> 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.

--
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

--
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