mysql executed or not?

[duckxtales]

Hi,

I ran this mysql statement:

mysql> update phpbb_posts_text, phpbb_posts set phpbb_posts_text.post_id = phpbb_posts.x where phpbb_posts_text.post_id = phpbb_posts.post_id;

But I got this error:

ERROR 1062 (23000): Duplicate entry '2717' for key 1

I just need to know if this statement made any PARTIAL changes to my database, but then stops abruptly when it finds an error. this in effect would make me have to recover my db from a backup.

[justageek]

The query failed completely.

JAG

[duckxtales]

i know, but does it modify my database even though it failed?

[justageek]

Nope. That's what I meant by failed completely. It died without changing anything. No modifications were done to the table.

JAG

[coopster]

Just to clarify, there is a possibility that your data integrity could be lost on an UPDATE operation even though the query fails at a DUPLICATE key entry. Any other updates that occurred prior to hitting the duplicate key remain intact. Of course, all of this depends on the storage type (transactional versus non). But for a typical ISAM table, you could indeed have a partial update. Case in point:

CREATE TABLE t1 (tid SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL); 
INSERT INTO t1 VALUES(NULL), (NULL), (NULL), (5), (7), (9); 
SELECT * FROM t1; 
+-----+ 
¦ tid ¦ 
+-----+ 
¦  1  ¦ 
¦  2  ¦ 
¦  3  ¦ 
¦  5  ¦ 
¦  7  ¦ 
¦  9  ¦ 
+-----+ 
UPDATE t1 SET tid = tid + 1 ORDER BY tid DESC; 
SELECT * FROM t1; 
+-----+ 
¦ tid ¦ 
+-----+ 
¦  2  ¦ 
¦  3  ¦ 
¦  4  ¦ 
¦  6  ¦ 
¦  8  ¦ 
¦  10 ¦ 
+-----+ 
UPDATE t1 SET tid = tid - 1 ORDER BY tid DESC; 
ERROR 1062 (23000): Duplicate entry '3' for key 1 
SELECT * FROM t1; 
+-----+ 
¦ tid ¦ 
+-----+ 
¦  2  ¦ 
¦  3  ¦ 
¦  4  ¦ 
¦  5  ¦ 
¦  7  ¦ 
¦  9  ¦ 
+-----+

Three rows were updated, three rows were not, even though the query failed. Be careful as you plan and code updates, especially when PRIMARY KEY columns are involved.