Tag: mariadb – ServerOK

MariaDB Change Open Files Limit

Posted on December 7, 2020 (December 7, 2020) by ServerOk

When using systemctl, you can set value for open_files_limit in my.cnf file. Default installation of MariaDB 10 have open_files_limit set to 16384. To increase value for open_files_limit, create file

mkdir /etc/systemd/system/mariadb.service.d/
vi /etc/systemd/system/mariadb.service.d/limitnofile.conf

1 2	mkdir /etc/systemd/system/mariadb.service.d/ vi /etc/systemd/system/mariadb.service.d/limitnofile.conf

Add

[Service]
LimitNOFILE=1048576

1 2	[Service] LimitNOFILE=1048576

Reload systemctl

systemctl daemon-reload

1	systemctl daemon-reload

Restart mariadb

systemctl restart mariadb

1	systemctl restart mariadb

After doing this, it get changed to 32184 instead of 1048576 we specified in limitnofile.conf To fix […]

Install Latest MariaDB on CentOS

Posted on November 5, 2020 (November 5, 2020) by ServerOk

To install latest MariaDB on CentOS server, go to https://downloads.mariadb.org/mariadb/repositories/#mirror=Fibergrid On this page, select CentOS, then select your CentOS version and MariaDB version you need. Below you will see instruction for your specific version of CentOS. Install MariaDB 10.5 on CentOS 7 CentOS 7 by default provide MariaDB 5.5. To install MariaDB 10.5 Create file […]

MariaDB ERROR 1524 Plugin unix_socket is not loaded

Posted on July 27, 2020 by ServerOk

After upgrading MariaDB on Ubuntu server, i got following error

root@server:~# mysql
ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded
root@server:~#

root@server:~# mysql

ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded

root@server:~#

Users created worked fine. Only root user had this error. To fix, you need to enable auth_socket.so plugin. Edit file

vi /etc/mysql/mariadb.conf.d/50-server.cnf

1	vi /etc/mysql/mariadb.conf.d/50-server.cnf

Find

[mysqld]

[mysqld]

Add below

plugin-load-add = auth_socket.so

1	plugin-load-add = auth_socket.so

Resatrt MariaDB

systemctl restart mysqld

1	systemctl restart mysqld

See MySQL […]

MySQL server has gone away

Posted on February 5, 2020 by ServerOk

When restoring a MySQL database, i get error “MySQL server has gone away”.

# mysql -u root -p'serverok123' sok_wp < backup.sql
ERROR 2006 (HY000) at line 5095: MySQL server has gone away
#

# 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.

max_allowed_packet = 256M

1	max_allowed_packet = 256M

Restart MySQL

systemctl restart mysql

1	systemctl restart mysql

If you use MariaDB

systemctl restart mariadb

1	systemctl restart mariadb

Ubuntu 18.04 MariaDB 10.2 Too many open files

Posted on December 13, 2019 by ServerOk

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

Install MariaDB 10.3 on CentOS 7

Posted on November 22, 2019 by ServerOk

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)

Posted on July 27, 2019 by ServerOk

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

MariaDB

Posted on July 27, 2019 (November 22, 2019) by ServerOk

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

MariaDB Repository Setup Script

Posted on July 27, 2019 (July 27, 2019) by ServerOk

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