I have a Databases and like to backup and restore my backups. My Database is more complex, but I will give a short example what is not working.
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);
I can backup the Database:
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!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 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`bug` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `bug`;
/*Table structure for table `doc` */
DROP TABLE IF EXISTS `doc`;
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;
/*Data for the table `doc` */
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
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;
/*Data for the table `user` */
/*Table structure for table `user_doc` */
DROP TABLE IF EXISTS `user_doc`;
/*!50001 DROP VIEW IF EXISTS `user_doc` */;
/*!50001 DROP TABLE IF EXISTS `user_doc` */;
/*!50001 CREATE TABLE `user_doc`(
`xls` varchar(16000) ,
`name` varchar(16000)
)*/;
/*View structure for view user_doc */
/*!50001 DROP TABLE IF EXISTS `user_doc` */;
/*!50001 DROP VIEW IF EXISTS `user_doc` */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_doc` AS select `d`.`xls` AS `xls`,`u`.`name` AS `name` from (`user` `u` join `doc` `d` on((`d`.`id` = `u`.`id`))) */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
but I can't restore the database. I get these Error:
Error Code: 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MySQL is creating a table user_doc that does not exist in my Database and wants to delete the table right after creating. I think MySQL is doing it because views can depend on views and the order of creating the views can be different. But now I can't import my dumpfile.
/*!50001 CREATE TABLE `user_doc`(
`xls` varchar(16000) ,
`name` varchar(16000)
)*/
/*!50001 DROP TABLE IF EXISTS `user_doc` */;
How can I restore my Database without editing my dump?
Oh it is not a mysql bug. The dump was taken by an App called SQLYog. If I create the dump with mysql, then the table user_doc will not be created. If take the dump with SQLYog then the table user_doc will be created. So it is not a mysql bug, it is a bug of SQLYog (13.2.0).