-
Notifications
You must be signed in to change notification settings - Fork 178
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
error calling CreateSQL: sql: expected 4 destination arguments in Scan, not 2 #275
Comments
The issue appears to be here, specifically that What is strange is that it is not caught in the integration tests, which do use tables with data in them (and shouldn't matter anyways). It could be a |
It definitely is not an 8.2.0 vs 8.3.0 issue. I didn't really expect it to be, but it was worth validating. With either of them: docker run -d --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -e MYSQL_DATABASE=test -e MYSQL_USER=testadmin -e MYSQL_PASS
WORD=examplePass123 mysql:8.3.0
7b32ecb8ac08e2123a3978ae6df4a846678927c7c263f2293c031a7f9098f419
ubuntu@ip-172-31-15-153:~/go/src/github.com/databacker/mysql-backup$ docker exec -it mysql bash
bash-4.4# mysql -u testadmin -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.3.0 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| test |
+--------------------+
3 rows in set (0.01 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table t1 (i1 int, i2 int);
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.01 sec)
mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`i1` int DEFAULT NULL,
`i2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec) You can see that Can you run |
I'm not sure if this is something related to the specific database I'm trying to dump. Here's an output sample:
Could it be that I have something misconfigured in the compose sample? How up-to-date is the README documentation w.r.t. |
Those all have 2 return columns, so unlikely to be what is triggering it. Unfortunately, the logging doesn't catch which table it is failing on. If your |
I'm getting the same error, which seems to be related to some MySQL versions? Because my dump in MySQL 8.0.33 works fine, but this error log appears in MySQL 5.6.44 |
@Caelebs thanks for the detailed error report (it always helps).
The error you got (same as @dabico) was on mysql 5.6.44? What about the is there any chance you have a scrubbed version of the database I can download? I have been struggling to recreate this issue, and would really like to. |
Sorry for the delayed response. I have a database running on MySQL 5.6.44. The error log mentioned above occurred when I was executing the dump command. Additionally, I have tried to backup several databases on MySQL 8.0 and above versions, and all of them completed normally. I can further attempt to see if the same error occurs on the MySQL 5.7 version. The content of the following output is the response executed on MySQL 8.0.36.
This database can be successfully backed up when executing the dump command. The following content is the response from MySQL version 5.7.37.
I have tried it in MySQL version 5.7.37, and it can be successfully backed up as well. I'm so embarrassed, I just realized that it seems to be related to my database tables. Because I tried the dump command on MySQL 5.6.44 version and it can also backup data successful. I need to check my table structure to find out which table returns 4 columns. |
Yes, please do. Once we find an example we can narrow in upon, we can then recreate it, figure out how to handle that edge case, and add tests.
Not at all! All part of the small and big things we all do every day. Smile and keep going. |
Chào những người anh em / Hello guys, It was the issue with mysql view which returns four columns: I've changed the dump function a bit to avoid that error. Hope it helps you out. func (table *table) CreateSQL() (string, error) {
fmt.Printf("Attempting to retrieve CREATE statement for object: %s\n", table.Name)
var objectName, objectSQL, dummy1, dummy2 sql.NullString
var objectType string
var query string
if err := table.data.tx.QueryRow("SHOW CREATE TABLE " + table.NameEsc()).Scan(&objectName, &objectSQL); err != nil {
query = "SHOW CREATE VIEW " + table.NameEsc()
if err := table.data.tx.QueryRow(query).Scan(&objectName, &objectSQL, &dummy1, &dummy2); err != nil {
fmt.Printf("Error retrieving CREATE statement for object %s: %v\n", table.Name, err)
return "", err
}
objectType = "VIEW"
} else {
query = "SHOW CREATE TABLE " + table.NameEsc()
objectType = "TABLE"
}
if objectName.String != table.Name {
return "", errors.New("Returned object is not the same as requested object")
}
fmt.Printf("Successfully retrieved CREATE %s statement for %s\n", objectType, table.Name)
return objectSQL.String, nil
} |
Hi @LeDoTruongAn ; thank you for jumping in to help. CreateSQL is called from the tableTmpl, which is executed for a table, which is called in dumpTable(), which is called in a for loop on all tables. That list of tables is populated from getTables(), which simply calls SHOW TABLES: rows, err := data.tx.Query("SHOW TABLES") Your point, I believe, is that I just ran a quick test, created a database with a single table mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| view1 |
+----------------+
2 rows in set (0.00 sec) As you correctly point out, it returns tables and views. mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`d` date DEFAULT NULL,
`t` time DEFAULT NULL,
`dt` datetime DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) As expected, mysql> show create table view1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view1 | CREATE ALGORITHM=UNDEFINED DEFINER=`testadmin`@`%` SQL SECURITY DEFINER VIEW `view1` AS select `t1`.`id` AS `id`,`t1`.`name` AS `name` from `t1` | latin1 | latin1_swedish_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
First, and most important, thank you for the very helpful sleuthing work. 😄 Second, @Caelebs and @dabico can you check if your use case is being tripped up by views? Of course, I see no reason not to fix this anyways, so let's do that. There is a slightly cleaner way to do it. |
I completely forgot that my schema has some views 🤦 . But I can indeed confirm that this is the most likely source of the issue:
|
With #301, I believe this is resolved. |
I'm trying to integrate this project as a docker service into one of my existing projects. Here's the configuration residing in the docker-compose file:
Attempting to run leads to the following error:
I should note that since this is an existing application, the database does already come with some data.
The text was updated successfully, but these errors were encountered: