>Description:
>From the MySQL on-line manual (Section 7.22):
REPLACE works exactly like INSERT, except that if an old record in the table
has the same value as a new record on a unique index, the old record is
deleted before the new record is inserted.
>From the MySQL on-line manual (F.2.3 Changes in release 3.23.34):
REPLACE will not replace a row that conflicts with an auto_increment generated key.
Table Structure
===============
The following table exists in a replicated environment. The problem described
below occurs only during replication on the slave. There are no problems at
all on the master.
mysql> describe ResumeRabbit_Status.status;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| OID | int(11) | | | 0 | |
| SiteCode | varchar(4) | | MUL | | |
| Status | char(1) | | | Q | |
| Time | int(11) | | | 0 | |
| LastFailed | int(11) | | | 0 | |
| CompletedAt | int(11) | | | 0 | |
| Username | varchar(255) | | | | |
| Password | varchar(8) | | | | |
+-------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
mysql> show index from ResumeRabbit_Status.status;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
|Cardinality | Sub_part | Packed | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| status | 0 | PRIMARY | 1 | ID | A |
|130516 | NULL | NULL | |
| status | 0 | SiteCode | 1 | SiteCode | A |
|115 | NULL | NULL | |
| status | 0 | SiteCode | 2 | OID | A |
|130516 | NULL | NULL | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
3 rows in set (0.00 sec)
mysql>
Problem
=======
After upgrading to 3.23.34, I started getting the following errors on the slave:
mysql> show slave status;
+----------------------+-------------+-------------+---------------+-------------+------+---------------+---------------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos
| | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno |
|Last_error
| | Skip_counter |
+----------------------+-------------+-------------+---------------+-------------+------+---------------+---------------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| www.resumerabbit.com | replicator | 3001 | 60 | ds1-bin.087 |
|8332 | No | ResumeRabbit_Status | | 1062 | error
|'Duplicate entry '197086' for key 1' on query 'REPLACE INTO status
|(OID,SiteCode,Time,Status) VALUES ('6048','AJ','985059892','P')' | 0 |
+----------------------+-------------+-------------+---------------+-------------+------+---------------+---------------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------+
1 row in set (0.00 sec)
mysql>
After reviewing the change log for 3.23.34, it appears that this is the new documented
behaviour. However, when I use the REPLACE command on the master (to alter the data
that will get replicated) or from the command line tool on the slave it works in
the previously defined manner (that is it deletes the existing record and inserts
a new one complete with a new auto_increment ID).
>How-To-Repeat:
Uncertain
>Fix:
Work-around: Replace the REPLACE INTO sql statement with the appropriate
DELETE and INSERT INTO statements
>Submitter-Id: <submitter ID>
>Originator: root
>Organization:
>MySQL support: none
>Synopsis: REPLACE not behaving as documented
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.34 (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 8.17 Distrib 3.23.34, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.34-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 6 days 7 hours 45 min 37 sec
Threads: 254 Questions: 259462 Slow queries: 5 Opens: 42 Flush tables: 1 Open
tables: 19 Queries per second avg: 0.475
>Environment:
System: Linux www 2.2.14-5.0 #1 Tue Mar 7 21:07:39 EST 2000 i686 unknown
Architecture: i686
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-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 May 12 2000 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x 1 root root 4101324 Feb 29 2000 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20272704 Feb 29 2000 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Feb 29 2000 /usr/lib/libc.so
Configure command: ./configure --with-lipwrap --with-charset=latin1 --without-debug
--with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static
Perl: This is perl, version 5.005_03 built for i386-linux
---------------------------------------------------------------------
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
