I am cleaning up a lot of my DB's/Tables, and have been running into an issue. When I execute most commands which change data on a table (i.e, DELETE FROM or UPDATE), the command returns as completed but it doesn't actually do anything.


mysql> SELECT * FROM members WHERE username = "Alex";
(Returns alex row).
mysql> DELETE FROM members WHERE username = "Alex";
(Says is completed, 0 row affected)
mysql> SELECT * FROM members WHERE username = "Alex";
(Returns alex row).

This shouldn't be happening, when I run a delete command I expect it to actually delete the rows. However, and here is the weird part, if I stay logged in for a while the rows will eventually become deleted. If I instantly exit, the rows don't delete at all.

This isn't the exact code I'm running (working with a lot more conditions/larger DB's), but the concept is the exact same. I am also checking for deletions using adminer.php (a 1 php file MySQL manager, like phpmyadmin), and that takes about an hour for any deletions/updates to acually register.

I assume the commands are being queued or cached or something like that, and I would like to stop MySQL from doing that. When I enter a command I would like MySQL to run that command straight away, and not return before it's finished actually executing the command. Some 500K deletions finished within 2 minutes (which I can't quite believe).

Actual Example:

mysql> UPDATE members  SET username = REPLACE(username, ' ', '');
Query OK, 0 rows affected (0.05 sec)
Rows matched: 24013  Changed: 0  Warnings: 0
mysql> SELECT * FROM members; 
(displays many usernames, still with whitespaces)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.