0

I am having trouble importing semi-large and large databases via SOURCE in MySQL. I am currently working with PHPMyAdmin and have found using SOURCE instead of importing via PHPMyAdmin is a lot faster. Databases that range from KB to a few hundred MB imports via SOURCE Just fine.

However, I am attempting to import a larger file, 705mb. The importation via SOURCE works fine, however, I run into an error a few minutes into the importing.

Query OK, 143 rows affected (0,00 sec)
Records: 143  Duplicates: 0  Warnings: 0

ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR: 
Can't connect to the server

No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR: 
Can't connect to the server

No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR: 
Can't connect to the server

No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR: 
Can't connect to the server

No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR: 
Can't connect to the server

No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR: 
Can't connect to the server

then it just outputs me back to the normal mysql, the server still runs and works fine.

Here is the fresh output of my log file.

2022-01-24T19:32:49.392256Z 0 [Warning] [MY-000081] [Server] option 'max_allowed_packet': unsigned value 2147483648 adjusted to 1073741824.
2022-01-24T19:32:49.393200Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27-0ubuntu0.20.04.1) starting as process 61918
2022-01-24T19:32:49.399762Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-01-24T19:32:51.373828Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-01-24T19:32:51.467545Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2022-01-24T19:32:51.474007Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2022-01-24T19:32:51.542533Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-01-24T19:32:51.542573Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-01-24T19:32:51.543651Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-01-24T19:32:51.543718Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-01-24T19:32:51.579154Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-01-24T19:32:51.579215Z 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).
Saman Salehi
  • 1,004
  • 1
  • 12
  • 19
SQLHelp324223
  • 1
  • 1
  • Remember, phpMyAdmin is a PHP written tool and therefore has the limitation of the PHP `max_execution_time` for a start. If you can run these from the command line I would suggest you do – RiggsFolly Jan 24 '22 at 18:26
  • Hello. I ended up modifying that in the php.ini file I believe. I am able to import any size database via IMPORT in phpmyadmin. I am thinking it is a memory issue. However still running into the issue with SOURCE in the mysql shell – SQLHelp324223 Jan 24 '22 at 18:30
  • try one of these https://stackoverflow.com/questions/17666249/how-do-i-import-an-sql-file-using-the-command-line-in-mysql – nbk Jan 24 '22 at 18:47
  • I have read some of the answers on that thread. None seem to help. Instead of using SOURCE I exited out of the sql shell. I used mysql -u ... -p ... database_name < /path/database.sql to import. I receive a similar error ERROR 2013 (HY000) at line 2093853: Lost connection to MySQL server during query – SQLHelp324223 Jan 24 '22 at 19:09
  • Large .SQL dumps must be loaded using command-line client. I.e. open CLI then give SOURCE command. And check max packet size setting. – Akina Jan 24 '22 at 19:13
  • Hello I am using SOURCE via CLI. about 60 seconds to a few minutes in I get this error Query OK, 143 rows affected (0,00 sec) Records: 143 Duplicates: 0 Warnings: 0 ERROR 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect... ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR: Can't connect to the server – SQLHelp324223 Jan 24 '22 at 19:20
  • Does the table(s) have big TEXT or BLOB columns? 143 dups out of 143 rows -- I would figure out that before worrying about the speed. – Rick James Jan 25 '22 at 18:04
  • Is the file several huge `INSERT` statements, as if created by `mysqldump`? – Rick James Jan 25 '22 at 18:06

1 Answers1

0

Does this server goes through any other workload other than loading this source file ? Check your /var/log/syslog or /var/log/messages for OOM [ Out of Memory Killer ] messages. Like others said please use mysql client cli to load the data instead of source. The above log that you got for failure is after the crash has happened you import failed and mysql error log shows it crashed but still the reason for crash is unclear. So I suspect either it is OOM killing mysql due to source which you can try to mitigate using mysql client.

SelvaRaj B
  • 96
  • 2