[Maria-discuss] query cache dump_at_shutdown/load_at_startup useful + no character set/collation?
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.
Hi, Daniel! On May 12, Daniel Black wrote:
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?
Yes, of course. See in sql_cache.{h,cc} the structure Query_cache_query_flags and what's in there. But query_cache_info plugin doesn't show flags.
Anyone see a work around to get the restore file usable on all queries?
Perhaps, extending query_cache_info plugin to show flags? Your script will need to restore a lot more than just a collation. Some flags cannot be set from the sql script whatsoever - but you can skip these entries in your "select concat" query. Regards, Sergei
----- On 15 May, 2015, at 8:05 PM, Sergei Golubchik serg@mariadb.org wrote:
Are query cache results actually predictable if they have the same statement running in a different session character set/collation?
Yes, of course. See in sql_cache.{h,cc} the structure Query_cache_query_flags and what's in there.
But query_cache_info plugin doesn't show flags.
Ah, thanks for the tip.
Anyone see a work around to get the restore file usable on all queries?
Perhaps, extending query_cache_info plugin to show flags?
Yep, almost got there ( https://github.com/MariaDB/server/pull/67 ) until I realised this has been done already in MDEV-4682 / https://github.com/MariaDB/server/pull/6 Been in review for over 6 months.
Your script will need to restore a lot more than just a collation. Some flags cannot be set from the sql script whatsoever - but you can skip these entries in your "select concat" query.
yep. easily. -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
participants (2)
-
Daniel Black -
Sergei Golubchik