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

[26 Aug 2019 6:21] Leon Zhang
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;
[26 Aug 2019 7:27] Umesh Shastry
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
[26 Aug 2019 7:38] Umesh Shastry
Bug #96646 marked as duplicate of this one