mysql strange "duplicate entry" error

I have a problem I don't quite understand. I parse some feeds with Ruby and save their contents in a database. I created a "hash"-column which is the md5-hash of every post url. That column is UNIQUE because I don't want to post anything twice.

It works fine actually:

Mysql::Error: Duplicate entry '28edb7c2b3cd074d226fc4ae37baedd7' for key 'hash'

But the script stops at this point. I don't get that, I know for a fact that using INSERT with PHP always worked like a charm, so if there was duplicate entry it ignored it and went on.

Can anybody help me? Would "INSERT IGNORE" create a dou开发者_运维问答ble entry or would it just ignore the error message and go on?

Sounds like your Ruby script needs some exception handling.

You can rewrite your query so that instead of INSERT INTO it uses

REPLACE INTO ...

or

INSERT INTO ... ON DUPLICATE KEY UPDATE

This way attempting to insert a duplicate key will update the existing record instead of erroring out.

See here and here for more information.

Update:

INSERT IGNORE not touch your existing data if it encounters a duplicate key. The documentation says:

You can use REPLACE instead of INSERT to overwrite old rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of new rows that contain unique key values that duplicate old rows: The new rows are used to replace the old rows rather than being discarded.

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.

In PHP, if MySQL returns an error, it doesn't normally kill the PHP script. It sounds to me as though that's not the case in Ruby. Either catch the exception and process it or use INSERT IGNORE, in which case MySQL returns a warning instead of an error (unless it was told not to).

"INSERT IGNORE" Should Prevent Ruby from exiting and shouldn't effect your data. However if you want to know when this is happening you have to put in some error handling.

           begin
                DATABASE.query(insertHash)
            rescue
                puts "Error: " + $!.to_s + "Backtrace >>: " + $@.to_s
            end 

Should show the error with out exiting the ruby script. Or you could use this to indicate to the user that there is already an entry

Hope this helps