Hello all,
I’ve upgraded my OSMC to the latest December release. For some reason this seems to have screwed up my own MySQL installation which I had installed using apt-get install mysql and had been working fine for months if not years.
If I attempt to start it by running systemctl start mysql it seems to reference MariaDB and fails.
More worryingly, I thought I would backup the MySQL DB somehow and could not find anything under /var/lib/mysql/.
Running systemctl status mysql returns the following:
root@osmc:/etc/mysql# systemctl status mysql
* mariadb.service - MariaDB database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Wed 2018-01-10 22:45:23 GMT; 16min ago
Process: 611 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
Process: 523 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 508 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 387 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Main PID: 611 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"
Jan 10 22:45:17 osmc systemd[1]: Starting MariaDB database server...
Jan 10 22:45:23 osmc mysqld[611]: 2018-01-10 22:45:23 1995759616 [Note] Using unique option prefix 'key_buffer' is error-prone and can break in the future. Please use the full name 'key_buffer_size' instead.
Jan 10 22:45:23 osmc mysqld[611]: 2018-01-10 22:45:23 1995759616 [Note] /usr/sbin/mysqld (mysqld 10.1.26-MariaDB-0+deb9u1) starting as process 611 ...
Jan 10 22:45:23 osmc mysqld[611]: 2018-01-10 22:45:23 1995759616 [Warning] Can't create test file /home/osmc/.mysql/osmc.lower-test
Jan 10 22:45:23 osmc mysqld[611]: [93B blob data]
Jan 10 22:45:23 osmc mysqld[611]: 2018-01-10 22:45:23 1995759616 [ERROR] Aborting
Jan 10 22:45:23 osmc systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Jan 10 22:45:23 osmc systemd[1]: Failed to start MariaDB database server.
Jan 10 22:45:23 osmc systemd[1]: mariadb.service: Unit entered failed state.
Jan 10 22:45:23 osmc systemd[1]: mariadb.service: Failed with result 'exit-code'.
Does the latest release change anything that would affect MySQL?
I think it is probably related to this: Moving from MySQL to MariaDB in Debian 9 - MariaDB Knowledge Base Money quote:
MariaDB 10.1 is now the default mysql server in Debian 9 “Stretch”.
Could you provide a full set of logs. I’m out of time for today but perhaps someone else will be able to take a look.
The update to Stretch upgraded MySQL to MariaDB. This should have been painless.
What happens if you try to start maria?
$ sudo systemctl start mysql
I wonder if the database migration didn’t complete. You didn’t pull power or force a reboot did you?
EDIT: Sorry I missed that you did trying to start manually.
You say that /var/lib/mysql is empty? That’s very strange. You may have had your SD card get some minor corruption unfortunately.
Thank you both for your prompt replies.
It looks like whatever the “upgrade” process did at least renamed the original mysql directory under /var/lib to mysql.bak which seems to contain a few sizeable files.
running systemctl start mysql fails with the following error:
> Job for mariadb.service failed because the control process exited with error code.
> See "systemctl status mariadb.service" and "journalctl -xe" for details.
Running journalctl -xe shows:
Jan 10 23:36:28 osmc mysqld[2421]: 2018-01-10 23:36:28 1996288000 [Note] Using unique option prefix ‘key_buffer’ is error-prone and can break in the future. Please use the full name ‘key_buffer_size’ instead.
Jan 10 23:36:29 osmc mysqld[2421]: 2018-01-10 23:36:29 1996288000 [Note] /usr/sbin/mysqld (mysqld 10.1.26-MariaDB-0+deb9u1) starting as process 2421 …
Jan 10 23:36:29 osmc mysqld[2421]: 2018-01-10 23:36:29 1996288000 [Warning] Can’t create test file /home/osmc/.mysql/osmc.lower-test
Jan 10 23:36:29 osmc mysqld[2421]: [93B blob data]
Jan 10 23:36:29 osmc mysqld[2421]: 2018-01-10 23:36:29 1996288000 [ERROR] Aborting
Jan 10 23:36:29 osmc systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Jan 10 23:36:29 osmc systemd[1]: Failed to start MariaDB database server.
– Subject: Unit mariadb.service has failed
– Defined-By: systemd
– Support: Debian -- User Support
– Unit mariadb.service has failed.
– The result is failed.
Jan 10 23:36:29 osmc systemd[1]: mariadb.service: Unit entered failed state.
Jan 10 23:36:29 osmc systemd[1]: mariadb.service: Failed with result ‘exit-code’.
What’s in the mysql.bak directory? If there a directories under that, then they hopefully are copies of your database. Check the dates on them to see when they were created. On my system that was upgraded there is no mysql.bak, but maybe it was removed since the migration worked. Or maybe you created it yourself sometime ago and forgot 
I find this warning interesting:
1996288000 [Warning] Can’t create test file /home/osmc/.mysql/osmc.lower-test
On my system there is no /home/osmc/.mysql directory. Do you have one?
I remembered that I had set the data dir location to /home/osmc/.mysql
I checked and the .mysql folder under /home/osmc contains my DBs. I’ve changed the names of the mysql folders under /var/lib/mysql and /etc/mysql as well as /home/osmc/.mysql and then purged mysql-server
I then installed mariadb-server and I still can’t start it. This time it seems to be trying to create /var/lib/mysql/osmc.lower-test which I suspect is probably the default location but running apt-get install mariadb-server does not create the configuration directories and files under /var/lib nor /etc so I need to find a way to “inititalise” MariaDB and then attempt to restore my DB
It’s probably permissions then. That’s what the warning I highlighted leads me to believe.
The .mysql directory should be owned by mysql:mysql
Try copying the directories under ~osmc/.mysql to /var/lib/mysql and make sure they are mysql:mysql
1 Like
I’ve had so many issues getting mysql back that I decided to rename the data folder and attempted to reinstall MariaDB.
I removed everything mysql/mariadb related and reinstalled mariadb-server and mariadb-client.
I can see that MariaDB (mysqld) is running and running systemctl status mariadb shows the following:
* mariadb.service - MariaDB database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Sun 2018-01-14 12:41:22 GMT; 52s ago
Process: 14657 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 14654 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Process: 14549 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 14545 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 14542 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Main PID: 14627 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
`-14627 /usr/sbin/mysqld
Jan 14 12:41:19 osmc systemd[1]: Starting MariaDB database server...
Jan 14 12:41:21 osmc mysqld[14627]: 2018-01-14 12:41:21 1989550080 [Note] /usr/sbin/mysqld (mysqld 10.1.26-MariaDB-0+deb9u1) starting as process 14627 ...
Jan 14 12:41:22 osmc systemd[1]: Started MariaDB database server.
However, I cannot connect to it. If I start by running “mysql -u root -p” I get
bash: mysql: command not found
What can I do to fix this? mariadb-client is installed and I’ve also tried installing mysql-client to no avail. What am I missing?
Try to reboot and run systemctl status mariadb again.
On a clean-ish test system I installed mariadb-server (no client) and this is what I see, without performing a reboot:
osmc@osmc:~$ systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Sun 2018-01-14 13:36:19 GMT; 2min 2s ago
Main PID: 5485 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─5485 /usr/sbin/mysqld
Jan 14 13:36:02 osmc systemd[1]: Starting MariaDB database server...
Jan 14 13:36:04 osmc mysqld[5485]: 2018-01-14 13:36:04 1995579392 [Note] /usr/sbin/mysqld (mysqld 10.1.26-MariaDB-0+deb9u1) starting as process 5485 .
Jan 14 13:36:19 osmc systemd[1]: Started MariaDB database server.
osmc@osmc:~$ which mysql
/usr/bin/mysql
Your system is clearly showing something very different.
If nothing changes after a reboot and you are sure that all your valuable files are safely out of harm’s way, I’d recommend that you purge the existing installation (sudo apt-get purge xxxxx) and reinstall just mariadb-server.
Thanks for your help.
I’ve managed to fix the problem. I had tried purging to no avail. In the end I managed to find a copy of my previous /etc/mysql config directory and copied it into /etc/mysql and reinstalled and it worked fine.
I don’t know what happened but it seems to work fine now with MariaDB.