Unknown system variable 'innodb_lock_wait_timeout'

[MoonBottle]

Summary

use s2dbc-mysql connect to doris(version 1.2.6) The following exception occurred, how to fix it Unknown system variable 'innodb_lock_wait_timeout'

Detailed description

Relevant context

Your environment

Additional information

Checklist

• [x] I have searched the existing issues to make sure my question has not been asked before
• [x] I have provided all the necessary information and context to help answer my question
• [x] I have read the documentation and/or README for this project (if applicable)

[jchrys]

Hello, @MoonBottle. Apologies for the delayed response. :(

Currently, we use 'inodb_lock_wait_timeout' to configure 'lock_wait_timeout' and to manage internal state. It appears that Doria doesn't provide support for the 'innodb_lock_wait_timeout' variable, which might indicate a lack of compatibility with MySQL.

Thanks :D

[MoonBottle]

Okay, thank you for your reply 😺

[kollin-francis]

@jchrys Is there a workaround for this at all, like disabling the setting/looking for innodb_lock_wait_timeout? Currently using r2dbc-mysql:0.9.7 to connect to Singlestore (mysql version 5.7.32) and having this issue.

[jchrys]

Currently, there isn't a workaround available for this issue. The problem lies in the fact that after the handshake, we send a query to retrieve the default timeout using SELECT @@innodb_lock_wait_timeout. However, I believe we could potentially provide a workaround for this issue in the future. One potential solution might involve introducing a configuration option like lockWaitTimeoutSystemVariable='lock_wait_timeout'. What are your thoughts on this, @mirromutth?

[mirromutth]

When I built r2dbc-mysql in the early days, I did not consider other MySQL-compatible databases.

Anyway, the innodb_lock_wait_timeout is obvious from the name that it is a InnoDB-specific variable. AFAIK, both MySQL and MariaDB support it.

I'm not sure but r2dbc-mariadb does not implement Connection.setLockWaitTimeout yet.

Maybe we can let the user set the engine dialect. For example, InnoDB variables are only allowed when the engine dialect is set to InnoDB.

Or we can load the current engine before loading InnoDB variables

[mirromutth]

@jchrys I think it should be changed to bug label, WDYT?

Yep, It makes sense :D

[mirromutth]

We can use SHOW VARIABLES LIKE 'innodb_lock_wait_timeout' to avoid the error.

If it is not supported, the server will return an empty result instead of an error.

Strictly speaking, _ is a wildcard that can match any one and only one character, so escaping it as \_ will be stricter.

SHOW VARIABLES LIKE 'innodb\\_lock\\_wait\\_timeout'

Of course, it is also a feasible option to query SELECT @@innodb_lock_wait_timeout separately and output the error as warning and ignore it.