| Bug #112539 | MySQL 8 can not restore dump | ||
|---|---|---|---|
| Submitted: | 27 Sep 2023 12:49 | Modified: | 4 Oct 2023 5:29 |
| Reporter: | Thomas Gertz | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.1.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | restore backup | ||
[27 Sep 2023 12:49]
Thomas Gertz
[28 Sep 2023 13:11]
MySQL Verification Team
Hi Mr. Gertz, Thank you for your bug report. However, this is not a bug. This is a well described behaviour. InnoDB has separate pages for the rows that fit into the default page size and separate pages for the TEXT / BLOB columns. Default page size is 16384 bytes, but you can change it to suit your needs. This is all described in our Reference Manual, for example here: https://dev.mysql.com/doc/refman/8.1/en/innodb-parameters.html#sysvar_innodb_page_size Not a bug.
[28 Sep 2023 13:13]
MySQL Verification Team
Hi, Also, please do note that size of the VARCHAR is expressed in the number of characters and not in bytes. Since your chosen character set is utfmb4, that means that you require 16000 * 4 bytes maximum for that particular column. Not a bug.
[28 Sep 2023 15:05]
Thomas Gertz
I think you miss understand me. I did not created a table user_doc with two colums with varchar(16000). [the backup did it] I created a view with two colums with varchar(16000). The view is working correctly! But if I take a backup and restore the backup then it fails. The bug is that the dump creates a table user_doc, that it should not, because the table does not exits in the database.
[29 Sep 2023 9:47]
MySQL Verification Team
Hi Mr. Gertz, Please send us a test case that will consist only of : * dump of the tables that are involved with that view, without that view * definition of the view * exact description of the way you dumped that schema that produced a table instead of the view Many thanks in advance.
[4 Oct 2023 5:19]
Thomas Gertz
Ok, that is the database without view: CREATE DATABASE `bug`CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; CREATE TABLE `bug`.`doc` ( `id` BIGINT, `xls` VARCHAR(16000) ); CREATE TABLE `bug`.`user` ( `id` BIGINT, `name` VARCHAR(16000) ); That is how I create the dump without the view: mysqldump -u root -p bug > bug_without_view.sql That is the dump without the view: -- MySQL dump 10.13 Distrib 8.1.0, for Win64 (x86_64) -- -- Host: localhost Database: bug -- ------------------------------------------------------ -- Server version 8.1.0 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `doc` -- DROP TABLE IF EXISTS `doc`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `doc` ( `id` bigint DEFAULT NULL, `xls` varchar(16000) COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `doc` -- LOCK TABLES `doc` WRITE; /*!40000 ALTER TABLE `doc` DISABLE KEYS */; /*!40000 ALTER TABLE `doc` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `user` -- DROP TABLE IF EXISTS `user`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `user` ( `id` bigint DEFAULT NULL, `name` varchar(16000) COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `user` -- LOCK TABLES `user` WRITE; /*!40000 ALTER TABLE `user` DISABLE KEYS */; /*!40000 ALTER TABLE `user` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2023-10-04 6:42:11 That is the database with the view. DROP DATABASE bug; CREATE DATABASE `bug`CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; CREATE TABLE `bug`.`doc` ( `id` BIGINT, `xls` VARCHAR(16000) ); CREATE TABLE `bug`.`user` ( `id` BIGINT, `name` VARCHAR(16000) ); CREATE VIEW `bug`.`user_doc` AS (SELECT d.xls,u.name FROM `user` u JOIN doc d ON d.id = u.id That is how I created the dump with the view: mysqldump -u root -p bug > bug_with_view.sql -- MySQL dump 10.13 Distrib 8.1.0, for Win64 (x86_64) -- -- Host: localhost Database: bug -- ------------------------------------------------------ -- Server version 8.1.0 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `doc` -- DROP TABLE IF EXISTS `doc`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `doc` ( `id` bigint DEFAULT NULL, `xls` varchar(16000) COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `doc` -- LOCK TABLES `doc` WRITE; /*!40000 ALTER TABLE `doc` DISABLE KEYS */; /*!40000 ALTER TABLE `doc` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `user` -- DROP TABLE IF EXISTS `user`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `user` ( `id` bigint DEFAULT NULL, `name` varchar(16000) COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `user` -- LOCK TABLES `user` WRITE; /*!40000 ALTER TABLE `user` DISABLE KEYS */; /*!40000 ALTER TABLE `user` ENABLE KEYS */; UNLOCK TABLES; -- -- Temporary view structure for view `user_doc` -- DROP TABLE IF EXISTS `user_doc`; /*!50001 DROP VIEW IF EXISTS `user_doc`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `user_doc` AS SELECT 1 AS `xls`, 1 AS `name`*/; SET character_set_client = @saved_cs_client; -- -- Final view structure for view `user_doc` -- /*!50001 DROP VIEW IF EXISTS `user_doc`*/; /*!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 = utf8mb3 */; /*!50001 SET character_set_results = utf8mb3 */; /*!50001 SET collation_connection = utf8mb3_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `user_doc` AS select `d`.`xls` AS `xls`,`u`.`name` AS `name` from (`user` `u` join `doc` `d` on((`d`.`id` = `u`.`id`))) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2023-10-04 6:46:57 In the dump u see mysql is creating a table user_doc. This table does not exist in my database. I did not created a table user_doc, like u see in my create skripts. However mysqldump creats the table user_doc in the dump file. And if I restore dumpfile a got the error.
[4 Oct 2023 5:29]
Thomas Gertz
Oh nevermind, If I create the dump with mysql, the table user_doc is not in the dumpfile. If I create the dump with sqlyog, that is another app, then in the dumpfile is the table user_doc. So it is a bug of sqlyog an not of mysql. Sorry.
[4 Oct 2023 11:10]
MySQL Verification Team
Hi Mr. Gertz, Thank you for saving us time in reproducing your bug, which you found out is not a bug in our product. Thanks again.
