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

mysql_upgrade (MariaDB 5.2.X --> MariaDB 10.3.X) does not fix auth_string to change it to authentication_string

    XMLWordPrintable

    Details

      Description

      As per KB link https://mariadb.com/kb/en/upgrading-between-major-mariadb-versions/

      One can upgrade from MariaDB 5.5 to the latest MariaDB version without any trouble. All older versions should work too (as long as the storage engines you were using are still around). But found direct upgrade will not take care of data dictionary mysql.user table structure.

      Newly installed MariaDB servers 10.3.22 have following in mysql.user

       
      plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
      `authentication_string` text COLLATE utf8_bin NOT NULL,
      

      MariaDB servers direct upgrade from 5.2.12 to 10.3.22 (skipping 5.5.68 ) have a difference in mysql.user

      `plugin` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
      `auth_string` text COLLATE utf8_bin NOT NULL,
      

      Difference found mainly for Two columns while up-gradation from 5.2.X to 10.3.X while going through MariaDB 5.6.XX version.

      Column name vary :-

      plugin
      auth_string
      authentication_string

      Considering authentication_string / auth_string

      Fresh installation of 5.5.12 and 5.3.12

      MariaDB [(none)]> desc mysql.user;
      +-----------------------+-----------------------------------+------+-----+---------+-------+
      | Field                 | Type                              | Null | Key | Default | Extra |
      +-----------------------+-----------------------------------+------+-----+---------+-------+
      | auth_string           | text                              | NO   |     | NULL    |       |
      +-----------------------+-----------------------------------+------+-----+---------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
       
        `auth_string` text COLLATE utf8_bin NOT NULL,
      

      Upgrading to MariaDB 5.5.68

       
      MariaDB [(none)]> desc mysql.user;
      +------------------------+-----------------------------------+------+-----+---------+-------+
      | Field                  | Type                              | Null | Key | Default | Extra |
      +------------------------+-----------------------------------+------+-----+---------+-------+
      | auth_string            | text                              | NO   |     | NULL    |       |
      +------------------------+-----------------------------------+------+-----+---------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
        `auth_string` text COLLATE utf8_bin NOT NULL,
      

      Column name "auth_string" continue till upgrading MariaDB 10.3.XX.

      While fresh Installation of 5.5.68

      MariaDB [(none)]> desc mysql.user;
      +------------------------+-----------------------------------+------+-----+---------+-------+
      | Field                  | Type                              | Null | Key | Default | Extra |
      +------------------------+-----------------------------------+------+-----+---------+-------+
      | authentication_string  | text                              | NO   |     | NULL    |       |
      +------------------------+-----------------------------------+------+-----+---------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
       
        `authentication_string` text COLLATE utf8_bin NOT NULL,
      

      Upgrading to MariaDB 10.0.38

      MariaDB [(none)]> desc mysql.user;
      +------------------------+-----------------------------------+------+-----+---------+-------+
      | Field                  | Type                              | Null | Key | Default | Extra |
      +------------------------+-----------------------------------+------+-----+---------+-------+
      | authentication_string  | text                              | NO   |     | NULL    |       |
      +------------------------+-----------------------------------+------+-----+---------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
       
        `authentication_string` text COLLATE utf8_bin NOT NULL,
        
      

      column name "authentication_string" continue till upgrading to MariaDB 10.3.XX

      For Plugin column data type change from MariaDB 5.6.XX version .

      MariaDB 5.2.12 (No changes for below columns)

      MariaDB [(none)]> desc mysql.user;
      +-----------------------+-----------------------------------+------+-----+---------+-------+
      | Field                 | Type                              | Null | Key | Default | Extra |
      +-----------------------+-----------------------------------+------+-----+---------+-------+
      | plugin                | char(60)                          | NO   |     |         |       |
      | auth_string           | text                              | NO   |     | NULL    |       |
      +-----------------------+-----------------------------------+------+-----+---------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
       
       `plugin` char(60) CHARACTER SET latin1 NOT NULL DEFAULT '',
        `auth_string` text COLLATE utf8_bin NOT NULL,
      

      Upgrading to MariaDB 5.3.12 (No changes for below columns)

      MariaDB [(none)]> desc mysql.user;
      +-----------------------+-----------------------------------+------+-----+---------+-------+
      | Field                 | Type                              | Null | Key | Default | Extra |
      +-----------------------+-----------------------------------+------+-----+---------+-------+
      | plugin                | char(60)                          | NO   |     |         |       |
      | auth_string           | text                              | NO   |     | NULL    |       |
      +-----------------------+-----------------------------------+------+-----+---------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
       
        `plugin` char(60) CHARACTER SET latin1 NOT NULL DEFAULT '',
        `auth_string` text COLLATE utf8_bin NOT NULL,
      

      Upgrading to MariaDB 5.5.68 (plugin` char(64) taken care by mysql_upgrade)

      MariaDB [(none)]> desc mysql.user;
      +------------------------+-----------------------------------+------+-----+---------+-------+
      | Field                  | Type                              | Null | Key | Default | Extra |
      +------------------------+-----------------------------------+------+-----+---------+-------+
      | plugin                 | char(64)                          | NO   |     |         |       |
      | auth_string            | text                              | NO   |     | NULL    |       |
      +------------------------+-----------------------------------+------+-----+---------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
       
        `plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
        `auth_string` text COLLATE utf8_bin NOT NULL,
      

      Upgrading to MariaDB 10.0.38 (No changes for below columns)

      MariaDB [(none)]> desc mysql.user;
      +------------------------+-----------------------------------+------+-----+---------+-------+
      | Field                  | Type                              | Null | Key | Default | Extra |
      +------------------------+-----------------------------------+------+-----+---------+-------+
      | plugin                 | char(64)                          | NO   |     |         |       |
      | auth_string            | text                              | NO   |     | NULL    |       |
      +------------------------+-----------------------------------+------+-----+---------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
       
        `plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
        `auth_string` text COLLATE utf8_bin NOT NULL,
      

      Upgrading to MariaDB 10.1.45 (No changes for below columns)

      MariaDB [(none)]> desc mysql.user;
      +------------------------+-----------------------------------+------+-----+----------+-------+
      | Field                  | Type                              | Null | Key | Default  | Extra |
      +------------------------+-----------------------------------+------+-----+----------+-------+
      | plugin                 | char(64)                          | NO   |     |          |       |
      | auth_string            | text                              | NO   |     | NULL     |       |
      +------------------------+-----------------------------------+------+-----+----------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
       
        `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
        `auth_string` text COLLATE utf8_bin NOT NULL,
        
      

      Upgrading to MariaDB 10.2.32 (No changes for below columns)

      MariaDB [(none)]> desc mysql.user;
      +------------------------+-----------------------------------+------+-----+----------+-------+
      | Field                  | Type                              | Null | Key | Default  | Extra |
      +------------------------+-----------------------------------+------+-----+----------+-------+
      | plugin                 | char(64)                          | NO   |     |          |       |
      | auth_string            | text                              | NO   |     | NULL     |       |
      +------------------------+-----------------------------------+------+-----+----------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
       
       `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
        `auth_string` text COLLATE utf8_bin NOT NULL,
      

      Upgrading to MariaDB 10.3.22 (No changes for below columns)

      MariaDB [(none)]> desc mysql.user;
      +------------------------+-----------------------------------+------+-----+----------+-------+
      | Field                  | Type                              | Null | Key | Default  | Extra |
      +------------------------+-----------------------------------+------+-----+----------+-------+
      | plugin                 | char(64)                          | NO   |     |          |       |
      | auth_string            | text                              | NO   |     | NULL     |       |
      +------------------------+-----------------------------------+------+-----+----------+-------+
       
      MariaDB [(none)]> show create table mysql.user\G
       
       `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
        `auth_string` text COLLATE utf8_bin NOT NULL,
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              pramod.mahto@mariadb.com Pramod Mahto
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: