Troubleshooting the reasons why MySQL deleted records do not take effect

A record of an online MySQL transaction problem

Last Friday, I performed an operation to delete a large table. During the deletion process, a small problem occurred, and I spent two hours in vain. I have recorded the general process here. Without further ado, let's just look at the process.

At that time, I wanted to delete it, so I tested the syntax of the delete statement first, and deleted one to try, as follows:

mysql ::>>select min(id) from XXXX_user_login;
+---------+
| min(id) |
+---------+
| |
+---------+
 row in set (0.00 sec)

mysql ::>>delete from XXXX_user_login where id < ;
Query OK, row affected (0.00 sec)

mysql ::>>select min(id) from XXXX_user_login;         
+---------+
| min(id) |
+---------+
| |
+---------+
 row in set (0.00 sec)

Then I logged in again using the MySQL client and found a strange problem:

[dba_mysql ~]$ /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P4306
Enter password: 
XXXXXXXXXXXXXXXXXXXXXX
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql ::>>select min(id) from XXXXX_user_login;                  
+---------+
| min(id) |
+---------+
| |
+---------+
 row in set (0.00 sec)

That is, the record that was just deleted has returned again.

It's quite strange to think about it. Did I delete it wrong? Or after deleting it, the business side reinserted the data. Isn't this a problem? . . Tried it again a few times, with the same result.

This phenomenon is very strange. I have never encountered it before. I first checked the script and confirmed that the deleted script was correct. Then I checked for a long time and finally found a breakthrough from the direction of the transaction. I suspected that it was caused by the transaction not being submitted. So I took a look at the parameters of the current transaction, as follows:

mysql ::>>show variables like '%commit%';   
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| autocommit | OFF |
| innodb_commit_concurrency | |
| innodb_flush_log_at_trx_commit | |
+--------------------------------+-------+
 rows in set (0.00 sec)

[email protected]:(none) ::>>
mysql ::>>show global variables like '%commit%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| autocommit | ON |
| innodb_commit_concurrency | |
| innodb_flush_log_at_trx_commit | |
+--------------------------------+-------+
 rows in set (0.00 sec)

Seeing this, the problem has basically been determined. It is because the auto-commit in the current session is set to off, so when deleting, it seems to have succeeded. After restarting, these transactions have been rolled back, so it seems that the deletion operation is "invalid".

Now that the problem has been located, we start looking for the root cause of the problem. Finally, we find the root cause in the configuration file, as follows:

[mysqldump]
quick
max_allowed_packet = M

[mysql]
no-auto-rehash
max_allowed_packet = M
prompt=mysql--\\u@\\h:\\d \\R:\\m:\\s>>
init-command="set interactive_timeout=28800;set wait_timeout=28800;set autocommit=0;"

In the last line of the configuration file, the autocommit configuration of the mysql client group was set to 0. Of course, it could not be submitted automatically. So I changed this parameter to 1 and tried the script again, but found that the problem still existed. . .

It seems that the changes are still not thorough.

We know that there is a sequence for MySQL to load configuration files. We can use the mysql --help|grep my.cnf command to view it. After checking, it is because the configuration in /etc/my.cnf is also autocommit=0, so the parameters of the current configuration file are overwritten. Finally, after changing the autocommit parameter content in the /etc/my.cnf file, reconnect to the MySQL server and find that the problem is solved.

In summary, the following small points of knowledge need to be noted:

1. When you find that the data cannot be deleted, you can first check whether the transaction submission parameter is set to off

2. Use show variables and show global variables to view the transaction parameters of the current session and global variables respectively;

3. The parameters in the mysql group in the my.cnf file are used to control the configuration of the mysql client.

4. The my.cnf file has a loading order. When changing it, all of them need to be changed. Or ensure that there is only one my.cnf file.

The above is the detailed content of troubleshooting the reasons why MySQL deleted records are not effective. For more information about MySQL deleted records not effective, please pay attention to other related articles on 123WORDPRESS.COM!