I was testing out using the query_cache_info plugin
(https://mariadb.com/kb/en/mariadb/query_cache_info-plugin/) to pre-cache data
into a server after an upgrade. The read only slaves have a fairly high cache
hit ratio.
select concat('use ', STATEMENT_SCHEMA, ';', STATEMENT_TEXT, ';') as q INTO
OUTFILE '/tmp/qc.txt' FIELDS ESCAPED BY '' TERMINATED BY '' from
information_schema.QUERY_CACHE_INFO;
Query OK, 7122 rows affected (0.03 sec)
When loading these it seems the statements have a collation however this isn't
stored in the query cache.
$ mysql --force < /tmp/qc.txt > /dev/null
ERROR 1267 (HY000) at line 119: Illegal mix of collations (latin1_bin,IMPLICIT)
and (utf8_general_ci,COERCIBLE) for operation '='
ERROR 1267 (HY000) at line 2390: Illegal mix of collations
(latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
ERROR 1267 (HY000) at line 7569: Illegal mix of collations
(latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
Attempts to force this with --default-character-set={utf8|utf8mb4} also
resulted in the same error or same with (echo "set
character_set_connection=utf8;"; cat /tmp/qc.txt; ) | time mysql --force..
Are query cache results actually predictable if they have the same statement
running in a different session character set/collation?
Anyone see a work around to get the restore file usable on all queries?
Is asking for query cache dump/load as a feature request (similar to
innodb_buffer_pool_dump_at_shutdown/innodb_buffer_pool_restore_at_startup)
going to be useful to anyone?
--
--
Daniel Black, Engineer @ Open Query (http://openquery.com.au)
Remote expertise & maintenance for MySQL/MariaDB server environments.
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
