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

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:	Windows (Windows 7 64bit)
Assigned to:		CPU Architecture:	Any
Tags:	jdbc, MySQL

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

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.

Using

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

we still get the same exception.

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.

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...

Thank you for update.

We will wait final feedback with queries which lead to this situation.

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.

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.

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".

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;

Please try 5.5.31 version and Shane recomendation. Thanks.

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".

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