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

          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.