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