Shulou(Shulou.com)05/31 Report--

This article is about how mysqlimport troubleshoots import errors. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

A colleague asked me a question about the import of mysqlimport, which seemed strange. A colleague imports a file on the client side. File size is 2.8G, then error mysqlimport: Error: 2013, Lost connection to MySQL server during query

My first feeling about this problem is that a CVM is not configured with swap, because in a previous migration, I was tortured by this kind of problem, encountered the problem of OOM-Killer, and finally found that it was caused by no configuration of swap.

But colleagues confirmed that this is a physical machine, and check the hardware situation, the configuration is good. Buffer_pool_size has more than 50 gigabytes of swap configured. It seems that the cause of this problem has been preliminarily ruled out.

There are also many opinions on the Internet about this problem. Some say max_allowed_packets caused it. Check that the current configuration is 1G, which looks smaller than the import file.

However, from the experience in Oracle, it seems that this statement is not entirely reliable, if I import a 100G dump, then this parameter will have to be configured more than 100G, which does not sound reasonable. And I have never seen an environment with an extremely high value.

I did a few simple tests for this.

First of all, a test environment is found, and the max_allowed_packets is more than 30 M.

# mysqladmin var | grep max_all

| | max_allowed_packet | 33554432 |

| | slave_max_allowed_packet | 1073741824 |

A small watch is locked.

] > select count (*) from t_fund_info

+-+

| | count (*) |

+-+

| | 1998067 |

By looking at the file size, it is about more than 400 M.

-rw-rw---- 1 mysql mysql 482344960 Oct 13 16:01 t_fund_info.ibd

This makes it easy to simulate the scenario of testing max_allowed_packets.

Export the file first.

> select * from t_fund_info into outfile'/ tmp/t_fund_info.txt'

Query OK, 1998067 rows affected (6.82 sec)

The exported text file is nearly 300m.

Ll t_fund_info.txt

-rw-rw-rw- 1 mysql mysql 291963062 Oct 20 22:25 t_fund_info.txt

Then use the simplest command to import:

Mysqlimport test'/ tmp/t_fund_info.txt'

After a short wait, it was successfully imported.

You can see the output of the slow log as follows:

# Time: 161020 22:31:49

# User@Host: root [root] @ localhost []

# Thread_id: 4321910 Schema: test Last_errno: 0 Killed: 0

# Query_time: 92.866443 Lock_time: 0.000074 Rows_sent: 0 Rows_examined: 0 Rows_affected: 1998067 Rows_read: 0

# Bytes_sent: 68

SET timestamp=1476973909

LOAD DATA INFILE'/ tmp/t_fund_info.txt' INTO TABLE `troomfund _ info` IGNORE 0 LINES

It turns out that mysqlimport will be converted to load data.

Then the delete,truncate,drop is rebuilt and all can be imported successfully.

What is the test is not in place, I began to look at this problem, we can simulate this problem in the test environment, based on the data that went wrong at the time. Then check the logs and screenshots provided by my colleagues, and find that the command used at that time is

Mysqlimport test'/ tmp/t_charextra.txt'-local-delete-default-character=gbk

The character set of this table has been confirmed to be latin, so it is doubtful whether it is caused by character set conversion.

ENGINE=InnoDB DEFAULT CHARSET=latin1

But after a short wait, it can still be successfully imported.

# mysqlimport test'/ tmp/t_charextra.txt'-local-delete-default-character=gbk

Test.t_charextra: Records: 480174 Deleted: 0 Skipped: 0 Warnings: 0

Under the premise that there is a large amount of data in the table t_charextra, let's continue to try the above method of import.

The whole import will be divided into two parts.

SET timestamp=1476975647

DELETE FROM t_charextra

SET timestamp=1476975748

LOAD DATA LOCAL INFILE'/ tmp/t_charextra.txt' INTO TABLE `t _ charextra` IGNORE 0 LINES

Can still be successfully imported

# mysqlimport test'/ tmp/t_charextra.txt'-local-delete-default-character=gbk

Test.t_charextra: Records: 480174 Deleted: 0 Skipped: 0 Warnings: 0

Is where the test is not in place? now the only thing we can think of is two points: one is called through the client, but the network delay between the client and the server is large.

The client user is configured to copy the exported text to the client to reproduce the problem.

> grant select,insert,delete on test.* to testdb@10.127.133.86 identified by 'mysqlnew'

Query OK, 0 rows affected (0.02 sec)

Then use the following command to try the client import.

# mysqlimport-h 10.127.xxxx-utestdb-pmysqlnew test'/ U01 Universe tacticharextra.txt'-local-- delete-- default-character=gbk

Warning: Using unique option prefix default-character instead of default-character-set is deprecated and will be removed in a future release. Please use the full name instead.

Test.t_charextra: Records: 480174 Deleted: 0 Skipped: 0 Warnings: 0

From the log, everything is normal except for a warning, so now the focus is on the network, this point still needs to be further confirmed, but so far many reasons have been ruled out, and presumably the reasons are about to be revealed.

Thank you for reading! This is the end of this article on "how to troubleshoot import error reports in mysqlimport". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.