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,
APPV_CREATE_DT
)
values
(
8163,
'TitleModelYear',
'1998',
'2001-09-20 10:08:58'
)'
-----
Oh, the my.cnf files:
----- SV1
[mysqld]
set-variable = key_buffer=32M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=4M
set-variable = record_buffer=1M
set-variable = thread_cache=8
set-variable = thread_concurrency=2
log-bin
server-id = 1
innodb_data_file_path = df1:1024M;df2:1024M
innodb_data_home_dir = /home/mysql/DB/data
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /home/mysql/DB/logs
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=256M
set-variable = innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=1
innodb_log_arch_dir = /home/mysql/DB/logs
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=192M
set-variable = innodb_additional_mem_pool_size=32M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
-----
----- SV2
[mysqld]
set-variable = key_buffer=32M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=4M
set-variable = record_buffer=1M
set-variable = thread_cache=8
set-variable = thread_concurrency=2
log-bin
log-slave-updates
skip-slave-start
server-id = 2
master-host = d1p-A
master-user = repl
master-password = xxxxx
master-port = 3306
replicate-ignore-db=mysql
innodb_data_file_path = df1:1024M;df2:1024M
innodb_data_home_dir = /home/mysql/DB/data
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /home/mysql/DB/logs
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=256M
set-variable = innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=1
innodb_log_arch_dir = /home/mysql/DB/logs
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=192M
set-variable = innodb_additional_mem_pool_size=32M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
-----
Yes, SV2 is also replicated to a third machine, SV3. However, this has no
been enabled for the second and third failures.
One more note. I thought that maybe InnoDB was having replication issues
with the "replace into" statement, so I "ALTER TABLE"ed the APPV table on
the slave machine to a MyISAM table and let it run again (that is, reloaded
the copied InnoDB files, ALTERed the table, reset the master/slave logs on
the slave, and started the slave again). It failed in the same place as
before, which I guess is a good thing.
Well, I'm not sure what I am going to try next. I will likely change the
"replace into" statement into a "delete-insert" and see if that doesn't get
things going. Obviously that is not the most desirable thing, because it
would seem that this should work.
Thanks,
robert
--
robert cope \ I'm like a bad penny. I always turn up.
[EMAIL PROTECTED] /
\
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
