SET SESSION TRANSACTION READ ONLY return error

[crazycs520]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql>SET SESSION TRANSACTION READ ONLY;
(1235, 'function READ ONLY has only noop implementation in tidb now, use tidb_enable_noop_functions to enable these functions')

2. What did you expect to see? (Required)

mysql>SET SESSION TRANSACTION READ ONLY;
Query OK, 0 rows affected

3. What did you see instead (Required)

Execute error.

4. What is your TiDB version? (Required)

***************************[ 1. row ]***************************
tidb_version() | Release Version: v6.1.0-alpha-398-g0703a64f76
Edition: Community
Git Commit Hash: 0703a64f76baf8f79126ee45780310737f55df0b
Git Branch: master
UTC Build Time: 2022-05-13 05:38:58
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

Reason

Since we not support read-only transcation now, so #23818 protect read only noop via tidb_enable_noop_functions. But it breaks the compatibility with MySQL.

[morgo]

@crazycs520 can you describe what your expectations are in terms of behavior?

If we do not support read-only, but we noop the syntax it is dangerous. That is why the tidb_enable_noop_functions variable exists.

[crazycs520]

I meet this issue in the https://github.com/go-sql-driver/mysql integration test. Since SET SESSION TRANSACTION READ ONLY; returns an error cause a test failed.

Does TiDB expect to support read-only transactions?

[mingfang]

I'm also getting this error when connecting to TiDB using the standard Trino Mysql driver.

Caused by: java.sql.SQLSyntaxErrorException: function READ ONLY has only noop implementation in tidb now, use tidb_enable_noop_functions to enable these functions
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:2169)
	at com.mysql.cj.jdbc.ConnectionImpl.setReadOnly(ConnectionImpl.java:2153)
	at io.trino.plugin.jdbc.ForwardingConnection.setReadOnly(ForwardingConnection.java:124)
	at io.trino.plugin.jdbc.BaseJdbcClient.getConnection(BaseJdbcClient.java:412)
	at io.trino.plugin.jdbc.ForwardingJdbcClient.getConnection(ForwardingJdbcClient.java:145)
	at io.trino.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$getConnection$12(StatisticsAwareJdbcClient.java:165)
	at io.trino.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:35)
	at io.trino.plugin.jdbc.jmx.StatisticsAwareJdbcClient.getConnection(StatisticsAwareJdbcClient.java:165)
	at io.trino.plugin.jdbc.CachingJdbcClient.getConnection(CachingJdbcClient.java:214)
	at io.trino.plugin.jdbc.JdbcRecordCursor.<init>(JdbcRecordCursor.java:81)

[yevhen-korientsov]

Same problem with trino.

[dveeden]

Note this:

mysql-8.0.11-TiDB-v8.5.4 [test]> create table t1 (id int primary key);
Query OK, 0 rows affected (0.019 sec)

mysql-8.0.11-TiDB-v8.5.4 [test]> SET SESSION TRANSACTION READ ONLY;
ERROR 1235 (42000): function READ ONLY has only noop implementation in tidb now, use tidb_enable_noop_functions to enable these functions
mysql-8.0.11-TiDB-v8.5.4 [test]> SET SESSION TRANSACTION READ ONLY AS OF TIMESTAMP NOW() - INTERVAL 5 SECOND;
Query OK, 0 rows affected (0.001 sec)

mysql-8.0.11-TiDB-v8.5.4 [test]> START TRANSACTION;
Query OK, 0 rows affected (0.001 sec)

mysql-8.0.11-TiDB-v8.5.4 [test]> insert into t1 values(1);
ERROR 1105 (HY000): only support read-only statement during read-only staleness transactions
mysql-8.0.11-TiDB-v8.5.4 [test]> 

So it looks like we do support read only transactions if we set a AS OF TIMESTAMP.

I think we should try to to support SET SESSION TRANSACTION READ ONLY by making it behave the same as the one with a AS OF TIMESTAMP, but then with a reasonable default.

Also note that readOnlyPropagatesToServer can be a good workaround.

Related:

• Support timestamp bounded read-only transactions on data replicas Tasklist #21094
• https://docs.pingcap.com/tidb/stable/stale-read/
• java-app-best-practices: Add readOnlyPropagatesToServer docs#22321

Feel free to vote for this issue by putting a 👍 on the issue description.