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).
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)