[opensuse-factory] 15.2 beta update / mariadb problem
Hi all, I need some guidance on how to create a proper bugreport. I'm running a server at home with Leap, which hosts among others an owncloud instance with mariadb as database backend. Since I was using a newer kernel because of XFS features anyway, yesterday I thought it would be a good idea to try updating to Leap 15.2 beta. I did take a fresh backup before doing this, which proved a good idea. After the update, the mariadb databases were broken. mysql root user was not able to access the database. Re-running "mysql_secure_installation" was not possible because of that. So I moved away /var/lib/mysql, rerun mysql_secure_installation and tried to restore the database dump with mysql -u root < /space3/backup/sda/mysqldump/2020-03-15.sql which choked on line 2009 2002 -- 2003 -- Table structure for table `user` 2004 -- 2005 2006 DROP TABLE IF EXISTS `user`; 2007 /*!40101 SET @saved_cs_client = @@character_set_client */; 2008 /*!40101 SET character_set_client = utf8 */; 2009 CREATE TABLE `user` ( 2010 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', 2011 `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', Because I needed the system up, the next try was "restore /var/lib/mysql from backup" which fortunately worked. My guess is that even though mariadb shows a post-upgrade message that you should backup /var/lib/mysql before restarting the database (which will perform an update), actually the RPM restarts the database and something went badly wrong at this step. As you might guess, I know nothing about mariadb and databases in general, so I'm type of lost on how to create a useful bugreport. I still have the (broken) contents of /var/lib/mysql that do not work. I also have a fairly complete backup of the system before the update, so if time permits, I might try to reproduce the issue in a virtual machine. So what information would the mariadb maintainer need from me in a good bugreport? Thanks, seife -- Stefan Seyfried "For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -- Richard Feynman -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
Stefan Seyfried wrote:
So I moved away /var/lib/mysql, rerun mysql_secure_installation and tried to restore the database dump with
mysql -u root < /space3/backup/sda/mysqldump/2020-03-15.sql
which choked on line 2009
2002 -- 2003 -- Table structure for table `user` 2004 -- 2005 2006 DROP TABLE IF EXISTS `user`; 2007 /*!40101 SET @saved_cs_client = @@character_set_client */; 2008 /*!40101 SET character_set_client = utf8 */; 2009 CREATE TABLE `user` ( 2010 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', 2011 `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
Hi seife 'choked' exactly how - ie. which error message? I would say failing on a "create table" is quite unusual. -- Per Jessen, Zürich (11.9°C) -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
Am 16.03.20 um 11:03 schrieb Per Jessen:
Stefan Seyfried wrote:
So I moved away /var/lib/mysql, rerun mysql_secure_installation and tried to restore the database dump with
mysql -u root < /space3/backup/sda/mysqldump/2020-03-15.sql
which choked on line 2009
2002 -- 2003 -- Table structure for table `user` 2004 -- 2005 2006 DROP TABLE IF EXISTS `user`; 2007 /*!40101 SET @saved_cs_client = @@character_set_client */; 2008 /*!40101 SET character_set_client = utf8 */; 2009 CREATE TABLE `user` ( 2010 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', 2011 `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
Hi seife
'choked' exactly how - ie. which error message? I would say failing on a "create table" is quite unusual.
It was a mysql error with a number, i googled for it and it seems to mean "table already exists" or such. Unfortunately, I have no exact records of the problem, and back then was very focused on getting it to work again. But this error should be easily reproducible for me on an fresh 15.2 installation when trying to restore this backup, so I can probably relatively easy file a bug for that. Best regards, seife -- Stefan Seyfried "For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -- Richard Feynman -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
Stefan Seyfried wrote:
Am 16.03.20 um 11:03 schrieb Per Jessen:
Stefan Seyfried wrote:
So I moved away /var/lib/mysql, rerun mysql_secure_installation and tried to restore the database dump with
mysql -u root < /space3/backup/sda/mysqldump/2020-03-15.sql
which choked on line 2009
2002 -- 2003 -- Table structure for table `user` 2004 -- 2005 2006 DROP TABLE IF EXISTS `user`; 2007 /*!40101 SET @saved_cs_client = @@character_set_client */; 2008 /*!40101 SET character_set_client = utf8 */; 2009 CREATE TABLE `user` ( 2010 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', 2011 `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
Hi seife
'choked' exactly how - ie. which error message? I would say failing on a "create table" is quite unusual.
It was a mysql error with a number, i googled for it and it seems to mean "table already exists" or such.
FWIW, I have reproduced your problem (with the export you provided in the bugreport). It appears that the mysql.user table is not being dropped despite 'DROP TABLE IF EXISTS `user`'. The issue is that 'user' in this version (10.4.12) of mariadb is not a table, but a view. In Leap 15.1, version 10.2.25, it was a table. -- Per Jessen, Zürich (16.7°C) Member, openSUSE Heroes -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
On 16/03/2020 10.29, Stefan Seyfried wrote: ...
So what information would the mariadb maintainer need from me in a good bugreport?
:-) Well, IMO exactly what you wrote here would make a good bugreport. The person that takes the bug would ask for whatever more he needs. -- Cheers / Saludos, Carlos E. R. (from 15.1 x86_64 at Telcontar)
Am 16.03.20 um 11:46 schrieb Carlos E. R.:
On 16/03/2020 10.29, Stefan Seyfried wrote:
...
So what information would the mariadb maintainer need from me in a good bugreport?
:-)
Well, IMO exactly what you wrote here would make a good bugreport. The person that takes the bug would ask for whatever more he needs.
On the receiving end of a bug report with as little concrete facts as I provided yet, I would probably be not amused, especially if it comes from an old-timer who should know how to create good bug reports ;-) I'm hoping for some hints like "you should provide /var/lib/mysql/foo.bar and baz.log files". -- Stefan Seyfried "For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -- Richard Feynman -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
Gesendet: Montag, 16. März 2020 um 13:09 Uhr Von: "Stefan Seyfried" <stefan.seyfried@googlemail.com> An: opensuse-factory@opensuse.org Betreff: Re: [opensuse-factory] 15.2 beta update / mariadb problem
Am 16.03.20 um 11:46 schrieb Carlos E. R.:
On 16/03/2020 10.29, Stefan Seyfried wrote:
...
So what information would the mariadb maintainer need from me in a good bugreport?
:-)
Well, IMO exactly what you wrote here would make a good bugreport. The person that takes the bug would ask for whatever more he needs.
On the receiving end of a bug report with as little concrete facts as I provided yet, I would probably be not amused, especially if it comes from an old-timer who should know how to create good bug reports ;-)
I'm hoping for some hints like "you should provide /var/lib/mysql/foo.bar and baz.log files". You should add MariaDB version numbers (old and new one) and the ownCloud version number, because that could be related to a special database and only reproducable with a special version of ownCloud.
Best regards, Sarah
-- Stefan Seyfried
"For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -- Richard Feynman -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
-- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
On 16/03/2020 13.09, Stefan Seyfried wrote:
Am 16.03.20 um 11:46 schrieb Carlos E. R.:
On 16/03/2020 10.29, Stefan Seyfried wrote:
...
So what information would the mariadb maintainer need from me in a good bugreport?
:-)
Well, IMO exactly what you wrote here would make a good bugreport. The person that takes the bug would ask for whatever more he needs.
On the receiving end of a bug report with as little concrete facts as I provided yet, I would probably be not amused, especially if it comes from an old-timer who should know how to create good bug reports ;-)
Well, I'm not a database expert, I would also need the asignee to tell me what else he would need. Else, "someone" add some automatics to the bugzilla to hint a link with information about what to add to a report on "whatever". If the whatever is YaST, I know there is a wiki page with detailed information about what to add, but I don't know about other components. -- Cheers / Saludos, Carlos E. R. (from 15.1 x86_64 at Telcontar)
On Mon, Mar 16, 2020 at 6:29 AM Stefan Seyfried <stefan.seyfried@googlemail.com> wrote:
Hi all,
I need some guidance on how to create a proper bugreport. I'm running a server at home with Leap, which hosts among others an owncloud instance with mariadb as database backend.
Since I was using a newer kernel because of XFS features anyway, yesterday I thought it would be a good idea to try updating to Leap 15.2 beta.
I did take a fresh backup before doing this, which proved a good idea.
After the update, the mariadb databases were broken. mysql root user was not able to access the database. Re-running "mysql_secure_installation" was not possible because of that.
So I moved away /var/lib/mysql, rerun mysql_secure_installation and tried to restore the database dump with
mysql -u root < /space3/backup/sda/mysqldump/2020-03-15.sql
which choked on line 2009
2002 -- 2003 -- Table structure for table `user` 2004 -- 2005 2006 DROP TABLE IF EXISTS `user`; 2007 /*!40101 SET @saved_cs_client = @@character_set_client */; 2008 /*!40101 SET character_set_client = utf8 */; 2009 CREATE TABLE `user` ( 2010 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', 2011 `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
Because I needed the system up, the next try was "restore /var/lib/mysql from backup" which fortunately worked.
My guess is that even though mariadb shows a post-upgrade message that you should backup /var/lib/mysql before restarting the database (which will perform an update), actually the RPM restarts the database and something went badly wrong at this step.
As you might guess, I know nothing about mariadb and databases in general, so I'm type of lost on how to create a useful bugreport.
I still have the (broken) contents of /var/lib/mysql that do not work. I also have a fairly complete backup of the system before the update, so if time permits, I might try to reproduce the issue in a virtual machine.
So what information would the mariadb maintainer need from me in a good bugreport?
Ditto, I still have a server with hosed mysql authentication.. happended in tumblweed too.. unfortunately I have not been able to completely shut it down for a few hours to do a full restore.. so it is running with skip-grant-tables and no networking whatsoever. apparently the mysql authentication tables ends with no users in, not even root.. I have database backups, yeah, but I 'll prefer not to restore them and fix the authentication issue instead. -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
Am 16.03.20 um 13:56 schrieb Cristian Rodríguez:
Ditto, I still have a server with hosed mysql authentication.. happended in tumblweed too.. unfortunately I have not been able to completely shut it down for a few hours to do a full restore.. so it is running with skip-grant-tables and no networking whatsoever. apparently the mysql authentication tables ends with no users in, not even root..
OK, I'll open a bug to get this started.
I have database backups, yeah, but I 'll prefer not to restore them and fix the authentication issue instead.
Well, I was unable to restore the db dump, but that may totally me being stupid :-) (I'm already trying to reproduce the issue) -- Stefan Seyfried "For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -- Richard Feynman -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
Am 16.03.20 um 14:05 schrieb Stefan Seyfried:
Am 16.03.20 um 13:56 schrieb Cristian Rodríguez:
Ditto, I still have a server with hosed mysql authentication.. happended in tumblweed too.. unfortunately I have not been able to completely shut it down for a few hours to do a full restore.. so it is running with skip-grant-tables and no networking whatsoever. apparently the mysql authentication tables ends with no users in, not even root..
OK, I'll open a bug to get this started.
JFTR, this is https://bugzilla.opensuse.org/show_bug.cgi?id=1166781
I have database backups, yeah, but I 'll prefer not to restore them and fix the authentication issue instead.
Well, I was unable to restore the db dump, but that may totally me being stupid :-)
and this (easily reproduced) is https://bugzilla.opensuse.org/show_bug.cgi?id=1166786 -- Stefan Seyfried "For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -- Richard Feynman -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
Am 16.03.20 um 14:44 schrieb Stefan Seyfried:
Am 16.03.20 um 14:05 schrieb Stefan Seyfried:
Am 16.03.20 um 13:56 schrieb Cristian Rodríguez:
Ditto, I still have a server with hosed mysql authentication.. happended in tumblweed too.. unfortunately I have not been able to completely shut it down for a few hours to do a full restore.. so it is running with skip-grant-tables and no networking whatsoever. apparently the mysql authentication tables ends with no users in, not even root..
OK, I'll open a bug to get this started.
JFTR, this is https://bugzilla.opensuse.org/show_bug.cgi?id=1166781
bugzilla (or the microfocus login site) is semui-down, so let's report here. I could reproduce this, by restoring the 15.1 filesystem into a VM and running the 15.2 update again. opensuse-test:~ # mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) So I have a test system available in broken state ready for examination :-)
I have database backups, yeah, but I 'll prefer not to restore them and fix the authentication issue instead. -- Stefan Seyfried
"For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -- Richard Feynman -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
On 3/17/20 8:52 PM, Stefan Seyfried wrote:
Am 16.03.20 um 14:44 schrieb Stefan Seyfried:
Am 16.03.20 um 14:05 schrieb Stefan Seyfried:
Am 16.03.20 um 13:56 schrieb Cristian Rodríguez:
Ditto, I still have a server with hosed mysql authentication.. happended in tumblweed too.. unfortunately I have not been able to completely shut it down for a few hours to do a full restore.. so it is running with skip-grant-tables and no networking whatsoever. apparently the mysql authentication tables ends with no users in, not even root..
OK, I'll open a bug to get this started.
JFTR, this is https://bugzilla.opensuse.org/show_bug.cgi?id=1166781
bugzilla (or the microfocus login site) is semui-down, so let's report here.
I could reproduce this, by restoring the 15.1 filesystem into a VM and running the 15.2 update again.
opensuse-test:~ # mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
So I have a test system available in broken state ready for examination :-)
I have database backups, yeah, but I 'll prefer not to restore them and fix the authentication issue instead.
I am very far from an expert in this area but from what I was reading the other day I believe that the change here is security related which is to follow upstream in no longer allowing the root user access to databases. I believe there are plans to include a post install message with this information but we should also make sure the change is well documented in the release notes. I hope this helps. -- Simon Lees (Simotek) http://simotek.net Emergency Update Team keybase.io/simotek SUSE Linux Adelaide Australia, UTC+10:30 GPG Fingerprint: 5B87 DB9D 88DC F606 E489 CEC5 0922 C246 02F0 014B
Am 20.03.20 um 00:09 schrieb Simon Lees:
On 3/17/20 8:52 PM, Stefan Seyfried wrote:
Am 16.03.20 um 14:44 schrieb Stefan Seyfried:
Am 16.03.20 um 14:05 schrieb Stefan Seyfried:
Am 16.03.20 um 13:56 schrieb Cristian Rodríguez:
JFTR, this is https://bugzilla.opensuse.org/show_bug.cgi?id=1166781
bugzilla (or the microfocus login site) is semui-down, so let's report here.
I could reproduce this, by restoring the 15.1 filesystem into a VM and running the 15.2 update again.
opensuse-test:~ # mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
So I have a test system available in broken state ready for examination :-)
I have database backups, yeah, but I 'll prefer not to restore them and fix the authentication issue instead.
I am very far from an expert in this area but from what I was reading the other day I believe that the change here is security related which is to follow upstream in no longer allowing the root user access to databases. I believe there are plans to include a post install message with this information but we should also make sure the change is well documented in the release notes. I hope this helps.
Look in the "resolved, it's a feature not a bug" bugzilla 1166781. It's a known regression in mariadb-upgrade when upgrading databases with a long history. https://jira.mariadb.org/browse/MDEV-21244 I also found a workaround that fixes the issue for me, not sure if that works for everyone, but my databse is upgraded now and still running. I still think it is pretty cynical to: a) get an message after update telling me that I should now back up /var/lib/mysql before restarting mariadb. b) having mariadb restarted automatically nevertheless (and the database is already broken now) => remove tat %service_del_postun from the spec file! c) the most impressive thing is, that this is considered a feature, not a bug I have resolved it for me now, but just out of interest, I'll check how debian handles this issue once they offer a stable distribution that had mariadb-10.4. -- Stefan Seyfried "For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -- Richard Feynman -- To unsubscribe, e-mail: opensuse-factory+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-factory+owner@opensuse.org
participants (7)
-
Carlos E. R. -
Cristian Rodríguez -
Per Jessen -
per＠opensuse.org -
Sarah Julia Kriesch -
Simon Lees -
Stefan Seyfried