0

I've ran into a problem with my computer, so I needed to format it and fully re-install all my OS (Linux Mint 20.3). This wasn't a problem, since I need to re-import all my databases from a previously created .sql dump that I've created before starting the process of cleaning my computer.

I've used phpMyAdmin to create a full dump of my MySQL server. But, after fully reinstalling my OS, softwares and MySQL Server (version 8.0.27), I can't re-import my .sql dump.

Tried using CLI mysql command ( mysql -u raphael < localhost.sql ) and directly from phpMyAdmin web interface, but I'm always receiving the following error:

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

This dump has some Wordpress databases, and the content at line 17476 is that:

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`);

It's a very large file (aprox. 2.116.647 lines) and it's crucial to me to restablish all databases, because most of them are related to my work.

I've already tried to edit the /etc/mysql/my.cnf file, adding the following content:

[mysqld]
max_allowed_packet = 512M
connect_timeout = 600

But the problem still persists.

Can someone please help me?

Thanks!

RBFraphael
  • 367
  • 1
  • 3
  • 13
  • what happens before that line, is data inserted into that same table? You could try to remove that index line and place it right after where the table is created and run it again – Raymond Nijland Jan 16 '22 at 15:41
  • The error means that the execution time is larger then client result waiting timeout. – Akina Jan 16 '22 at 15:53
  • @RaymondNijland before that line, it has another ALTER TABLE... ```ALTER TABLE `wp_postmeta` ADD PRIMARY KEY (`meta_id`), ADD KEY `post_id` (`post_id`), ADD KEY `meta_key` (`meta_key`(191));``` – RBFraphael Jan 16 '22 at 15:55
  • @Akina Sorry, but I'm not expert in MySQL configuration haha... What I need to do? – RBFraphael Jan 16 '22 at 15:57
  • when the timeout occurs after 10 minutes (=600 seconds) , you should increase the `connect_timeout` to a higher value, and the other timeouts mentioned here: https://stackoverflow.com/a/19610568/724039 – Luuk Jan 16 '22 at 16:04
  • @Luuk the problem is the timeout occurs in about 5~6 seconds, not 10 minutes, but I've already set the connect_timeout in my.cnf to 600 (in [mysqld], [mysql] and [client] sections)... – RBFraphael Jan 16 '22 at 16:18
  • @RBFraphael You should check the `MySQL` and server logs for further inspect. Run `tail -f /var/log/syslog` when `ERROR 2013 (HY000) at line 17476: Lost connection to MySQL server during query` occured you will get a hint on the logs. Check server physical memory and memory cache to. One time i faced the same problem was memory cache increasing – Ergest Basha Jan 16 '22 at 16:26
  • @ErgestBasha My server has 16GB physical memory, and, unfortunately, there's no useful information at logs... The MySQL error.log and syslog only shows "mysql.server failed with result exit-code", followed by three lines of MySQL restart process ("Stopped MySQL Community Server", "Starting MySQL Community Server" and "Starter MySQL Community Server")... – RBFraphael Jan 16 '22 at 16:34
  • connect timeout is not the right parameter (it says how long you wait to connect to the server, e.g. login). What you probably want is to increase "wait_timeout". – Solarflare Jan 16 '22 at 16:35
  • @Solarflare, `wait_timeout` times out idle connections, not active connections. – Bill Karwin Jan 16 '22 at 16:40
  • @Solarflare Already set wait_timeout to 600 in [mysqld] section of my.cnf file, but stills not working... :( – RBFraphael Jan 16 '22 at 16:41
  • *Tried using CLI mysql command ( mysql -u raphael < localhost.sql )* !!! Start CLI in interactive mode. Execute command `SOURCE '/path/localhost.sql'`. Wait until finishing or falling, in last case report the error message and the statement which causes the error. – Akina Jan 16 '22 at 18:05

1 Answers1

0

I've tried a lot of things, and finally (I think) figured out WHY and HOW to fix this.

I've found somewhere in the internet someone that said "after upgrading from MySQL Server 5.7 to 8.0, phpMyAdmin is corrupting my dumps"... In my case, I've already using MySQL Server 8, but this made me think about...

So, I tried to create a VirtualBox VM with any Linux distro (I've used Ubuntu Server, but doesn't matter), installed MySQL 5.7 (downloaded from here) and tried to restore my .sql file. I'm surprised that worked fine. After that, I've used the CLI mysqldump to re-create all my localhost dump (from VirtualMachine), and imported the resulting .sql file inside my working machine, and all worked fine.

So, phpMyAdmin was the problem, even using the latest version downloaded directly from official website. There's something in phpMyAdmin (maybe a configuration that I don't know about, or a bug, or something else) that causes dumps from MySQL 8 to be corrupted, wrong file formatting, or maybe the moon isn't aligned with Jupiter and Venus... Is the first time this happens to me.

Thank you for all support, comments and ideas. I really don't know if this solution was the best (or the correct), but it was the only thing that worked for me. Maybe other people can take benefits with this solution.

RBFraphael
  • 367
  • 1
  • 3
  • 13
  • 1
    see: [How to solve time out in phpmyadmin?](https://stackoverflow.com/questions/16707532/how-to-solve-time-out-in-phpmyadmin), but indeed a better solution is: Do not use phpMyAdmin. – Luuk Jan 16 '22 at 19:19