Bug #117431 Get ERROR 4501 via Router w/ RW split executing "show keys from table"

• Submitted: 10 Feb 16:30
• Modified: 11 Feb 8:43
• Reporter: Keith Hollman
• Status: Analyzing
• Category: MySQL Router
• Severity: S3 (Non-critical)
• Version: 8.4.4
• OS: Red Hat (el9_5)
• Assigned to: MySQL Verification Team
• CPU Architecture: x86
• Tags: 4501, access_mode is 'auto', read-write splitting

[10 Feb 16:30] Keith Hollman

Description:
With 8.4.4 router setup with RW splitting accessing a 8.0.40 server, I execute the following and get the 4501 error:

(port 3306 is condigured as RW split)
mysqlsh --login-path=icadmin -hweb-01 --sqlc -e "SHOW KEYS FROM authmap WHERE Key_name = 'PRIMARY'"
ERROR: 4501 (HY000) at line 1: Statement not allowed if access_mode is 'auto'

If I add --redirect-primary, it works, but this means correcting all connections strings, i.e. no RW split therefore it defeats the whole RW split objective.

"--redirect-secondary" gives the same original message, logical.
Executing against the RO port 3307 works fine.

How to repeat:
# Create a table with a PK:
use opsdata;
CREATE TABLE `authmap` (
  `uid` int unsigned NOT NULL DEFAULT '0',
  `provider` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`uid`,`provider`),
  UNIQUE KEY `authname_provider` (`authname`,`provider`)) ENGINE=InnoDB ;

# Use mysqlsh:
mysqlsh --login-path=icadmin -hweb-01 --table --redirect-primary -D opsdata --sqlc -e "SHOW KEYS FROM authmap WHERE Key_name = 'PRIMARY'"

Reconnecting to the PRIMARY instance of an InnoDB cluster (db-01:3306)...

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| authmap |          0 | PRIMARY  |            1 | uid         | A         |        1031 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| authmap |          0 | PRIMARY  |            2 | provider    | A         |        1100 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

mysqlsh --login-path=icadmin -hopsdt-e-web-01 --table  --redirect-secondary -D opsdata --sqlc -e "SHOW KEYS FROM authmap WHERE Key_name = 'PRIMARY'"
NOTE: --redirect-secondary ignored because target is already a SECONDARY
ERROR: 4501 (HY000) at line 1: Statement not allowed if access_mode is 'auto'

"SHOW KEYS FROM authmap" also fails.

Suggested fix:
- Correct the "show keys" (and, just in case, all "show" commands as read-only
- Include "show keys" in "Other administrative statements" (https://dev.mysql.com/doc/refman/8.4/en/other-administrative-statements.html) when accessing the RW split port.

[10 Feb 16:51] Keith Hollman

"SHOW index FROM authmap WHERE Key_name = 'PRIMARY'" works fine:

mysqlsh --login-path=icadmin -hweb-01 --table  --redirect-secondary -D opsdata --sqlc -e "SHOW index FROM authmap WHERE Key_name = 'PRIMARY'"
NOTE: --redirect-secondary ignored because target is already a SECONDARY
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| authmap |          0 | PRIMARY  |            1 | uid         | A         |        1031 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| authmap |          0 | PRIMARY  |            2 | provider    | A         |        1100 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+