Closed dabico closed 5 months ago
deitch
commented
6 months ago The issue appears to be here, specifically that SHOW CREATE TABLE expects to return 4 values, not 2.
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 mysql:8.3.0 (which you are using) vs 8.2.0 (which the integration tests are using) issue, but unlikely. Will keep digging to see if we can recreate it and patch it.
deitch
commented
6 months ago 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 SHOW CREATE TABLE only returns 2 columns.
Can you run SHOW CREATE TABLE on yours and see what you get? Something is different. I not only want to fix it for you, but want to understand it so that this can do the right thing and handle scenarios.
dabico
commented
6 months ago I'm not sure if this is something related to the specific database I'm trying to dump. Here's an output sample:
bash-4.4# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 130
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 |
+--------------------+
| gse |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE gse;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
+-------------------------------------+
| Tables_in_gse |
+-------------------------------------+
| count_git_repo_by_language |
| count_git_repo_by_language_analyzed |
| count_git_repo_by_language_mined |
| flyway_schema_history |
| git_repo |
| git_repo_label |
| git_repo_language |
| git_repo_metric |
| git_repo_metric_aggregate |
| git_repo_topic |
| hibernate_sequence |
| label |
| label_ranked |
| language |
| language_progress |
| language_statistics |
| license |
| topic |
| topic_ranked |
+-------------------------------------+
19 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE git_repo;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| git_repo | CREATE TABLE `git_repo` (
`id` bigint NOT NULL,
`language_id` bigint NOT NULL,
`license_id` bigint DEFAULT NULL,
`name` varchar(140) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_fork_project` bit(1) DEFAULT NULL,
`commits` bigint DEFAULT NULL,
`branches` bigint DEFAULT NULL,
`default_branch` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`releases` bigint DEFAULT NULL,
`contributors` bigint DEFAULT NULL,
`watchers` bigint DEFAULT NULL,
`stargazers` bigint DEFAULT NULL,
`forks` bigint DEFAULT NULL,
`size` bigint DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`pushed_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`homepage` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`total_issues` bigint DEFAULT NULL,
`open_issues` bigint DEFAULT NULL,
`total_pull_requests` bigint DEFAULT NULL,
`open_pull_requests` bigint DEFAULT NULL,
`last_commit` datetime DEFAULT NULL,
`last_commit_sha` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
`has_wiki` bit(1) DEFAULT NULL,
`archived` bit(1) DEFAULT NULL,
`locked` bit(1) DEFAULT NULL,
`disabled` bit(1) DEFAULT NULL,
`last_pinged` timestamp NULL DEFAULT NULL,
`last_analyzed` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_repo_name` (`name`),
KEY `numeric_column_idx` (`name`,`commits`,`contributors`,`total_issues`,`open_issues`,`total_pull_requests`,`open_pull_requests`,`branches`,`releases`,`stargazers`,`watchers`,`forks`,`created_at`,`pushed_at`),
KEY `bit_column_index` (`has_wiki`,`is_fork_project`),
KEY `language_id` (`language_id`),
KEY `license_id` (`license_id`),
CONSTRAINT `git_repo_ibfk_1` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`),
CONSTRAINT `git_repo_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `license` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE git_repo_language;
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| git_repo_language | CREATE TABLE `git_repo_language` (
`repo_id` bigint NOT NULL,
`language_id` bigint NOT NULL,
`size_of_code` bigint NOT NULL,
PRIMARY KEY (`repo_id`,`language_id`),
KEY `language_id` (`language_id`),
CONSTRAINT `git_repo_language_ibfk_1` FOREIGN KEY (`repo_id`) REFERENCES `git_repo` (`id`),
CONSTRAINT `git_repo_language_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE language;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| language | CREATE TABLE `language` (
`id` bigint NOT NULL,
`name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_language_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)Could it be that I have something misconfigured in the compose sample? How up-to-date is the README documentation w.r.t. 1.0.0-rc2? I'll check the other tables too and report if I find anything unusual.
deitch
commented
6 months ago 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 SHOW CREATE TABLE on all of them doesn't find one with 4 columns, we can update it to report it.
Caelebs
commented
5 months ago failed to dump database ePMS: template: mysqldumpTable:9:3: executing "mysqldumpTable" at <.CreateSQL>: error calling CreateSQL: sql: expected 4 destination arguments in Scan, not 2mysql> SHOW CREATE TABLE ACT_EVT_LOG;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ACT_EVT_LOG | CREATE TABLE `ACT_EVT_LOG` (
`LOG_NR_` bigint(20) NOT NULL AUTO_INCREMENT,
`TYPE_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`PROC_DEF_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`PROC_INST_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`EXECUTION_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`TASK_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`TIME_STAMP_` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
`USER_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`DATA_` longblob,
`LOCK_OWNER_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`LOCK_TIME_` timestamp(3) NULL DEFAULT NULL,
`IS_PROCESSED_` tinyint(4) DEFAULT '0',
PRIMARY KEY (`LOG_NR_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |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
deitch
commented
5 months ago @Caelebs thanks for the detailed error report (it always helps).
this error log appears in MySQL 5.6.44
The error you got (same as @dabico) was on mysql 5.6.44? What about the SHOW CREATE line with the 2 columns of output? Is that also from the same 5.6.44 database which gave the error?
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.
Caelebs
commented
5 months ago 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.
[root@localhost bin]# ./mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29282
Server version: 8.0.36 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 |
+--------------------+
| alarm |
| epms |
| information_schema |
| mysql |
| performance_schema |
| product |
| sys |
+--------------------+
7 rows in set (0.01 sec)
mysql> use epms
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
+-----------------------------------+
| Tables_in_epms |
+-----------------------------------+
| act_evt_log |
| act_ge_bytearray |
| act_ge_property |
| act_hi_actinst |
| act_hi_attachment |
| act_hi_comment |
| act_hi_detail |
| act_hi_identitylink |
| act_hi_procinst |
| act_hi_taskinst |
| act_hi_varinst |
| act_id_group |
| act_id_info |
| act_id_membership |
| act_id_user |
| act_re_deployment |
| act_re_model |
| act_re_procdef |
| act_ru_event_subscr |
| act_ru_execution |
| act_ru_identitylink |
| act_ru_job |
| act_ru_task |
| act_ru_variable |
| activemq_acks |
| activemq_lock |
| activemq_msgs |
...
+-----------------------------------+
94 rows in set (0.01 sec)
mysql> SHOW CREATE TABLE act_evt_log;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| act_evt_log | CREATE TABLE `act_evt_log` (
`LOG_NR_` bigint NOT NULL AUTO_INCREMENT,
`TYPE_` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
`PROC_DEF_ID_` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
`PROC_INST_ID_` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
`EXECUTION_ID_` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
`TASK_ID_` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
`TIME_STAMP_` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
`USER_ID_` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
`DATA_` longblob,
`LOCK_OWNER_` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
`LOCK_TIME_` timestamp(3) NULL DEFAULT NULL,
`IS_PROCESSED_` tinyint DEFAULT '0',
PRIMARY KEY (`LOG_NR_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)This database can be successfully backed up when executing the dump command.
The following content is the response from MySQL version 5.7.37.
root@eeacd921e131:/# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 119
Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> use epms
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------+
| Tables_in_epms |
+---------------------+
| ACTIVEMQ_ACKS |
| ACTIVEMQ_LOCK |
| ACTIVEMQ_MSGS |
| ACT_EVT_LOG |
| ACT_GE_BYTEARRAY |
| ACT_GE_PROPERTY |
| ACT_HI_ACTINST |
| ACT_HI_ATTACHMENT |
| ACT_HI_COMMENT |
| ACT_HI_DETAIL |
| ACT_HI_IDENTITYLINK |
| ACT_HI_PROCINST |
| ACT_HI_TASKINST |
| ACT_HI_VARINST |
| ACT_ID_GROUP |
| ACT_ID_INFO |
| ACT_ID_MEMBERSHIP |
| ACT_ID_USER |
| ACT_RE_DEPLOYMENT |
| ACT_RE_MODEL |
| ACT_RE_PROCDEF |
| ACT_RU_EVENT_SUBSCR |
| ACT_RU_EXECUTION |
| ACT_RU_IDENTITYLINK |
| ACT_RU_JOB |
| ACT_RU_TASK |
| ACT_RU_VARIABLE |
+---------------------+
27 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE ACTIVEMQ_ACKS;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ACTIVEMQ_ACKS | CREATE TABLE `ACTIVEMQ_ACKS` (
`CONTAINER` varchar(250) NOT NULL,
`SUB_DEST` varchar(250) DEFAULT NULL,
`CLIENT_ID` varchar(250) NOT NULL,
`SUB_NAME` varchar(250) NOT NULL,
`SELECTOR` varchar(250) DEFAULT NULL,
`LAST_ACKED_ID` bigint(20) DEFAULT NULL,
`PRIORITY` bigint(20) NOT NULL DEFAULT '5',
`XID` varchar(250) DEFAULT NULL,
PRIMARY KEY (`CONTAINER`,`CLIENT_ID`,`SUB_NAME`,`PRIORITY`),
KEY `ACTIVEMQ_ACKS_XIDX` (`XID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)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.
deitch
commented
5 months ago 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.
I'm so embarrassed
Not at all! All part of the small and big things we all do every day. Smile and keep going.
LeDoTruongAn
commented
5 months ago Chào những người anh em / Hello guys,
It was the issue with mysql view which returns four columns: View, Create View, character_set_client, and collation_connection.
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 SHOW TABLES not only returns tables (whose SHOW CREATE is 2 columns, and therefore work), but also views (whose SHOW CREATE is 4 columns, and therefore do not).
I just ran a quick test, created a database with a single table t1, and then a view view.
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, SHOW CREATE TABLE t1 returns 2 columns.
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)SHOW CREATE TABLE view1 returns four columns, hence the issue.
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.
dabico
commented
5 months ago Second, @Caelebs and @dabico can you check if your use case is being tripped up by views?
I completely forgot that my schema has some views 🤦 . But I can indeed confirm that this is the most likely source of the issue:
Welcome to the MySQL monitor. Commands end with ; or \g.
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> USE gse;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW FULL TABLES IN gse;
+-------------------------------------+------------+
| Tables_in_gse | Table_type |
+-------------------------------------+------------+
| count_git_repo_by_label | VIEW |
| count_git_repo_by_language | VIEW |
| count_git_repo_by_language_analyzed | VIEW |
| count_git_repo_by_language_mined | VIEW |
| count_git_repo_by_license | VIEW |
| count_git_repo_by_topic | VIEW |
| event_log | BASE TABLE |
| flyway_schema_history | BASE TABLE |
| git_repo | BASE TABLE |
| git_repo_label | BASE TABLE |
| git_repo_language | BASE TABLE |
| git_repo_metric | BASE TABLE |
| git_repo_metric_aggregate | BASE TABLE |
| git_repo_topic | BASE TABLE |
| hibernate_sequence | BASE TABLE |
| label | BASE TABLE |
| label_statistics | BASE TABLE |
| language | BASE TABLE |
| language_progress | BASE TABLE |
| language_statistics | BASE TABLE |
| license | BASE TABLE |
| license_statistics | BASE TABLE |
| topic | BASE TABLE |
| topic_statistics | BASE TABLE |
+-------------------------------------+------------+
24 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE count_git_repo_by_label;
+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| count_git_repo_by_label | CREATE ALGORITHM=UNDEFINED DEFINER=`gseadmin`@`%` SQL SECURITY DEFINER VIEW `count_git_repo_by_label` AS select `label`.`id` AS `label_id`,count(`git_repo_label`.`repo_id`) AS `count` from (`label` left join `git_repo_label` on((`git_repo_label`.`label_id` = `label`.`id`))) group by `label`.`id` | utf8mb4 | utf8mb4_unicode_ci |
+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)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.