When restoring a MySQL database, i get error “MySQL server has gone away”.
|
1 2 3 |
# mysql -u root -p'serverok123' sok_wp < backup.sql ERROR 2006 (HY000) at line 5095: MySQL server has gone away # |
To fix this, edit MySQL configuration file. Add following line under [mysqld] section.
|
1 |
max_allowed_packet = 256M |
Restart MySQL
|
1 |
systemctl restart mysql |
If you use MariaDB
|
1 |
systemctl restart mariadb |
Related Posts
On my computer running Ubuntu 18.04, MriaDB stopped working. PHP application i run on my computer failed with error
|
1 |
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ok_test.feeds' doesn't exist (SQL: select * from `feeds` where `processed` = 0) |
Application can’t find the table. So i tried to login to MySQL and see if table is there or not. But i get error
|
1 2 3 4 5 6 7 8 9 10 11 12 |
boby@sok-01:~$ mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2504 Server version: 10.2.30-MariaDB-1:10.2.30+maria~bionic-log mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; ERROR 1018 (HY000): Can't read dir of '.' (errno: 24 "Too many open files") MariaDB [(none)]> |
Next i checked if this is due to any MySQL upgrade. I found MySQL got updated today
|
1 2 3 4 5 6 |
root@sok-01:~# grep mariadb /var/log/dpkg.log 2019-12-13 08:02:46 status triggers-pending mariadb-server-10.2:amd64 1:10.2.29+maria~bionic 2019-12-13 08:02:46 upgrade mariadb-common:all 1:10.2.29+maria~bionic 1:10.2.30+maria~bionic 2019-12-13 08:02:46 status half-configured mariadb-common:all 1:10.2.29+maria~bionic 2019-12-13 08:02:46 status unpacked mariadb-common:all 1:10.2.29+maria~bionic 2019-12-13 08:02:46 status half-installed mariadb-common:all 1:10.2.29+maria~bionic |
To fix this error, run
|
1 |
systemctl edit mysql |
This wil open a text editor. Add following
|
1 2 |
[Service] LimitNOFILE=8192 |
Save and quit the editor. Restart MaraDB with
|
1 |
systemctl restart mysql |
MySQL will work properly now. systemctl edit mysql will create file /etc/systemd/system/mysql.service.d/override.conf
|
1 2 3 4 5 |
root@sok-01:~# cat /etc/systemd/system/mysql.service.d/override.conf [Service] LimitNOFILE=8192 root@sok-01:~# |
MariaDB is an Open Source MySQL drop in replacement. MariaDB provides repository for various operating systems at
http://downloads.mariadb.org/mariadb/repositories/
To install MariaDB 10.3 on CentOS 7, create file
|
1 |
vi /etc/yum.repos.d/MariaDB.repo |
Add
|
1 2 3 4 5 |
[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.3/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 |
Install MariaDB server and client with
|
1 |
yum install MariaDB-server MariaDB-client |
Enable MariaDB start on boot
|
1 |
systemctl enable mariadb |
Start MariaDB
|
1 |
systemctl start mariadb |
When trying to restore a MySQL databse bakcup taken on MySQL 5.7 server to MairaDB 10.1 server i get following error.
|
1 2 3 4 5 6 |
root@server:~# mysql -u vcon_user -p vcon_sbc < vcon_sbc.sql Enter password: ERROR 1064 (42000) at line 287: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` ti' at line 3 root@server:~# |
Source server
|
1 2 3 |
root@ip-172-31-16-209:~# mysql --version mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper root@ip-172-31-16-209:~# |
Destination server
|
1 2 3 |
root@server:~# mysql --version mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 root@server:~# |
I opend the SQL file in editor and checked line 287, i run the SQL in mysql, that resulted in error
|
1 2 3 4 5 6 7 8 9 10 11 |
MariaDB [test]> CREATE TABLE `current_job_status_reports` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `jobs` json DEFAULT NULL, -> `created_at` timestamp NULL DEFAULT NULL, -> `updated_at` timestamp NULL DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` ti' at line 3 MariaDB [test]> |
I run the command on a MariaDB 10.2 server and it worked.
To fix the problem, upgrade MariaDB to version 10.2 or above. In this case, i went with MariaDB 10.04 and it worked fine.
This script configure MariaDB repository on your server. You can find more details on offical page
https://mariadb.com/kb/en/library/mariadb-package-repository-setup-and-usage/
To setup mariadb repo, run
|
1 |
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash |
I was expecting it ask me what version of MariaDB i like to setup. But it just setup MariaDB 10.4. I wanted to upgrade MariaDB to version 10.2, anyway i did upgrade to 10.4 and everything worked fine.
If you need a speicfic version, you can run like
|
1 |
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.3" |
Availabe versions are
|
1 2 3 4 5 6 |
mariadb-5.5 mariadb-10.0 mariadb-10.1 mariadb-10.2 mariadb-10.3 mariadb-10.4 |
When i run the script on Debian 9 server, i get following error
|
1 2 3 4 5 6 7 8 9 |
root@server:~# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash [info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list [info] Adding trusted package signing keys... Executing: /tmp/apt-key-gpghome.TKjSzTY6ww/gpg.1.sh --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 0x8167EE24 0xE3C94F49 0xcbcb082a1bb943db 0xF1656F24C74CD1D8 0x135659e928c12247 gpg: failed to start the dirmngr '/usr/bin/dirmngr': No such file or directory gpg: connecting dirmngr at '/run/user/0/gnupg/d.3y8gtbm7kaj8h4royj6yoyug/S.dirmngr' failed: No such file or directory gpg: keyserver receive failed: No dirmngr [error] Failed to add trusted package signing keys. root@server:~# |
This is fixed by running
|
1 |
apt -y install dirmngr |
