* Alexander Nozdrin <alik@stripped> [07/07/11 23:05]:
> ChangeSet@stripped, 2007-07-11 22:35:05+04:00, anozdrin@ibm. +6 -0
> Fix for 5.1 for BUG#10491: Server returns data as charset binary
> SHOW CREATE TABLE or SELECT FROM I_S.
The patch is OK to push, see below.
> This is the last patch for this bug, which depends on the big
> CS patch and was pending.
>
> The problem was that SHOW CREATE statements returned original
> queries in the binary character set. That could cause the query
> to be unreadable.
>
> The fix is to use original character_set_client when sending
> the original query to the client. In order to preserve the query
> in mysqldump, binary character set should be set when issuing SHOW
> CREATE statement.
... binary character set of results should be set when issuing SHOW
CREATE statement. If either source or destination character set is
"binary" , no conversion is performed. The idea is that since the
source character set is no longer 'binary', we fix the destination
character set to still produce valid dumps.
>
> +
> +static void switch_character_set_results(MYSQL *mysql, const char *cs_name)
> +{
> + char query_buffer[1024];
Is there a constant you could use instead?
> +
> + sprintf(query_buffer,
> + "SET SESSION character_set_results = '%s'",
> + (const char *) cs_name);
> +
> + mysql_query(mysql, query_buffer);
> +}
> +
> /*
> Open a new .sql file to dump the table or view into
>
> @@ -1706,6 +1718,8 @@ static uint dump_events_for_db(char *db)
> if (fetch_db_collation(db_name_buff, db_cl_name, sizeof (db_cl_name)))
> DBUG_RETURN(1);
>
> + switch_character_set_results(mysql, "binary");
> +
I think you should do the same for base tables in your 5.0 version
of the patch.
Please write a test case that demonstrates the need for this
change and fix 5.0 version.
> --- a/mysql-test/t/show_check.test 2007-07-02 01:54:58 +04:00
> +++ b/mysql-test/t/show_check.test 2007-07-11 22:35:02 +04:00
> @@ -897,4 +897,62 @@ DROP TABLE t1;
> DROP PROCEDURE p1;
> DEALLOCATE PREPARE stmt1;
>
> +#
> +# BUG#10491: Server returns data as charset binary SHOW CREATE TABLE or SELECT
> +# FROM INFORMATION_SCHEMA.
> +#
> +# Here are test cases for the related problem: SHOW CREATE statements return
> +# original query in the binary character set. Note: BUG#10491 mentiones
> +# different problem: according to the bug, the text columns (meta-data) are
> +# binary (defined as binary). The problem here is that content of the columns
> +# (data) is binary.
OK, I don't understand this comment.
I suggest the following comment.
Before the change performed to fix the bug, the metadata of the
output of SHOW CREATE statements would always describe the result
as 'binary'. That would ensure that the result is never converted
to character_set_client. Now we return to the client the actual
character set of the object -- which is character_set_client of
the connection that issues the CREATE statement, and this triggers
an automatic conversion to character_set_results of the
connection that issues SHOW CREATE statement.
This test demonstrates that this conversion indeed is taking
place.
> +#
> +
> +# Prepare.
> +
Prepare: create objects in a one character set.
> +set names koi8r;
> +
> +--disable_warnings
> +DROP VIEW IF EXISTS v1;
> +DROP PROCEDURE IF EXISTS p1;
> +DROP FUNCTION IF EXISTS f1;
> +DROP TABLE IF EXISTS t1;
> +DROP EVENT IF EXISTS ev1;
> +--enable_warnings
> +
> +CREATE VIEW v1 AS SELECT '> +
> +CREATE PROCEDURE p1() SELECT '> +
> +CREATE FUNCTION f1() RETURNS CHAR(10) RETURN '> +
> +CREATE TABLE t1(c1 CHAR(10));
> +CREATE TRIGGER t1_bi BEFORE INSERT ON t1
> + FOR EACH ROW
> + SET NEW.c1 = '> +
> +CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO SELECT '> +
> +# Test.
Test: switch the character set and show that SHOW CREATE output is
automatically converted to the new character_set_client.
> +
> +set names utf8;
> +
> +SHOW CREATE VIEW v1;
> +
> +SHOW CREATE PROCEDURE p1;
> +
> +SHOW CREATE FUNCTION f1;
> +
> +SHOW CREATE TRIGGER t1_bi;
> +
> +SHOW CREATE EVENT ev1;
> +
> +# Cleanup.
> +
> +DROP VIEW v1;
> +DROP PROCEDURE p1;
> +DROP FUNCTION f1;
> +DROP TABLE t1;
> +DROP EVENT ev1;
> +
Please alert the connectors team that we've made this change and
they might need to adjust (an email to dev-connectors should
suffice).
Thank you for looking into this, looks like an indeed a nice fix
(and a small one :).
--
-- Konstantin Osipov Software Developer, Moscow, Russia
-- MySQL AB, www.mysql.com The best DATABASE COMPANY in the GALAXY
