#2006 - MySQL server has gone away on db import

Question

So I have a new computer and I'm trying to set everything up for some projects.

When I try to import some databases I'm getting this message after the import fails in phpmyadmin:

Missing expression. (near "ON" at position 25)
SET FOREIGN_KEY_CHECKS = ON;
MySQL error 2006: mysql server has gone away

and I get logged out of phpmyadmin alltough the import just takes few seconds.

I already read some hints and I already did:

1. Set the my.cnf values like:

[mysql]
max_allowed_packet=512M

[mysqld]
max_allowed_packet=512M
wait_timeout=600
interactive_timeout = 86400

2. and also adjusted my php.ini to:

max_execution_time = 500
max_input_time = 500
memory_limit = 512M
post_max_size = 512M
upload_max_filesize = 256M

3. Tried the import via command line:

mysql -u USER -p database < import.sql

The command line gives me this as an error:

ERROR 2013 (HY000) at line 12042: Lost connection to MySQL server during query

So apparently this is not just some php stuff.

The import seems to fail. I tried it with multiple Databases. Some where 10MB, some where 120MB. If I start a fresh web application or a wordpress instance, everything works fine and there is no error at all. But the failing databases should also work fine. At least they do on production, staging and on my former working machine.

So I'm a bit lost here.

Here are the current version:

Ubuntu 20.04
mysql  Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
PHP 7.4.25 (cli) (built: Oct 22 2021 12:34:33) ( NTS )
phpmyadmin 4:4.9.5+dfsg1-2

Update: I looked into the error.log of mysql:

2021-10-29T13:10:12.337942Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.27-0ubuntu0.20.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).
2021-10-29T13:14:31.622915Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: ddl0builder.cc:1495:n >= IO_BLOCK_SIZE thread 140053145696000
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
13:14:31 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x100000

Maybe someone did run into this?

Cervical

copy the sql files to the server and import there with mysql -u – Pinnacle

Thanks for your reply. This is a local dev machine. I already tried it with the command line as mentioned above. – Cervical

this looks like an internal bug you can try to report it at the bugtracker – Pinnacle

Ok this is again me. Yesterday I set up docker containers. So this works as a solution. But I still don't understand the cause of the error. But: I found out, that when I mysqldump the data directly from the console, the import with that SQL works fine. Before I used the export functionality from phpmyadmin. Somehow the export adds something to the dump, that doesn't work well with a different mysql version (old system 5.something.something new mysql 8.0.27). – Cervical

Having exactly same issues with 8.0.27, there is a stack trace on the first occurence though :-/ – Crepitate

@JanDrábek I resolved it with the command mysqldump. Exporting with phpmyadmin added some stuff to the SQL file, which generated some problems on the import. With the dump through the console everything was fine. Maybe this also solves your problem? – Cervical

@Cervical mostly, the crash disappeared but another error with constraints has appeared. Resigned on Mysql 8.0 afterwards and went to 5.7 and there everything works as expected. – Crepitate

Answer 1

This answer is piggybacking off this answer from emanuelv. Unfortunately, I do not have enough reputation to comment.

If this is indeed an error due to phpMyAdmin attempting to add table indices after data insertion, and MySQL 8.0 chokes on tables with a lot of data already present during import, you can tell phpMyAdmin to use IF NOT EXISTS during table creation, thus forcing indices to be created before any data is inserted.

On the database export screen, select the "Custom" export method, and enable the following option:

[✓] IF NOT EXISTS (less efficient as indexes will be generated during table creation).
Screenshot of the option

Brune

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Felecia

Thank you for sharing, Ed! That helped me get an import going that would otherwise cost me more than just a day's work. – Moyna

Thank you, been dealing with that issue for the last 3 days. That checkbox should be active by default, makes no sense to allow you let you export if when you need you can't import some databases – Ianteen

I was also able to resolve the issue in phpMyAdmin by exporting the structure and data in 2 separate files. – Lesslie

Answer 2

This error happens when dumping databases with PhpMyAdmin, since it adds index creation in a separate statement at the end. It first creates the TABLE without indices, then INSERTs all the data and at the end it creates indices with ALTER statement. The import will fail on MySQL 8 when creating multiple indices in one ALTER statement.

So, this will fail (if the data is already in the table):

ALTER TABLE `wp_posts`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `post_name` (`post_name`(191)),
  ADD KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  ADD KEY `post_parent` (`post_parent`),
  ADD KEY `post_author` (`post_author`);

And this will not:

ALTER TABLE `wpul_posts`  ADD PRIMARY KEY (`ID`),
ALTER TABLE `wpul_posts`  ADD KEY `post_name` (`post_name`(191)),
ALTER TABLE `wpul_posts`  ADD KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
ALTER TABLE `wpul_posts`  ADD KEY `post_parent` (`post_parent`),
ALTER TABLE `wpul_posts`  ADD KEY `post_author` (`post_author`);

Still trying to figure out what exactly is the deal here, looks like something to do with collations / charsets.

Would appreciate any further info on this error.

Flagella

Answer 3

By now, I switched to docker containers. But I still found the reason for the problem and want to share the solution with you:

On my old system with mysql 5.something I exported the database directly from phpmyadmin. This file couldn't be imported to mysql 8.0.27 with the import function or the command line.

The solution was a dump with mysqldump. This SQL had no problems when importing.

Somehow I thought, that the export of phpmyadmin is the same as the mysqldump.

So if someone runs into this problem, mysqldump is the solution. :)

Cervical

Answer 4

We solved this problem with mysqldump, too. The client had a phpMyAdmin 4.9.7 SQL Dump from MySQL server 5.7.36 to be imported on MySQL Server 8.0.27. I broke it down to the following reproducer:

-- phpMyAdmin SQL Dump
-- version 4.9.7
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Erstellungszeit: 29. Nov 2021 um 21:06
-- Server-Version: 5.7.36
-- PHP-Version: 7.3.33

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
DROP TABLE IF EXISTS `s_mail_log`;
CREATE TABLE `s_mail_log` (
  `id` int(11) NOT NULL,
  `type_id` int(11) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `shop_id` int(10) UNSIGNED DEFAULT NULL,
  `subject` longtext COLLATE utf8_unicode_ci,
  `sender` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `sent_at` datetime NOT NULL,
  `content_html` longtext COLLATE utf8_unicode_ci,
  `content_text` longtext COLLATE utf8_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `s_mail_log` (`id`, `type_id`, `order_id`, `shop_id`, `subject`, `sender`, `sent_at`, `content_html`, `content_text`) VALUES
(3586,
2,
9463,
1,
'A',
'B',
'2021-09-22 12:51:39',
'1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222233333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333334444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444123456789',
'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222223333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444455555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555556666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777788888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888889999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999111111111122222222223333333333444444444455555555556666666666123456789012345678'
),
(3587,
2,
9465,
1,
'1234567890123456789012345678',
'123456789012345',
'2021-09-22 14:20:30',
'11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111',
'22222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222');
ALTER TABLE `s_mail_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `s_mail_log_idx_type_id` (`type_id`),
  ADD KEY `s_mail_log_idx_order_id` (`order_id`),
  ADD KEY `s_mail_log_idx_shop_id` (`shop_id`);
 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Fails to console with

ERROR 2013 (HY000) at line 64: Lost connection

and in the mysql.err with

[ERROR] [MY-013183] [InnoDB] Assertion failure: ddl0builder.cc:1495:n >= IO_BLOCK_SIZE thread 140481166030592
InnoDB: We intentionally generate a memory trap.

at

ADD KEY `s_mail_log_idx_shop_id` (`shop_id`);

Prisoner

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Bucharest