We have a very frequently selects to table and when we are trying to save the dataFrame with override option we are getting error like:
py4j.protocol.Py4JJavaError: An error occurred while calling o364.save.
: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2730)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1618)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1549)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.truncateTable(JdbcUtils.scala:113)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:56)
at org.apache.spark.sql.execution.datasources.SaveIntoDataSourceCommand.run(SaveIntoDataSourceCommand.scala:45)
at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:70)
at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:68)
I am sure that this issue is not related to wating_timeout and interative_timeout. But unfortunately, there are a lot of things which can cause this issue. The problem is when we are trying to truncate the table there are select queries that are locking the table.
For now, it can be fixed by manually deleting the sessions from DB, but it's not a solution that I want. Maybe someone knows what I can do to avoid this situations?
After investigation I found that the issue itself was very stupid, so sometimes we had SELECT queries at the same time with TRUNCATE then both queries are stacking with wait metadata lock statement. And the timeout for this situation was set up to 50 sec by default.