Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
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_ |