Bug 1166781

I can reproduce it with my test data set uploaded here:

https://drive.google.com/file/d/1optsli8AWCqeNnDheRitrXC537vOOf1d/view?usp=sharing

(contains /var/lib/mysql, user root@localhost, passwd mysql)

* install 15.1 minimal server system with online repos (updates already installed)
* zypper in mariadb
* rm -rf /var/lib/mysql
* untar above tarball in /var/lib
* systemctl start mariadb
* mysql -u root -pmysql
  => works, "show databases;"
* zypper --releasever 15.2 ref
* zypper --releasever 15.2 -v dup --no-recommends
* mysql -u root -pmysql =>
  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Now comes the interesting part:
* systemctl stop mariadb.service
* rm -rf /var/lib/mysql
* untar tarball again
* systemctl start mariadb.service
* mysql -u root -pmysql => works.

To break it again:
* touch /var/lib/misc/.mariadb_run_update
* echo -n 10.2 /var/lib/misc/mariadb_upgrade_info
* systemctl restart mariadb
  => broken

So it is the update routine in
[Service]
ExecStartPre=/usr/lib/mysql/mysql-systemd-helper  install
ExecStartPre=/usr/lib/mysql/mysql-systemd-helper  upgrade
ExecStart=/usr/lib/mysql/mysql-systemd-helper     start

that does kill the DB users table.

Hello Stefan,

I did created together an internal feature request  PM-1771  to see if PM thinks it's something that should be addressed.

Engineering says that it's expected behavior of current codestream so I'm resolving this bug and leave it on the feature request.

Thanks for reporting the issue.

Why on earth is this considered a feature ?! should I go and try to fix myself then? if I upgrade mariadb with the package manager I expect it to:

a) Fail the upgrade telling that a manual procedure is needed.

b) handle the upgrade gracefully as it does usually.


Neither of this happens, I also have a server with completely broken authentication after update, users are not there any longer.

I agree with Cristian:

100% reproducible data loss cannot be sold as a feature.

The database gets broken on update, and most likely even if you restore the /var/lib/mysql from a filesystem backup, it will get broken again next time a package update causes the update routine to run again.

Add to this that due to bug#1166786 you cannot even restore a database dump made with 15.1 this is a recipe for disaster.

Not going to argue here any longer, but I'll create a Critsit once SLES15-SP2 is out, we'll then get the fix from SLES ;-)

Cristian: the mysql.global_priv table is created but empty after running mariadb-update, I guess that is the culprit.

With a still working DB (old /var/lib/mysql content), running "mariadb-upgrade" is enough break it.

https://jira.mariadb.org/browse/MDEV-21244

Cristian, this fixed a broken mysql for me:

opensuse-test:/var/lib # mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

### restart mysqld with --skip-grant-tables

MariaDB [(none)]> use mysql

MariaDB [mysql]> alter table user change column `auth_string` `authentication_string` text;
Query OK, 8 rows affected (0,176 sec)              
Records: 8  Duplicates: 0  Warnings: 0
MariaDB [mysql]> drop table global_priv;
Query OK, 0 rows affected (0,028 sec)

MariaDB [mysql]> ^DBye

opensuse-test:/var/lib # mariadb-upgrade
Phase 1/7: Checking and upgrading mysql database
...
opensuse-test:/var/lib # killall mysqld ### the --skip-grant-tables one
opensuse-test:/tmp # systemctl start mariadb
opensuse-test:/var/lib # mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.12-MariaDB MariaDB package

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

So it is:
* alter table user change column `auth_string` `authentication_string` text;
* drop table global_priv;
* mariadb-upgrade

I was not yet brave enough to try on my productive DB ;-)

It really looks like you hit an upstream bug (MDEV-21244). Let's wait if upstream fix it in the next release.

---

Btw. This bug initially looked like it was caused by the authentication changes in MariaDB 10.4 that cause that (simplified) one has to run "mysql" command as a root or sudo from now on. I'm just adding a note about it here because it can be a solution for some users.

"Two all-privilege accounts were created. One is root@localhost, it has no password, but you need to be system 'root' user to connect. Use, for example, sudo mysql. The second is mysql@localhost, it has no password either, but you need to be the system 'mysql' user to connect. After connecting you can set the password, if you would need to be able to connect as any of these users with a password and without sudo."

Please see https://mariadb.com/kb/en/authentication-from-mariadb-104 for more details about authentication changes.

Upstream fixed it in 10.4.13, so when the new MariaDB version is released, we will update it.


Patch: https://github.com/MariaDB/server/commit/b976b9bfc3ec12cf4dcf57da828c0315916c1413

The updated mariadb seems to be accepted in sle and seems present in the leap.

SUSE-RU-2023:3956-1: An update that solves 221 vulnerabilities and contains three features can now be installed.

Category: recommended (moderate)
Bug References: 1001367, 1005555, 1005558, 1005562, 1005564, 1005566, 1005569, 1005581, 1005582, 1006539, 1008253, 1012075, 1013882, 1019948, 1020873, 1020875, 1020877, 1020878, 1020882, 1020884, 1020885, 1020890, 1020891, 1020894, 1020896, 1020976, 1022428, 1038740, 1039034, 1041525, 1041891, 1042632, 1043328, 1047218, 1055165, 1055268, 1058374, 1058729, 1060110, 1062583, 1067443, 1068906, 1069401, 1080891, 1083087, 1088681, 1092544, 1098683, 1101676, 1101677, 1101678, 1103342, 1111858, 1111859, 1112368, 1112377, 1112384, 1112386, 1112391, 1112397, 1112404, 1112415, 1112417, 1112421, 1112432, 1112767, 1116686, 1118754, 1120041, 1122198, 1122475, 1127027, 1132666, 1136035, 1142909, 1143215, 1144314, 1156669, 1160285, 1160868, 1160878, 1160883, 1160895, 1160912, 1166781, 1168380, 1170204, 1173028, 1173516, 1174559, 1175596, 1177472, 1178428, 1180014, 1182218, 1182255, 1182739, 1183770, 1185870, 1185872, 1186031, 1189320, 1192497, 1195325, 1195334, 1195339, 1196016, 1197459, 1198603, 1198604, 1198605, 1198606, 1198607, 1198609, 1198610, 1198611, 1198612, 1198613, 1198628, 1198629, 1198630, 1198631, 1198632, 1198633, 1198634, 1198635, 1198636, 1198637, 1198638, 1198639, 1198640, 1199928, 1200105, 1201161, 1201163, 1201164, 1201165, 1201166, 1201167, 1201168, 1201169, 1201170, 1202863, 332530, 353120, 357634, 359522, 366820, 371000, 387746, 420313, 425079, 427384, 429618, 435519, 437293, 463586, 520876, 525065, 525325, 539243, 539249, 557669, 635645, 747811, 763150, 779476, 789263, 792444, 796164, 829430, 841709, 859345, 889126, 894479, 902396, 914370, 921955, 934789, 937754, 937767, 937787, 942908, 943096, 957174, 963810, 971456, 979524, 983938, 984858, 986251, 989913, 989919, 989922, 989926, 990890, 998309
CVE References: CVE-2006-0903, CVE-2006-4226, CVE-2006-4227, CVE-2007-5969, CVE-2007-5970, CVE-2007-6303, CVE-2007-6304, CVE-2008-2079, CVE-2008-7247, CVE-2009-4019, CVE-2009-4028, CVE-2009-4030, CVE-2012-4414, CVE-2012-5611, CVE-2012-5612, CVE-2012-5615, CVE-2012-5627, CVE-2013-1976, CVE-2015-4792, CVE-2015-4802, CVE-2015-4807, CVE-2015-4815, CVE-2015-4816, CVE-2015-4819, CVE-2015-4826, CVE-2015-4830, CVE-2015-4836, CVE-2015-4858, CVE-2015-4861, CVE-2015-4864, CVE-2015-4866, CVE-2015-4870, CVE-2015-4879, CVE-2015-4895, CVE-2015-4913, CVE-2015-5969, CVE-2015-7744, CVE-2016-0505, CVE-2016-0546, CVE-2016-0596, CVE-2016-0597, CVE-2016-0598, CVE-2016-0600, CVE-2016-0606, CVE-2016-0608, CVE-2016-0609, CVE-2016-0610, CVE-2016-0616, CVE-2016-0640, CVE-2016-0641, CVE-2016-0642, CVE-2016-0644, CVE-2016-0646, CVE-2016-0649, CVE-2016-0650, CVE-2016-0651, CVE-2016-0668, CVE-2016-2047, CVE-2016-3477, CVE-2016-3492, CVE-2016-3521, CVE-2016-3615, CVE-2016-5440, CVE-2016-5584, CVE-2016-5616, CVE-2016-5624, CVE-2016-5626, CVE-2016-5629, CVE-2016-6662, CVE-2016-6663, CVE-2016-6664, CVE-2016-7440, CVE-2016-8283, CVE-2016-9843, CVE-2017-10268, CVE-2017-10286, CVE-2017-10320, CVE-2017-10365, CVE-2017-10378, CVE-2017-10379, CVE-2017-10384, CVE-2017-15365, CVE-2017-3238, CVE-2017-3243, CVE-2017-3244, CVE-2017-3257, CVE-2017-3258, CVE-2017-3265, CVE-2017-3291, CVE-2017-3302, CVE-2017-3308, CVE-2017-3309, CVE-2017-3312, CVE-2017-3313, CVE-2017-3317, CVE-2017-3318, CVE-2017-3453, CVE-2017-3456, CVE-2017-3464, CVE-2017-3636, CVE-2017-3641, CVE-2017-3653, CVE-2018-25032, CVE-2018-2562, CVE-2018-2612, CVE-2018-2622, CVE-2018-2640, CVE-2018-2665, CVE-2018-2668, CVE-2018-2755, CVE-2018-2759, CVE-2018-2761, CVE-2018-2766, CVE-2018-2767, CVE-2018-2771, CVE-2018-2777, CVE-2018-2781, CVE-2018-2782, CVE-2018-2784, CVE-2018-2786, CVE-2018-2787, CVE-2018-2810, CVE-2018-2813, CVE-2018-2817, CVE-2018-2819, CVE-2018-3058, CVE-2018-3060, CVE-2018-3063, CVE-2018-3064, CVE-2018-3066, CVE-2018-3143, CVE-2018-3156, CVE-2018-3162, CVE-2018-3173, CVE-2018-3174, CVE-2018-3185, CVE-2018-3200, CVE-2018-3251, CVE-2018-3277, CVE-2018-3282, CVE-2018-3284, CVE-2019-18901, CVE-2019-2510, CVE-2019-2537, CVE-2019-2614, CVE-2019-2627, CVE-2019-2628, CVE-2019-2737, CVE-2019-2739, CVE-2019-2740, CVE-2019-2758, CVE-2019-2805, CVE-2019-2938, CVE-2019-2974, CVE-2020-13249, CVE-2020-14765, CVE-2020-14776, CVE-2020-14789, CVE-2020-14812, CVE-2020-15180, CVE-2020-2574, CVE-2020-2752, CVE-2020-2760, CVE-2020-2812, CVE-2020-2814, CVE-2020-7221, CVE-2021-2154, CVE-2021-2166, CVE-2021-2372, CVE-2021-2389, CVE-2021-27928, CVE-2021-35604, CVE-2021-46657, CVE-2021-46658, CVE-2021-46659, CVE-2021-46661, CVE-2021-46663, CVE-2021-46664, CVE-2021-46665, CVE-2021-46668, CVE-2021-46669, CVE-2022-21427, CVE-2022-21595, CVE-2022-24048, CVE-2022-24050, CVE-2022-24051, CVE-2022-24052, CVE-2022-27376, CVE-2022-27377, CVE-2022-27378, CVE-2022-27379, CVE-2022-27380, CVE-2022-27381, CVE-2022-27382, CVE-2022-27383, CVE-2022-27384, CVE-2022-27386, CVE-2022-27387, CVE-2022-27444, CVE-2022-27445, CVE-2022-27446, CVE-2022-27447, CVE-2022-27448, CVE-2022-27449, CVE-2022-27451, CVE-2022-27452, CVE-2022-27455, CVE-2022-27456, CVE-2022-27457, CVE-2022-27458, CVE-2022-32081, CVE-2022-32083, CVE-2022-32084, CVE-2022-32085, CVE-2022-32086, CVE-2022-32087, CVE-2022-32088, CVE-2022-32089, CVE-2022-32091, CVE-2022-38791, CVE-2022-47015
Jira References: PED-2455, SLE-12253, SLE-8269
Sources used:
SUSE Linux Enterprise High Performance Computing 15 SP1 LTSS 15-SP1 (src): mariadb104-10.4.30-150100.3.5.10, python-mysqlclient-1.4.6-150100.3.3.7
SUSE Linux Enterprise Server 15 SP1 LTSS 15-SP1 (src): mariadb104-10.4.30-150100.3.5.10, python-mysqlclient-1.4.6-150100.3.3.7
SUSE Linux Enterprise Server for SAP Applications 15 SP1 (src): mariadb104-10.4.30-150100.3.5.10, python-mysqlclient-1.4.6-150100.3.3.7
SUSE CaaS Platform 4.0 (src): mariadb104-10.4.30-150100.3.5.10, python-mysqlclient-1.4.6-150100.3.3.7

NOTE: This line indicates an update has been released for the listed product(s). At times this might be only a partial fix. If you have questions please reach out to maintenance coordination.

SUSE-RU-2023:4991-1: An update that solves 221 vulnerabilities and contains three features can now be installed.

Category: recommended (moderate)
Bug References: 1001367, 1005555, 1005558, 1005562, 1005564, 1005566, 1005569, 1005581, 1005582, 1006539, 1008253, 1012075, 1013882, 1019948, 1020873, 1020875, 1020877, 1020878, 1020882, 1020884, 1020885, 1020890, 1020891, 1020894, 1020896, 1020976, 1022428, 1038740, 1039034, 1041525, 1041891, 1042632, 1043328, 1047218, 1055165, 1055268, 1058374, 1058729, 1060110, 1062583, 1067443, 1068906, 1069401, 1080891, 1083087, 1088681, 1092544, 1098683, 1101676, 1101677, 1101678, 1103342, 1111858, 1111859, 1112368, 1112377, 1112384, 1112386, 1112391, 1112397, 1112404, 1112415, 1112417, 1112421, 1112432, 1112767, 1116686, 1118754, 1120041, 1122198, 1122475, 1127027, 1132666, 1136035, 1142909, 1143215, 1144314, 1156669, 1160285, 1160868, 1160878, 1160883, 1160895, 1160912, 1166781, 1168380, 1170204, 1173028, 1173516, 1174559, 1175596, 1177472, 1178428, 1180014, 1182218, 1182255, 1182739, 1183770, 1185870, 1185872, 1186031, 1189320, 1192497, 1195325, 1195334, 1195339, 1196016, 1197459, 1198603, 1198604, 1198605, 1198606, 1198607, 1198609, 1198610, 1198611, 1198612, 1198613, 1198628, 1198629, 1198630, 1198631, 1198632, 1198633, 1198634, 1198635, 1198636, 1198637, 1198638, 1198639, 1198640, 1199928, 1200105, 1201161, 1201163, 1201164, 1201165, 1201166, 1201167, 1201168, 1201169, 1201170, 1202863, 332530, 353120, 357634, 359522, 366820, 371000, 387746, 420313, 425079, 427384, 429618, 435519, 437293, 463586, 520876, 525065, 525325, 539243, 539249, 557669, 635645, 747811, 763150, 779476, 789263, 792444, 796164, 829430, 841709, 859345, 889126, 894479, 902396, 914370, 921955, 934789, 937754, 937767, 937787, 942908, 943096, 957174, 963810, 971456, 979524, 983938, 984858, 986251, 989913, 989919, 989922, 989926, 990890, 998309
CVE References: CVE-2006-0903, CVE-2006-4226, CVE-2006-4227, CVE-2007-5969, CVE-2007-5970, CVE-2007-6303, CVE-2007-6304, CVE-2008-2079, CVE-2008-7247, CVE-2009-4019, CVE-2009-4028, CVE-2009-4030, CVE-2012-4414, CVE-2012-5611, CVE-2012-5612, CVE-2012-5615, CVE-2012-5627, CVE-2013-1976, CVE-2015-4792, CVE-2015-4802, CVE-2015-4807, CVE-2015-4815, CVE-2015-4816, CVE-2015-4819, CVE-2015-4826, CVE-2015-4830, CVE-2015-4836, CVE-2015-4858, CVE-2015-4861, CVE-2015-4864, CVE-2015-4866, CVE-2015-4870, CVE-2015-4879, CVE-2015-4895, CVE-2015-4913, CVE-2015-5969, CVE-2015-7744, CVE-2016-0505, CVE-2016-0546, CVE-2016-0596, CVE-2016-0597, CVE-2016-0598, CVE-2016-0600, CVE-2016-0606, CVE-2016-0608, CVE-2016-0609, CVE-2016-0610, CVE-2016-0616, CVE-2016-0640, CVE-2016-0641, CVE-2016-0642, CVE-2016-0644, CVE-2016-0646, CVE-2016-0649, CVE-2016-0650, CVE-2016-0651, CVE-2016-0668, CVE-2016-2047, CVE-2016-3477, CVE-2016-3492, CVE-2016-3521, CVE-2016-3615, CVE-2016-5440, CVE-2016-5584, CVE-2016-5616, CVE-2016-5624, CVE-2016-5626, CVE-2016-5629, CVE-2016-6662, CVE-2016-6663, CVE-2016-6664, CVE-2016-7440, CVE-2016-8283, CVE-2016-9843, CVE-2017-10268, CVE-2017-10286, CVE-2017-10320, CVE-2017-10365, CVE-2017-10378, CVE-2017-10379, CVE-2017-10384, CVE-2017-15365, CVE-2017-3238, CVE-2017-3243, CVE-2017-3244, CVE-2017-3257, CVE-2017-3258, CVE-2017-3265, CVE-2017-3291, CVE-2017-3302, CVE-2017-3308, CVE-2017-3309, CVE-2017-3312, CVE-2017-3313, CVE-2017-3317, CVE-2017-3318, CVE-2017-3453, CVE-2017-3456, CVE-2017-3464, CVE-2017-3636, CVE-2017-3641, CVE-2017-3653, CVE-2018-25032, CVE-2018-2562, CVE-2018-2612, CVE-2018-2622, CVE-2018-2640, CVE-2018-2665, CVE-2018-2668, CVE-2018-2755, CVE-2018-2759, CVE-2018-2761, CVE-2018-2766, CVE-2018-2767, CVE-2018-2771, CVE-2018-2777, CVE-2018-2781, CVE-2018-2782, CVE-2018-2784, CVE-2018-2786, CVE-2018-2787, CVE-2018-2810, CVE-2018-2813, CVE-2018-2817, CVE-2018-2819, CVE-2018-3058, CVE-2018-3060, CVE-2018-3063, CVE-2018-3064, CVE-2018-3066, CVE-2018-3143, CVE-2018-3156, CVE-2018-3162, CVE-2018-3173, CVE-2018-3174, CVE-2018-3185, CVE-2018-3200, CVE-2018-3251, CVE-2018-3277, CVE-2018-3282, CVE-2018-3284, CVE-2019-18901, CVE-2019-2510, CVE-2019-2537, CVE-2019-2614, CVE-2019-2627, CVE-2019-2628, CVE-2019-2737, CVE-2019-2739, CVE-2019-2740, CVE-2019-2758, CVE-2019-2805, CVE-2019-2938, CVE-2019-2974, CVE-2020-13249, CVE-2020-14765, CVE-2020-14776, CVE-2020-14789, CVE-2020-14812, CVE-2020-15180, CVE-2020-2574, CVE-2020-2752, CVE-2020-2760, CVE-2020-2812, CVE-2020-2814, CVE-2020-7221, CVE-2021-2154, CVE-2021-2166, CVE-2021-2372, CVE-2021-2389, CVE-2021-27928, CVE-2021-35604, CVE-2021-46657, CVE-2021-46658, CVE-2021-46659, CVE-2021-46661, CVE-2021-46663, CVE-2021-46664, CVE-2021-46665, CVE-2021-46668, CVE-2021-46669, CVE-2022-21427, CVE-2022-21595, CVE-2022-24048, CVE-2022-24050, CVE-2022-24051, CVE-2022-24052, CVE-2022-27376, CVE-2022-27377, CVE-2022-27378, CVE-2022-27379, CVE-2022-27380, CVE-2022-27381, CVE-2022-27382, CVE-2022-27383, CVE-2022-27384, CVE-2022-27386, CVE-2022-27387, CVE-2022-27444, CVE-2022-27445, CVE-2022-27446, CVE-2022-27447, CVE-2022-27448, CVE-2022-27449, CVE-2022-27451, CVE-2022-27452, CVE-2022-27455, CVE-2022-27456, CVE-2022-27457, CVE-2022-27458, CVE-2022-32081, CVE-2022-32083, CVE-2022-32084, CVE-2022-32085, CVE-2022-32086, CVE-2022-32087, CVE-2022-32088, CVE-2022-32089, CVE-2022-32091, CVE-2022-38791, CVE-2022-47015
Jira References: PED-2455, SLE-12253, SLE-8269
Sources used:
SUSE Linux Enterprise Software Development Kit 12 SP5 (src): mariadb-connector-c-3.1.22-2.35.1, lz4-1.8.0-3.5.2, python-mysqlclient-1.3.14-8.9.2, mariadb104-10.4.30-8.5.46
SUSE Linux Enterprise High Performance Computing 12 SP5 (src): mariadb-connector-c-3.1.22-2.35.1, lz4-1.8.0-3.5.2, python-mysqlclient-1.3.14-8.9.2, mariadb104-10.4.30-8.5.46
SUSE Linux Enterprise Server 12 SP5 (src): mariadb-connector-c-3.1.22-2.35.1, lz4-1.8.0-3.5.2, python-mysqlclient-1.3.14-8.9.2, mariadb104-10.4.30-8.5.46
SUSE Linux Enterprise Server for SAP Applications 12 SP5 (src): mariadb-connector-c-3.1.22-2.35.1, lz4-1.8.0-3.5.2, python-mysqlclient-1.3.14-8.9.2, mariadb104-10.4.30-8.5.46

NOTE: This line indicates an update has been released for the listed product(s). At times this might be only a partial fix. If you have questions please reach out to maintenance coordination.