Bug #65378 "Table definition has changed, please retry transaction" when trying to insert
Submitted: 21 May 2012 7:59 Modified: 26 May 2013 0:46
Reporter: Markus Surudo Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.5.24 OS:Microsoft Windows (Windows 7 64bit)
Assigned to: CPU Architecture:Any
Tags: jdbc, MySQL

[21 May 2012 7:59] Markus Surudo
Description:
When I first insert Data into my schema and try to query the data in the next step i get following exception

org.eclipse.net4j.db.DBException: java.sql.SQLException: Table definition has changed, please retry transaction
...
Caused by: java.sql.SQLException: Table definition has changed, please retry transaction
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:911)
	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1408)
	at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2829)
	at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:468)
	at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2534)
	at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1749)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2159)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.HorizontalBranchingMappingStrategy.rawImportReviseOldRevisions(HorizontalBranchingMappingStrategy.java:131)

How to repeat:
1. Insert Data into the database
2. exectue select-query on the database (PreparedStatement.executeQuery)
3. see exception
[21 May 2012 8:56] Valeriy Kravchuk
Are you sure that ALTER TABLE had not been executed from some other session in the meantime? If you are, please, check if your application has the same problem with a newer version of server, 5.5.24.
[30 May 2012 12:58] Markus Surudo
Using

MySQL 5.5.24 Windows 64bit
mysql-connector-java-5.1.20-bin.jar

we still get the same exception.
[30 May 2012 16:56] Sveta Smirnova
Thank you for the report.

Please provide all session where you get error. Would be good if you can create small Java test case.

Also would be useful if you watch parallel queries. You can enable general query log, then search it for queries executed in time of problematic transaction.
[31 May 2012 17:14] Eike Stepper
The database in question is used by just one application and the reported problem occurs at application startup. We're pretty sure that there's just this single JDBC connection that replicates (inserts) lots of data that's read from the network. Markus will try to follow your query log hint to proove this assertion...
[31 May 2012 17:27] Sveta Smirnova
Thank you for update.

We will wait final feedback with queries which lead to this situation.
[1 Jun 2012 5:44] Markus Surudo
Okay, I checked the log now and added a file. If I see it correctly the rollback with all drops in the end is due to the error I get.

So in the beginning there are a lot of CREATE/UPDATE-Statements, then INSERT-Statements and later one SELECT-Statement, which crashes the application.
[1 Jun 2012 11:50] Sveta Smirnova
Thank you for the feedback.

You still have few threads recorded in the log.

Please specify us which SELECT fail, so we can grep, then try to reproduce error.
[2 Jul 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[25 Feb 2013 19:02] MySQL Verification Team
http://dev.mysql.com/doc/refman/5.5/en/innodb-create-index-limitations.html

If table definitions changed, you need to re-prepare statements, or commit and start a transaction over again.  See here, a simple example:

connection1:
------------
drop table if exists t1,t2;
create table t1(a int)engine=innodb;
start transaction;

connection2:
------------
create table t2(a int)engine=innodb;

connection1:
------------
start transaction;
select * from t1;

connection2:
------------
alter table t2 add column b int;

connection1:
------------
select * from t2;
[26 Apr 2013 0:46] MySQL Verification Team
Please try 5.5.31 version and Shane recomendation. Thanks.
[26 May 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[19 Feb 2020 17:43] Alexey Melnikov
In my case the problem happens after executing "truncate <table name>.

Table has auto increment field.

I also tried to wrap table truncation into separate transaction - no luck. The same error.

Current setup mysql-java-connector 5.1.48 with mysql-ce-8.0.19.

The same problem with mysql-java-connector-8.0.19 with mysql-ce-8.0.19