Improve handling of `START TRANSACTION` with MySQL-like databases

pingcap / dumpling

Dumpling is a fast, easy-to-use tool written by Go for dumping data from the database(MySQL, TiDB...) to local/cloud(S3, GCP...) in multifarious formats(SQL, CSV...).

Apache License 2.0

280 stars 85 forks source link

Improve handling of `START TRANSACTION` with MySQL-like databases #309

Closed dveeden closed 2 years ago

dveeden commented 3 years ago

Bug Report

The SQL query from sql.go has a version specific comment to only execute on MySQL 4.1.8.

query = "START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */"

However with Vitess and MemSQL/SingleStore seems to have the same issue.

I would suggest to run a START TRANSACTION without the version-specific comment if the original statement fails.

Also with --consistency none this still fails.

SunRunAway commented 3 years ago

I suggest we should do a full test for memsql/singlestore.

dveeden commented 3 years ago

https://docs.singlestore.com/db/v7.3/en/reference/sql-reference/data-manipulation-language-dml/begin.html indeed doesn't show the WITH CONSISTENT SNAPSHOT.

dveeden commented 3 years ago

I suggest we should do a full test for memsql/singlestore.

Yes and maybe Vitess. But as Vitess has a set of MySQL servers as backend and dumping directly from those and combining the data should work. However that could complicate table names and might copy Vitess specific metadata that should have been excluded.