Robert,
there was a bug in 3.23.41 which caused InnoDB to ignore
SET INSERT_ID=...
That is a command used in replication of auto-inc columns.
That bug was fixed in .42, but obviously still some bug
remains.
It would be valuable to know how the master handles the
failing statement? What is the auto-inc value assigned
there? What is the original SQL statement, is it a multi-line
replace?
I am sending a copy of this email to Sasha.
He is responsible for replication.
Regards,
Heikki
Hello,
Before I say anything, know that I decided to kick the caffeine habit this
week... :)
I am having some problems with replication that I have been unable to to
figure out. Let me describe my systems. They are identical PIII PowerEdge
350s with 512megs of RAM and two 60GB drives in a RAID-1 array. I am
running Linux, kernel version 2.4.9 patched for EXT3. MySQL 3.23.42 has
been configured and built with these options:
CFLAGS=-O3 -march=i686 -mcpu=i686 CXX=gcc CXXFLAGS=-O3 -march=i686
-mcpu=i686 -felide-constructors -fno-exceptions -fno-rtti ./configure
--prefix=/home/mysql --enable-assembler --with-mysqld-ldflags=-all-static
--with-innodb --without-docs --without-bench --with-gnu-ld
I am using gcc-2.95.3, glibc-2.4.4 and binutils-2.11.2. As you may have
guessed, we are using InnoDB tables.
When initially setting up the environment, I had a mysqldump produced
dataset (from our old environment) that I used to load each of the MySQL
servers (sv1 and sv2). At that point, I enabled the slave and turned on
the application (my company refinances auto loans; we have written a
workflow system that handles the loan applications that come in). I had
tested this setup in a lab environment, and as expected, everything
worked fine. After about a day of running, sv2, the slave, died with this
error:
-
ERROR: 1062 Duplicate entry '1063030' for
key 1
010917 13:30:13 Slave: error running query 'replace into APPV_APP_VAR
(
APPV_APPL_ID,
APPV_PROP_NAME,
APPV_PROP_VALUE,
APPV_CREATE_DT
)
values
(
7706,
'TradeInValue',
'9625',
'2001-09-17 13:28:58'
)'
-
This is an auto-incremented table, so key 1 with a value of 1073030
does exist. The funny thing about this is that this statement, because it
is a replace into statement, should work. The application runs fine on
the primary machine, and ran fine for months on another server (without
replication).
I know for sure that nothing besides the master is touching the slave.
After the first failure, I attempted to resync the databases using the
following commands:
-
sv1 mysql: flush tables with read lock;
sv2 sh: mysqldump --all --opt --host=xxx --user=xxx -p database
database.dump
sv1 mysql: reset master;
sv1 mysql: reset slave;
sv1 mysql: unlock tables;
sv2 sh: mysql -u xxx -p database database.dump
sv2 mysql: reset master;
sv2 mysql: reset slave;
sv2 mysql: slave start;
-
Once again, after about a day, the slave failed with the following error:
-
ERROR: 1062 Duplicate entry '1063030' for key 1
010918 12:19:58 Slave: error running query 'replace into APPV_APP_VAR
(
APPV_APPL_ID,
APPV_PROP_NAME,
APPV_PROP_VALUE,
APPV_CREATE_DT
)
values
(
7706,
'TradeInValue',
'9625',
'2001-09-17 13:28:58'
)'
-
At this point I thought that maybe I should try another method of syncing
things up. So I shut down the application and the databases and pysically
copied the InnoDB data and log files from sv1 to sv2. And again, failure:
-
ERROR: 1062 Duplicate entry '1122600' for key 1
010920 15:10:28 Slave: error running query 'replace into APPV_APP_VAR
(
APPV_APPL_ID,
APPV_PROP_NAME,
APPV_PROP_VALUE,
