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
