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

This article mainly explains "how to solve the pit of mybatisplus". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to solve the pit of mybatisplus".

Mybatisplus's pit insert tag insert into select has no parameters

Found in the actual project

Insert into xxxx select xxxx

Will report an error.

Org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:

# Error updating database. Cause: java.lang.NullPointerException

# Cause: java.lang.NullPointerException

At org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible (MyBatisExceptionTranslator.java:77)

After several investigations

Com.baomidou.mybatisplus.processBatch method TableInfo tableInfo = TableInfoHelper.getTableInfo (parameterObject.getClass ())

Here, the parameter object is taken as the table information. Since no parameter is passed, an error is reported.

Switch to label, problem solving ~

The pit of insert into select statement

Please use Insert into select carefully. On this day, xxx received a request to migrate the data from Table A to Table B to make a backup. I wanted to check it out first through the program and then insert it in batch. However, xxx felt that this was a bit slow and would cost a lot of network IBO, so he decided to take another approach to implement it. By roaming in the ocean of Baidu, he found that it could be implemented using insert into select, so that he could avoid using the network Imando O and rely directly on the database iMacro O to do it using SQL, which is simply not great. And then he got fired.

The course of the accident

Due to the large amount of order_today data in the data database, there seemed to be 700W at that time and was increasing at the rate of 30W every day. So the boss ordered xxx to migrate some of the data in order_today to order_record and delete the data in order_today. This reduces the amount of data in the order_ to day table. Considering that it will take up the database iMaple O, in order not to affect the business, the plan is to start migration after 9:00, but xxx at 8:00, tried to migrate a small part of the data (1000 items), and thought there was no problem, so he began to consider mass migration.

In the process of migration, the emergency group first reflects the payment failure of a small number of users, and then reflects the payment failure of a large number of users and the failure to initialize the order. At the same time, Tencent also starts to report to the police.

Then xxx panicked and immediately stopped the migration.

I thought I could recover if I stopped the migration, but I didn't. You can make up for what happens later.

Accident reduction

Set up a simplified version of the database locally and generate 100w of data. Simulate what happens online.

Establish the table structure

Order form

CREATE TABLE `order_ today` (`id`varchar (32) NOT NULL COMMENT 'key', `merchant_ id` varchar (32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'merchant number', `amount `decimal (15jing2) NOT NULL COMMENT 'order amount', `pay_success_ time`datetime NOT NULL COMMENT 'payment success time', `order_ status` varchar (10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'payment status S: payment succeeded, F: order payment failed' `remark` varchar (100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'remarks', `remark`time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', `update_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time-automatically updated when modified', PRIMARY KEY (`id`) USING BTREE,KEY `idx_merchant_ id` (`merchant_ id`) USING BTREE COMMENT 'merchant number') ENGINE=InnoDB DEFAULT CHARSET=utf8

Order record sheet

CREATE TABLE order_record like order_today

Simulated migration

Migrate all the data before the 8th to the order_record table.

INSERT INTO order_record SELECT * FROM order_today WHERE pay_success_time < '2020-03-08 00 purse 0000'

Run the migrated sql in navicat and open another window to insert data to simulate placing an order.

From the above, it can be found that it can be inserted normally at first, but then it suddenly gets stuck, and it takes 23 seconds to succeed, and then it can continue to insert. At this time, the migration has been successful, so it can be plugged in normally.

The reason for the appearance

Under the default transaction isolation level: insert into order_record select * from order_today locking rules are: order_record table lock, order_today step-by-step lock (scan a lock) to analyze the execution process.

By observing the execution of the migrated sql, you will find that the order_today is a full table scan, which means that when executing the insert into select from statement, mysql will scan the records in the order_today from top to bottom and lock them, which is the same as locking the table directly.

This can also explain why only a small number of users failed to pay at the beginning, followed by a large number of users who failed to pay and failed to initialize the order, because only a small part of the data was locked at the beginning. the data that is not locked can still be modified to normal. As more and more data are locked, there are a large number of payment failures. Finally, it was all locked, so that the order could not be inserted, and the initialization of the order failed.

Solution

As the query conditions will lead to order_today full table scan, what can avoid full table scan, it is very simple, to the pay_success_time field to add an idx_pay_suc_time index, because the index query, there will be no scan of the whole table and lock the table, only lock the records that meet the conditions.

Final sqlINSERT INTO order_record SELECT * FROM order_today FORCE INDEX (idx_pay_suc_time) WHERE pay_success_time

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.