Index processing differences between 3.x and 4.x or 5.x

I have 600,000 plus record database which is updated nightly.
It currently runs under MySQL version 3.x. There are duplicate
records from the source which I eliminate by selecting from
one table where duplicates are allowed into another table where
there is a unique index defined on two fields, item,mfg.

In version 3.x selecting from the table with duplicates into the
table with the unique index effectively eliminates the duplicates.

Under version 4.x and 5.x the same selection statement causes the
program to terminate with an error when it encounters the first
duplicate.

I'm still searching through the documentation but if someone could
help me out with the syntax I would appreciate it.

J.R0wan wrote:
> I have 600,000 plus record database which is updated nightly.
> It currently runs under MySQL version 3.x. There are duplicate
> records from the source which I eliminate by selecting from
> one table where duplicates are allowed into another table where
> there is a unique index defined on two fields, item,mfg.
>
> In version 3.x selecting from the table with duplicates into the
> table with the unique index effectively eliminates the duplicates.
>
> Under version 4.x and 5.x the same selection statement causes the
> program to terminate with an error when it encounters the first
> duplicate.
>
> I'm still searching through the documentation but if someone could
> help me out with the syntax I would appreciate it.
Ok, I have found my answer. I was not aware of the ignore option on an
insert statement. Starting with my table with dupes I use the command
below to copy into the table which requires unique values. Now I just
have to figure out what the 2033 Warnings were:

insert ignore into items2 (item,mfg) select /
substring(item,5,length(item)-4),substring(item,1,3) from afterprepwork;

Query OK, 534787 rows affected, 2033 warnings (10 min 1.67 sec)
Records: 822301 Duplicates: 287514 Warnings: 2033

Hi J.R0wan,

J.R0wan wrote:
> J.R0wan wrote:
>> I have 600,000 plus record database which is updated nightly.
>> It currently runs under MySQL version 3.x. There are duplicate
>> records from the source which I eliminate by selecting from
>> one table where duplicates are allowed into another table where
>> there is a unique index defined on two fields, item,mfg.
>>
>> In version 3.x selecting from the table with duplicates into the
>> table with the unique index effectively eliminates the duplicates.
>>
>> Under version 4.x and 5.x the same selection statement causes the
>> program to terminate with an error when it encounters the first
>> duplicate.
<snip>
> Ok, I have found my answer. I was not aware of the ignore option on an
> insert statement. Starting with my table with dupes I use the command
> below to copy into the table which requires unique values. Now I just
> have to figure out what the 2033 Warnings were:
>
> insert ignore into items2 (item,mfg) select /
> substring(item,5,length(item)-4),substring(item,1,3) from afterprepwork;
>
> Query OK, 534787 rows affected, 2033 warnings (10 min 1.67 sec)
> Records: 822301 Duplicates: 287514 Warnings: 2033

SHOW WARNINGS will list those for you.
http://dev.mysql.com/doc/refman/4.1/...-warnings.html

Each time you ignore a duplicate record, one warning is generated. I
would assume that there were probably 2033 duplicates in your source table.
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN

Are you MySQL certified? www.mysql.com/certification