Insert multiple value, UNIQ, and integrity

[Palard Julien]

Hi,

I'm using python-mysql to do a multiple insert (~1000 rows) against a table
which have UNIQ keys.

As the MySQL documentation says : http://dev.mysql.com/doc/refman/5.0/en/inse
rt.html (i run MySQL 5.0)

If you use an INSERT ... VALUES statement with multiple value lists , the
statement returns an information string in this format:

Records: 100 Duplicates: 0 Warnings: 0

Records indicates the number of rows processed by the statement. (This is not
necessarily the number of rows actually inserted because Duplicates can be
nonzero.) Duplicates indicates the number of rows that could not be inserted
because they would duplicate some existing unique index value.

So, if there's, say, 1 conflicting values in my 1000 rows, I should be able to
insert 999 rows. But when I try to do it using _mysql or MySQLdb, le query()
raise an IntegrityError and returns 0, in my table, and no rows where inserted
)o: (my test table is empty, so the conflicts are inside the 1000 inserted
rows but in production it can be from anywhere)

I set connection.autocommit(True)

And in case of the "commit" is missed due to the exception, i tried to commit
in the "except IntegrityError" without success...

What happend ?

[estrand]

This is expected behavior. The statement will work only if the all records
within the statement satisfy the unique constraint in regard to the records in
the statement and the records already in the table. In your example it is not
correct that 999 rows should be inserted.

[Palard Julien]

So, if there is some conflict in my statement, it will fail, but if there is
some conflicts between my statement and my table, the statement will work as
described in the MySQL documentation ?

[Andy Dustman]

If you want to overwrite duplicates, use (non-standard) REPLACE instead of
INSERT. The error is correct in this case.

[Palard Julien]

@adustman

If you want to overwrite duplicates, use (non-standard) REPLACE instead of
INSERT. The error is correct in this case.

I dont want to override duplicates, and the error is not correct according to
tue officiel MySQL documentation stating that non-duplicates rows will be
inserted while duplicates will not, just counting duplicates as information,
please read : http://dev.mysql.com/doc/refman/5.0/en/insert.html and the quote of my first post.

[Andy Dustman]

I'm not changing how it raises IntegrityError. I'm not going to silently
ignore duplicate records. If you want to, then use INSERT_IGNORE instead.

[Palard Julien]

I'm not asking you to change how it raises IntegrityError, i'm trying to
understand what appened, i am true saying :

-> I insert some non-duplicates and some duplicates in my table, mysql accept them but informs that there is some duplicates
-> mysql-python see the duplicates information and raise an error preventing me to commit the statement, so it's never applied ?

[Andy Dustman]

Try ignoring the exception and comitting anyway

[Palard Julien]

As i said in my first message :

And in case of the "commit" is missed due to the exception, i tried to
commit in the "except IntegrityError" without success...

So i can't understand what happend here ...

[Leo Hourvitz]

jpalard >

I interpret the MySQL Reference Manual differently. In particular, please see
the following section under INSERT modifiers on the My SQL 5.0 INSERT Syntax
page:

If you use the IGNORE keyword, errors that occur while executing the INSERT
statement are treated as warnings instead. For example, without IGNORE, a row
that duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted. With IGNORE, the
row still is not inserted, but no error is issued. Data conversions that would
trigger errors abort the statement if IGNORE is not specified.

I read that to mean that (without the IGNORE modifier) duplicate keys cause
the statement to fail, so I think this is proceeding according to the docs...
Have you tried adding the IGNORE modifier? (sorry if you already explained
that and I missed it).

[Palard Julien]

Hum, good point, so i'll try adding IGNORE, thanks !