Mysql – 9000 rows affected message, but no rows actually changed

MySQLphpmyadmin

I'm having a problem with MySQL. When I run the code below, via phpmyadmin:

UPDATE wp_posts 
    SET post_content = REPLACE
        (
        post_content,
        '31.media.tumblr.com/',
        '41.media.tumblr.com/'
        ) 
    WHERE post_content LIKE '%31.media.tumblr.com/%' 
    AND post_content NOT LIKE '%.gif%';

It returns:

9000 rows affected

If I make a search, it shows that it replaced all the needed things successfully. But if I go and hit the 'EDIT' option in phpmyadmin on one of the rows inside the post_content column, no changes are done inside it; everything stays the same.

What might be the reason?

For me there's something wrong with the code above. Maybe it returns NULL or something and stops the update. If I remove this part:

AND post_content NOT LIKE '%.gif%';

…it does work properly.

The database is a default WordPress one:

Example of data inside post_content:

<p>some text</p>
<img src="http://31.media.tumblr.com/eet1r5co8so1/tumblr_mzlt6zqeet1r5co8so1.gif" alt="" />
<img src="http://11.media.tumblr.com/L4B1q/tumblr_ml5i2cGL4B1qz4fjqo1.gif" alt="" />
other text
<img src="http://51.media.tumblr.com/n42sryL4JG1rda3bfo1/tumblr_n42sryL4JG1rda3bfo1.jpg" alt="" />
<img src="http://31.media.tumblr.com/eet1r5co8so1/tumblr_noui3gHzxp1qfvouao1_1280.jpg" alt="" />
some other text
...

Best Answer

I did the same as you did and it worked.

mysql> INSERT INTO `test`.`wp_posts` (`idws_posts`, `post_content`) VALUES ('1', '<img src="http://31.media.tumblr.com/eet1r5co8so1/tumblr_mzlt6zqeet1r5co8so1.gif" alt="" />');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `test`.`wp_posts` (`idws_posts`, `post_content`) VALUES ('2', '<img src="http://11.media.tumblr.com/L4B1q/tumblr_ml5i2cGL4B1qz4fjqo1.gif" alt="" />');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`wp_posts` (`idws_posts`, `post_content`) VALUES ('3', '<img src="http://51.media.tumblr.com/n42sryL4JG1rda3bfo1/tumblr_n42sryL4JG1rda3bfo1.jpg" alt="" />');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`wp_posts` (`idws_posts`, `post_content`) VALUES ('4', '<img src="http://31.media.tumblr.com/eet1r5co8so1/tumblr_noui3gHzxp1qfvouao1_1280.jpg" alt="" />');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT * FROM test.wp_posts;
+------------+----------------------------------------------------------------------------------------------------+
| idws_posts | post_content                                                                                       |
+------------+----------------------------------------------------------------------------------------------------+
|          1 | <img src="http://31.media.tumblr.com/eet1r5co8so1/tumblr_mzlt6zqeet1r5co8so1.gif" alt="" />        |
|          2 | <img src="http://11.media.tumblr.com/L4B1q/tumblr_ml5i2cGL4B1qz4fjqo1.gif" alt="" />               |
|          3 | <img src="http://51.media.tumblr.com/n42sryL4JG1rda3bfo1/tumblr_n42sryL4JG1rda3bfo1.jpg" alt="" /> |
|          4 | <img src="http://31.media.tumblr.com/eet1r5co8so1/tumblr_noui3gHzxp1qfvouao1_1280.jpg" alt="" />   |
+------------+----------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> 
mysql> UPDATE test.wp_posts 
    ->     SET post_content = REPLACE(post_content,'31.media.tumblr.com/','41.media.tumblr.com/') 
    ->     WHERE post_content LIKE '%31.media.tumblr.com/%' AND post_content NOT LIKE '%.gif%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>         
mysql> SELECT * FROM test.wp_posts;
+------------+----------------------------------------------------------------------------------------------------+
| idws_posts | post_content                                                                                       |
+------------+----------------------------------------------------------------------------------------------------+
|          1 | <img src="http://31.media.tumblr.com/eet1r5co8so1/tumblr_mzlt6zqeet1r5co8so1.gif" alt="" />        |
|          2 | <img src="http://11.media.tumblr.com/L4B1q/tumblr_ml5i2cGL4B1qz4fjqo1.gif" alt="" />               |
|          3 | <img src="http://51.media.tumblr.com/n42sryL4JG1rda3bfo1/tumblr_n42sryL4JG1rda3bfo1.jpg" alt="" /> |
|          4 | <img src="http://41.media.tumblr.com/eet1r5co8so1/tumblr_noui3gHzxp1qfvouao1_1280.jpg" alt="" />   |
+------------+----------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

Are not you missing something?

Related Solutions

Mysql – Difference in between MySQL affected num rows and num rows

Here's mysql_num_rows:

int mysql_num_rows ( resource $result )

Retrieves the number of rows from a result set. This command is only valid for statements like SELECT or SHOW that return an actual result set. To retrieve the number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query, use mysql_affected_rows().

And here's mysql_affected_rows:

int mysql_affected_rows ([ resource $link_identifier = NULL ] )

Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier.

Notice that the input types are different: the first wants a result set, from which it counts the number of rows returned; the second wants a db handle. That makes sense, though, because when you INSERT (or UPDATE, DELETE, REPLACE) a row, you typically don't want a result set -- you just want to know if it succeeded or failed, or how many rows it updated/replaced/deleted.

MySQL: Loop over cursor results ends ahead of schedule

You should strictly check for the end of the loop inside the loop

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 
    until done
end repeat; 

close my_cursor; 

end

This would also eliminate have to check the count

Give it a Try !!!

Looking at your pseudcode

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 

    select some_value into some_field from other_table where some_conditions;
    if (some_field is null) then
        delete from my_table where my_condition;
    else
        update my_table set key1 = value1 where condition1;

    until done
end repeat; 

close my_cursor; 

end

It may not be a good idea to perform an UPDATE on my_table when you are right in the middle of an after DELETE trigger on the same table. Also, notice you are also causing a DELETE under if (some_field is null) then on the same table.

You are probably better off writing this trigger as a Stored Procedure and manually using the Call to it instead of nesting UPDATE of a table inside DELETE on the same table.

Best Answer

Related Solutions

Mysql – Difference in between MySQL affected num rows and num rows

MySQL: Loop over cursor results ends ahead of schedule

Related Question