Description: While setting up a mutual master-slave/slave-master MySQL application for High-Availability, redundancy, and load-balancing, the autoincrement functionality of disparate servers seem to be detached from replication functionality. It's likely the replication code can be improved to consider multiple master-slave arrangements trivially. How-To-Repeat: Get yourself a 64-bit DEC Alpha Linux server (Not Necessary, but, Fun): Linux tempe.dedserius.com 2.4.2 #3 Sun Feb 25 16:07:31 MST 2001 alpha unknown root@tempe:~# cat /proc/cpuinfo cpu : Alpha cpu model : EV5 cpu variation : 0 cpu revision : 0 cpu serial number : Linux_is_Great! system type : Alcor system variation : 0 system revision : 0 system serial number : MILO-0000 cycle frequency [Hz] : 300014100 est. timer frequency [Hz] : 1024.00 page size [bytes] : 8192 phys. address bits : 40 max. addr. space # : 127 BogoMIPS : 593.48 kernel unaligned acc : 106 (pc=fffffc00004f26e0,va=fffffc000417a836) user unaligned acc : 1 (pc=1200cda00,va=20000f8a912) platform string : N/A cpus detected : 0 root@tempe:~# free total used free shared buffers cached Mem: 189440 184224 5216 0 42696 17808 -/+ buffers/cache: 123720 65720 Swap: 257008 3104 253904 In separate terminals the following was run from Tempe on a 100/Mbps HUB-based network: Tempe passes data at 8MBytes/Sec over his NIC. Connectivity is not likely to be a factor. /usr/local/apache/bin/ab -n 100 -c 20 http://scottsdale.ds.lan/index.php3 /usr/local/apache/bin/ab -n 100 -c 20 http://pima.ds.lan/index.php3 The index.php3 script does many things, but, included is an insert to a db with 2 tables: Newstats with the following structure: CREATE TABLE newstats ( nid int(10) NOT NULL auto_increment, Referer varchar(150) NOT NULL, UserAgent varchar(75) NOT NULL, ScriptFilename varchar(150) NOT NULL, RemoteHost varchar(50) NOT NULL, RemoteAddr varchar(15) NOT NULL, HitTime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, Host varchar(25) NOT NULL, AcceptLanguage varchar(15) NOT NULL, QryString varchar(150) NOT NULL, PRIMARY KEY (nid), KEY HitTime (HitTime), KEY RemoteHost (RemoteHost), KEY RemoteAddr (RemoteAddr), KEY ScriptFilename (ScriptFilename) ); CREATE TABLE pagestats ( uri varchar(96) NOT NULL, hits bigint(21) NOT NULL, since datetime, PRIMARY KEY (uri) ); CREATE TABLE excludes ( ip varchar(25) ); The query selects from excludes and if the host isn't in there does an insert into both of the first tables. After the query on excludes, it does a select from both tables. So for each hit, there's a select on excludes and an insert if not found, then a select on the tables. Queries: SELECT * FROM excludes WHERE ip = '192.168.1.2' UPDATE pagestats SET hits=hits+1 WHERE uri = '/www/www.dedserius.com/index.php3' INSERT INTO newstats VALUES ('', '', 'ApacheBench/1.3c', '/www/www.dedserius.com/index.php3', 'tempe.ds.lan', '192.168.1.2', '20010320 00:34:47', 'scottsdale.ds.lan', '', '/index.php3') Scottsdale is a Dual 400 MHz Celeron with 128MBytes RAM; Pima is a Single PII 200 MHz with 64MBytes RAM; Both machines run Slackware Linux 7.2; patches for GLIBC 2.2. scottsdale:/mysql# gcc -v Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.2/specs gcc version 2.95.2 19991024 (release) scottsdale:/mysql# uname -a Linux scottsdale 2.3.99-pre9 #2 SMP Fri Feb 23 16:14:56 MST 2001 i686 unknown root@pima:/mysql# gcc -v Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.2/specs gcc version 2.95.2 19991024 (release) root@pima:/mysql# uname -a Linux pima 2.4.2 #2 Wed Mar 14 01:56:45 MST 2001 i586 unknown MySQL-3.23.33 built from source. ./configure --with-mysqld-user=mysql --localstatedir=/mysql I have public and private name servers. Public servers end with dedserius.com. Private with ds.lan and reverse nslookup on privates function. DBase host authentication is based on DNS not Host lookups. This is for future implementation of round-robbin DNS decisions. The replication succeeded when hitting Scottsdale, first. Failed when hitting Pima first. Failure was due to autoincrement field containing duplicate value during the benchmark. my.cnf: Scottsdale: # The MySQL server [mysqld] port = 3306 log-bin = scottsdale-bin.log server-id = 14 socket = /tmp/mysql.sock skip-locking master-host = pima master-user = scottsdale master-password = password set-variable = max_sort_length=64 set-variable = key_buffer=96M set-variable = table_cache=1024 set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = sort_buffer=96M set-variable = record_buffer=32M Pima: [mysqld] port = 3306 log-bin = pima-bin.log socket = /tmp/mysql.sock server-id = 19 master-host = scottsdale master-user = pima master-password = password skip-locking set-variable = max_sort_length=64 set-variable = key_buffer=32M set-variable = table_cache=1024 set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = sort_buffer=32M set-variable = record_buffer=32M Fails with the following on Pima: ERROR: 1062 Duplicate entry '238947' for key 1 010320 0:46:15 Slave: error running query 'INSERT INTO newstats VALUES ('', '', 'ApacheBench/1.3c', '/www/www.dedserius.com/index.php3', 'tempe.ds.lan', '192.168.1.2', '20010320 00:34:43', 'scottsdale.ds.lan', '', '/index.php3')' 010320 0:46:15 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave - log 'scottsdale-bin.log' position 38094149 010320 0:46:15 Slave thread exiting, replication stopped in log 'scottsdale-bin.log' at position 38094149 Fails with the following on Scottsdale: ERROR: 1062 Duplicate entry '238947' for key 1 010320 0:45:02 Slave: error running query 'INSERT INTO newstats VALUES ('', '', 'ApacheBench/1.3c', '/www/www.dedserius.com/index.php3', 'tempe.ds.lan', '192.168.1.2', '20010320 00:35:00', 'pima.ds.lan', '', '/index.php3')' 010320 0:45:02 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave - log 'pima-bin.log' position 38090788 010320 0:45:02 Slave thread exiting, replication stopped in log 'pima-bin.log' at position 38090788 Fix: delete from newstats where UserAgent = 'ApacheBench/1.3c' Shutdown and restart each master/slave. Replicate another dbase with: mysqldump -C -h sedona -p -u vanboers cdarchive | mysql cdarchive It works. >:) >Submitter-Id: <submitter ID> >Originator: [EMAIL PROTECTED] >Organization: Ded Serius Music MySQL support: Long-Time User Synopsis: Unique Keys Shouldn't Be Generated by Two Servers {Was :forwarded message from Van} Severity: serious Priority: medium Category: mysql Class: change-request >Release: mysql-3.23.33 (Source distribution) >Environment: System: Linux pima 2.4.2 #2 Wed Mar 14 01:56:45 MST 2001 i586 unknown Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Mar 19 18:44 /lib/libc.so.6 -> libc-2.2.1.so -rwxr-xr-x 1 root root 1013224 Mar 21 2000 /lib/libc-2.1.3.so -rwxr-xr-x 1 root root 4693661 Jan 14 06:24 /lib/libc-2.2.1.so -rwxr-xr-x 1 root root 4804291 Jan 10 05:04 /lib/libc-2.2.so -rw-r--r-- 1 root root 24000586 Jan 14 06:21 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jan 14 06:21 /usr/lib/libc.so lrwxrwxrwx 1 root root 29 Mar 4 16:03 /usr/lib/libc.so.1 -> /usr/i486-sysv4/lib/libc.so.1 Configure command: ./configure --localstatedir=/mysql --with-mysqld-user=mysql --------------------------------------------------------------------- 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