Error Code: 1231. Variable 'sql_mode' can't be set to the value of 'no_auto_create_user'

[shahidt]

Spider for Mariadb on version 10.4 works great with MySQL 5.7 as one of the nodes for sharding.

However, when I try to partition to a host with MySQL 8, it fails with an error: Error Code: 1231. Variable 'sql_mode' can't be set to the value of 'no_auto_create_user'

As per Deprecation and Removal Notes for MySQL 8 (https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html):

Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.

I realise that MariaDB may be the focus for Spide Storage Engine going forward, but it would be great to have a fix for the above. Perhaps MariaDB may also deprecate it in the future. So better to be prepared.

There may be others like me who have a strong use case of requiring to implement Sharding on an existing MySQL database with data in Terrabytes.