Problems with MySQL Delete list

Background

The table of the test environment is synchronized to another database server, but some tables inside the data is huge, (actually do not synchronize the table of data on the line, then did not think too much), tens of millions of of the data!!

Steps

1. Now that the data has been synchronized, then delete it directly, how big? So

Delete  from where 1 = 1;

The result was dumbfounded, executed a moment on the card dead, to the card dead!

2. So the question comes, is it a deadlock? How do you judge the deadlock?

SHOW processlist;

Execute this command to view the current process of the database

3. View the current thing

#当前运行的所有事务mysql > SELECT *  from information_schema. Innodb_trx, #当前出现的锁mysql>SELECT* from information_schema. Innodb_locks, #锁等待的对应关系mysql>SELECT* from Information_schema . Innodb_lock_waits;

This can be done if you are executing MySQL commands directly on Linux.

SELECT * from information_schema. Innodb_trx\g
In this way, the display data will be changed, more humane

4. You can kill some database processes with the KILL command

Kill 2;

This can be, this only solves the problem of the card, the execution of the same command or will be stuck to death;

5. Then think about why you're stuck. There was one such parameter.

This exception is thrown when the general wait time-out occurs:

Lock wait timeout exceeded; Try restarting transaction

Because of MySQL, the default lock wait time is 50s, that is, 50s will be error, so you need to modify the next time

MySQL configuration is generally in the my.cnf file, find their own

Add/Modify innodb_lock_wait_timeout=500 to

6. After the change, restart MySQL, which involves MySQL shutdown

Bin/-shutdown

Shut down the SQL, do not recommend direct kill-9, do not know what will appear the moth

Restart, or use Mysql_safe to start

7. In fact, delete the table data, with truncate on the line,

truncate table table_name;

8. Then compare the difference between the Drop,truncate,delete

A. Drop is not the entire table, the table structure is not gone, truncate and delete table structure is still in;

B. Delete can specify which row the where condition deletes, truncate is the entire table;

C. Come to Baidu a bit, ^_^

　　

1. Truncate and delete only delete data does not delete the structure of the table (definition) The DROP statement will delete the structure of the table that is dependent on the constraint (constrain), the trigger (Trigger), Index (Index); Stored procedures that depend on the table/The function is preserved, but becomes invalid state.2The . Delete statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed. truncate, the drop is DDL, the operation takes effect immediately, the original data is not placed in the rollback segment, and cannot be rolled back. The operation does not trigger the trigger.3The . Delete statement does not affect the extent occupied by the table, and the high waterline (hi Watermark) remains in its original position. Apparently the drop statement frees all the space occupied by the table.truncateThe statement by default sees space released to minextents extent unless reuse storage is used; Truncate will reset the high watermark (back to the beginning). 4. Speed, in general:Drop>;truncate >;Delete 5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.

Problems with MySQL Delete list