How do I resolve 'Unexpected number of row removed!'?

Question

I'm trying to delete all row of data in my tables and reset the auto increment. When I do, I get this message :

Unexpected number of rows removed!

One row was not removed. Reload the table to be sure that the contents
have not changed in the meantime. Check the Console for possible errors 
inside the primary keys of this table!

update When I do TRUNCATE TABLE 'users' I get this message

    Cannot truncate a table referenced in a foreign key constraint
 (`marketing`.`designer_application`, CONSTRAINT
 `designer_application_user_id_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES 
`marketing`.`users` (`id`))

Truncate tablename; There is also a item in the Menu – Bernd Buffen Oct 30 '15 at 06:00 — Bernd Buffen, Oct 30 '15 at 06:00
This will help you to decide Visit: – Gaurav Lad Oct 30 '15 at 06:10 — Gaurav Lad, Oct 30 '15 at 06:10

score 1 · Accepted Answer · answered Oct 30 '15 at 06:01

Likely another process acted on the same rows that you selected before you were able to delete them. If you are using the UI to delete the rows, consider using a SQL query instead. Using TRUNCATE will clear the contents of a table and reset the auto increment to 0.

TRUNCATE TABLE `tablename`;

score 0 · Answer 2 · answered Aug 26 '23 at 19:31

If you're working with tables that have FOREIGN KEY constraints, using DELETE FROM tablename is preferable over TRUNCATE TABLE. The DELETE command will also remove corresponding entries from tables that have a foreign key relationship with it.

Alternatively, you can temporarily disable the foreign key constraints by running:

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE tablename;
SET FOREIGN_KEY_CHECKS=1;

However, be aware that this won't remove associated data from other tables.

If you encounter an error message saying "Unexpected number of rows removed!" when trying to delete rows, it could be due to table locks. You can identify and terminate these locking queries as follows:

To find the queries that are locking the table:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB = 'DATABASE_NAME'

To generate 'KILL' commands for these queries:

SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') FROM information_schema.processlist WHERE user = 'DATABASE_NAME'

After obtaining the 'KILL' queries, execute them to remove the table locks.

How do I resolve 'Unexpected number of row removed!'?

2 Answers2