Description of problem: Mageia 7 contains mariadb version 10.3 and Mageia 8 version 10.5. mysql_upgrade from 10.5 can only upgrade a database from one updated to 10.4 meaning that databases created before the upgrade of the OS are no-longer upgradable. This causes problems with schema and prevents password changes or adding of new users to the database and other issues. Version-Release number of selected component (if applicable): How reproducible: Fully. Steps to Reproduce: 1.Create database under Mageia version before 8 2.Upgrade system to Mageia 8 3.Try to run mysql_upgrade
Thank you for the report. I am surprised that Mageia 7 stopped at version 10.3, because we moved from 10.3.16 to 10.4.6 (at least in Cauldron) in June 2019; to 10.4.13 May 2020; to 10.5.4 June 2020. I shall check my M7 system to see how far it got before pushing this report. Back soon.
CC: (none) => lewyssmith
Hmmm. You are indeed right: the last M7 mariadb update I have is: mariadb-10.3.29-1.mga7.x86_64 7 June 2021 so it is surprising that you are the first reporter. Assigning this to the tools people re the upgrade, CC'ing MarcK for MariaDB, DaveH for possible helpful advice. There must be a way round this - finding a version 10.4 as the stepping stone?
Priority: Normal => HighCC: lewyssmith => davidwhodgins, mageiaAssignee: bugsquad => mageiatoolsSeverity: normal => major
One of my m7 to m8 upgrade logs shows I upgraded from mariadb-10.3.29-1.mga7.x86_64 to mariadb-10.5.11-2.mga8.x86_64.rpm I don't recall whether or not I ran mysql-upgrade since then, though I also have encountered no errors since then with things such as phpmyadmin or any other programs using mariadb. Just ran mysql-upgrade now to see what would happen and it completed with Phase 7/7: Running 'FLUSH PRIVILEGES' OK and also OK for all of the preceding databases and tables. Seems skipping a version did not cause any problems. Stephen, have you tried running mysql-upgrade?
True, due to our policy, we sticked mga7 with MariaDB 10.3. But like other software, mariadb releases ~ 1 version each year. I did not see any need to backport 10.4 to mga7. My MariaDB installs did the upgrade very well. Like Dave, I never had any trouble updating. Even mysql_upgrade did not show any problems. Maybe you can giv us your output of mysql_upgrade, so we can see what prevents updating your tables.
OK, I'm wondering if a previous version upgrade screwed up as I get the following: % mysql_upgrade -p Enter password: Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.global_priv OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.index_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user_info OK Phase 2/7: Installing used storage engines Checking for tables with unknown storage engine Phase 3/7: Fixing views mysql.user Error : The user specified as a definer ('mariadb.sys'@'localhost') does not exist error : Corrupt Phase 4/7: Running 'mysql_fix_privilege_tables' ERROR 1449 (HY000) at line 8: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 17: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 60: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 69: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 87: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 173: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 182: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 184: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 195: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 200: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 203: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 327: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 330: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 398: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 403: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 405: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 408: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 412: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 417: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 438: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1449 (HY000) at line 446: The user specified as a definer ('mariadb.sys'@'localhost') does not exist ERROR 1005 (HY000) at line 691: Can't create table `mysql`.`innodb_table_stats` (errno: 165 "Table is read only") ERROR 1005 (HY000) at line 695: Can't create table `mysql`.`innodb_index_stats` (errno: 165 "Table is read only") ERROR 1005 (HY000) at line 699: Can't create table `mysql`.`transaction_registry` (errno: 165 "Table is read only") ERROR 1005 (HY000) at line 771: Can't create table `mysql`.`gtid_slave_pos` (errno: 165 "Table is read only") ERROR 1036 (HY000) at line 774: Table 'ndb_binlog_index' is read only ERROR 1036 (HY000) at line 793: Table 'accounts' is read only ERROR 1007 (HY000) at line 798: Can't create database 'performance_schema'; database exists FATAL ERROR: Upgrade failed %
could you please run mysql-upgrade as root user without "-p" - this should not require entering any passwords if run locally.
Nope, because the root user has a password: [root@nowhere ~]# mysql_upgrade Version check failed. Got the following error when calling the 'mysql' command line client ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) FATAL ERROR: Upgrade failed [root@nowhere ~]# which mysql_upgrade /bin/mysql_upgrade With the password it does as before.
sorry, I still can't reproduce. I've just installed mga7 on a virtual maschine, installed mariadb, secured the installation, by setting a root password. After that, updated to mag8 and run mysql_upgrade -p and it ran just fine, wout any error. After that verified table mysql.user and the user "mariadb.sys" was added as expected.
Summary: Upgrade from Mageia 7 to 8 skips mariadb version breaking database upgrade path. => mariadb failing due to "('mariadb.sys'@'localhost') does not exist"
The question becomes, what happened to the user mariadb.sys? On my installs, in the database "mysql", view "user" has an entry for Host "localhost", User "mariadb.sys". Are you using docker? Asking as I found ... https://github.com/MariaDB/mariadb-docker/commit/f2ba44905eb3aed53fca34536f05f109055d9bc2
Also see https://www.thegeekdiary.com/what-is-the-purpose-of-mysql-syslocalhost-user/
Thank you both Marc & Dave for probing this, and at such lengths. I have changed the assignment from mageiatools to Marc because it is clear that the upgrade process is sound, so the problem is elsewhere.
Assignee: mageiatools => mageiaCC: mageia => (none)
Fixed in versions: 10.2.42 10.3.33 10.4.23 10.5.14 10.6.6 10.7.2 10.8.1 waiting for the update to come
URL: (none) => https://jira.mariadb.org/browse/MDEV-27236
versions have been released
Status: NEW => RESOLVEDResolution: (none) => FIXED