MySQL query Replace Into fails

PROBLEM:

"Replace into" fails. It creates duplicate records instead of replacing original ones.

In following these instructions:

http://www.bluebox.net/about/blog/2009/07/mysql_encoding/

I am on section:

Step 4E: Re-insert fixed rows back into the original table

 

The command

replace into db_content_props (select * from temptable);

Reports something like:

Query OK, 352 rows affected (0.01 sec)

Records: 352 Duplicates: 0 Warnings: 0

but it DOES NOT overwrite the old values with the ones from the temptable. Instead, it creates a DUPLICATE of the field record, with the same key.

The field I am modifying is of type "text" with encoding "utf_general_ci".

So is the original one.

 

 

SOLUTIONS:

1. Find out the version of your MySQL server:

mysql --version

mysql Ver 14.12 Distrib 5.0.96, for debian-linux-gnu (i486) using readline 5.2

 

Or a more proper command is:

Run this command line to obtain server version: mysql -u dbusername -p -h hostname -e "select version()"

where -e stands for "execute"

 

My version is 5.0.96.

 

If your version is 5.0 or 5.1, it may be affected by bug:

http://bugs.mysql.com/bug.php?id=19906

Current MySQL version is 5.5.28. This bug has been reported on 18 May 2006. The date of this writing is December 2012. Shows you how behind the current version my server software is!!!

Versions affected by this bug: 5.0, 5.1 (Documented bugfix in 5.0.23 and 5.1.12 changelogs).

 

Description of bug:

Having a table with primary key and unique index, the TEXT fields are not updated correctly. It does work when there is no unique index.

tags: MySQL replace into text fails, replace into text doesn't work, replace into text does not.

 

2. Your replacement table does not have a PRIMARY KEY, or that PRIMARY KEY differs from the destination table.

As an example, when creating new tables by

CREATE TABLE `TABLENAME` (SELECT * FROM ...);

The PRIMARY KEY and other INDEX attribute are not copied over. You need to examine the original table, and make the one created have the same KEY or INDEX attributes.

Also, your table might not have had any KEYs or INDEXes to begin with. If you later need to overwrite the original table with the temporary one, you need to first set a PRIMARY KEY.

 

3. Replace Into alternative:

1) Alternative for replace into:

INSERT INTO ... ON DUPLICATE KEY UPDATE

According to what I have read, REPLACE INTO is not the best command for this occasion, because it does a DELETE followed by an INSERT.

Per Charles, "Watch out, REPLACE INTO is implemented internally as a DELETE and then an INSERT. The delete is a normal delete, meaning it will check foreign keys and obey ON DELETE clauses. You probably want INSERT INTO ... ON DUPLICATE KEY UPDATE ... instead anyway."

Per Marcus Adams, "Additionally, if you attempt to update a row with the same (existing) values, MySQL will detect that the values are the same and will skip the update, saving time, locks, etc.--another reason to use INSERT INTO...ON DUPLICATE KEY UPDATE... versus deleting the existing row first."

Per piotrm, "REPLACE INTO is not recommended here - you don't really need to replace anything. It does DELETE followed by INSERT, with all the consequences. For example all indexes have to be updated, which leads to unnecessary work and index fragmenting if you use it frequently.

On the other hand there is ON DUPLICATE KEY UPDATE, which is used mainly for counters, but you are not updating your row with increments or any other value changes, so you would have to use weird syntax like SET id=id or something similar."

Sources: http://stackoverflow.com/questions/9267294/replace-into-versus-insert-if

 

One "side effect" of REPLACE is that it would increment autoincrement values. Also, REPLACE INTO can cause problems if you have a foreign key constraint pointing at that row. In this situation the REPLACE could fail or worse: if your foreign key is set to cascade delete, the REPLACE will cause rows from other tables to be deleted. This can happen even though the constraint was satisfied both before and after the REPLACE operation. Source: http://stackoverflow.com/questions/9168928/what-are-practical-differences-between-replace-and-insert-on-duplicate-ke

 

2) Temporarily remove unique index, then recreate. (I don't know whether this is a good idea, or of it is at all doable).

3) Upgrade your MySQL server to v5.2+ (not a solution for shared hosting environments).

 

For further reading:

http://stackoverflow.com/questions/7206822/inserting-into-a-mysql-table-and-overwritng-any-current-data

http://dev.mysql.com/doc/refman/5.5/en/insert-select.html

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

http://dev.mysql.com/doc/refman/5.6/en/replace.html

http://82.157.70.109/mirrorbooks/mysqlcertification/0672326329/ch07lev1sec1.html

http://code.openark.org/blog/mysql/replace-into-think-twice

 

Continue reading: Use INSERT INTO ... ON DUPLICATE KEY UPDATE instead of REPLACE INTO



˅˅˅ Additional valuable information is available at one of the links below: ˅˅˅

 

Did you like the article? Let Google Search know by clicking this button: . Please link to content that you find useful on this website on your own website, forum or blog! You can also comment on this page below, or to ask a question or suggest a topic for me to research. There is a user-editable Wiki available on my website, as well as a Forum that you can contribute to. Site Map.

Page last modified 06-Jan-13 20:13:44 EST
Comments on this page: