MySQL Bugs: #96647: Query Rewrite plugin database_pattern column length is too shot

Bug #96647	Query Rewrite plugin database_pattern column length is too shot
Submitted:	26 Aug 2019 6:21	Modified:	26 Aug 2019 7:27
Reporter:	Leon Zhang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.22, 5.7.27, 8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Query Rewrite

Description:
As the document mentioned max database name length is 64 characters.

https://dev.mysql.com/doc/refman/8.0/en/identifiers.html

Bug in Query Rewrite rules, `database_pattern` column use varchar(20). If database name longer than 20 characters, it will be truncated and do not take any effect. If SQL_MODE WITH STRICT_TRANS_TABLE Query Rewrite rules will insert failed.

How to repeat:
shell> mysql -u root -p < install_rewriter.sql
Enter password: (enter root password here)

create database `abcdefghijklmnopqrstuvwxyz`;

use abcdefghijklmnopqrstuvwxyz;

create table tb (a int);

select * from tb;

INSERT INTO query_rewrite.rewrite_rules
    (pattern, replacement, pattern_database) VALUES(
    'SELECT * FROM tb',
    'SELECT 1',
    'abcdefghijklmnopqrstuvwxyz'
    );

CALL query_rewrite.flush_rewrite_rules();

select * from tb;

Should get value 1, not the empty set.

Suggested fix:
I try to alter the table definition directly, it can fix this problem. So I think it's better to change the install script `install_rewriter.sql` with MySQL Server release.

ALTER TABLE query_rewrite.rewrite_rules MODIFY `pattern_database` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

Hello Leon Zhang,

Thank you for the report and feedback.
Verifying this report to increase the width of the column "query_rewrite.rewrite_rules.pattern_database" so as not to fail if the database identifiers length exceeds 20 chars and strict mode in place.

regards,
Umesh

Bug #96646 marked as duplicate of this one