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

mysql_upgrade misses some changes to mysql schema

Details

    Description

      Over the time and different versions, some structures in mysql schema get changed, but not all the changes make it to the scripts executed by mysql_upgrade. It means that a schema freshly created by mysql_install_db on a version 10.x.y differs from a schema created on an earlier version and upgraded to 10.x.y by mysql_upgrade.

      All diffs below are from diff -u <upgraded schema> <newly created schema>.

      5.5 => 10.0+

       CREATE TABLE IF NOT EXISTS `general_log` (
      		 
         `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
      		 
         `user_host` mediumtext NOT NULL,
      		 
      -  `thread_id` int(11) NOT NULL,
      		 
      +  `thread_id` bigint(21) unsigned NOT NULL,
      		 
         `server_id` int(10) unsigned NOT NULL,
      		 
         `command_type` varchar(64) NOT NULL,
      		 
         `argument` mediumtext NOT NULL

      10.1 => 10.2+

       CREATE TABLE `servers` (
      		 
         `Server_name` char(64) NOT NULL DEFAULT '',
      		 
      -  `Host` char(64) NOT NULL DEFAULT '',
      		 
      +  `Host` varchar(2048) NOT NULL DEFAULT '',
      		 
         `Db` char(64) NOT NULL DEFAULT '',
      		 
         `Username` char(80) NOT NULL DEFAULT '',
      		 
         `Password` char(64) NOT NULL DEFAULT '',
      		 
         `Port` int(4) NOT NULL DEFAULT 0,
      		 
         `Socket` char(64) NOT NULL DEFAULT '',
      		 
         `Wrapper` char(64) NOT NULL DEFAULT '',
      		 
      -  `Owner` char(64) NOT NULL DEFAULT '',
      		 
      +  `Owner` varchar(512) NOT NULL DEFAULT '',
      		 
         PRIMARY KEY (`Server_name`)

      10.4 => 10.5

        CREATE TABLE `help_relation` (
      		 
         `help_topic_id` int(10) unsigned NOT NULL,
      		 
         `help_keyword_id` int(10) unsigned NOT NULL,
      		 
      -  PRIMARY KEY (`help_keyword_id`,`help_topic_id`)
      		 
      +  PRIMARY KEY (`help_keyword_id`,`help_topic_id`),
      		 
      +  KEY `help_topic_id` (`help_topic_id`)
      		 
       ) ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1 TRANSACTIONAL=0 COMMENT='keyword-topic relation';

      10.3 => 10.4+

      This is not a miss, but a difference worth mentioning. With the same 10.4 build in the same environment, on a newly created schema I get mysql.user with charset latin1, while upon upgrade from 10.3 – with utf8.

       /*!50001 SET @saved_cs_client          = @@character_set_client */;
      		 
       /*!50001 SET @saved_cs_results         = @@character_set_results */;
      		 
       /*!50001 SET @saved_col_connection     = @@collation_connection */;
      		 
      -/*!50001 SET character_set_client      = utf8 */;
      		 
      -/*!50001 SET character_set_results     = utf8 */;
      		 
      -/*!50001 SET collation_connection      = utf8_general_ci */;
      		 
      +/*!50001 SET character_set_client      = latin1 */;
      		 
      +/*!50001 SET character_set_results     = latin1 */;
      		 
      +/*!50001 SET collation_connection      = latin1_swedish_ci */;
      		 
       /*!50001 CREATE ALGORITHM=UNDEFINED */
      		 
       /*!50013 DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER */
      		 
       /*!50001 VIEW `user` AS select `global_priv`.`Host` AS `Host`,`global_priv`.`User` AS `User`,if(json_value(`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova created issue -
          elenst Elena Stepanova added a comment -

          Assigned to serg for the unlikely possibility that [some of] the omissions are intentional.

          elenst Elena Stepanova added a comment - Assigned to serg for the unlikely possibility that [some of] the omissions are intentional.
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Description Over the time and different versions, some structures in {{mysql}} schema get changed, but not all the changes make it to the scripts executed by {{mysql_upgrade}}. It means that a schema freshly created by {{mysql_install_db}} on a version 10.x.y differs from a schema created on an earlier versions and upgraded to 10.x.y by {{mysql_upgrade}}.

          All diffs below are from {{diff -u <upgraded schema> <newly created schema>}}.

          *5.5 => 10.0+*

          {code:sql}
           CREATE TABLE IF NOT EXISTS `general_log` (
             `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
             `user_host` mediumtext NOT NULL,
          - `thread_id` int(11) NOT NULL,
          + `thread_id` bigint(21) unsigned NOT NULL,
             `server_id` int(10) unsigned NOT NULL,
             `command_type` varchar(64) NOT NULL,
             `argument` mediumtext NOT NULL
          {code}

          *10.1 => 10.2+*
          {code:sql}
           CREATE TABLE `servers` (
             `Server_name` char(64) NOT NULL DEFAULT '',
          - `Host` char(64) NOT NULL DEFAULT '',
          + `Host` varchar(2048) NOT NULL DEFAULT '',
             `Db` char(64) NOT NULL DEFAULT '',
             `Username` char(80) NOT NULL DEFAULT '',
             `Password` char(64) NOT NULL DEFAULT '',
             `Port` int(4) NOT NULL DEFAULT 0,
             `Socket` char(64) NOT NULL DEFAULT '',
             `Wrapper` char(64) NOT NULL DEFAULT '',
          - `Owner` char(64) NOT NULL DEFAULT '',
          + `Owner` varchar(512) NOT NULL DEFAULT '',
             PRIMARY KEY (`Server_name`)
          {code}

          *10.4 => 10.5*

          {code:sql}
            CREATE TABLE `help_relation` (
             `help_topic_id` int(10) unsigned NOT NULL,
             `help_keyword_id` int(10) unsigned NOT NULL,
          - PRIMARY KEY (`help_keyword_id`,`help_topic_id`)
          + PRIMARY KEY (`help_keyword_id`,`help_topic_id`),
          + KEY `help_topic_id` (`help_topic_id`)
           ) ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1 TRANSACTIONAL=0 COMMENT='keyword-topic relation';
          {code}

          *10.3 => 10.4+*

          This is not a miss, but a difference worth mentioning. With the same 10.4 build in the same environment, on a newly created schema I get {{mysql.user}} with charset {{latin1}}, while upon upgrade from 10.3 -- with {{utf8}}.

          {code:sql}
           /*!50001 SET @saved_cs_client = @@character_set_client */;
           /*!50001 SET @saved_cs_results = @@character_set_results */;
           /*!50001 SET @saved_col_connection = @@collation_connection */;
          -/*!50001 SET character_set_client = utf8 */;
          -/*!50001 SET character_set_results = utf8 */;
          -/*!50001 SET collation_connection = utf8_general_ci */;
          +/*!50001 SET character_set_client = latin1 */;
          +/*!50001 SET character_set_results = latin1 */;
          +/*!50001 SET collation_connection = latin1_swedish_ci */;
           /*!50001 CREATE ALGORITHM=UNDEFINED */
           /*!50013 DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER */
           /*!50001 VIEW `user` AS select `global_priv`.`Host` AS `Host`,`global_priv`.`User` AS `User`,if(json_value(`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_
          {code}           		Over the time and different versions, some structures in {{mysql}} schema get changed, but not all the changes make it to the scripts executed by {{mysql_upgrade}}. It means that a schema freshly created by {{mysql_install_db}} on a version 10.x.y differs from a schema created on an earlier version and upgraded to 10.x.y by {{mysql_upgrade}}.

          All diffs below are from {{diff -u <upgraded schema> <newly created schema>}}.

          *5.5 => 10.0+*

          {code:sql}
           CREATE TABLE IF NOT EXISTS `general_log` (
             `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
             `user_host` mediumtext NOT NULL,
          - `thread_id` int(11) NOT NULL,
          + `thread_id` bigint(21) unsigned NOT NULL,
             `server_id` int(10) unsigned NOT NULL,
             `command_type` varchar(64) NOT NULL,
             `argument` mediumtext NOT NULL
          {code}

          *10.1 => 10.2+*
          {code:sql}
           CREATE TABLE `servers` (
             `Server_name` char(64) NOT NULL DEFAULT '',
          - `Host` char(64) NOT NULL DEFAULT '',
          + `Host` varchar(2048) NOT NULL DEFAULT '',
             `Db` char(64) NOT NULL DEFAULT '',
             `Username` char(80) NOT NULL DEFAULT '',
             `Password` char(64) NOT NULL DEFAULT '',
             `Port` int(4) NOT NULL DEFAULT 0,
             `Socket` char(64) NOT NULL DEFAULT '',
             `Wrapper` char(64) NOT NULL DEFAULT '',
          - `Owner` char(64) NOT NULL DEFAULT '',
          + `Owner` varchar(512) NOT NULL DEFAULT '',
             PRIMARY KEY (`Server_name`)
          {code}

          *10.4 => 10.5*

          {code:sql}
            CREATE TABLE `help_relation` (
             `help_topic_id` int(10) unsigned NOT NULL,
             `help_keyword_id` int(10) unsigned NOT NULL,
          - PRIMARY KEY (`help_keyword_id`,`help_topic_id`)
          + PRIMARY KEY (`help_keyword_id`,`help_topic_id`),
          + KEY `help_topic_id` (`help_topic_id`)
           ) ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1 TRANSACTIONAL=0 COMMENT='keyword-topic relation';
          {code}

          *10.3 => 10.4+*

          This is not a miss, but a difference worth mentioning. With the same 10.4 build in the same environment, on a newly created schema I get {{mysql.user}} with charset {{latin1}}, while upon upgrade from 10.3 -- with {{utf8}}.

          {code:sql}
           /*!50001 SET @saved_cs_client = @@character_set_client */;
           /*!50001 SET @saved_cs_results = @@character_set_results */;
           /*!50001 SET @saved_col_connection = @@collation_connection */;
          -/*!50001 SET character_set_client = utf8 */;
          -/*!50001 SET character_set_results = utf8 */;
          -/*!50001 SET collation_connection = utf8_general_ci */;
          +/*!50001 SET character_set_client = latin1 */;
          +/*!50001 SET character_set_results = latin1 */;
          +/*!50001 SET collation_connection = latin1_swedish_ci */;
           /*!50001 CREATE ALGORITHM=UNDEFINED */
           /*!50013 DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER */
           /*!50001 VIEW `user` AS select `global_priv`.`Host` AS `Host`,`global_priv`.`User` AS `User`,if(json_value(`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_
          {code}
          serg Sergei Golubchik made changes -
          Assignee Sergei Golubchik [ serg ] Oleksandr Byelkin [ sanja ]
          serg Sergei Golubchik added a comment -

          You're right, they are not intentional, as far as I can see

          serg Sergei Golubchik added a comment - You're right, they are not intentional, as far as I can see
          danblack Daniel Black made changes -
          Labels beginner-friendly
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 109067 ] MariaDB v4 [ 141976 ]
          danblack Daniel Black made changes -
          Assignee Oleksandr Byelkin [ sanja ] Daniel Black [ danblack ]
          danblack Daniel Black added a comment -

          10.4 resolved thanks to Lorna Luo from AWS.

          Didn't resolve mysql.user view - hasn't caused a problem that I've seen. Can relook if needed.

          Anticipating 10.5 fix per PR #2511

          danblack Daniel Black added a comment - 10.4 resolved thanks to Lorna Luo from AWS. Didn't resolve mysql.user view - hasn't caused a problem that I've seen. Can relook if needed. Anticipating 10.5 fix per PR #2511
          danblack Daniel Black made changes -
          issue.field.resolutiondate 2023-03-02 02:46:56.0 2023-03-02 02:46:56.385
          danblack Daniel Black made changes -
          Fix Version/s 10.4.29 [ 28510 ]
          Fix Version/s 10.5.20 [ 28512 ]
          Fix Version/s 10.6.13 [ 28514 ]
          Fix Version/s 10.8.8 [ 28518 ]
          Fix Version/s 10.9.6 [ 28520 ]
          Fix Version/s 10.10.4 [ 28522 ]
          Fix Version/s 10.11.3 [ 28524 ]
          Fix Version/s 11.0.2 [ 28706 ]
          Fix Version/s 10.3 [ 22126 ]
          Fix Version/s 10.4 [ 22408 ]
          Fix Version/s 10.5 [ 23123 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          danblack Daniel Black added a comment -

          Forgotten my own work:

          MDEV-30065: mariadb-install-db --enforce-storage-engine=InnoDB fails … pr #2437 - changes help_relation to remove the FK and add the secondary index help_topic_id, which this MDEV required anyway.

          danblack Daniel Black added a comment - Forgotten my own work: MDEV-30065 : mariadb-install-db --enforce-storage-engine=InnoDB fails … pr #2437 - changes help_relation to remove the FK and add the secondary index help_topic_id, which this MDEV required anyway.

          People

            danblack Daniel Black
            elenst Elena Stepanova
            Votes:
            1 Vote for this issue
            Watchers:
            4 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.