1

I am connecting to read-replica instance of MySQL (actually, Google Cloud SQL). The JDBC driver seems to be able to create temporary table but not able to insert data into it. But, using another client (in my case MySQL workbench), I am able to create and insert data into the temporary table. Looks like a bug in JDBC driver? What are the options to get it working?

Here is the exception I get (I cannot post the entire stacktrace due to company policies):

Caused by: java.sql.SQLException: The MySQL server is running with the --read-only option so it cannot execute this statement
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2450)
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006)

Version Details:
MySQL version = 5.7.25-google-log
JDBC driver = mysql-connector-java-5.1.21.jar

Note: Running on the master instance (instead of read-replica) will be my last option. I pretty much know it will work.

Learner
  • 533
  • 5
  • 18
  • You are using an extremely old version of the MySQL Connector/J driver, maybe you should try with a newer version. And if you think it is a bug, you should contact MySQL/Oracle. – Mark Rotteveel Mar 22 '20 at 09:08
  • Ok, will try with the latest version of MySQL driver. Hope drivers (at least in theory) are backward compatible. – Learner Mar 22 '20 at 11:37

2 Answers2

0

No, the driver version is by 99% not the problem cause (I guess). Because I wrote to temporary tables successfully on MySQL 5.0, 5.1, 5.6 and 5.7 with the matching JDBC driver version.

The error message tells me that the server itself is rejecting the attempt due to its "read only" configuration.

There is a related bug report: https://bugs.mysql.com/bug.php?id=64992

Are you sure that you table is really a temporary one (not a regular table) and that you are using it within the same session/connection? Because otherwise it does not work on purpose.

Stefan
  • 1,789
  • 1
  • 11
  • 16
  • Yes, it's a temporary table. I too thought it's something which comes from server itself and not the driver. That's when I tried to create and insert data to the temporary table through MySQL workbench - and it was successful. – Learner Mar 22 '20 at 11:36
  • MySQL Workbench keeps the current session/connection open. Try a recent version of the JDBC driver and a recent version of the database (e.g. MariaDB 10.x). If that does not help, then you can be 100% sure that issue is in your own program. – Stefan Mar 22 '20 at 13:00
0

Tried with recent version of JDBC driver (v5.1.48) and it worked! So there was a bug in the version of JDBC driver I was using earlier (v5.1.21).

Learner
  • 533
  • 5
  • 18