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

Upgrade removes all changes to 'mysql' database

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.36, 10.0.10
    • 5.5.37, 10.0.11
    • None
    • None
    • Debian Wheezy, 64bit

    Description

      Every server upgrade or downgrade removes (probably) all changes done to 'mysql' database including changes used to increase login length described here:
      https://mariadb.com/kb/en/create-user/#user-names

      Attachments

        Issue Links

          Activity

            Hi,
            What do you mean by "probably"? What exactly do you observe?

            elenst Elena Stepanova added a comment - Hi, What do you mean by "probably"? What exactly do you observe?
            azurit azurit added a comment -

            I observed that changes made to increase login length were all removed - logins are, again, char(16).

            azurit azurit added a comment - I observed that changes made to increase login length were all removed - logins are, again, char(16).
            azurit azurit added a comment -

            Is 5.5.37 really going to be released on 14.4.2014? Will fix for this bug get into it? Thank you.

            azurit azurit added a comment - Is 5.5.37 really going to be released on 14.4.2014? Will fix for this bug get into it? Thank you.

            I observed that changes made to increase login length were all removed - logins are, again, char(16).

            You are right. There is an ancient logic in the upgrade SQL, which modifies system tables' columns on a totally unrelated reason, but since MODIFY includes the column definition, it ends up to be reverted to the old one.

            Is 5.5.37 really going to be released on 14.4.2014? Will fix for this bug get into it? Thank you.

            April 14 is the current plan, yes.
            Whether or not the fix will be included depends on how fast we are able to come up with a reasonably safe way to fix it.

            elenst Elena Stepanova added a comment - I observed that changes made to increase login length were all removed - logins are, again, char(16). You are right. There is an ancient logic in the upgrade SQL, which modifies system tables' columns on a totally unrelated reason, but since MODIFY includes the column definition, it ends up to be reverted to the old one. Is 5.5.37 really going to be released on 14.4.2014? Will fix for this bug get into it? Thank you. April 14 is the current plan, yes. Whether or not the fix will be included depends on how fast we are able to come up with a reasonably safe way to fix it.
            azurit azurit added a comment -

            Maybe you should warn users that upgrade can results in truncated logins which can results in broken applications.

            azurit azurit added a comment - Maybe you should warn users that upgrade can results in truncated logins which can results in broken applications.
            elenst Elena Stepanova added a comment - - edited

            azurit,
            I'm not sure we can get away with just a warning, since there is no a good enough workaround for the users. Dumping system tables every time before a minor upgrade doesn't sound like a great idea.

            serg,
            The problem affects both 5.5 and 10.0. In 10.0, even though we extend the length of the columns at the end of mysql_system_tables_fix.sql, earlier there is an ALTER that truncates them (and hence the data) to 16 symbols. The fix is simple there, we just need to modify the alter to use the correct length.

            With 5.5, the only idea I have at the moment is doing it via prepared statements, something like the example below.

            Unfortunately, it will have to be done in different places, so it's going to look ugly.
            Anyway, if you think that's what we should do (both for 5.5 and 10.0), I can go through the scripts and make the changes, so you don't need to waste time on that.

            === modified file 'scripts/mysql_system_tables_fix.sql'
            --- scripts/mysql_system_tables_fix.sql	2014-03-17 12:04:28 +0000
            +++ scripts/mysql_system_tables_fix.sql	2014-04-10 14:21:34 +0000
            @@ -156,10 +156,20 @@
             
             # Convert all tables to UTF-8 with binary collation
             # and reset all char columns to correct width
            +
            +SELECT character_maximum_length INTO @col_length FROM INFORMATION_SCHEMA.COLUMNS
            +  WHERE table_schema = 'mysql' AND table_name = 'user' AND column_name = 'user';
            +
            +SET @stmt = CONCAT("
             ALTER TABLE user
               MODIFY Host char(60) NOT NULL default '',
            -  MODIFY User char(16) NOT NULL default '',
            +  MODIFY User char(", @col_length, ") NOT NULL default '',
            -  ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
            +  ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin
            +");
            +PREPARE stmt FROM @stmt;
            +EXECUTE stmt;
            +
             ALTER TABLE user
               MODIFY Password char(41) character set latin1 collate latin1_bin NOT NULL default '',
               MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,

            elenst Elena Stepanova added a comment - - edited azurit , I'm not sure we can get away with just a warning, since there is no a good enough workaround for the users. Dumping system tables every time before a minor upgrade doesn't sound like a great idea. serg , The problem affects both 5.5 and 10.0. In 10.0, even though we extend the length of the columns at the end of mysql_system_tables_fix.sql, earlier there is an ALTER that truncates them (and hence the data) to 16 symbols. The fix is simple there, we just need to modify the alter to use the correct length. With 5.5, the only idea I have at the moment is doing it via prepared statements, something like the example below. Unfortunately, it will have to be done in different places, so it's going to look ugly. Anyway, if you think that's what we should do (both for 5.5 and 10.0), I can go through the scripts and make the changes, so you don't need to waste time on that. === modified file 'scripts/mysql_system_tables_fix.sql' --- scripts/mysql_system_tables_fix.sql 2014-03-17 12:04:28 +0000 +++ scripts/mysql_system_tables_fix.sql 2014-04-10 14:21:34 +0000 @@ -156,10 +156,20 @@ # Convert all tables to UTF-8 with binary collation # and reset all char columns to correct width + +SELECT character_maximum_length INTO @col_length FROM INFORMATION_SCHEMA.COLUMNS + WHERE table_schema = 'mysql' AND table_name = 'user' AND column_name = 'user'; + +SET @stmt = CONCAT(" ALTER TABLE user MODIFY Host char(60) NOT NULL default '', - MODIFY User char(16) NOT NULL default '', + MODIFY User char(", @col_length, ") NOT NULL default '', - ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin +"); +PREPARE stmt FROM @stmt; +EXECUTE stmt; + ALTER TABLE user MODIFY Password char(41) character set latin1 collate latin1_bin NOT NULL default '', MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,

            An alternative would be to stop pretending that we support direct upgrades from 3.23 and remove these ALTER TABLE statements.

            serg Sergei Golubchik added a comment - An alternative would be to stop pretending that we support direct upgrades from 3.23 and remove these ALTER TABLE statements.

            Fixed using the conservative approach.

            In 5.5:
            http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/4132

            Fixed as suggested above, by checking the length of User column and using it to create a prepared statement.
            To avoid mutiple queries to I_S.COLUMNS (which in big databases can be expensive), the logic assumes that if system tables were modified, it was done consistently as described here: https://mariadb.com/kb/en/create-user/#user-names .
            mysql.user.User field length is checked – if it's old, then old lengths are used everywhere. If it's modified, then the new lengths are used.

            In 10.0:
            http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/4148
            Fixed simply by using correct lengths in intermediate ALTER statements.

            Tested by creating dumps before and after upgrade and comparing (diff-ing) them.

            Pushed into 5.5 and 10.0, accordingly.

            Also, created a new task MDEV-6069 for clean-up that serg suggested in the previous comment.

            elenst Elena Stepanova added a comment - Fixed using the conservative approach. In 5.5: http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/4132 Fixed as suggested above, by checking the length of User column and using it to create a prepared statement. To avoid mutiple queries to I_S.COLUMNS (which in big databases can be expensive), the logic assumes that if system tables were modified, it was done consistently as described here: https://mariadb.com/kb/en/create-user/#user-names . mysql.user.User field length is checked – if it's old, then old lengths are used everywhere. If it's modified, then the new lengths are used. In 10.0: http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/4148 Fixed simply by using correct lengths in intermediate ALTER statements. Tested by creating dumps before and after upgrade and comparing (diff-ing) them. Pushed into 5.5 and 10.0, accordingly. Also, created a new task MDEV-6069 for clean-up that serg suggested in the previous comment.

            People

              elenst Elena Stepanova
              azurit azurit
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.