Bug #85340 mysqld 5.7 sanity check fails with non default datadir
Submitted: 7 Mar 2017 9:26 Modified: 9 Mar 2017 19:52
Reporter: Martin Heineck Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.7.17 OS:Debian (jessie)
Assigned to: CPU Architecture:Any

[7 Mar 2017 9:26] Martin Heineck
The systemd service of package mysql-community-server in version 5.7.17-1debian8  invokes a sanity check before starting the real mysqld instance including configuration of /etc/mysql.
This sanity check does not work when datadir differs from default!
Here my issue:
I was reinitializing an already configured mysqld under debian jessie.
The datadir differs to default. Mysqld was started and initialized the datadir correctly at first boot. At second boot this code dropped my root password and set auth_socket as authentication plugin which is not allowed by policy inside the environment infrastructure here:
        if [ ! "$(ls -A ${MYSQLDATA}/mysql)" ];
                SQL=$(mktemp -u ${MYSQLFILES}/XXXXXXXXXX)
                install /dev/null -m0600 -omysql -gmysql "${SQL}"
                cat << EOF > ${SQL}
USE mysql;
INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'auth_socket';

While another systemd service script written and delivered inside the same debian package fulfills the same approach without hardcoding:
-- /etc/init.d/mysql --
MYSQLDATA=$(get_mysql_option mysqld datadir "/var/lib/mysql")

This bug is misslieading and security relevant. Also this bug is destroying reboot safity.

dpkg -l |grep mysql |grep ^ii
ii  libdbd-mysql-perl                    4.028-2+deb8u2                amd64        Perl5 database interface to the MySQL database
ii  libmysqlclient18:amd64               5.5.53-0+deb8u1               amd64        MySQL database client library
ii  mysql-client                         5.7.17-1debian8               amd64        MySQL Client meta package depending on latest version
ii  mysql-common                         5.7.17-1debian8               amd64        MySQL Common
ii  mysql-community-client               5.7.17-1debian8               amd64        MySQL Client
ii  mysql-community-server               5.7.17-1debian8               amd64        MySQL Server
ii  php5-mysql                           5.6.29+dfsg-0+deb8u1          amd64        MySQL module for php5

How to repeat:
1. Provided you got a running mysql installation of debian jessie using packages from mysql repository (http://repo.mysql.com/apt/debian jessie) and you want to move the datadir into a special location (i guess this is easier to reproduce then my scenario):
2. shutdown mysql:
sudo systemctl stop mysql
3. create a new datadir:
install -o mysql -g mysql -d /var/lib/mysql/data
4. configure mysql to use new datadir:
sudo vim  /etc/mysql/mysql.conf.d/mysqld.cnf
-datadir         = /var/lib/mysql
+datadir         = /var/lib/mysql/data
5. mv your data to the new directory
mv /var/lib/mysql/{mysql,YourDataBasses,..} /var/lib/mysql/data
6. restart mysqld and see the error log
sudo systemctl start mysql && sudo tail -f /var/log/mysql/error.log

You will see that mysql provides an non default socket which you can use to connect, but your root password was deleted!

Suggested fix:
use something like this inside the bash code of  /usr/share/mysql/mysql-systemd-start:
MYSQLDATA=$(get_mysql_option mysqld datadir "/var/lib/mysql")
[9 Mar 2017 19:52] Terje Røsten

Thanks for your report.

This is duplicate of https://bugs.mysql.com/bug.php?id=82709