[#MDEV-22683] mysql_upgrade misses some changes to mysql schema

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_

[MDEV-22683] mysql_upgrade misses some changes to mysql schema Created: 2020-05-23 Updated: 2023-03-03 Resolved: 2023-03-02
Status:	Closed
Project:	MariaDB Server
Component/s:	Scripts & Clients, Upgrades
Affects Version/s:	10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s:	10.11.3, 11.0.2, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4

[MDEV-22683] mysql_upgrade misses some changes to mysql schema Created: 2020-05-23 Updated: 2023-03-03 Resolved: 2023-03-02