How to Solve Error Message MySQL Server Has Gone Away when Restoring SQL File in a Database running in MariaDB Server

Posted on

[root@localhost backup-db]#

Introduction

When restoring a SQL file to a database running in a MariaDB Server, the process end in failure. The failure exist as in the following process for restoring an SQL file into a database running in MariaDB server :

[root@localhost backup-db]# mysql -uroot -p db_latest < db_restore.sql
Enter password:
ERROR 2006 (HY000) at line 317: MySQL server has gone away
[root@localhost backup-db]#

As in the above process, there is an error message appear with the specific error as follows :

ERROR 2006 (HY000) at line 317: MySQL server has gone away

Solution

Actually, the process is very simple in order to solve the problem. One of the reason of the error to appear is because of the size of the SQL file. Actually, because of a certain restriction in the MariaDB server for importing or restoring file into it, it is triggering the error message. Basically, by default. The process for solving the problem exist as follows :

  1. First of all, access the file and check the size of it by executing a certain command as follows :

    [root@localhost backup-db]# du -sh db_restore.sql
    83M db_restore.sql
    [root@localhost backup-db]#
  2. After confirming the size of the file, there is a certain article giving the information about the same error message. The possible cause is because of the size of the file does not meet the requirement for further processing. In this article, it is the process for restoring an SQL file into a database. The database in this context is running in MySQL database or MariaDB database for an example.

  3. In order to meet the requirement for restoring the SQL file into the database, modify the setting or configuration of the database. Just add the following line of configuration to meet the requirement for restoring the SQL file into the database :

    max_allowed_packet=size

    The configuration entry above as a pattern will be available in the MySQL or MariaDB server to fulfill the requirement. Usually, the file exist in ‘/etc/my.cnf’. So, open the file and add the above configuration pattern in the [mysqld] block as follows :

    [mysqld]
    max_allowed_packet=100M
    

    The above configuration instruct the MySQL or MariaDB to accept or to allow the packet processing with the maximum size of 100 M. So, it will process the SQL file with the size below 100 M like in the previous step. It is a file with the name of ‘db_restore.sql’ with the size of 83M.

  4. Continue on the step, restart the service of MySQL or MariaDB server. It is important in order to implement the modification of it. Just check the status of the service first as follows :

    [root@localhost backup-db]# systemctl status mariadb
    ● mariadb.service - MariaDB database server
    Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
    Active: active (running) since Tue 2021-10-12 10:46:49; 21h ago
    Process: 20951 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
    Process: 20919 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
    Main PID: 20950 (mysqld_safe)
    CGroup: /system.slice/mariadb.service
    ├─20950 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
    └─21124 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin -...
    
    Oct 12 10:46:46 localhost systemd[1]: Starting MariaDB database server...
    Oct 12 10:46:47 localhost mariadb-prepare-db-dir[20919]: Database MariaDB is probably initialized in /var/lib/mys...one.
    Oct 12 10:46:47 localhost mariadb-prepare-db-dir[20919]: If this is not the case, make sure the /var/lib/mysql is...dir.
    Oct 12 10:46:47 localhost mysqld_safe[20950]: 211012 10:46:47 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
    Oct 12 10:46:47 localhost mysqld_safe[20950]: 211012 10:46:47 mysqld_safe Starting mysqld daemon with databases ...mysql
    Oct 12 10:46:49 localhost systemd[1]: Started MariaDB database server.
    Hint: Some lines were ellipsized, use -l to show in full.
    [root@localhost backup-db]#
    

    Following after, just execute the following command to restart the MariaDB server :

    [root@localhost backup-db]# systemctl restart mariadb
    [root@localhost backup-db]#
    
  5. Last but not least, just execute the command to restore the SQL file into the database once more. If there is no other error appear, the restore process will end in a success.

Leave a Reply