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://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:
Page last modified 06-Jan-13 20:13:44 EST
Previous page: Non-latin1 characters in a MySQL version 4 database
Next page: INSERT INTO ... ON DUPLICATE KEY UPDATE instead of REPLACE INTO