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
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
