mysqldump with generated column doesn't work while importing #105
Comments
|
@karthikeayan can you run the dump manually off the database and see? Also, if you run the Also, can you paste the relevant part of the dump output, based on the above, line 82 and its surrounding areas? Again, masking any sensitive info. |
|
I am not sure if this is fixable as this seems an mismatch between mysql and mariadb json functions. Your dockerfile uses alpine and alpine by default install mariadb client not mysql client. I also tried changing the base image to ubuntu in your Dockerfile to take dump. It works with 30MB database but when I use my image on 30GB database, mysqldump was very very slow and after creating 5-6GB of sql file the container got killed. |
And so it appears. I would be tempted to ask the MariaDB team to fix it (they are pretty good about it), but this just may be a divergence. We may need to have separate mysql and mariadb images then.
If you can replicate it without using your own sensitive data, can you open a new issue and put in exact steps to replicate? |
I am curious as to why this hasn't been an issue until now. Do only certain scenarios trigger it? If you look at your dump file, what is it that triggered the problem? |
|
Important reference point: https://elephantdolphin.blogspot.com/2018/11/a-tale-of-two-json-implementations.html |
|
I am just posting the first few characters from the line 82, as the complete line is too huge. https://gist.github.com/karthikeayan/2f4e18f204786c78ff2874becf6af260 |
|
Can you put it in a gist or somewhere we can see it? |
|
Created PR |
|
Hey, what's the status of this? I'm facing this issue when try to restore to the same db server I created backup from. I have the following:
Tnx |
|
There is a PR open, linked in this issue, needs to be rebased and cleaned. |
|
@deitch The only thing really needed is to replace Submitted a PR, could you take a quick look at it? |
|
Still facing with same error :( |
|
What happens with the go-based one? You can download the actual binary from GitHub releases page, or run the docker image with the |
|
Unfortunately, I'm still facing the same issue. I tested this out with version: '3.9'
services:
test-database:
image: mysql:8.2.0
container_name: test-database
restart: "always"
volumes:
- data:/var/lib/mysql
- ./mysql.cnf:/etc/mysql/conf.d/mysql.cnf
environment:
MYSQL_ALLOW_EMPTY_PASSWORD: yes
MYSQL_DATABASE: test
MYSQL_USER: testadmin
MYSQL_PASSWORD: L33Th4Xx0r
TZ: UTC
healthcheck:
test: mysqladmin ping -h localhost
start_period: 10s
interval: 10s
timeout: 5s
retries: 5
test-backup:
image: databack/mysql-backup:latest
container_name: test-backup
restart: "no"
volumes:
- backup:/backup
- ./bkpdb/pre:/scripts.d/pre-backup
- ./bkpdb/post:/scripts.d/post-backup
environment:
TZ: UTC
DB_NAMES: test
DB_PORT: 3306
DB_USER: testadmin
DB_PASS: L33Th4Xx0r
DB_SERVER: test-database
DB_DUMP_TARGET: /backup
DB_DUMP_DEBUG: true
DB_DUMP_SAFECHARS: true
DB_DUMP_BEGIN: "0"
RUN_ONCE: true
depends_on:
gse-database:
condition: service_healthy
volumes:
data:
name: test-data
backup:
name: test-data-backupsAssume that the -- MariaDB dump 10.19 Distrib 10.6.12-MariaDB, for Linux (x86_64)
--
-- Host: test-database Database: test
-- ------------------------------------------------------
-- Server version 8.2.0Performing dumps manually from the |
|
|
|
Okay got it, I thought |
|
Yeah, it only points to the most recent semver. Having it point to not-yet-released versions (beta or release candidates, etc.) would put out a version that is not ready. Like the current go-based one. Very close, but not there yet.
Yes. Took advantage of the major version release to clean up and make much more consistent CLI flags and env vars and names, and add a config file option. |
|
After specifying the command I get the following error: I only added a |
|
Yes and no. Yes, (some of) the env vars have been renamed. No, it should not crash no matter what. Well, that is why it is a "release candidate" and not "generally available". Any way I can recreate that? |
|
Here's a repository with an MRE: https://github.com/dabico/mysql-backup-issue-105 |
Did you just create the database, load it with the Hmm, there were some bugs fixed between rc1 and now, (rc2 not yet released), I wonder if this is one of them. Should be able to find out pretty quickly. |
Yeah, you just have to run Edit: Should this be another issue? |
I figured it out once I read how your compose was configured. I didn't use compose in the end. Started mysql in docker with same config, but ran mysql-backup via CLI. Can confirm it is an issue, not resolved by latest commit. Working on it.
I was going to say no, but I think this is best as a new issue. Thanks. |
Here is is #250 |
|
That fix was merged in. A new I will post here once it is ready. |
|
Ready. Try the tag |
|
I updated the image tag in the MRE. The previous issue has been resolved, but now I get an error saying: I find this odd, especially since |
Probably not. But once we get this all working, time to cut a new release candidate 😁 |
|
Do you mind opening a new issue for it? I see the problem, want to track it, but not sure going to get to it today. |
|
I updated the image tag in the MRE to Again, this is strange because the password is specified in the compose config. |
|
I had that at one point, turned out to be a copy-paste error in my case. I am running it on command-line, so I missed pasting part of it. Are you sure you have the right var for the password? I would check the README, but I think it is
Every you time you write that, my first reaction is, "Meals Ready to Eat"? (US Army combat rations). They are on my mind because there was a whole issue with them that made the business press a few years back. Funny what sticks in your mind. |
Yeah, my bad I was using the wrong environment variable. I'll give it another go. |
|
Well, the good news is that it executes without any errors! 🥳 Unfortunately, the dump still contains the generated column values in the insert statements. 😢 Here's the output from the reproduction repository: -- Go SQL Dump 0.6.0
--
-- Host: test-database Database: test
-- ------------------------------------------------------
-- Server version 8.2.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 */;
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `test`;
--
-- Table structure for table `ignore`
--
DROP TABLE IF EXISTS `ignore`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ignore` (
`id` int NOT NULL AUTO_INCREMENT,
`value` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ignore`
--
LOCK TABLES `ignore` WRITE;
/*!40000 ALTER TABLE `ignore` DISABLE KEYS */;
INSERT INTO `ignore` VALUES (1,10),(2,11),(3,12),(4,13),(5,14),(6,15),(7,16),(8,17),(9,18),(10,19);
/*!40000 ALTER TABLE `ignore` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`value` int NOT NULL,
`generated` int GENERATED ALWAYS AS ((`value` + 1)) STORED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES (1,0,1),(2,1,2),(3,2,3),(4,3,4),(5,4,5),(6,5,6),(7,6,7),(8,7,8),(9,8,9),(10,9,10);
/*!40000 ALTER TABLE `test` 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 2024-01-07 15:09:19I'm not familiar with what the Go driver for MySQL uses under the hood, but to me, it seems that only the |
|
No way "bad news", this is very good news. We have something we can work with now. |
|
Now that we recreated it with the latest commits, can we review how to recreate this? Does the replication in the repo your provided do this? What are the steps to recreate it now? |
Yes, same repository, just run Btw, forgot to mention that I had to add to the |
|
What would the "control group" be, then? Run |
That's how I tested it. I ran: -- MySQL dump 10.13 Distrib 8.2.0, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 8.2.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 `test`
--
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`value` int NOT NULL,
`generated` int GENERATED ALWAYS AS ((`value` + 1)) STORED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` (`id`, `value`) VALUES (1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),(8,7),(9,8),(10,9);
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `test_before_insert` BEFORE INSERT ON `test` FOR EACH ROW BEGIN END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `test_after_insert` AFTER INSERT ON `test` FOR EACH ROW BEGIN END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Dumping events for database 'test'
--
/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;
/*!50106 DROP EVENT IF EXISTS `event_stub` */;
DELIMITER ;;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client = latin1 */ ;;
/*!50003 SET character_set_results = latin1 */ ;;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone = @@time_zone */ ;;
/*!50003 SET time_zone = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `event_stub` ON SCHEDULE EVERY 1 MINUTE STARTS '2024-01-07 15:26:12' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN END */ ;;
/*!50003 SET time_zone = @saved_time_zone */ ;;
/*!50003 SET sql_mode = @saved_sql_mode */ ;;
/*!50003 SET character_set_client = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection = @saved_col_connection */ ;;
DELIMITER ;
/*!50106 SET TIME_ZONE= @save_time_zone */ ;
--
-- Dumping routines for database 'test'
--
/*!50003 DROP PROCEDURE IF EXISTS `procedure_stub` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_stub`()
BEGIN END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 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 2024-01-07 18:03:35 |
|
That helps. We have a good comparison here. Going to work with it. |
|
With all of that out of the way, and some additional fixes, we can get to the core issue here. You should not really restore (or dump) a generated column. It is calculated, not stored. We just need to filter those columns out. |
I am getting below error when importing mysqldump taken from this docker image.
ERROR 3105 (HY000) at line 82: The value specified for generated column 'column_name' in table 'table_name' is not allowed.Source and destination mysql server having same version.
MySQL server version:
mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))MySQL Dump client version:
mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64)The text was updated successfully, but these errors were encountered: