error calling CreateSQL: sql: expected 4 destination arguments in Scan, not 2

[dabico]

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:

version: '3.9'
name: 'test'

services:

  database:
    image: mysql:8.3.0
    container_name: 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: examplePass123
      TZ: UTC
    healthcheck:
      test: mysqladmin ping -h localhost
      start_period: 10s
      interval: 10s
      timeout: 5s
      retries: 5
    labels:
      - "com.centurylinklabs.watchtower.scope=test"

  backup:
    image: databack/mysql-backup:1.0.0-rc2
    container_name: backup
    restart: "no"
    volumes:
      - backup:/home/appuser/backup
      - ./bkpdb/clean_pre.sh:/scripts.d/pre-backup/00_clean_flyway_history.sh
      - ./bkpdb/clean_post.sh:/scripts.d/post-backup/00_clean_old_dumps.sh
    environment:
      TZ: UTC
      DB_NAMES: test
      DB_PORT: 3306
      DB_USER: testadmin
      DB_PASS: examplePass123
      DB_SERVER: database
      DB_DUMP_BEGIN: "+0"
      DB_DUMP_TARGET: /home/appuser/backup
      DB_DUMP_DEBUG: true
      DB_DUMP_SAFECHARS: true
      MYSQLDUMP_OPTS: >-
        --quick
        --compact
        --events
        --triggers
        --routines
        --single-transaction
        --max-allowed-packet=1G
    command: dump
    depends_on:
      database:
        condition: service_healthy
    labels:
      - "com.centurylinklabs.watchtower.scope=test"
    networks:
      - default

  watchtower:
    image: containrrr/watchtower:latest
    container_name: watchtower
    restart: "no"
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
    environment:
      WATCHTOWER_SCOPE: 'test'
      WATCHTOWER_TIMEOUT: '60s'
      WATCHTOWER_SCHEDULE: '0 0 */6 * * *'
      WATCHTOWER_LOG_LEVEL: 'debug'
      WATCHTOWER_INCLUDE_STOPPED: true
    labels:
      - "com.centurylinklabs.watchtower.scope=test"
    networks:
      - default

volumes:
  data:
    name: test-data
  backup:
    name: test-data-backups

networks:
  default:
    name: test-network

Attempting to run leads to the following error:

2024-03-06T10:32:40.556074895Z time="2024-03-06T10:32:40Z" level=info msg="beginning dump 2024-03-06T10:32:40Z"
2024-03-06T10:32:40.572913107Z Error: error backing up: failed to dump database: failed to dump database test: template: mysqldumpTable:9:3: executing "mysqldumpTable" at <.CreateSQL>: error calling CreateSQL: sql: expected 4 destination arguments in Scan, not 2
2024-03-06T10:32:40.573461507Z Usage:
2024-03-06T10:32:40.573473804Z   mysql-backup dump [flags]
2024-03-06T10:32:40.573476548Z 
2024-03-06T10:32:40.573478448Z Aliases:
2024-03-06T10:32:40.573514761Z   dump, backup
2024-03-06T10:32:40.573519105Z 
2024-03-06T10:32:40.573522603Z Flags:
2024-03-06T10:32:40.573526412Z       --begin 2330                         What time to do the first dump. Must be in one of two formats: Absolute: HHMM, e.g. 2330 or `0415`; or Relative: +MM, i.e. how many minutes after starting the container, e.g. `+0` (immediate), `+10` (in 10 minutes), or `+90` in an hour and a half (default "+0")
2024-03-06T10:32:40.573530657Z       --compression gzip                   Compression to use. Supported are: gzip, `bzip2` (default "gzip")
2024-03-06T10:32:40.573534189Z       --cron string                        Set the dump schedule using standard [crontab syntax](https://en.wikipedia.org/wiki/Cron), a single line.
2024-03-06T10:32:40.573537704Z       --exclude strings                    databases to exclude from the dump.
2024-03-06T10:32:40.573540818Z       --filename-pattern string            Pattern to use for filename in target. See documentation. (default "db_backup_{{ .now }}.{{ .compression }}")
2024-03-06T10:32:40.573544200Z       --frequency int                      how often to run backups, in minutes (default 1440)
2024-03-06T10:32:40.573547439Z   -h, --help                               help for dump
2024-03-06T10:32:40.573550233Z       --include strings                    names of databases to dump; empty to do all
2024-03-06T10:32:40.573553426Z       --max-allowed-packet int             Maximum size of the buffer for client/server communication, similar to mysqldump's max_allowed_packet. 0 means to use the default size. (default 4194304)
2024-03-06T10:32:40.573557321Z       --no-database-name USE <database>;   Omit USE <database>; in the dump, so it can be restored easily to a different database.
2024-03-06T10:32:40.573560858Z       --once                               Override all other settings and run the dump once immediately and exit. Useful if you use an external scheduler (e.g. as part of an orchestration solution like Cattle or Docker Swarm or [kubernetes cron jobs](https://kubernetes.io/docs/concepts/workloads/controllers/cron-jobs/)) and don't want the container to do the scheduling internally.
2024-03-06T10:32:40.573564398Z       --post-backup-scripts .sh            Directory wherein any file ending in .sh will be run post-backup but pre-send to target.
2024-03-06T10:32:40.573569179Z       --pre-backup-scripts .sh             Directory wherein any file ending in .sh will be run pre-backup.
2024-03-06T10:32:40.573572489Z       --safechars :                        The dump filename usually includes the character : in the date, to comply with RFC3339. Some systems and shells don't like that character. If true, will replace all `:` with `-`.
2024-03-06T10:32:40.573586853Z       --target strings                     full URL target to where the backups should be saved. Should be a directory. Accepts multiple targets. Supports three formats:
2024-03-06T10:32:40.573591757Z                                            Local: If if starts with a "/" character of "file:///", will dump to a local path, which should be volume-mounted.
2024-03-06T10:32:40.573595631Z                                            SMB: If it is a URL of the format smb://hostname/share/path/ then it will connect via SMB.
2024-03-06T10:32:40.573598840Z                                            S3: If it is a URL of the format s3://bucketname/path then it will connect via S3 protocol.
2024-03-06T10:32:40.573602493Z 
2024-03-06T10:32:40.573605409Z Global Flags:
2024-03-06T10:32:40.573608416Z       --aws-access-key-id string       Access Key for s3 and s3 interoperable systems; ignored if not using s3.
2024-03-06T10:32:40.573611740Z       --aws-endpoint-url string        Specify an alternative endpoint for s3 interoperable systems e.g. Digitalocean; ignored if not using s3.
2024-03-06T10:32:40.573615233Z       --aws-region string              Region for s3 and s3 interoperable systems; ignored if not using s3.
2024-03-06T10:32:40.573618488Z       --aws-secret-access-key string   Secret Access Key for s3 and s3 interoperable systems; ignored if not using s3.
2024-03-06T10:32:40.573621780Z       --config-file string             config file to use, if any; individual CLI flags override config file
2024-03-06T10:32:40.573625010Z       --pass string                    password for database server
2024-03-06T10:32:40.573628111Z       --port int                       port for database server (default 3306)
2024-03-06T10:32:40.573631243Z       --server string                  hostname for database server
2024-03-06T10:32:40.573634257Z       --smb-domain string              SMB domain
2024-03-06T10:32:40.573637117Z       --smb-pass string                SMB username
2024-03-06T10:32:40.573640114Z       --smb-user string                SMB username
2024-03-06T10:32:40.573642998Z       --tmp string                     temporary directory base for working directory, defaults to OS (default "/tmp")
2024-03-06T10:32:40.573646319Z       --user string                    username for database server
2024-03-06T10:32:40.573650116Z   -v, --verbose int                    set log level, 1 is debug, 2 is trace
2024-03-06T10:32:40.573653229Z 
2024-03-06T10:32:40.573656096Z time="2024-03-06T10:32:40Z" level=fatal msg="error backing up: failed to dump database: failed to dump database gse: template: mysqldumpTable:9:3: executing \"mysqldumpTable\" at <.CreateSQL>: error calling CreateSQL: sql: expected 4 destination arguments in Scan, not 2"

I should note that since this is an existing application, the database does already come with some data.

[deitch]

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]

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]

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]

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]

failed to dump database ePMS: template: mysqldumpTable:9:3: executing "mysqldumpTable" at <.CreateSQL>: error calling CreateSQL: sql: expected 4 destination arguments in Scan, not 2

mysql> 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]

@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]

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]

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]

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
}

[deitch]

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]

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)

[deitch]

With #301, I believe this is resolved.