CODEWITHSUNDEEP
mysqlsequelprodatabase-management
jackjoesmith
jackjoesmith

Reputation: 971

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

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

Upvotes: 1

Views: 5171

Answers (2)

Mohamad Hamouday
Mohamad Hamouday

Reputation: 2743

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.

Upvotes: 0

doublesharp
doublesharp

Reputation: 27599

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`;

Upvotes: 1

Related Questions