Error in Temporary Table After Adding Transaction to Program

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 程序添加事务后临时表报错

| username: Kongdom

.NET program, using version 6.5.2 database, reports an error when using a temporary table after adding a transaction in the code. The same code executes normally when connected to MySQL 8.0. Executing the traced SQL in the query analyzer works fine.

TiDB logs:

[2024/03/29 11:20:19.803 +08:00] [INFO] [session.go:3764] [GENERAL_LOG] [conn=2937701555370986845] [user=root@192.168.135.1] [schemaVersion=10423] [txnStartTS=0] [forUpdateTS=0] [isReadConsistency=false] [currentDB=] [isPessimistic=false] [sessionTxnMode=PESSIMISTIC] [sql="use `tidb_test`"]

[2024/03/29 11:20:19.803 +08:00] [INFO] [session.go:3764] [GENERAL_LOG] [conn=2937701555370986845] [user=root@192.168.135.1] [schemaVersion=10423] [txnStartTS=0] [forUpdateTS=0] [isReadConsistency=false] [currentDB=tidb_test] [isPessimistic=false] [sessionTxnMode=PESSIMISTIC] [sql="SET NAMES utf8mb4;"]

[2024/03/29 11:20:19.803 +08:00] [INFO] [session.go:3764] [GENERAL_LOG] [conn=2937701555370986845] [user=root@192.168.135.1] [schemaVersion=10423] [txnStartTS=0] [forUpdateTS=0] [isReadConsistency=false] [currentDB=tidb_test] [isPessimistic=false] [sessionTxnMode=PESSIMISTIC] [sql="set session transaction isolation level repeatable read;"]

[2024/03/29 11:20:19.804 +08:00] [INFO] [session.go:3764] [GENERAL_LOG] [conn=2937701555370986845] [user=root@192.168.135.1] [schemaVersion=10423] [txnStartTS=0] [forUpdateTS=0] [isReadConsistency=false] [currentDB=tidb_test] [isPessimistic=false] [sessionTxnMode=PESSIMISTIC] [sql="start transaction;"]

[2024/03/29 11:20:23.043 +08:00] [INFO] [conn.go:1181] ["command dispatched failed"] [conn=2937701555370986845] [connInfo="id:2937701555370986845, addr:192.168.135.1:57074 status:11, collation:utf8mb4_general_ci, user:root"] [command=Query] [status="inTxn:1, autocommit:1"] [sql="
    CREATE TEMPORARY TABLE tmp_customer_base(c_no varchar(20), PRIMARY KEY (c_no));
    INSERT IGNORE INTO tmp_customer_base(c_no) values('11');
"] [txn_mode=PESSIMISTIC] [timestamp=448707276245827608] [err="[schema:1146]Table 'tidb_test.tmp_customer_base' doesn't exist"]

[2024/03/29 11:20:23.138 +08:00] [INFO] [session.go:3764] [GENERAL_LOG] [conn=2937701555370986845] [user=root@192.168.135.1] [schemaVersion=10423] [txnStartTS=448707276245827608] [forUpdateTS=448707276245827608] [isReadConsistency=false] [currentDB=tidb_test] [isPessimistic=true] [sessionTxnMode=PESSIMISTIC] [sql=rollback]

Combining the SQL from the logs and executing it works fine without errors.

USE `tidb_test`;
SET NAMES utf8mb4;
set session transaction isolation level repeatable read;

START TRANSACTION;

CREATE TEMPORARY TABLE tmp_customer_base(
    c_no varchar(20),
    PRIMARY KEY (c_no)
);

INSERT IGNORE INTO tmp_customer_base(c_no) values('11');
| username: vcdog | Original post link

  1. Check Transaction Syntax: Ensure that your transaction syntax is compatible with the v6.5.2 version of the database. Some database versions may have different syntax for statements like BEGIN TRANSACTION, COMMIT, and ROLLBACK.
  2. Update Driver: If the database driver you are using is not the latest version, try updating to the latest version, as newer versions of drivers often fix known compatibility issues.
| username: Kongdom | Original post link

The same SQL does not report an error when executed manually in the query analyzer.
The same SQL, when placed in the program, does not cause any issues if the program does not add a transaction, and adding a transaction within the SQL statement also does not cause any problems.

| username: TiDBer_jYQINSnf | Original post link

Once the DDL is executed, it is committed immediately and cannot be controlled by transactions. I am not sure if the table will still exist if you execute a begin; create table; rollback in MySQL 8.0.

| username: Kongdom | Original post link

This is a temporary table, not a physical table. Also, the table cannot be found after creation.

| username: TiDBer_jYQINSnf | Original post link

Then report a bug to TiDB, syntax incompatibility :grin:

| username: Kongdom | Original post link

:flushed: Should we review the question again? No errors when executed manually, but errors occur when executed by the program. :joy:

| username: TiDBer_jYQINSnf | Original post link

I carefully reviewed it again. When the program executes, your create and insert are sent together, but when you execute manually, they are done in two steps, right?
Try looking for the multiStatement setting. If you turn it off in the program, will it be the same as doing it manually?
Just a guess, looking forward to your final answer.

| username: Kongdom | Original post link

:flushed: That actually makes sense. I haven’t tracked TiDB logs using the other two methods. I’ll check it out this afternoon.

| username: Kongdom | Original post link

Indeed, the above section adds a transaction within the statement, and then the program executes it. The section below adds a transaction within the program. Correspondingly, there are two entries in the TiDB logs.

| username: TiDBer_jYQINSnf | Original post link

Then modify your program, remove that parameter, and send the SQL statements one by one. Can the SQL execute correctly? If it can execute correctly, then it is a bug in TiDB, as multistmt is not handled properly.

| username: tidb菜鸟一只 | Original post link

According to your manual execution record, didn’t your transaction end after the create table? DDL statements automatically commit transactions.

| username: Kongdom | Original post link

Huh? Let me verify~

| username: DBAER | Original post link

After BEGIN, executing DDL is considered as committing the transaction.

| username: TiDBer_jYQINSnf | Original post link

Please verify what I mentioned, set it in the connection string, and then check if the program sends one by one. Can TiDB handle it correctly?

| username: Kongdom | Original post link

Verified, no transaction was committed. After creating, I added an update to the entity table. After an error was reported, the value of the entity table did not change. If the DDL committed the transaction, the value of the entity table should have changed.

| username: Kongdom | Original post link

Verified, no transaction was committed. After I added an update to the entity table following the create, the entity table’s values did not change after an error was reported. If the DDL committed the transaction, the entity table’s values should have changed.

| username: Kongdom | Original post link

Verified, executing one by one works. But it’s not feasible in actual code operations. :joy:

| username: DBAER | Original post link

Try it in the command line. After creating a temporary table, add an update, and then check if the data is correct after a rollback.

| username: TiDBer_jYQINSnf | Original post link

It can be done. If you turn off the multi-statement switch in the connection string of the program, the driver will automatically split it by semicolon. It doesn’t matter how you write it in your program.