It appears that the stable, production version, 3.23 doesn't give an SQL
error when an INSERT ... SELECT would cause a duplicate primary key. 4.0
does not appear to have the problem. (I discovered it when replicating from
3.23.49 Linux to a 4.0.1 Windows 2000--the server did not detect the
duplicate key, but replication crashed.)
The short script below creates a table TEMP_READ and inserts a row with key
(123, 123). If I then insert the same thing with an INSERT ... SELECT, I
don't get an SQL error. If I do the insert using INSERT ... VALUES, I do
(correctly) get the error.
This problem occurs on 3.23.49 (and Linux 7.3) and 3.23.54 (Win Me). It
does not occur on 4.0.1 (Win 2k) and 4.0.8 (Linux 7.3). The problem
occurred on 3.23.49 (Linux 7.3) using JDBC, as well as the MySQL client.
I looked in the list archives, but did not find it.
-----------
create database if not exists test;
use test;
drop table if exists GROUPS;
drop table if exists TEMP_READ;
CREATE temporary TABLE GROUPS
(A int, B int);
insert into GROUPS values
(123, 123);
CREATE TEMPORARY TABLE TEMP_READ
(
SUBJECT INT NOT NULL,
TARGET INT NOT NULL,
PRIMARY KEY (SUBJECT, TARGET)
);
INSERT INTO TEMP_READ
values (123,123);
/* should fail, but does not */
INSERT INTO TEMP_READ
SELECT * from GROUPS;
/* fails (correctly) */
INSERT INTO TEMP_READ
values (123,123);
---------------------------------------------------------------------
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
