Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5010

InnoDB errors appearing in logs with upgrade from 10.0.0 to 10.0.4

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.0.4
    • 10.0.7
    • None
    • Centos 6.4

    Description

      When upgrading from 10.0.0 to 10.0.4 the following error appears in the log files:

      2013-09-11 10:27:09 7f6dc5bae700 InnoDB: Error: There are 1 foreign key(s) pointing to "mysql"."innodb_table_stats", but there must be 0.
      2013-09-11 10:27:09 7f6dc5bae700 InnoDB: Error: Fetch of persistent statistics requested for table "XXXXXX"."XXXXXX" but the required system tables mysql.innodb_table_stats and 
      mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

      Attachments

        Issue Links

          Activity

            Same happens while upgrading from MySQL 5.6.5 to MySQL 5.6.10 or to MySQL 5.6.12.

            elenst Elena Stepanova added a comment - Same happens while upgrading from MySQL 5.6.5 to MySQL 5.6.10 or to MySQL 5.6.12.

            ... or to MySQL 5.6.13.
            But for upgrade from MySQL 5.6.10 to 5.6.13 it works all right, so I presume it should be okay for further versions of MariaDB 10.0 as wel.

            To my understanding, as a general rule, MySQL doesn't guarantee live upgrade from a non-GA version. While I haven't found mentioning of this specific problem in the manual, it does say this:

            "For upgrades between versions of a MySQL release series that has reached General Availability status, you can move the MySQL format files and data files between different versions on systems with the same architecture. For upgrades to a version of a MySQL release series that is in development status, that is not necessarily true. Use of development releases is at your own risk."

            I suppose what we have here is an example of "not true".

            We can try to report it to MySQL, but I'm pretty sure that it will be said to be "not a bug", due to the reason above.

            elenst Elena Stepanova added a comment - ... or to MySQL 5.6.13. But for upgrade from MySQL 5.6.10 to 5.6.13 it works all right, so I presume it should be okay for further versions of MariaDB 10.0 as wel. To my understanding, as a general rule, MySQL doesn't guarantee live upgrade from a non-GA version. While I haven't found mentioning of this specific problem in the manual, it does say this: "For upgrades between versions of a MySQL release series that has reached General Availability status, you can move the MySQL format files and data files between different versions on systems with the same architecture. For upgrades to a version of a MySQL release series that is in development status, that is not necessarily true. Use of development releases is at your own risk." I suppose what we have here is an example of "not true". We can try to report it to MySQL, but I'm pretty sure that it will be said to be "not a bug", due to the reason above.

            It's an upstream bug, but as Sergei suggested, it can be fixed just by adding an ALTER to the fix_privilege_tables.sql. I tried the following draft change:

            — scripts/mysql_system_tables_fix.sql 2013-07-04 13:01:36 +0000
            +++ scripts/mysql_system_tables_fix.sql 2013-09-15 16:39:27 +0000
            @@ -648,5 +648,12 @@

            1. This should not be needed, but gives us some extra testing that the above
            2. changes was correct

            +set @have_innodb= (select count(engine) from information_schema.engines where engine='INNODB' and support != 'NO');
            +SET @innodb_index_stats_fk= (select count from information_schema.referential_constraints where constraint_schema='mysql' and table_name = 'innodb_index_stats' and referenced_table_name = 'innodb_table_stats' and constraint_name = 'innodb_index_stats_ibfk_1');
            +SET @str=IF(@innodb_index_stats_fk > 0 and @have_innodb > 0, "ALTER TABLE mysql.innodb_index_stats DROP FOREIGN KEY `innodb_index_stats_ibfk_1`", "SET @dummy = 0");
            +PREPARE stmt FROM @str;
            +EXECUTE stmt;
            +DROP PREPARE stmt;
            +
            flush privileges;

            It seems to work, although in an attempt to make it look/work similarly to other InnoDB-related changes, I ended up with an ugly construct.

            Jan,

            Could you please take a look, and unless you see any danger in the change, make it look all pretty and neat and apply to 10.0?

            Thanks.

            elenst Elena Stepanova added a comment - It's an upstream bug, but as Sergei suggested, it can be fixed just by adding an ALTER to the fix_privilege_tables.sql. I tried the following draft change: — scripts/mysql_system_tables_fix.sql 2013-07-04 13:01:36 +0000 +++ scripts/mysql_system_tables_fix.sql 2013-09-15 16:39:27 +0000 @@ -648,5 +648,12 @@ This should not be needed, but gives us some extra testing that the above changes was correct +set @have_innodb= (select count(engine) from information_schema.engines where engine='INNODB' and support != 'NO'); +SET @innodb_index_stats_fk= (select count from information_schema.referential_constraints where constraint_schema='mysql' and table_name = 'innodb_index_stats' and referenced_table_name = 'innodb_table_stats' and constraint_name = 'innodb_index_stats_ibfk_1'); +SET @str=IF(@innodb_index_stats_fk > 0 and @have_innodb > 0, "ALTER TABLE mysql.innodb_index_stats DROP FOREIGN KEY `innodb_index_stats_ibfk_1`", "SET @dummy = 0"); +PREPARE stmt FROM @str; +EXECUTE stmt; +DROP PREPARE stmt; + flush privileges; It seems to work, although in an attempt to make it look/work similarly to other InnoDB-related changes, I ended up with an ugly construct. Jan, Could you please take a look, and unless you see any danger in the change, make it look all pretty and neat and apply to 10.0? Thanks.

            I do not fully understand why there is a foreign key to that table or is this a incorrect message ? Where that foreign key is created ?

            jplindst Jan Lindström (Inactive) added a comment - I do not fully understand why there is a foreign key to that table or is this a incorrect message ? Where that foreign key is created ?

            There used to be a foreign key in the older version (created by install/upgrade). Now it's not there and shouldn't be. But when we do live upgrade from the older version to the current one, the foreign key is not dropped.

            elenst Elena Stepanova added a comment - There used to be a foreign key in the older version (created by install/upgrade). Now it's not there and shouldn't be. But when we do live upgrade from the older version to the current one, the foreign key is not dropped.

            Disclaimer: bellow is an educated guess, I didn't try to verify any of that.

            I suppose, that
            1. innodb_table_stats is created from scripts/mysql_system_tables_fix.sql
            2. there is a function in the innodb code that verifies that the table has the correct definition
            3. in earlier 5.6 versions this tables used to have a foreign key
            4. later it was removed
            5. if you upgrade, your innodb_table_stats is created by the earlier mariadb version
            6. so a newer version will complain that the table is incorrectly defined
            7. an ALTER TABLE in the mysql_fix_privilege_tables.sql will fix that

            serg Sergei Golubchik added a comment - Disclaimer: bellow is an educated guess, I didn't try to verify any of that. I suppose, that 1. innodb_table_stats is created from scripts/mysql_system_tables_fix.sql 2. there is a function in the innodb code that verifies that the table has the correct definition 3. in earlier 5.6 versions this tables used to have a foreign key 4. later it was removed 5. if you upgrade, your innodb_table_stats is created by the earlier mariadb version 6. so a newer version will complain that the table is incorrectly defined 7. an ALTER TABLE in the mysql_fix_privilege_tables.sql will fix that

            IIRC the table is created in mysql_system_tables.sql (not _fix).
            I can't say anything about the function in the innodb code, but as described earlier, I did try to add DROP <fk> to mysql_system_tables_fix.sql and it seemed to have fixed the problem, so the guess seems to be correct.

            elenst Elena Stepanova added a comment - IIRC the table is created in mysql_system_tables.sql (not _fix). I can't say anything about the function in the innodb code, but as described earlier, I did try to add DROP <fk> to mysql_system_tables_fix.sql and it seemed to have fixed the problem, so the guess seems to be correct.

            Fixed as suggested.

            jplindst Jan Lindström (Inactive) added a comment - Fixed as suggested.

            People

              jplindst Jan Lindström (Inactive)
              mike.ashton Mike Ashton (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.