MySQL Bugs: #111658: Session with autocommit=0 keeps old view of tables

Bug #111658	Session with autocommit=0 keeps old view of tables
Submitted:	4 Jul 2023 12:54	Modified:	4 Jul 2023 14:19
Reporter:	Iwo P	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Dropped tables can still be visible for sessions that turned off auto-commit.

How to repeat:
Session 01 > select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0,00 sec)

Session 01 > CREATE TABLE A( id int );
Query OK, 0 rows affected (0,02 sec)

Session 01 > SET autocommit = 0;
Query OK, 0 rows affected (0,00 sec)

Session 01> show tables;
+----------------+
| Tables_in_test |
+----------------+
| A              |
+----------------+
1 row in set (0,00 sec)

Session 02 > drop table A;
Query OK, 0 rows affected (0,02 sec)

Session 02 > show tables;
Empty set (0,00 sec)

Session 01> show tables;
+----------------+
| Tables_in_test |
+----------------+
| A              |
+----------------+
1 row in set (0,00 sec)
(Here Session 01 should not see A, as it was dropped just before in Session 02)
Session 01 > select * from A;
ERROR 1146 (42S02): Table 'test.A' doesn't exist

Suggested fix:
Session 01 should not see the tables that were dropped.

Hi Mr. P,

Thank you for your bug report.

However, we are not able to repeat what you are reporting with our 8.0.33.

We started both sessions with autocommit being disabled, as it is default. We could not repeat what you are reporting as both sessions did not show any table;

Then we start both sessions with autocommit enabled and again, we got the same result. SHOW TABLES again returned an empty set.

Can't repeat.

Test report

Attachment: bug111658.test (application/octet-stream, text), 245 bytes.

I've attached MTR test case.

Sample run:
```
$ perl mysql-test-run.pl t/bug111658.test --nocheck-testcases
Logging: mysql-test-run.pl  t/bug111658.test --nocheck-testcases
MySQL Version 8.0.33
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/home/iwo/opt/oracle_mysql/mysql-8.0.33-linux-glibc2.28-x86_64/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE A ( id int );
SET autocommit = 0;
SHOW TABLES;
Tables_in_test
A
# Connection con1
# Sending:
DROP TABLE A;
SHOW TABLES;
Tables_in_test
# Connection default
SHOW TABLES;
Tables_in_test
A
```

Hi,

Thank you for your bug report.

We were able to repeat the test case.

One session is in auto-commit mode , while the other is not.

Verified as reported.