bk commit into 5.1 tree (anozdrin:1.2528) BUG#10491

[Alexander Nozdrin]

Below is the list of changes that have just been committed into a local
5.1 repository of alik. When alik does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

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.
  
  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.

  client/mysqldump.c@stripped, 2007-07-11 22:35:02+04:00, anozdrin@ibm. +33 -0
    Switch character_set_results of mysqldump-connection before
    calling SHOW CREATE statements for the objects.

  mysql-test/r/show_check.result@stripped, 2007-07-11 22:35:02+04:00, anozdrin@ibm. +39 -0
    Result file.

  mysql-test/t/show_check.test@stripped, 2007-07-11 22:35:02+04:00, anozdrin@ibm. +58 -0
    Add test case for the part of BUG#10491.

  sql/events.cc@stripped, 2007-07-11 22:35:02+04:00, anozdrin@ibm. +2 -1
    Send original query in the original character set.

  sql/sp_head.cc@stripped, 2007-07-11 22:35:02+04:00, anozdrin@ibm. +2 -1
    Send original query in the original character set.

  sql/sql_show.cc@stripped, 2007-07-11 22:35:02+04:00, anozdrin@ibm. +10 -2
    Send original query in the original character set.

diff -Nrup a/client/mysqldump.c b/client/mysqldump.c
--- a/client/mysqldump.c	2007-06-29 16:52:02 +04:00
+++ b/client/mysqldump.c	2007-07-11 22:35:02 +04:00
@@ -1201,6 +1201,18 @@ static void restore_time_zone(FILE *sql_
           (const char *) delimiter);
 }
 
+
+static void switch_character_set_results(MYSQL *mysql, const char *cs_name)
+{
+  char query_buffer[1024];
+
+  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");
+
     while ((event_list_row= mysql_fetch_row(event_list_res)) != NULL)
     {
       event_name= quote_name(event_list_row[1], name_buff, 0);
@@ -1774,6 +1788,8 @@ static uint dump_events_for_db(char *db)
     } /* end of list of events */
     fprintf(sql_file, "DELIMITER ;\n");
     fprintf(sql_file, "/*!50106 SET TIME_ZONE= @save_time_zone */ ;\n");
+
+    switch_character_set_results(mysql, default_charset);
   }
   mysql_free_result(event_list_res);
 
@@ -1853,6 +1869,8 @@ static uint dump_routines_for_db(char *d
   if (fetch_db_collation(db_name_buff, db_cl_name, sizeof (db_cl_name)))
     DBUG_RETURN(1);
 
+  switch_character_set_results(mysql, "binary");
+
   /* 0, retrieve and dump functions, 1, procedures */
   for (i= 0; i <= 1; i++)
   {
@@ -1990,6 +2008,8 @@ static uint dump_routines_for_db(char *d
     mysql_free_result(routine_list_res);
   } /* end of for i (0 .. 1)  */
 
+  switch_character_set_results(mysql, default_charset);
+
   if (lock_tables)
     VOID(mysql_query_with_error_report(mysql, 0, "UNLOCK TABLES"));
   DBUG_RETURN(0);
@@ -2542,6 +2562,8 @@ static void dump_triggers_for_table(char
   if (fetch_db_collation(db_name, db_cl_name, sizeof (db_cl_name)))
     DBUG_VOID_RETURN;
 
+  switch_character_set_results(mysql, "binary");
+
   /* Dump triggers. */
 
   while ((row= mysql_fetch_row(result)))
@@ -2637,6 +2659,8 @@ static void dump_triggers_for_table(char
 
   mysql_free_result(result);
 
+  switch_character_set_results(mysql, default_charset);
+
   /*
     make sure to set back opt_compatible mode to
     original value
@@ -4390,14 +4414,21 @@ static my_bool get_view_structure(char *
   result_table=     quote_name(table, table_buff, 1);
   opt_quoted_table= quote_name(table, table_buff2, 0);
 
+  switch_character_set_results(mysql, "binary");
+
   my_snprintf(query, sizeof(query), "SHOW CREATE TABLE %s", result_table);
+
   if (mysql_query_with_error_report(mysql, &table_res, query))
+  {
+    switch_character_set_results(mysql, default_charset);
     DBUG_RETURN(0);
+  }
 
   /* Check if this is a view */
   field= mysql_fetch_field_direct(table_res, 0);
   if (strcmp(field->name, "View") != 0)
   {
+    switch_character_set_results(mysql, default_charset);
     verbose_msg("-- It's base table, skipped\n");
     DBUG_RETURN(0);
   }
@@ -4539,6 +4570,8 @@ static my_bool get_view_structure(char *
     mysql_free_result(table_res);
     dynstr_free(&ds_view);
   }
+
+  switch_character_set_results(mysql, default_charset);
 
   /* If a separate .sql file was opened, close it now */
   if (sql_file != md_result_file)
diff -Nrup a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
--- a/mysql-test/r/show_check.result	2007-07-02 11:27:54 +04:00
+++ b/mysql-test/r/show_check.result	2007-07-11 22:35:02 +04:00
@@ -1282,4 +1282,43 @@ t1_bi		CREATE DEFINER=`root`@`localhost`
 DROP TABLE t1;
 DROP PROCEDURE p1;
 DEALLOCATE PREPARE stmt1;
+set names koi8r;
+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;
+CREATE VIEW v1 AS SELECT '����' AS test;
+CREATE PROCEDURE p1() SELECT '����' AS test;
+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 '����' AS test;
+set names utf8;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1`
AS select _koi8r'тест' AS `test`	koi8r	koi8r_general_ci
+SHOW CREATE PROCEDURE p1;
+Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database
Collation
+p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+SELECT 'тест' AS test	koi8r	koi8r_general_ci	latin1_swedish_ci
+SHOW CREATE FUNCTION f1;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database
Collation
+f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS char(10) CHARSET latin1
+RETURN 'тест'	koi8r	koi8r_general_ci	latin1_swedish_ci
+SHOW CREATE TRIGGER t1_bi;
+Trigger	sql_mode	SQL Original
Statement	character_set_client	collation_connection	Database Collation
+t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1
+FOR EACH ROW
+SET NEW.c1 = 'тест'	koi8r	koi8r_general_ci	latin1_swedish_ci
+SHOW CREATE EVENT ev1;
+Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database
Collation
+ev1		SYSTEM	CREATE EVENT `ev1` ON SCHEDULE AT '2030-01-01 00:00:00' ON COMPLETION NOT
PRESERVE ENABLE DO SELECT 'тест' AS test	koi8r	koi8r_general_ci	latin1_swedish_ci
+DROP VIEW v1;
+DROP PROCEDURE p1;
+DROP FUNCTION f1;
+DROP TABLE t1;
+DROP EVENT ev1;
 End of 5.1 tests
diff -Nrup a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
--- 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.
+#
+
+# Prepare.
+
+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 '����' AS test;
+
+CREATE PROCEDURE p1() SELECT '����' AS test;
+
+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 '����' AS test;
+
+# Test.
+
+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;
+
 --echo End of 5.1 tests
diff -Nrup a/sql/events.cc b/sql/events.cc
--- a/sql/events.cc	2007-06-28 21:34:48 +04:00
+++ b/sql/events.cc	2007-07-11 22:35:02 +04:00
@@ -717,7 +717,8 @@ send_show_create_event(THD *thd, Event_t
   protocol->store(et->name.str, et->name.length, system_charset_info);
   protocol->store(sql_mode.str, sql_mode.length, system_charset_info);
   protocol->store(tz_name->ptr(), tz_name->length(), system_charset_info);
-  protocol->store(show_str.c_ptr(), show_str.length(), &my_charset_bin);
+  protocol->store(show_str.c_ptr(), show_str.length(),
+                  et->creation_ctx->get_client_cs());
   protocol->store(et->creation_ctx->get_client_cs()->csname,
                   strlen(et->creation_ctx->get_client_cs()->csname),
                   system_charset_info);
diff -Nrup a/sql/sp_head.cc b/sql/sp_head.cc
--- a/sql/sp_head.cc	2007-07-02 01:46:52 +04:00
+++ b/sql/sp_head.cc	2007-07-11 22:35:02 +04:00
@@ -2260,7 +2260,8 @@ sp_head::show_create_routine(THD *thd, i
   protocol->store(sql_mode.str, sql_mode.length, system_charset_info);
 
   if (full_access)
-    protocol->store(m_defstr.str, m_defstr.length, &my_charset_bin);
+    protocol->store(m_defstr.str, m_defstr.length,
+                    m_creation_ctx->get_client_cs());
   else
     protocol->store_null();
 
diff -Nrup a/sql/sql_show.cc b/sql/sql_show.cc
--- a/sql/sql_show.cc	2007-06-28 21:34:49 +04:00
+++ b/sql/sql_show.cc	2007-07-11 22:35:02 +04:00
@@ -639,7 +639,8 @@ mysqld_show_create(THD *thd, TABLE_LIST 
 
   if (table_list->view)
   {
-    protocol->store(buffer.ptr(), buffer.length(), &my_charset_bin);
+    protocol->store(buffer.ptr(), buffer.length(),
+                    table_list->view_creation_ctx->get_client_cs());
 
     protocol->store(table_list->view_creation_ctx->get_client_cs()->csname,
                     system_charset_info);
@@ -5983,6 +5984,8 @@ static bool show_create_trigger_impl(THD
   LEX_STRING trg_connection_cl_name;
   LEX_STRING trg_db_cl_name;
 
+  CHARSET_INFO *trg_client_cs;
+
   /*
     TODO: Check privileges here. This functionality will be added by
     implementation of the following WL items:
@@ -6008,6 +6011,11 @@ static bool show_create_trigger_impl(THD
                                                      trg_sql_mode,
                                                      &trg_sql_mode_str);
 
+  /* Resolve trigger client character set. */
+
+  if (resolve_charset(trg_client_cs_name.str, NULL, &trg_client_cs))
+    return TRUE;
+
   /* Send header. */
 
   fields.push_back(new Item_empty_string("Trigger", NAME_LEN));
@@ -6054,7 +6062,7 @@ static bool show_create_trigger_impl(THD
 
   p->store(trg_sql_original_stmt.str,
            trg_sql_original_stmt.length,
-           &my_charset_bin);
+           trg_client_cs);
 
   p->store(trg_client_cs_name.str,
            trg_client_cs_name.length,