MySQL Bugs: #98677: "Table definition has changed, please retry transaction" when trying to select

Bug #98677	"Table definition has changed, please retry transaction" when trying to select
Submitted:	20 Feb 2020 6:35	Modified:	26 Feb 2020 5:07
Reporter:	Alexey Melnikov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	MacOS
Assigned to:	MySQL Verification Team	CPU Architecture:	x86
Tags:	jdbc

Description:
Server returns "Table definition has changed, please retry transaction" on select after insert operation which happens after table truncation.

I'm working with database via mysql-java-connector.

Problem occurs after table truncation (table with auto_increment field). 
Even when table truncation is done in separate transaction it still throws the same error.
The same code worked without any problems with MySQL 5.7.x

As workaround I'm using two operations right now instead of truncate:
"DELETE FROM tablename;"
"ALTER TABLE tablename AUTO_INCREMENT=1;"

How to repeat:
Have an InnoDB table with auto_increment attribute.

1) Connect to DB, truncate table.
2) Insert new record.
3) Try to select new auto_increment value for just inserted row.
4) Got error.

Execute next code with mysql-jdbc-connector-8.0.19

    PreparedStatement stmt = null;
    try {
      stmt = con.prepareStatement("TRUNCATE tablename");
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  con = ConnectionUtil.get();
  stmt = con.prepareStatement("INSERT INTO tablename");
  stmt.executeUpdate();
  close(stmt);

  con = ConnectionUtil.get();
  stmt = con.prepareStatement("SELECT aivalue FROM tablename");
  rs = stmt.executeQuery();
      while (rs.next()) {
        result = rs.getLong(1);
      }
  close(stmt);

Hi,

This is not a bug, table definition did change. When you do truncate it is often faster to drop table and create a new one and that's exactly what happened so this is now a new table with the same name. 

kind regards
Bogdan