Tag: mariadb – ServerOK

Ubuntu 18.04 MariaDB 10.2 Too many open files

December 13, 2019

by ServerOk with No Comment Ubuntu

On my computer running Ubuntu 18.04, MriaDB stopped working. PHP application i run on my computer failed with error

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ok_test.feeds' doesn't exist (SQL: select * from `feeds` where `processed` = 0)

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

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)]>

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

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

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

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

systemctl edit mysql

1	systemctl edit mysql

This wil open a text editor. Add following

[Service]
LimitNOFILE=8192

1 2	[Service] LimitNOFILE=8192

Save and quit the editor. Restart MaraDB with

systemctl restart mysql

1	systemctl restart mysql

MySQL will work properly now. systemctl edit mysql will create file /etc/systemd/system/mysql.service.d/override.conf

root@sok-01:~# cat /etc/systemd/system/mysql.service.d/override.conf 
[Service]
LimitNOFILE=8192

root@sok-01:~#

root@sok-01:~# cat /etc/systemd/system/mysql.service.d/override.conf

[Service]

LimitNOFILE=8192

root@sok-01:~#

Install MariaDB 10.3 on CentOS 7

November 22, 2019

by ServerOk with No Comment MySQL

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

vi /etc/yum.repos.d/MariaDB.repo

1	vi /etc/yum.repos.d/MariaDB.repo

Add

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

[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

yum install MariaDB-server MariaDB-client

1	yum install MariaDB-server MariaDB-client

Enable MariaDB start on boot

systemctl enable mariadb

1	systemctl enable mariadb

Start MariaDB

systemctl start mariadb

1	systemctl start mariadb

MariaDB DEFAULT NULL ERROR 1064 (42000)

July 27, 2019

by ServerOk with No Comment MySQL

When trying to restore a MySQL databse bakcup taken on MySQL 5.7 server to MairaDB 10.1 server i get following error.

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:~#

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

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:~#

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

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:~#

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

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]>

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.

MariaDB

July 27, 2019

by ServerOk with No Comment Linux

MariaDB Repository Setup Script
MariaDB DEFAULT NULL ERROR 1064 (42000)
Install MariaDB 10.3 on CentOS 7

MariaDB Repository Setup Script

July 27, 2019

by ServerOk with No Comment Ubuntu

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

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

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

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.3"

1	curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup \| sudo bash -s -- --mariadb-server-version="mariadb-10.3"

Availabe versions are

mariadb-5.5
mariadb-10.0
mariadb-10.1
mariadb-10.2
mariadb-10.3
mariadb-10.4

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

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:~#

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

apt -y install dirmngr

1	apt -y install dirmngr