dump data failed with syntax error when the entire table is generated columns

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do? If possible, provide a recipe for reproducing the error.

dumpling -o . -P 4000 -u root -h host

2. What did you expect to see?
   dump data successfully.
3. What did you see instead?

[2020/08/27 14:42:01.739 +08:00] [ERROR] [main.go:212] ["dump failed error stack info"] [error="SELECT  FROM `0247__TICASE_1512_WWSVHA`.`t` LIMIT 1: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 12 near \"FROM `0247__TICASE_1512_WWSVHA`.`t` LIMIT 1\" "] [errorVerbose="err = SELECT  FROM `0247__TICASE_1512_WWSVHA`.`t` LIMIT 1: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 12 near \"FROM `0247__TICASE_1512_WWSVHA`.`t` LIMIT 1\" \ngoroutine 1 [running]:\nruntime/debug.Stack(0x19aec00, 0xc000272f40, 0x19afec0)\n\t/usr/local/go/src/runtime/debug/stack.go:24 +0x9f\ngithub.com/pingcap/dumpling/v4/export.withStack(0x19aec00, 0xc000272f40, 0xc00018a000, 0xc0001a2c00)\n\t/Users/aytrack/gitproject/pingcap/dumpling/v4/export/error.go:59 +0x8f\ngithub.com/pingcap/dumpling/v4/export.GetColumnTypes(0xc000304180, 0x0, 0x0, 0xc00023ffe0, 0x18, 0x1dd1720, 0x1, 0x0, 0x0, 0x0, ...)\n\t/Users/aytrack/gitproject/pingcap/dumpling/v4/export/sql.go:263 +0x325\ngithub.com/pingcap/dumpling/v4/export.SelectAllFromTable(0xc00029e000, 0xc000304180, 0xc00023ffe0, 0x18, 0x1dd1720, 0x1, 0x1, 0xc00001a000, 0xcf, 0x0)\n\t/Users/aytrack/gitproject/pingcap/dumpling/v4/export/sql.go:141 +0x105\ngithub.com/pingcap/dumpling/v4/export.dumpTable(0x19c4cc0, 0xc0001a8cc0, 0xc00029e000, 0xc000304180, 0xc00023ffe0, 0x18, 0xc0003a1e00, 0x19c32c0, 0xc00029e000, 0xc0003f5350, ...)\n\t/Users/aytrack/gitproject/pingcap/dumpling/v4/export/dump.go:321 +0xc5\ngithub.com/pingcap/dumpling/v4/export.dumpDatabases(0x19c4cc0, 0xc0001a8cc0, 0xc00029e000, 0xc00031e0e0, 0x19c32c0, 0xc00029e000, 0x0, 0x0)\n\t/Users/aytrack/gitproject/pingcap/dumpling/v4/export/dump.go:232 +0x353\ngithub.com/pingcap/dumpling/v4/export.Dump(0x19c4d00, 0xc00018a000, 0xc00029e000, 0x0, 0x0)\n\t/Users/aytrack/gitproject/pingcap/dumpling/v4/export/dump.go:199 +0xc25\nmain.main()\n\t/Users/aytrack/gitproject/pingcap/dumpling/cmd/dumpling/main.go:210 +0x152a\n"] [stack="github.com/pingcap/dumpling/v4/log.Error\n\t/Users/aytrack/gitproject/pingcap/dumpling/v4/log/log.go:78\nmain.main\n\t/Users/aytrack/gitproject/pingcap/dumpling/cmd/dumpling/main.go:212\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:204"]

dump failed: SELECT  FROM `0247__TICASE_1512_WWSVHA`.`t` LIMIT 1: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 12 near "FROM `0247__TICASE_1512_WWSVHA`.`t` LIMIT 1"   

4. Versions of the cluster

   • Dumpling version (run dumpling -V):

     Release version: v4.0.3-12-g8e00b82
     Git commit hash: 8e00b826f68dda6734d69d7d5c96d082b0e88ee6
     Git branch:      master
     Build timestamp: 2020-08-27 06:25:31Z
     Go version:      go version go1.15 darwin/amd64
     

   • Source database version (execute SELECT version(); in a MySQL client):

     +-------------------------------------------------------------------+
     | tidb_version()                                                    |
     +-------------------------------------------------------------------+
     | Release Version: v4.0.0-beta.2-1006-g6831e487a                    |
     | Edition: Community                                                |
     | Git Commit Hash: 6831e487a84c8bde625de8c73feec9e6ec02f426         |
     | Git Branch: master                                                |
     | UTC Build Time: 2020-08-23 13:37:28                               |
     | GoVersion: go1.13                                                 |
     | Race Enabled: false                                               |
     | TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 |
     | Check Table Before Drop: false                                    |
     +-------------------------------------------------------------------+
     

   • Other interesting information (system version, hardware config, etc):

5. Operation logs

   • Please upload dumpling.log for Dumpling if possible
   • Other interesting logs

6. Configuration of the cluster and the task

   • running command for Dumpling if possible

[lichunzhu]

Could you please run show create table 0247__TICASE_1512_WWSVHA.t and list the result in this issue?

[kennytm]

the SQL is

SELECT  FROM `0247__TICASE_1512_WWSVHA`.`t` LIMIT 1

maybe all columns are generated 🤔

create table a (a int as (1));
insert into a values (), (), ();

---

dumping this with mydumper fails too.

** Message: 17:20:46.137: Server version reported as: 8.0.20
** Message: 17:20:46.162: Connected to a MySQL server
** Message: 17:20:46.199: Started dump at: 2020-08-27 17:20:46

** Message: 17:20:46.203: Written master status
** Message: 17:20:46.215: Thread 1 connected using MySQL connection ID 13
** Message: 17:20:46.225: Thread 2 connected using MySQL connection ID 14
** Message: 17:20:46.231: Thread 3 connected using MySQL connection ID 15
** Message: 17:20:46.236: Thread 4 connected using MySQL connection ID 16
** Message: 17:20:46.286: Non-InnoDB dump complete, unlocking tables
** Message: 17:20:46.286: Thread 1 dumping data for `test`.`a`
** Message: 17:20:46.286: Thread 2 dumping schema for `test`.`a`
** Message: 17:20:46.286: Thread 4 shutting down
** Message: 17:20:46.286: Thread 3 shutting down
** Message: 17:20:46.294: Thread 2 shutting down

** (mydumper:21857): CRITICAL **: 17:20:46.314: Error dumping table (test.a) data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `test`.`a`' at line 1
** Message: 17:20:46.314: Empty table test.a
** Message: 17:20:46.315: Thread 1 shutting down
** Message: 17:20:46.317: Finished dump at: 2020-08-27 17:20:46

Meanwhile mysqldump generates this funny result

-- MySQL dump 10.13  Distrib 8.0.20, for Win64 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       8.0.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `a`
--

DROP TABLE IF EXISTS `a`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `a` (
  `a` int GENERATED ALWAYS AS (1) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `a`
--

LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` () VALUES ),)),)));
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-08-27 17:28:43

[kennytm]

leaving this up for contributors for now.

suggestion: use TDD. build dumpling and then test against the example table above, fixing any SQL error you encountered, until the correct output is produced.

basically you need to handle the case where fields is an empty string, starting from

dumpling/v4/export/sql.go

Lines 259 to 267 in 42ac4de

	func GetColumnTypes(db *sql.Conn, fields, database, table string) ([]*sql.ColumnType, error) {
	query := fmt.Sprintf("SELECT %s FROM `%s`.`%s` LIMIT 1", fields, escapeString(database), escapeString(table))
	rows, err := db.QueryContext(context.Background(), query)
	if err != nil {
	return nil, withStack(errors.WithMessage(err, query))
	}
	defer rows.Close()
	return rows.ColumnTypes()
	}

[Abingcbc]

@kennytm I would like to take this one. I have finished the work and I have tested the example above. It works well.

Release version: v4.0.7-5-g1004016-dev
Git commit hash: 1004016d3d49ffaeb904920de697c883b71278dc
Git branch:      master
Build timestamp: 2020-10-18 07:02:33Z
Go version:      go version go1.15.2 darwin/amd64

[2020/10/18 15:02:39.739 +08:00] [INFO] [config.go:178] ["detect server type"] [type=TiDB]
[2020/10/18 15:02:39.739 +08:00] [INFO] [config.go:196] ["detect server version"] [version=4.0.0-beta.2-1178-g449587a08-dirty]
[2020/10/18 15:02:39.739 +08:00] [WARN] [dump.go:105] ["If the amount of data to dump is large, criteria: (data more than 60GB or dumped time more than 10 minutes)\nyou'd better adjust the tikv_gc_life_time to avoid export failure due to TiDB GC during the dump process.\nBefore dumping: run sql `update mysql.tidb set VARIABLE_VALUE = '720h' where VARIABLE_NAME = 'tikv_gc_life_time';` in tidb.\nAfter dumping: run sql `update mysql.tidb set VARIABLE_VALUE = '10m' where VARIABLE_NAME = 'tikv_gc_life_time';` in tidb.\n"]
[2020/10/18 15:02:39.748 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=METRICS_SCHEMA]
[2020/10/18 15:02:39.749 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=PERFORMANCE_SCHEMA]
[2020/10/18 15:02:39.749 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=mysql]
[2020/10/18 15:02:39.749 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=INFORMATION_SCHEMA]
[2020/10/18 15:02:39.763 +08:00] [INFO] [dump.go:249] ["Skip to dump data of table tidb_test.a, because all of its columns are generated"]
[2020/10/18 15:02:39.767 +08:00] [INFO] [main.go:229] ["dump data successfully, dumpling will exit now"]

But when I tried to add a unit test case, it seems that there is something wrong with the mock db.

SELECT COLUMN_NAME,EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? ORDER BY ORDINAL_POSITION;: all expectations were already fulfilled, call to Query 'SELECT COLUMN_NAME,EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? ORDER BY ORDINAL_POSITION;' with args [{Name: Ordinal:1 Value:test} {Name: Ordinal:2 Value:a}] was not expected ("SELECT COLUMN_NAME,EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? ORDER BY ORDINAL_POSITION;: all expectations were already fulfilled, call to Query 'SELECT COLUMN_NAME,EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? ORDER BY ORDINAL_POSITION;' with args [{Name: Ordinal:1 Value:test} {Name: Ordinal:2 Value:a}] was not expected")

This function causes the error.

dumpling/v4/export/sql.go

Line 523 in 1004016

rows, err := db.QueryContext(context.Background(), query, dbName, tableName)

Is the reason because data-dog's sqlmock doesn't support table with all generated columns? Should I raise a new issue or just skip the unit test and raise my PR?

Thanks!

[lance6716]

maybe you could raise your current PR to give more information 😜

[Abingcbc]

maybe you could raise your current PR to give more information 😜

Thanks for your reply! I have raised my PR. I have committed my unit test code, but it cannot pass. After figuring out the reason of error above, I will modify the code.