Bugzilla – Bug 1166781
mysql / mariadb database broken after update from 15.1 to 15.2 beta
Last modified: 2023-12-29 12:30:16 UTC
yesterday I updated my Leap 15.1 server running owncloud-files-10.4.0 on mariadb After the update, no access to the DB was possible. version before update was mysql command did return "access denied to root (using password: YES)" (from memory, I have no exact records). mariadb version before update was mariadb-10.2.31-lp151.2.12.1.x86_64 mariadb version after update is mariadb-10.4.12-lp152.2.1.x86_64 I tried to restore the dump which I had taken before, but this did not go through (will open a separate bug for that) Finally I stopped mariadb, restored /var/lib/mysql from the filesystem backup I had (lucky me!) taken before the update, restarted mysql and it seems to work fine now. Cristian reported similar issues on Tumbleweed. I still have the "broken" data dir (old /var/lib/mysql) in case it is useful for testing. I am a total amateur when it comes to databases, so please speak in easy words ;-)
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.