Tag: mariadb

How to fix InnoDB: ERROR: the age of the last checkpoint
On a MySQL server, the error log keeps getting the following error message
```
221024 17:50:03  InnoDB: ERROR: the age of the last checkpoint is X,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
```
The error is because you are trying to insert more data into InnoDB and the InnoDB log is getting full before the data can be written into the data file. You need to set the value of innodb_log_file_size value at least ten times bigger than max_allowed_packet.

To fix the error, edit your MySQL configuration file

On Debian/Ubuntu with MairaDB
```
vi /etc/mysql/mariadb.conf.d/50-server.cnf
```
Under [mysqld] section, add
```
innodb_buffer_pool_size=3G
innodb_log_file_size=1G
innodb_log_buffer_size=32M
```
To select a proper size for these MySQL variables for your system, use MySQL tuner.
```
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl
```
It will suggest you recommended values of innodb_buffer_pool_size and innodb_log_file_size.

Disable fast MySQL shutdown.
```
mysql -u root -p -e "SET GLOBAL innodb_fast_shutdown = 0;"
```
This will flush all changes done on MySQL from log file to data file so we can safely remove MySQL InnoDB log files. We need to remove existing log files before MySQL can create log files with different size.

Stop MySQL server.
systemctl stop mysql

Move log files out
```
mkdir ~/mysql-old-log
mv /var/lib/mysql/ib_logfile* ~/mysql-old-log
```
Start MySQL service with
```
systemctl start mysql
```
This will recreate MySQL log files (ib_logfile0 and ib_logfile1) with the new size.

See MySQL
October 29, 2022

One of the configured repositories failed (MariaDB100)

On a Cpanel server, when running “yum update”, I got the error message “One of the configured repositories failed (MariaDB100)”.

[root@cp ~]# yum update
Loaded plugins: fastestmirror, universal-hooks
Loading mirror speeds from cached hostfile
* EA4: 178.18.193.52
* cpanel-addons-production-feed: 178.18.193.52
* cpanel-plugins: 178.18.193.52
* base: centos.uni-sofia.bg
* extras: centos.uni-sofia.bg
* updates: centos.uni-sofia.bg
http://yum.mariadb.org/10.0/centos7-amd64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found
Trying other mirror.
To address this issue please refer to the below wiki article

https://wiki.centos.org/yum-errors

If above article doesn't help to resolve this issue please use https://bugs.centos.org/.

One of the configured repositories failed (MariaDB100),
and yum doesn't have enough cached data to continue. At this point the only
safe thing yum can do is fail. There are a few ways to work "fix" this:

1. Contact the upstream for the repository and get them to fix the problem.

2. Reconfigure the baseurl/etc. for the repository, to point to a working
upstream. This is most often useful if you are using a newer
distribution release than is supported by the repository (and the
packages for the previous distribution release still work).

3. Run the command with the repository temporarily disabled
yum --disablerepo=MariaDB100 ...

4. Disable the repository permanently, so yum won't use it by default. Yum
will then just ignore the repository until you permanently enable it
again or use --enablerepo for temporary usage:

yum-config-manager --disable MariaDB100
or
subscription-manager repos --disable=MariaDB100

5. Configure the failing repository to be skipped, if it is unavailable.
Note that yum will try to contact the repo. when it runs most commands,
so will have to try and fail each time (and thus. yum will be be much
slower). If it is a very temporary problem though, this is often a nice
compromise:

yum-config-manager --save --setopt=MariaDB100.skip_if_unavailable=true

failure: repodata/repomd.xml from MariaDB100: [Errno 256] No more mirrors to try.
http://yum.mariadb.org/10.0/centos7-amd64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found
[root@cp ~]#

The error is because MariaDB 10.0 is the end of life.

To fix the error message, remove the MairaDB repo file

cd /etc/yum.repos.d/
mv MariaDB* ~/

Now yum update will work.

To update MairaDB from 10.0 to the supported 10.2 version, create a file

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

with following content

# MariaDB 10.2 CentOS repository list - created 2022-07-31 16:26 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
baseurl = https://mirror.rackspace.com/mariadb/yum/10.2/centos7-amd64
gpgkey=https://mirror.rackspace.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

This is for CentOS 7 + MariaDB 10.2, if you have different Linux distribution or MairaDB version, you can generate repository config at

https://mariadb.org/download/?t=repo-config

Take a backup of current MySQL databases with

mysqldump --opt --triggers --routines --events --all-databases > all-db.sql

Upgrade MariaDB with

yum update

After MariaDB packages are updated to the newer version, update Databases with

mysql_upgrade

See Errors

July 31, 2022

Change MariaDB user password
To change the MariaDB root password, log in to MySQL as the user root
```
mysql -u root -p
```
Enter your current password when prompted.

Use the following command to change the root password
```
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NEW_PW_HERE');
```
Replace NEW_PW_HERE with your new MySQL root password.

Change a user password
```
SET PASSWORD FOR 'USER_HERE'@'localhost' = PASSWORD('NEW_PW_HERE');
```
Example
```
SET PASSWORD FOR 'admin'@'localhost' = PASSWORD('serverok123');
```
For more info, see

https://mariadb.com/kb/en/set-password/
July 19, 2022

Can’t create table `catalog_product_relation` (errno: 140 “Wrong create options”)

When I try to restore the MySQL database of a Magento 1.x site on MariaDB 10.3.32 (source MySQL 5.7.36), I get the following error

boby@sok-01:/www/magento/amazingplans/backup$ mysql amazingplans < amazingp_demo.sql 
ERROR 1005 (HY000) at line 4080: Can't create table `amazingplans`.`catalog_product_relation` (errno: 140 "Wrong create options")
boby@sok-01:/www/magento/amazingplans/backup$

On checking the line in the error message, I found the error is due to the following SQL statement.

CREATE TABLE `catalog_product_relation` (
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Parent ID',
  `child_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Child ID',
  PRIMARY KEY (`parent_id`,`child_id`),
  KEY `IDX_CATALOG_PRODUCT_RELATION_CHILD_ID` (`child_id`),
  CONSTRAINT `FK_CAT_PRD_RELATION_CHILD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`child_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_RELATION_PARENT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`parent_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Catalog Product Relation Table';

Fixed it by removing

ROW_FORMAT=FIXED

From the SQL.

December 8, 2021

MariaDB Change Open Files Limit
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
```
Add
```
[Service]
LimitNOFILE=1048576
```
Reload systemctl
```
systemctl daemon-reload
```
Restart mariadb
```
systemctl restart mariadb
```
After doing this, it get changed to 32184 instead of 1048576 we specified in limitnofile.conf

To fix this edit

On Cpanel/RHEL
```
vi /etc/my.cnf.d/server.cnf
```
On Ubuntu/Debian
```
vi /etc/mysql/mariadb.conf.d/50-server.cnf
```
Under [mysqld], add
```
open_files_limit = 102400
```
Now after restarting MariaDB, i get 1048576 for open_files_limit.

This value depends on kernals fs.nr_open parameter. If the value is low, you can increase by editing
```
vi /etc/sysctl.conf
```
Add
```
fs.nr_open=1048576‬
```
Then make it active with command
```
sysctl -p
```
To see current value, you can run
```
sysctl -a | grep fs.nr_open
```
To set the value for the current session, run
```
sysctl -w fs.nr_open=1048576‬
```
Plesk Debian 8 General error: 23 Out of resources when opening

See MySQL
December 7, 2020
Install Latest MariaDB on CentOS
To install the latest MariaDB on the CentOS server, go to

https://mariadb.org/download/?t=repo-config

On this page, select CentOS, then select your CentOS version and MariaDB version you need.

Below you will see instructions 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
```
vi /etc/yum.repos.d/MariaDB.repo
```
Add
```
# MariaDB 10.5 CentOS repository list - created 2020-11-05 08:27 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
```
Now you can install MariDB with command
```
yum install MariaDB-server MariaDB-client
```
To start MariaDB, run
```
systemctl start mariadb
```
You can replace start with stop/restart/status. To auto start MariaDB on boot, run
```
systemctl enable mariadb
```
See MySQL
November 5, 2020
MariaDB ERROR 1524 Plugin unix_socket is not loaded
After upgrading MariaDB on Ubuntu server, i got following error
```
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
```
Find
```
[mysqld]
```
Add below
```
plugin-load-add = auth_socket.so
```
Resatrt MariaDB
```
systemctl restart mysqld
```
See MySQL
July 27, 2020
MySQL server has gone away
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
# 
```
To fix this, edit MySQL configuration file. Add following line under [mysqld] section.
```
max_allowed_packet = 256M
```
Restart MySQL
```
systemctl restart mysql
```
If you use MariaDB
```
systemctl restart mariadb
```
Related Posts

MySQL
February 5, 2020

Ubuntu 18.04 MariaDB 10.2 Too many open files

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)

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

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

To fix this error, run

systemctl edit mysql

This wil open a text editor. Add following

[Service]
LimitNOFILE=8192

Save and quit the editor. Restart MaraDB with

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

December 13, 2019

Install MariaDB 10.3 on CentOS 7
MariaDB is an Open Source MySQL drop-in replacement. MariaDB provides a repository for various operating systems at

https://mariadb.org/download/?tab=repo-config

To install MariaDB 10.3 on CentOS 7, create a file
```
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
```
Install the MariaDB server and client with
```
yum install MariaDB-server MariaDB-client
```
Enable MariaDB to start on boot
```
systemctl enable mariadb
```
Start MariaDB
```
systemctl start mariadb
```
November 22, 2019

MariaDB DEFAULT NULL ERROR 1064 (42000)

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

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

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

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

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.

July 27, 2019

MariaDB

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

July 27, 2019

Tag: mariadb

Change a user password

Install MariaDB 10.5 on CentOS 7