MySQL import not working / hangs - depending on data in qa_cookies

Question

Just wanted to report a bug that I have faced the second time.

1. Export DB

2. Import DB into a new system

Does not matter if you use phpmyadmin or mysqldump.

Result: The import hangs. System becomes unresponsive.

As far as I could track down the issue, all tables until qa_cookies have been created.

I remember some time ago, that there were some illegal characters in the qa_cookies data that led to this issue.

Comments

How big is the file you're trying to import? The table before qa_cookies is qa_contentwords I think, which is usually one of the biggest.

---

qa_cookies was huge with 134 MB. I emptied the table, then there were no import problems. Still have the table file here, just need to find the time to test-import it on my localhost.

---

Okay, tested it quickly. Doing a manual import (pasting as sql queries in phpmyadmin).

Result: MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO `qa_cookies` (`cookieid`, `created`, `createip`, `written`, `writeip'

SQL query:

INSERT INTO `qa_cookies` (`cookieid`, `created`, `createip`, `written`, `writeip`) VALUES (12591317700600175973, '2016-05-12 23:39:42', 0, NULL, NULL), (12591319754066601742, '2016-07-15 20:10:41', 624635076, NULL, NULL), (12591334243999101289, '2015-10-15 16:07:41', 2731721825, NULL, NULL), (12591339743182897185, '2015-11-15 12:31:42', 1123634854, NULL, NULL), (12591341415055706741, '2015-08-04 00:10:12', 3233513122, NULL, NULL), (12591343278499382761, '2015-03-19 17:43:13', 3475901827, NULL, NULL), (12591352822975634793, '2015-02-08 12:56:22', 2637637604, NULL, NULL), (12591357700685679348, '2016-03-06 02:14:22', 1753416650, NULL, NULL), (12591400352378080566, '2015-03-02 02:58:42', 3475901748, NULL, NULL), (12591400604362287661, '2016-05-17 03:11:18', 1388412687, NULL, NULL), (12591408397068174396, '2016-05-01 15:06:54', 1152706300, NULL, NULL), (12591419443417439275, '2015-04-12 10:08:57', 3475901770, NULL, NULL), (12591427502161833066, '2015-12-08 06:31:49', 2637637[...]

---

I could not track down the error. Here is the file: http://www.q2apro.com/test/testimport.sql

Answer 1

Probably, you should adjust EXPORT option on export page of phpmyadmin OR mysqldump command.

1. "Maximal length of created query" (phpmyadmin)
2. "--skip-extended-insert" (phpmyadmin/mysqldump)

These options are closely related to the MySQL max_allowed_packet option. If the import by phpmyadmin fails, you may also need to adjust max_execution_time of PHP.

In addition, If logical backup fails, raw backup might be necessary. For example, Percona XtraBackup. However, this tool would need root privileges.