Renaming a table inside a transaction and using it doesn't work

[dveeden]

This happens only if that multi-statement query is inside a transaction (which is how diesel runs migrations).

Here is the minimal reproduction:

mariadb --host=127.0.0.1 --port=4000 --user=root --database test --delimiter ';;;' --execute "$(cat << EOF
-- First migration creates the table foo
BEGIN;
;;;
CREATE TABLE foo(pk CHAR(1) PRIMARY KEY);
;;;
COMMIT;
-- Second migration renames foo, creates a new foo, 
-- and selects into the new foo from the old foo
;;;
BEGIN;
;;;
ALTER TABLE foo RENAME TO bar; 
CREATE TABLE foo(pk CHAR(1) PRIMARY KEY);
INSERT INTO foo (pk) SELECT pk FROM bar;
;;;
COMMIT;
;;;
EOF
)"

Tested against a new instance run by tiup:

*************************** 1. row ***************************
tidb_version(): Release Version: v6.4.0
Edition: Community
Git Commit Hash: cf36a9ce2fe1039db3cf3444d51930b887df18a1
Git Branch: heads/refs/tags/v6.4.0
UTC Build Time: 2022-11-13 05:25:30
GoVersion: go1.19.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

Originally posted by @Pythoner6 in #24194 (comment)

[dveeden]

$ mysql --host=127.0.0.1 --port=4000 --user=root --database test --delimiter ';;;' --execute "$(cat << EOF
> -- First migration creates the table foo
BEGIN;
;;;
CREATE TABLE foo(pk CHAR(1) PRIMARY KEY);
;;;
COMMIT;
-- Second migration renames foo, creates a new foo, 
-- and selects into the new foo from the old foo
;;;
BEGIN;
;;;
ALTER TABLE foo RENAME TO bar; 
CREATE TABLE foo(pk CHAR(1) PRIMARY KEY);
INSERT INTO foo (pk) SELECT pk FROM bar;
;;;
COMMIT;
;;;
EOF
)"
ERROR 1146 (42S02) at line 12: Table 'test.bar' doesn't exist

And from the logs:

[2022/12/06 10:35:18.992 +02:00] [INFO] [conn.go:1152] ["command dispatched failed"] [conn=9120160880355443095] [connInfo="id:9120160880355443095, addr:127.0.0.1:36118 status:11, collation:utf8mb4_0900_ai_ci, user:root"] [command=Query] [status="inTxn:1, autocommit:1"] [sql="ALTER TABLE foo RENAME TO bar; \nCREATE TABLE foo(pk CHAR(1) PRIMARY KEY);\nINSERT INTO foo (pk) SELECT pk FROM bar;"] [txn_mode=PESSIMISTIC] [timestamp=437863243827904521] [err="[schema:1146]Table 'test.bar' doesn't exist"]

[tiancaiamao]

Between "begin ... commit", any ddl statement would commit the current transaction automatically.
In TiDB we're following MySQL's behaviour here https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

[dveeden]

Between "begin ... commit", any ddl statement would commit the current transaction automatically. In TiDB we're following MySQL's behaviour here https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

Yes I'm fully aware of that. But with that I still don't expect the "Table 'test.bar' doesn't exist" message.

The way this ORM does this isn't great for MySQL (compatible) databases, but it should still work.

[tiancaiamao]

Oh, I see.
This should be a multiple-statement related bug.
multiple-statement is not battle-tested in tidb, I'll try to reproduce and fix it. @dveeden

[seiya-annie]

/found community