[MDEV-21989] Server Start Failed when tuning InnoDB system variables Created: 2020-03-20  Updated: 2020-04-05  Resolved: 2020-04-05

Status: Closed
Project: MariaDB Server
Component/s: Configuration
Affects Version/s: 5.5.64
Fix Version/s: N/A

Type: Bug Priority: Trivial
Reporter: Antonio Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: innodb
Environment:

Centos 7 Linux kernel 3.10.0-327.10.1.el7.x86_64


 Description   

Hello, when tuning InnoDB system variables in /etc/my.cnf , the service won't start.

Note that rolling back the changes makes the service start smoothly again.

Installation details:

[root@tesrv.local ~]# mysqladmin version -p
 
Enter password: 
mysqladmin  Ver 9.0 Distrib 5.5.64-MariaDB, for Linux on x86_64
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Server version		5.5.64-MariaDB
 
Protocol version	10
 
Connection		Localhost via UNIX socket
 
UNIX socket		/var/lib/mysql/mysql.sock
 
Uptime:			35 sec
 
 
 
Threads: 1  Questions: 1  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.028

Without InnoDB system variables tuning, server starts just fine:

[root@tesrv.local ~]# my_print_defaults mysqld
 
--datadir=/var/lib/mysql
 
--socket=/var/lib/mysql/mysql.sock
 
--symbolic-links=0
 
[root@tesrv.local ~]# systemctl restart mariadb
 
[root@tesrv.local ~]# systemctl status mariadb
 
● mariadb.service - MariaDB database server
 
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
 
   Active: active (running) since Fri 2020-03-20 11:23:01 CET; 3s ago
 
  Process: 14801 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
 
  Process: 14765 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 
 Main PID: 14800 (mysqld_safe)
 
   CGroup: /system.slice/mariadb.service
 
           ├─14800 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
 
           └─14962 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/v...
 
 
 
Mar 20 11:22:59 tesrv.local systemd[1]: Stopped MariaDB database server.
 
Mar 20 11:22:59 tesrv.local systemd[1]: Starting MariaDB database server...
 
Mar 20 11:22:59 tesrv.local mariadb-prepare-db-dir[14765]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
 
Mar 20 11:22:59 tesrv.local mysqld_safe[14800]: 200320 11:22:59 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
 
Mar 20 11:22:59 tesrv.local mysqld_safe[14800]: 200320 11:22:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
 
Mar 20 11:23:01 tesrv.local systemd[1]: Started MariaDB database server.
 
[root@tesrv.local ~]#

When editing InnoDB system variables, the service won't start anymore:

[root@tesrv.local ~]# vi /etc/my.cnf
 
[root@tesrv.local ~]# my_print_defaults mysqld
 
--datadir=/var/lib/mysql
 
--socket=/var/lib/mysql/mysql.sock
 
--symbolic-links=0
 
--innodb_buffer_pool=4G
 
[root@tesrv.local ~]# systemctl restart mariadb
 
Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
 
[root@tesrv.local ~]# systemctl status mariadb
 
● mariadb.service - MariaDB database server
 
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
 
   Active: failed (Result: exit-code) since Fri 2020-03-20 11:23:30 CET; 1min 12s ago
 
  Process: 15075 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=1/FAILURE)
 
  Process: 15074 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
 
  Process: 15039 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 
 Main PID: 15074 (code=exited, status=0/SUCCESS)
 
 
 
Mar 20 11:23:29 tesrv.local systemd[1]: Stopped MariaDB database server.
 
Mar 20 11:23:29 tesrv.local systemd[1]: Starting MariaDB database server...
 
Mar 20 11:23:29 tesrv.local mariadb-prepare-db-dir[15039]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
 
Mar 20 11:23:29 tesrv.local mysqld_safe[15074]: 200320 11:23:29 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
 
Mar 20 11:23:30 tesrv.local mysqld_safe[15074]: 200320 11:23:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
 
Mar 20 11:23:30 tesrv.local systemd[1]: mariadb.service: control process exited, code=exited status=1
 
Mar 20 11:23:30 tesrv.local systemd[1]: Failed to start MariaDB database server.
 
Mar 20 11:23:30 tesrv.local systemd[1]: Unit mariadb.service entered failed state.
 
Mar 20 11:23:30 tesrv.local systemd[1]: mariadb.service failed.
 
[root@tesrv.local ~]# journalctl -xe
 
 
 
Mar 20 11:23:27 tesrv.local polkitd[4495]: Registered Authentication Agent for unix-process:15022:694777 (system bus name :1.67 [/usr/bin/pkttyagent --notify-fd 5 --fallback], object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US.UTF-8)
 
 
 
Mar 20 11:23:27 tesrv.local systemd[1]: Stopping MariaDB database server...
 
-- Subject: Unit mariadb.service has begun shutting down
 
-- Defined-By: systemd
 
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
 
-- 
-- Unit mariadb.service has begun shutting down.
 
Mar 20 11:23:29 tesrv.local systemd[1]: Stopped MariaDB database server.
 
-- Subject: Unit mariadb.service has finished shutting down
 
-- Defined-By: systemd
 
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
 
-- 
-- Unit mariadb.service has finished shutting down.
 
Mar 20 11:23:29 tesrv.local systemd[1]: Starting MariaDB database server...
 
-- Subject: Unit mariadb.service has begun start-up
 
-- Defined-By: systemd
 
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
 
-- 
-- Unit mariadb.service has begun starting up.
 
Mar 20 11:23:29 tesrv.local mariadb-prepare-db-dir[15039]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
 
Mar 20 11:23:29 tesrv.local mariadb-prepare-db-dir[15039]: If this is not the case, make sure the /var/lib/mysql is empty before running mariadb-prepare-db-dir.
 
Mar 20 11:23:29 tesrv.local mysqld_safe[15074]: 200320 11:23:29 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
 
Mar 20 11:23:30 tesrv.local mysqld_safe[15074]: 200320 11:23:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
 
Mar 20 11:23:30 tesrv.local systemd[1]: mariadb.service: control process exited, code=exited status=1
 
Mar 20 11:23:30 tesrv.local systemd[1]: Failed to start MariaDB database server.
 
-- Subject: Unit mariadb.service has failed
 
-- Defined-By: systemd
 
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
 
-- 
-- Unit mariadb.service has failed.
 
-- 
-- The result is failed.
 
Mar 20 11:23:30 tesrv.local systemd[1]: Unit mariadb.service entered failed state.
 
Mar 20 11:23:30 tesrv.local systemd[1]: mariadb.service failed.
 
Mar 20 11:23:30 tesrv.local polkitd[4495]: Unregistered Authentication Agent for unix-process:15022:694777 (system bus name :1.67, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US.UTF-8) (disconnected from bus)

Please note that I've tried with multiple values of innodb_buffer_pool: 1G, 2G, 4G, 7G.
The server has 8GB RAM, with more than 7GB always free if not counting the mysqld process.

Looks like a bug to me, or maybe I'm doing something wrong?

Thanks,
Antonio

 Comments   
Comment by Antonio [ 2020-03-30 ]

Hello, updating & fixing syntax solved the issue, how can I withdrawn this ticket? Thanks! Regards

Generated at Thu Feb 08 09:11:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.