Shulou(Shulou.com)06/01 Report--

A brief introduction to the dangerous and weird update operation and shock of MySQL for 5 minutes.

Part1: write at the front

When I was very busy at work, a news came that the R & D staff had manipulated the database by mistake. Without where conditions, update the Orz of the whole table, but also let people live a good life, ten thousand XXX ah ~ helpless, distinguish the priority of things, give priority to deal with this accident.

After simple communication, we learned that the cause of the accident was that the R & D staff forgot to bring the where condition when using update. There is nothing weird about this in itself. what is weird is that when deciding whether or not to recover, the author hesitated a little, because it seems that there is no need for recovery, so what is the specific situation?

Part2: dangerous scene reappearance

Update, a developer, used the wrong syntax, which meant update helei3 set axiom 1 'where bounded like a'.

As a result, it was written as update helei3 set axiom 1 'and bounded like a'

This causes the a column of the helei3 table to be batch modified to 0 or 1.

After a few seconds, I found that I had made a mistake and hit enter, and the update statement had not been updated yet. Ctrl+c immediately.

So is the data written dirty or not?

Reappearance

Part1: create the required tables

First of all, let's create a test table. Columns an and b are of varchar type.

Root@127.0.0.1 (helei) > show create table helei3\ gateway * 1. Row * * Table: helei3Create Table: CREATE TABLE `helei3` (`a` varchar (10) DEFAULT NULL, `b` varchar (255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The data in the table is as follows

Root@127.0.0.1 (helei) > select * from helei3;+-+-+ | a | b | +-+-+ | 1 | a | 2 | b | 3 | c | +-+-+ 3 rows in set (0.00 sec)

Part2: error statement generation

As we all know, the syntax of update is update tablename set col1=val,col2=val2 where xxx.

So what are the serious consequences when the comma is changed to and?

At this time, because there is no where condition, the whole table is updated. Guess what the follow-up result will be.

Root@127.0.0.1 (helei) > update helei3 set axioms 1 'and breadwinners rootstocks 127.0.0.1 (helei) > select * from helei3;+-+-+ | a | b | +-+-+ | 1 | a | 0 | b | 0 | c | +-+-+ 4 rows in set (0.00 sec)

Yes, this SQL updates the entire table of column a to 0, and the reason why the first aq1 is true is that the condition is true, so it is 1.

Part3:ctrl+c

After learning about Part2, let's take a look at whether ctrl+c can roll back to avoid misoperation immediately after the update full table update discovers misoperation.

Prepare a table with 500000 data in advance

Root@127.0.0.1 (helei) > select count (*) from helei +-+ | count (*) | +-+ | 500000 | +-+ 1 row in set (0.06 sec) root@127.0.0.1 (helei) > show create table helei\ gateway * 1. Row * * Table: heleiCreate Table: CREATE TABLE `helei` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT `c1` int (10) NOT NULL DEFAULT '0mm, `c2` int (10) unsigned DEFAULT NULL, `c5` int (10) unsigned NOT NULL DEFAULT' 0mm, `c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c4` varchar (200) NOT NULL DEFAULT'', PRIMARY KEY (`id`), KEY `idx_ c1` (`c1`), KEY `idx_ c2` (`c2`) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec)

After misoperation, update the whole table and wait a few seconds immediately ctrl + c

Root@127.0.0.1 (helei) > update helei set c2o1; ^ CCtrl-C-- sending "KILL QUERY 2133" to server... Ctrl-C-- query abord.^ CCtrl-C-- sending "KILL 2133" to server... Ctrl-C-- query aborted.ERROR 2013 (HY000): Lost connection to MySQL server during queryroot@127.0.0.1 (helei) > select * from helei where c2prof1Empty set

You can see that the c2 column is not partially updated to 1, which means that the operation of the entire table update is rolled back.

If you are careful, you can see that the binlog pos number has not changed.

Root@127.0.0.1 (helei) > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | mysql-bin.000004 | 124886658 | +-+ 1 row in set (sec)

Part4: weird

After reading the first three chapters, let's take a look at what's weird. In a production environment, we don't know if the SQL has updated some of the data, so we use this approach to verify it.

Root@127.0.0.1 (helei) > select * from helei3 where astatine 0 root@127.0.0.1 + | a | b | +-+-+ | 0 | b | | 0 | c | +-+-+ 2 rows in set (0.00 sec) root@127.0.0.1 (helei) > select * from helei3 where aqui0 +-+-+ | a | b | +-+-+ | 0 | b | | 0 | c | zz | zz | +-+-+ 3 rows in set (0.00 sec)

Found that the data is inconsistent, the production environment is more bluffing, the column does not store 0, but letters or pure numbers, when I implement the above two SQL, I found that the results are much worse, but also exposed a lot of warnings.

| | Warning | 1292 | Truncated incorrect DOUBLE value: 'XXX' |

So I want to know if the misoperation just took effect, and why there is such a difference in quotation marks.

Analysis.

Part1: building data

Root@127.0.0.1 (helei) > insert into helei3 values ('zz','zz'); root@127.0.0.1 (helei) > select * from helei3;+-+-+ | a | b | +-+-+ | 1 | a | 0 | b | | 0 | c | | zz | zz | +-+-+ 4 rows in set (0.00 sec)

Part2: query comparison

So when we execute a query, there are two results.

Root@127.0.0.1 (helei) > select * from helei3 where astatine 0 root@127.0.0.1 + | a | b | +-+-+ | 0 | b | | 0 | c | +-+-+ 2 rows in set (0.00 sec) root@127.0.0.1 (helei) > select * from helei3 where aqui0 +-+-+ | a | b | +-+-+ | 0 | b | | 0 | c | zz | zz | +-+-+ 3 rows in set (0.00 sec)

Why is that?

Part3:root cause

Root@127.0.0.1 (helei) > select 'zz'=0;+-+ |' zz'=0 | +-+ | 1 | +-+ 1 row in set, 1 warning (0.00 sec) root@127.0.0.1 (helei) > select 'zz3'=0 +-+ | 'zz3'=0 | +-+ | 1 | +-+ 1 row in set, 1 warning (0.00 sec) root@127.0.0.1 (helei) > select' 3 sec 0 | +-+ | 0 | +-+ 1 row in set (0.00 sec)

As you can see, when the letter is included, mysql thinks that = 0 is true and throws warning.

Root@127.0.0.1 (helei) > show warnings +-- + | Level | Code | Message | +- -+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'zz' | +-+ 1 row in set (0.00 sec)

Part4:MySQL Doc

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error.

Part5: my understanding

The InnoDB storage engine conforms to the ACID characteristics of transactions. It will complete all operations at once or will not perform operations and rollback in the event of an interruption. InnoDB is also the default engine for MySQL 5.5 and above.

But for non-transactional MyISAM storage engines. His atomic operations are done one by one. So if you interrupt the process, it will be updated / deleted wherever it goes.

-- Summary.

Through this article, you can understand the serious consequences of using the wrong syntax of and in update, and whether ctrl + c is effective before the execution of the SQL statement. Because the author's level is limited and the writing time is very short, it is inevitable that there will be some errors or inaccuracies in the article. I urge readers to criticize and correct them.

Like readers can click like to follow, your praise and attention is the greatest encouragement and support for the author to continue to post!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.