| List: | Commits | « Previous MessageNext Message » | |
| From: | Alexander Nozdrin | Date: | March 8 2007 12:31pm |
| Subject: | bk commit into 5.0 tree (anozdrin:1.2389) BUG#11986 | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.0 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-03-08 14:31:35+03:00, anozdrin@stripped +49 -0 Patch for the following bugs: - BUG#11986: Stored routines and triggers can fail if the code has a non-ascii symbol - BUG#16291: mysqldump corrupts string-constants with non-ascii-chars - BUG#25212: Character set of string constant is ignored (stored routines) - BUG#25221: Character set of string constant is ignored (triggers) General overview ---------------- There were 2 general problems that caused these bugs: 1. Character set of definition-query for views, triggers, stored routines was lost. 2. mysqldump outputs query in client character set, which can be inappropriate to encode definition-query. 1. No query-definition-character set. We execute/process objects in the following way: - Stored routines are stored in the connection cache. In other words, stored routines are compiled at first execution in the user connection. - Triggers are compiled when the table is loaded. The problem here was that character set of original query could be different from the actual character set at the next compilation. The fix is to store character set and use it each time we compile the object. Actually, three character sets should be kept: client character set, connection character set and server character set. 2. Wrong mysqldump-output. The problem here was that we tried to recode definition query to the mysqldump-client character set. Moreover, we stored queries in different character sets for different objects (views, for one, used UTF8, triggers used original character set). The solution is - to store definition queries in the original character set; - to change SHOW CREATE statement to output definition query in the binary character set (i.e. without any conversion); - to dump special statements to switch character set to original one before dumping definition-query. Fix in details -------------- 1. Stored routines. - Add client_cs, connection_cs, server_cs columns (as char(32)) to mysql.proc table to store client-character-set, connection-character-set, server-character-set respectively. - Store definition context (three character sets) in these fields when stored routine is created. - Switch client, connection and server character sets each time when definition query is parsed (i.e. stored routine is compiled). - In order to preserve backward compatibility, assume that the query is in current client character set if there is no definition context for a stored routine. Also, throw a warning in this case. - Add client_cs, connection_cs, server_cs columns in INFORMATION_SCHEMA.ROUTINES pseudo-table to show client, connection and server character sets respectively. - Add client_cs, connection_cs, server_cs columns in SHOW PROCEDURE | FUNCTION STATUS statement to show client, connection and server character sets respectively. - Add client_cs, connection_cs, server_cs columns in SHOW CREATE PROCEDURE | FUNCTION statement to show client, connection and server character sets respectively. - Change character set of Create-column (definition query) in SHOW CREATE PROCEDURE | FUNCTION statement to binary to show definition query without any conversion. 2. Triggers. - Store client, connection and server character sets in TRG-file. - Switch client, connection and server character sets each time when triggers for a table are parsed (loaded and compiled). - In order to preserve backward compatibility, assume that the query is in the current client character set if there is no definition context for a trigger. Also, throw a warning in this case. - Add client_cs, connection_cs, server_cs columns in INFORMATION_SCHEMA.TRIGGERS pseudo-table to show client, connection and server character sets respectively. - Add client_cs, connection_cs, server_cs columns in SHOW TRIGGERS statement to show client, connection and server character sets respectively. - Add SHOW CREATE TRIGGER so that mysqldump is able to dump triggers properly. 3. Views - Store client, connection and server character sets in FRM-file. - Store view-definition-query in the original character set in FRM-file. - Switch client, connection and server character sets each time when definition query is parsed and the view is executed. - In order to provide backward compatibility, assume that the query is in UTF8 if there is no definition context for a stored routine. Also, throw a warning in this case. - Add client_cs, connection_cs, server_cs columns in INFORMATION_SCHEMA.VIEWS pseudo-table to show client, connection and server character sets respectively. - Add client_cs, connection_cs, server_cs columns in SHOW CREATE VIEW statement to show client, connection and server character sets respectively. - Change character set of Create-column (definition query) in SHOW CREATE VIEW statement to binary to show definition query without any conversion. 4. mysqldump - Dump of definition query should be done as follows: - Save current character sets (client, connection, server); - Switch character sets to the definition query character sets; - Dump definition query (CREATE-statement); - Restore character sets. Not fixed problems ------------------ - INFORMATION_SCHEMA does not show definition query correctly. Fix: add additional attribute for definition of routines/triggers -- UTF8-query, which is generated on parser stage and is used only for showing in I_S. - SHOW TRIGGERS does not work correctly Fix: this will be fixed as soon as the problem with INFORMATION_SCHEMA is fixed. client/mysqldump.c@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +133 -51 Set original character set and collation before dumping definition query. mysql-test/lib/init_db.sql@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +3 -0 Added columns for mysql.proc. mysql-test/r/func_in.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +2 -2 Updated result file. mysql-test/r/gis.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +2 -2 Updated result file. mysql-test/r/grant.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +12 -12 Updated result file. mysql-test/r/information_schema.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +51 -48 Updated result file. mysql-test/r/information_schema_db.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +2 -2 Updated result file. mysql-test/r/lowercase_view.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +6 -6 Updated result file. mysql-test/r/mysqldump.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +417 -61 Updated result file. mysql-test/r/rpl_ddl.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +26 -14 Updated result file. mysql-test/r/rpl_sp.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +24 -24 Updated result file. mysql-test/r/rpl_trigger.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +3 -3 Updated result file. mysql-test/r/rpl_view.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +2 -2 Updated result file. mysql-test/r/show_check.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +20 -20 Updated result file. mysql-test/r/skip_grants.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +6 -6 Updated result file. mysql-test/r/sp-destruct.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +2 -2 Updated result file. mysql-test/r/sp-error.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +6 -6 Updated result file. mysql-test/r/sp-security.result@stripped, 2007-03-08 14:31:29+03:00, anozdrin@stripped +18 -18 Updated result file. mysql-test/r/sp.result@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +228 -65 Updated result file. mysql-test/r/sql_mode.result@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +12 -12 Updated result file. mysql-test/r/system_mysql_db.result@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +3 -0 Updated result file. mysql-test/r/temp_table.result@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +2 -2 Updated result file. mysql-test/r/trigger-compat.result@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +3 -3 Updated result file. mysql-test/r/trigger-grant.result@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +9 -9 Updated result file. mysql-test/r/trigger.result@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +76 -5 Updated result file. mysql-test/r/view.result@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +87 -80 Updated result file. mysql-test/r/view_grant.result@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +28 -28 Updated result file. mysql-test/t/information_schema.test@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +1 -1 Provide values for 3 new columns. mysql-test/t/mysqldump.test@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +77 -3 Add test case for BUG#16291. mysql-test/t/sp.test@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +186 -1 Add test case for BUG#25212. mysql-test/t/trigger.test@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +109 -0 Add test case for BUG#25221. mysql-test/t/view.test@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +18 -6 Make the test less dependent on environment. scripts/mysql_create_system_tables.sh@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +3 -0 Add 3 columns to mysql.proc. scripts/mysql_fix_privilege_tables.sql@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +10 -1 Add 3 columns to mysql.proc. sql/mysql_priv.h@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +17 -0 Added aux functions. sql/share/errmsg.txt@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +29 -0 Add error messages. sql/sp.cc@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +181 -45 1. Store original query charsets (definition context) in mysql.proc and use it to compile SP each time. Show definition context in I_S.ROUTINES, SHOW CREATE PROCEDURE | FUNCTION, SHOW PROCEDURE STATUS. 2. Join sp_show_create_procedure() and sp_show_create_function() into sp_show_create_routine(). sql/sp.h@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +1 -4 Join sp_show_create_procedure() and sp_show_create_function() into sp_show_create_routine(). sql/sp_head.cc@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +90 -84 1. Store original query charsets (definition context) in mysql.proc and use it to compile SP each time. Show definition context in I_S.ROUTINES, SHOW CREATE PROCEDURE | FUNCTION, SHOW PROCEDURE STATUS. 2. Join show_create_procedure() and show_create_function() into show_create_routine(). sql/sp_head.h@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +5 -5 1. Store original query charsets (definition context) in mysql.proc and use it to compile SP each time. Show definition context in I_S.ROUTINES, SHOW CREATE PROCEDURE | FUNCTION, SHOW PROCEDURE STATUS. 2. Join show_create_procedure() and show_create_function() into show_create_routine(). sql/sql_lex.h@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +1 -0 Added code for SHOW CREATE TRIGGER statement. sql/sql_parse.cc@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +15 -2 1. Polishing. 2. Added support for SHOW CREATE TRIGGER statement. sql/sql_show.cc@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +290 -9 Show definition context in I_S.ROUTINES, I_S.VIEWS, I_S.TRIGGERS; sql/sql_trigger.cc@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +445 -104 Store original query charsets (definition context) in TRG-file and use it when parsing (compiling) the triggers each time. Show definition context in I_S.TRIGGERS, SHOW TRIGGERS. sql/sql_trigger.h@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +25 -2 Store original query charsets (definition context) in TRG-file and use it when parsing (compiling) the triggers each time. Show definition context in I_S.TRIGGERS, SHOW TRIGGERS. sql/sql_view.cc@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +121 -7 Store original query charsets (definition context) in FRM-file and use it when parsing and executing the view each time. Show definition context in I_S.TRIGGERS, SHOW VIEWS, SHOW CREATE VIEW. sql/sql_yacc.yy@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +6 -0 Added support for SHOW CREATE TRIGGER. sql/table.cc@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +22 -0 Auxilary functions. sql/table.h@stripped, 2007-03-08 14:31:30+03:00, anozdrin@stripped +43 -0 1. Introduce auxiliry structure to store original-query-charsets (query definition context). 2. Add necessary attributes to handle definition context in TABLE_LIST structure. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: anozdrin # Host: booka.opbmk # Root: /home/alik/Documents/MySQL/devel/5.0-rt-charsets-2 --- 1.254/client/mysqldump.c 2007-01-12 14:22:48 +03:00 +++ 1.255/client/mysqldump.c 2007-03-08 14:31:29 +03:00 @@ -1432,19 +1432,21 @@ Cover DEFINER-clause in version-specific comments. TODO: this is definitely a BAD IDEA to parse SHOW CREATE output. - We should user INFORMATION_SCHEMA instead. The only problem is - that now INFORMATION_SCHEMA does not provide information about - routine parameters. + However, we can not use INFORMATION_SCHEMA instead: + 1. INFORMATION_SCHEMA provides data in UTF8, but here we + need data in the original character set; + 2. INFORMATION_SCHEMA does not provide information about + routine parameters now. */ - definer_begin= strstr(row[2], " DEFINER"); + definer_begin= strcasestr(row[2], " DEFINER"); if (definer_begin) { - char *definer_end= strstr(definer_begin, " PROCEDURE"); + char *definer_end= strcasestr(definer_begin, " PROCEDURE"); if (!definer_end) - definer_end= strstr(definer_begin, " FUNCTION"); + definer_end= strcasestr(definer_begin, " FUNCTION"); if (definer_end) { @@ -1469,14 +1471,30 @@ /* we need to change sql_mode only for the CREATE PROCEDURE/FUNCTION otherwise we may need to re-quote routine_name - */; - fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=\"%s\"*/;;\n", - row[1] /* sql_mode */); - fprintf(sql_file, "/*!50003 %s */;;\n", - (query_str != NULL ? query_str : row[2])); + */ fprintf(sql_file, - "/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/" - ";;\n"); + "/*!50003 SET @saved_sql_mode = @@sql_mode */;;\n" + "/*!50003 SET @saved_cs_client = @@character_set_client */;;\n" + "/*!50003 SET @saved_cs_results = @@character_set_results */;;\n" + "/*!50003 SET @saved_col_connection = @@collation_connection */;;\n" + "/*!50003 SET @saved_col_database = @@collation_database */;;\n" + "/*!50003 SET sql_mode = '%s' */;;\n" + "/*!50003 SET character_set_client = %s */;;\n" + "/*!50003 SET character_set_results = %s */;;\n" + "/*!50003 SET collation_connection = %s */;;\n" + "/*!50003 SET collation_database = %s */;;\n" + "/*!50003 %s */;;\n" + "/*!50003 SET sql_mode = @saved_sql_mode */;;\n" + "/*!50003 SET character_set_client = @saved_cs_client */;;\n" + "/*!50003 SET character_set_results = @saved_cs_results */;;\n" + "/*!50003 SET collation_connection = @saved_col_connection */;;\n" + "/*!50003 SET collation_database = @saved_col_database */;;\n", + (const char *) row[1], + (const char *) row[3], + (const char *) row[3], + (const char *) row[4], + (const char *) row[5], + (const char *) (query_str != NULL ? query_str : row[2])); my_free(query_str, MYF(MY_ALLOW_ZERO_PTR)); } @@ -2042,53 +2060,96 @@ safe_exit(EX_MYSQLERR); DBUG_VOID_RETURN; } - if (mysql_num_rows(result)) - fprintf(sql_file, "\n/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n\ -DELIMITER ;;\n"); + while ((row= mysql_fetch_row(result))) { - fprintf(sql_file, - "/*!50003 SET SESSION SQL_MODE=\"%s\" */;;\n" - "/*!50003 CREATE */ ", - row[6] /* sql_mode */); + MYSQL_RES *res2; + MYSQL_ROW row2; - if (mysql_num_fields(result) > 7) + my_snprintf(query_buff, sizeof (query_buff), + "SHOW CREATE TRIGGER %s", + quote_name(row[0], name_buff, TRUE)); + + if (mysql_query_with_error_report(mysql, &res2, query_buff)) { + if (path) + my_fclose(sql_file, MYF(MY_WME)); + safe_exit(EX_MYSQLERR); + DBUG_VOID_RETURN; + } + + while ((row= mysql_fetch_row(res2))) + { + char *query_str= NULL; + char *definer_begin; + /* - mysqldump can be run against the server, that does not support definer - in triggers (there is no DEFINER column in SHOW TRIGGERS output). So, - we should check if we have this column before accessing it. + Cover DEFINER-clause in version-specific comments. + + TODO: this is definitely a BAD IDEA to parse SHOW CREATE output. + However, we can not use INFORMATION_SCHEMA instead: + 1. INFORMATION_SCHEMA provides data in UTF8, but here we + need data in the original character set; + 2. INFORMATION_SCHEMA does not provide information about + routine parameters now. */ - uint user_name_len; - char user_name_str[USERNAME_LENGTH + 1]; - char quoted_user_name_str[USERNAME_LENGTH * 2 + 3]; - uint host_name_len; - char host_name_str[HOSTNAME_LENGTH + 1]; - char quoted_host_name_str[HOSTNAME_LENGTH * 2 + 3]; + definer_begin= strcasestr(row[2], " DEFINER"); - parse_user(row[7], strlen(row[7]), user_name_str, &user_name_len, - host_name_str, &host_name_len); + if (definer_begin) + { + char *definer_end= strcasestr(definer_begin, " TRIGGER"); + + if (definer_end) + { + char *query_str_tail; + + /* + Allocate memory for new query string: original string + from SHOW statement and version-specific comments. + */ + query_str= alloc_query_str(strlen(row[2]) + 23); + + query_str_tail= strnmov(query_str, row[2], + definer_begin - row[2]); + query_str_tail= strmov(query_str_tail, "*/ /*!50017"); + query_str_tail= strnmov(query_str_tail, definer_begin, + definer_end - definer_begin); + query_str_tail= strxmov(query_str_tail, "*/ /*!50003", + definer_end, NullS); + } + } fprintf(sql_file, - "/*!50017 DEFINER=%s@%s */ ", - quote_name(user_name_str, quoted_user_name_str, FALSE), - quote_name(host_name_str, quoted_host_name_str, FALSE)); - } + "/*!50003 SET @saved_sql_mode = @@sql_mode */;\n" + "/*!50003 SET @saved_cs_client = @@character_set_client */;\n" + "/*!50003 SET @saved_cs_results = @@character_set_results */;\n" + "/*!50003 SET @saved_col_connection = @@collation_connection */;\n" + "/*!50003 SET @saved_col_database = @@collation_database */;\n" + "/*!50003 SET sql_mode = '%s' */;\n" + "/*!50003 SET character_set_client = %s */;\n" + "/*!50003 SET character_set_results = %s */;\n" + "/*!50003 SET collation_connection = %s */;\n" + "/*!50003 SET collation_database = %s */;\n" + "DELIMITER ;;\n" + "/*!50003 %s */;;\n" + "DELIMITER ;\n" + "/*!50003 SET sql_mode = @saved_sql_mode */;\n" + "/*!50003 SET character_set_client = @saved_cs_client */;\n" + "/*!50003 SET character_set_results = @saved_cs_results */;\n" + "/*!50003 SET collation_connection = @saved_col_connection */;\n" + "/*!50003 SET collation_database = @saved_col_database */;\n", + (const char *) row[1], + (const char *) row[3], + (const char *) row[3], + (const char *) row[4], + (const char *) row[5], + (const char *) (query_str != NULL ? query_str : row[2])); - fprintf(sql_file, - "/*!50003 TRIGGER %s %s %s ON %s FOR EACH ROW%s%s */;;\n\n", - quote_name(row[0], name_buff, 0), /* Trigger */ - row[4], /* Timing */ - row[1], /* Event */ - result_table, - (strchr(" \t\n\r", *(row[3]))) ? "" : " ", - row[3] /* Statement */); + my_free(query_str, MYF(MY_ALLOW_ZERO_PTR)); + } + mysql_free_result(res2); } - if (mysql_num_rows(result)) - fprintf(sql_file, - "DELIMITER ;\n" - "/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n"); mysql_free_result(result); /* make sure to set back opt_compatible mode to @@ -3572,8 +3633,9 @@ my_snprintf(query, sizeof(query), - "SELECT CHECK_OPTION, DEFINER, SECURITY_TYPE " \ - "FROM information_schema.views " \ + "SELECT CHECK_OPTION, DEFINER, SECURITY_TYPE, " + " CLIENT_CS, CONNECTION_COL, DATABASE_COL " + "FROM information_schema.views " "WHERE table_name=\"%s\" AND table_schema=\"%s\"", table, db); if (mysql_query(mysql, query)) { @@ -3660,7 +3722,27 @@ } /* Dump view structure to file */ - fprintf(sql_file, "/*!50001 %s */;\n", ds_view.str); + + fprintf(sql_file, + "/*!50001 SET @saved_cs_client = @@character_set_client */;\n" + "/*!50001 SET @saved_cs_results = @@character_set_results */;\n" + "/*!50001 SET @saved_col_connection = @@collation_connection */;\n" + "/*!50001 SET @saved_col_database = @@collation_database */;\n" + "/*!50001 SET character_set_client = %s */;\n" + "/*!50001 SET character_set_results = %s */;\n" + "/*!50001 SET collation_connection = %s */;\n" + "/*!50001 SET collation_database = %s */;\n" + "/*!50001 %s */;\n" + "/*!50001 SET character_set_client = @saved_cs_client */;\n" + "/*!50001 SET character_set_results = @saved_cs_results */;\n" + "/*!50001 SET collation_connection = @saved_col_connection */;\n" + "/*!50001 SET collation_database = @saved_col_database */;\n", + (const char *) row[3], + (const char *) row[3], + (const char *) row[4], + (const char *) row[5], + (const char *) ds_view.str); + check_io(sql_file); mysql_free_result(table_res); dynstr_free(&ds_view); --- 1.429/sql/mysql_priv.h 2007-01-17 21:43:41 +03:00 +++ 1.430/sql/mysql_priv.h 2007-03-08 14:31:30 +03:00 @@ -1681,4 +1681,21 @@ #define check_stack_overrun(A, B, C) 0 #endif +bool get_trn_path_for_trigger(THD *thd, + const sp_name *trg_name, + LEX_STRING *trn_path); + +bool get_table_name_for_trigger(THD *thd, + const sp_name *trg_name, + const LEX_STRING *trn_path, + LEX_STRING *tbl_name); + +bool show_create_trigger(THD *thd, const sp_name *trg_name); + +inline void set_lex_string(LEX_STRING *lex_str, const char *c_str) +{ + lex_str->str= (char *) c_str; + lex_str->length= strlen(c_str); +} + #endif /* MYSQL_CLIENT */ --- 1.237/sql/sql_lex.h 2007-01-15 13:06:10 +03:00 +++ 1.238/sql/sql_lex.h 2007-03-08 14:31:30 +03:00 @@ -94,6 +94,7 @@ SQLCOM_XA_START, SQLCOM_XA_END, SQLCOM_XA_PREPARE, SQLCOM_XA_COMMIT, SQLCOM_XA_ROLLBACK, SQLCOM_XA_RECOVER, SQLCOM_SHOW_PROC_CODE, SQLCOM_SHOW_FUNC_CODE, + SQLCOM_SHOW_CREATE_TRIGGER, /* This should be the last !!! */ SQLCOM_END --- 1.601/sql/sql_parse.cc 2007-01-18 20:37:41 +03:00 +++ 1.602/sql/sql_parse.cc 2007-03-08 14:31:30 +03:00 @@ -4760,7 +4760,7 @@ my_error(ER_TOO_LONG_IDENT, MYF(0), lex->spname->m_name.str); goto error; } - if (sp_show_create_procedure(thd, lex->spname) != SP_OK) + if (sp_show_create_routine(thd, lex->spname, TYPE_ENUM_PROCEDURE) != SP_OK) { /* We don't distinguish between errors for now */ my_error(ER_SP_DOES_NOT_EXIST, MYF(0), SP_COM_STRING(lex), lex->spname->m_name.str); @@ -4775,7 +4775,7 @@ my_error(ER_TOO_LONG_IDENT, MYF(0), lex->spname->m_name.str); goto error; } - if (sp_show_create_function(thd, lex->spname) != SP_OK) + if (sp_show_create_routine(thd, lex->spname, TYPE_ENUM_FUNCTION) != SP_OK) { /* We don't distinguish between errors for now */ my_error(ER_SP_DOES_NOT_EXIST, MYF(0), SP_COM_STRING(lex), lex->spname->m_name.str); @@ -4822,6 +4822,19 @@ break; } #endif // ifndef DBUG_OFF + case SQLCOM_SHOW_CREATE_TRIGGER: + { + if (lex->spname->m_name.length > NAME_LEN) + { + my_error(ER_TOO_LONG_IDENT, MYF(0), lex->spname->m_name.str); + goto error; + } + + if (show_create_trigger(thd, lex->spname)) + goto error; /* Error has been already logged. */ + + break; + } case SQLCOM_CREATE_VIEW: { if (end_active_trans(thd)) --- 1.337/sql/sql_show.cc 2007-01-18 20:37:41 +03:00 +++ 1.338/sql/sql_show.cc 2007-03-08 14:31:30 +03:00 @@ -417,6 +417,10 @@ } buffer.length(0); + + if (table_list->view) + buffer.set_charset(table_list->view_def_ctx.client_cs); + if ((table_list->view ? view_store_create_info(thd, table_list, &buffer) : store_create_info(thd, table_list, &buffer))) @@ -428,6 +432,9 @@ field_list.push_back(new Item_empty_string("View",NAME_LEN)); field_list.push_back(new Item_empty_string("Create View", max(buffer.length(),1024))); + field_list.push_back(new Item_empty_string("Client_cs", MY_CS_NAME_SIZE)); + field_list.push_back(new Item_empty_string("Connection_col", MY_CS_NAME_SIZE)); + field_list.push_back(new Item_empty_string("Database_col", MY_CS_NAME_SIZE)); } else { @@ -451,10 +458,26 @@ else protocol->store(table_list->table->alias, system_charset_info); } - protocol->store(buffer.ptr(), buffer.length(), buffer.charset()); + + if (table_list->view) + { + protocol->store(buffer.ptr(), buffer.length(), &my_charset_bin); + + protocol->store(table_list->view_def_ctx.client_cs->csname, + system_charset_info); + + protocol->store(table_list->view_def_ctx.connection_cl->name, + system_charset_info); + + protocol->store(table_list->view_def_ctx.db_cl->name, + system_charset_info); + } + else + protocol->store(buffer.ptr(), buffer.length(), buffer.charset()); if (protocol->write()) DBUG_RETURN(TRUE); + send_eof(thd); DBUG_RETURN(FALSE); } @@ -690,7 +713,7 @@ if (q == EOF) { - packet->append(name, length, system_charset_info); + packet->append(name, length, packet->charset()); return; } @@ -708,7 +731,7 @@ uchar chr= (uchar) *name; length= my_mbcharlen(system_charset_info, chr); /* - my_mbcharlen can retur 0 on a wrong multibyte + my_mbcharlen can return 0 on a wrong multibyte sequence. It is possible when upgrading from 4.0, and identifier contains some accented characters. The manual says it does not work. So we'll just @@ -718,7 +741,7 @@ length= 1; if (length == 1 && chr == (uchar) quote_char) packet->append("e_char, 1, system_charset_info); - packet->append(name, length, packet->charset()); + packet->append(name, length, system_charset_info); } packet->append("e_char, 1, system_charset_info); } @@ -2025,6 +2048,7 @@ LEX *lex= thd->lex; *with_i_schema= 0; get_index_field_values(lex, idx_field_vals); + if (is_wild_value) { /* @@ -2245,6 +2269,11 @@ } if (lower_case_table_names) orig_base_name= thd->strdup(base_name); + + /* + In the call to make_table_list() below base_name can be changed + lower case if lower_case_table_names is set. + */ } List_iterator_fast<char> it_files(files); @@ -2974,6 +3003,16 @@ get_field(thd->mem_root, proc_table->field[15], &tmp_string); table->field[18]->store(tmp_string.ptr(), tmp_string.length(), cs); table->field[19]->store(definer.ptr(), definer.length(), cs); + + get_field(thd->mem_root, proc_table->field[16], &tmp_string); + table->field[20]->store(tmp_string.ptr(), tmp_string.length(), cs); + + get_field(thd->mem_root, proc_table->field[17], &tmp_string); + table->field[21]->store(tmp_string.ptr(), tmp_string.length(), cs); + + get_field(thd->mem_root, proc_table->field[18], &tmp_string); + table->field[22]->store(tmp_string.ptr(), tmp_string.length(), cs); + return schema_table_store_record(thd, table); } } @@ -3181,6 +3220,19 @@ table->field[7]->store(STRING_WITH_LEN("DEFINER"), cs); else table->field[7]->store(STRING_WITH_LEN("INVOKER"), cs); + + table->field[8]->store(tables->view_def_ctx.client_cs->csname, + strlen(tables->view_def_ctx.client_cs->csname), + cs); + + table->field[9]->store(tables->view_def_ctx.connection_cl->name, + strlen(tables->view_def_ctx.connection_cl->name), + cs); + + table->field[10]->store(tables->view_def_ctx.db_cl->name, + strlen(tables->view_def_ctx.db_cl->name), + cs); + if (schema_table_store_record(thd, table)) DBUG_RETURN(1); if (res) @@ -3274,7 +3326,10 @@ enum trg_action_time_type timing, LEX_STRING *trigger_stmt, ulong sql_mode, - LEX_STRING *definer_buffer) + LEX_STRING *definer_buffer, + LEX_STRING *client_cs_name, + LEX_STRING *connection_cl_name, + LEX_STRING *db_cl_name) { CHARSET_INFO *cs= system_charset_info; byte *sql_mode_str; @@ -3299,7 +3354,13 @@ sql_mode, &sql_mode_len); table->field[17]->store((const char*)sql_mode_str, sql_mode_len, cs); - table->field[18]->store((const char *)definer_buffer->str, definer_buffer->length, cs); + table->field[18]->store((const char *)definer_buffer->str, + definer_buffer->length, cs); + table->field[19]->store(client_cs_name->str, client_cs_name->length, cs); + table->field[20]->store(connection_cl_name->str, + connection_cl_name->length, cs); + table->field[21]->store(db_cl_name->str, db_cl_name->length, cs); + return schema_table_store_record(thd, table); } @@ -3335,19 +3396,29 @@ ulong sql_mode; char definer_holder[USER_HOST_BUFF_SIZE]; LEX_STRING definer_buffer; + LEX_STRING client_cs_name; + LEX_STRING connection_cl_name; + LEX_STRING db_cl_name; + definer_buffer.str= definer_holder; if (triggers->get_trigger_info(thd, (enum trg_event_type) event, (enum trg_action_time_type)timing, &trigger_name, &trigger_stmt, &sql_mode, - &definer_buffer)) + &definer_buffer, + &client_cs_name, + &connection_cl_name, + &db_cl_name)) continue; if (store_trigger(thd, table, base_name, file_name, &trigger_name, (enum trg_event_type) event, (enum trg_action_time_type) timing, &trigger_stmt, sql_mode, - &definer_buffer)) + &definer_buffer, + &client_cs_name, + &connection_cl_name, + &db_cl_name)) DBUG_RETURN(1); } } @@ -3785,7 +3856,7 @@ int make_proc_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table) { - int fields_arr[]= {2, 3, 4, 19, 16, 15, 14, 18, -1}; + int fields_arr[]= {2, 3, 4, 19, 16, 15, 14, 18, 20, 21, 22, -1}; int *field_num= fields_arr; ST_FIELD_INFO *field_info; Name_resolution_context *context= &thd->lex->select_lex.context; @@ -4113,6 +4184,9 @@ {"SQL_MODE", 65535, MYSQL_TYPE_STRING, 0, 0, 0}, {"ROUTINE_COMMENT", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Comment"}, {"DEFINER", 77, MYSQL_TYPE_STRING, 0, 0, "Definer"}, + {"CLIENT_CS", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, "Client_cs"}, + {"CONNECTION_COL", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, "Connection_col"}, + {"DATABASE_COL", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, "Database_col"}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} }; @@ -4148,6 +4222,9 @@ {"IS_UPDATABLE", 3, MYSQL_TYPE_STRING, 0, 0, 0}, {"DEFINER", 77, MYSQL_TYPE_STRING, 0, 0, 0}, {"SECURITY_TYPE", 7, MYSQL_TYPE_STRING, 0, 0, 0}, + {"CLIENT_CS", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, 0}, + {"CONNECTION_COL", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, 0}, + {"DATABASE_COL", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, 0}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} }; @@ -4269,6 +4346,9 @@ {"CREATED", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Created"}, {"SQL_MODE", 65535, MYSQL_TYPE_STRING, 0, 0, "sql_mode"}, {"DEFINER", 65535, MYSQL_TYPE_STRING, 0, 0, "Definer"}, + {"CLIENT_CS", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, "Client_cs"}, + {"CONNECTION_COL", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, "Connection_col"}, + {"DATABASE_COL", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, "Database_col"}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} }; @@ -4335,3 +4415,204 @@ template class List_iterator_fast<char>; template class List<char>; #endif + + +static bool show_create_trigger(THD *thd, + Table_triggers_list *triggers, + int trigger_idx) +{ + int ret_code; + + Protocol *p= thd->protocol; + List<Item> fields; + + LEX_STRING trg_name; + ulonglong trg_sql_mode; + char *trg_sql_mode_str; + ulong trg_sql_mode_length; + LEX_STRING trg_sql_original_stmt; + LEX_STRING trg_client_cs_name; + LEX_STRING trg_connection_cl_name; + LEX_STRING trg_db_cl_name; + + /* + TODO: Check privileges here. This functionality will be added by + implementation of the following WL items: + - WL#2227: New privileges for new objects + - WL#3482: Protect SHOW CREATE PROCEDURE | FUNCTION | VIEW | TRIGGER + properly + + SHOW TRIGGERS and I_S.TRIGGERS will be affected too. + */ + + /* Prepare trigger "object". */ + + triggers->get_trigger_info(thd, + trigger_idx, + &trg_name, + &trg_sql_mode, + &trg_sql_original_stmt, + &trg_client_cs_name, + &trg_connection_cl_name, + &trg_db_cl_name); + + trg_sql_mode_str= + (char *) sys_var_thd_sql_mode::symbolic_mode_representation( + thd, trg_sql_mode, &trg_sql_mode_length); + + /* Send header. */ + + fields.push_back(new Item_empty_string("Trigger", NAME_LEN)); + fields.push_back(new Item_empty_string("sql_mode", trg_sql_mode_length)); + + { + /* + NOTE: SQL statement field must be not less than 1024 in order not to + confuse old clients. + */ + + Item_empty_string *stmt_fld= + new Item_empty_string("SQL Original Statement", + max(trg_sql_original_stmt.length, 1024)); + + stmt_fld->maybe_null= TRUE; + + fields.push_back(stmt_fld); + } + + fields.push_back(new Item_empty_string("Client_cs", MY_CS_NAME_SIZE)); + fields.push_back(new Item_empty_string("Connection_col", MY_CS_NAME_SIZE)); + fields.push_back(new Item_empty_string("Database_col", MY_CS_NAME_SIZE)); + + if (p->send_fields(&fields, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) + return TRUE; + + /* Send data. */ + + p->prepare_for_resend(); + + p->store(trg_name.str, + trg_name.length, + system_charset_info); + + p->store(trg_sql_mode_str, + trg_sql_mode_length, + system_charset_info); + + p->store(trg_sql_original_stmt.str, + trg_sql_original_stmt.length, + &my_charset_bin); + + p->store(trg_client_cs_name.str, + trg_client_cs_name.length, + system_charset_info); + + p->store(trg_connection_cl_name.str, + trg_connection_cl_name.length, + system_charset_info); + + p->store(trg_db_cl_name.str, + trg_db_cl_name.length, + system_charset_info); + + ret_code= p->write(); + + if (!ret_code) + send_eof(thd); + + return ret_code != 0; +} + + +bool show_create_trigger(THD *thd, const sp_name *trg_name) +{ + LEX_STRING tbl_name; + + char trn_path_buff[FN_REFLEN]; + LEX_STRING trn_path= { trn_path_buff, 0 }; + + if (get_trn_path_for_trigger(thd, trg_name, &trn_path)) + { + my_error(ER_TRG_DOES_NOT_EXIST, MYF(0)); + return TRUE; + } + + if (get_table_name_for_trigger(thd, trg_name, &trn_path, &tbl_name)) + return TRUE; /* Error has been already logged. */ + + LEX *lex= thd->lex; + enum_sql_command original_sql_command= lex->sql_command; + + SELECT_LEX select_lex; + TABLE_LIST *table_lst; /* = select_lex.table_list.first */ + + bool err_status; + + /* + NOTE: Set the "parent_lex" of "select_lex" because it is needed by + SELECT_LEX::init_query() which is called inside make_table_list(). + */ + + select_lex.parent_lex= lex; + + if (make_table_list(thd, &select_lex, trg_name->m_db.str, tbl_name.str)) + return TRUE; /* Error has been already logged. */ + + table_lst= (TABLE_LIST*) select_lex.table_list.first; + + lex->all_selects_list= &select_lex; + lex->derived_tables= 0; + lex->sql_command= SQLCOM_SHOW_FIELDS; + + err_status= + open_normal_and_derived_tables(thd, table_lst, MYSQL_LOCK_IGNORE_FLUSH); + + lex->sql_command= original_sql_command; + + if (err_status) + { + /* + This means that we just could not open trigger table. + Probably database "mysql" is inconsistent. + */ + + my_error(ER_TRG_CANT_OPEN_TABLE, MYF(0), + (const char *) trg_name->m_db.str, + (const char *) tbl_name.str); + + /* Perform closing actions and return error status. */ + } + else if (table_lst->table->triggers) + { + int trigger_idx= + table_lst->table->triggers->find_trigger_by_name(&trg_name->m_name); + + if (trigger_idx < 0) + { + /* + This means that either TRG-file has not been loaded correctly, or + mysql-database is inconsistent -- there is TRN-file, but there is no + information about the trigger in TRG-file. + */ + + my_error(ER_TRG_CORRUPTED_FILE, MYF(0), + (const char *) trg_name->m_db.str, + (const char *) tbl_name.str); + + return TRUE; + } + + err_status= show_create_trigger(thd, table_lst->table->triggers, trigger_idx); + + /* + NOTE: if show_create_trigger() failed, that means we could not send + data to the client. In this case we simply raise the error status and + client connection will be closed. + */ + } + + close_tables_for_reopen(thd, &table_lst); + DBUG_ASSERT(!lex->query_tables_own_last); + + return err_status; +} --- 1.503/sql/sql_yacc.yy 2007-01-19 04:37:49 +03:00 +++ 1.504/sql/sql_yacc.yy 2007-03-08 14:31:30 +03:00 @@ -6961,6 +6961,12 @@ Lex->spname= $3; #endif } + | CREATE TRIGGER_SYM sp_name + { + LEX *lex= Lex; + lex->sql_command= SQLCOM_SHOW_CREATE_TRIGGER; + lex->spname= $3; + } ; show_engine_param: --- 1.241/sql/table.cc 2007-01-25 00:22:47 +03:00 +++ 1.242/sql/table.cc 2007-03-08 14:31:30 +03:00 @@ -3071,3 +3071,25 @@ template class List<String>; template class List_iterator<String>; #endif + + +/***************************************************************************** + Query charset context. +*****************************************************************************/ + +bool switch_query_ctx(THD *thd, const st_query_ctx *ctx) +{ + thd->variables.character_set_client= ctx->client_cs; + thd->variables.collation_connection= ctx->connection_cl; + thd->variables.collation_database= ctx->db_cl; + + thd->update_charset(); +} + + +bool save_current_query_ctx(THD *thd, st_query_ctx *ctx) +{ + ctx->client_cs= thd->variables.character_set_client; + ctx->connection_cl= thd->variables.collation_connection; + ctx->db_cl= thd->variables.collation_database; +} --- 1.137/sql/table.h 2006-12-30 23:02:07 +03:00 +++ 1.138/sql/table.h 2007-03-08 14:31:30 +03:00 @@ -24,6 +24,30 @@ class COND_EQUAL; class Security_context; +/** + @brief Query charset context. It contains the character sets/collations + used for parsing and executing query. +*/ + +struct st_query_ctx +{ + /* + The following fields use only character set attribute. + */ + + CHARSET_INFO *client_cs; + + /* + The following fields use both character set and collation attributes. + */ + + CHARSET_INFO *connection_cl; + CHARSET_INFO *db_cl; +}; + +bool switch_query_ctx(THD *thd, const st_query_ctx *ctx); +bool save_current_query_ctx(THD *thd, st_query_ctx *ctx); + /* Order clause list element */ typedef struct st_order { @@ -637,6 +661,25 @@ used for implicit LOCK TABLES only and won't be used in real statement. */ bool prelocking_placeholder; + + /* View definition query context. */ + + st_query_ctx view_def_ctx; + + /* + View definition query context names. Ther are required only to be able + to use existing parser to load view-definition file. As soon as the + parser parsed the file, view definition context is initialized and + the names become redundant. + + These attributes MUST NOT be used for any purposes but the parsing. + */ + + LEX_STRING view_client_cs_name; + LEX_STRING view_connection_cl_name; + LEX_STRING view_db_cl_name; + + /* End of character ser context. */ void calc_md5(char *buffer); void set_underlying_merge(); --- 1.75/sql/share/errmsg.txt 2006-11-13 19:06:41 +03:00 +++ 1.76/sql/share/errmsg.txt 2007-03-08 14:31:30 +03:00 @@ -5634,3 +5634,32 @@ ER_NON_INSERTABLE_TABLE eng "The target table %-.100s of the %s is not insertable-into" +ER_VIEW_NO_DEF_CONTEXT + eng "View `%-.64s`.`%-.64s` has no definition context (character set information). Non-ascii symbols can be lost. Set appropriate character set and collation manually and recreate the view" +ER_VIEW_UNKNOWN_CLIENT_CHARSET + eng "Unknown client character set (%-.32s) for view `%-.64s`.`%-.64s'" +ER_VIEW_UNKNOWN_CONNECTION_CHARSET + eng "Unknown connection character set (%-.32s) for view `%-.64s`.`%-.64s'" +ER_VIEW_UNKNOWN_DB_CHARSET + eng "Unknown server character set (%-.32s) for view `%-.64s`.`%-.64s'" + +ER_SR_NO_DEF_CONTEXT + eng "Stored routine `%-.64s`.`%-.64s` has no definition context (field `%-.64s` is NULL). Non-ascii symbols can be lost. Set appropriate character set and collation manually and recreate the routine" +ER_SR_INVALID_CHARSET + eng "Invalid character set (%-.32s) in field `%-.64s` for stored routine `%-.64s`.`%-.64s`" +ER_SR_UNKNOWN_CHARSET + eng "Unknown character set (%-.32s) in field `%-.64s` for stored routine `%-.64s`.`%-.64s`" + +ER_TRG_CORRUPTED_FILE + eng "Corrupted TRG-file for table `%-.64s`.`%-.64s`" +ER_TRG_NO_DEF_CONTEXT + eng "Triggers for table `%-.64s`.`%-.64s` has no definition context (character set information). Default character set is used. Non-ascii symbols can be lost. Set appropriate character set and collation manually and recreate all triggers for the table" +ER_TRG_UNKNOWN_CLIENT_CHARSET + eng "Unknown client character set `%-.32s` in TRG file for table `%-.64s`.`%-.64s`" +ER_TRG_UNKNOWN_CONNECTION_CHARSET + eng "Unknown connection character set `%-.32s` in TRG file for table `%-.64s`.`%-.64s`" +ER_TRG_UNKNOWN_DB_CHARSET + eng "Unknown database character set `%-.32s` in TRG file for table `%-.64s`.`%-.64s`" + +ER_TRG_CANT_OPEN_TABLE + eng "Can not open table for trigger `%-.64s`.`%-.64s`" --- 1.4/mysql-test/r/trigger-compat.result 2006-05-12 20:58:49 +04:00 +++ 1.5/mysql-test/r/trigger-compat.result 2007-03-08 14:31:30 +03:00 @@ -33,9 +33,9 @@ Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER -NULL mysqltest_db1 wl2818_trg1 INSERT NULL mysqltest_db1 t1 0 NULL INSERT INTO t2 VALUES(CURRENT_USER()) ROW BEFORE NULL NULL OLD NEW NULL -NULL mysqltest_db1 wl2818_trg2 INSERT NULL mysqltest_db1 t1 0 NULL INSERT INTO t2 VALUES(CURRENT_USER()) ROW AFTER NULL NULL OLD NEW NULL mysqltest_dfn@localhost +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CLIENT_CS CONNECTION_COL DATABASE_COL +NULL mysqltest_db1 wl2818_trg1 INSERT NULL mysqltest_db1 t1 0 NULL INSERT INTO t2 VALUES(CURRENT_USER()) ROW BEFORE NULL NULL OLD NEW NULL latin1 latin1_swedish_ci latin1_swedish_ci +NULL mysqltest_db1 wl2818_trg2 INSERT NULL mysqltest_db1 t1 0 NULL INSERT INTO t2 VALUES(CURRENT_USER()) ROW AFTER NULL NULL OLD NEW NULL mysqltest_dfn@localhost latin1 latin1_swedish_ci latin1_swedish_ci DROP TRIGGER wl2818_trg1; Warnings: Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. --- 1.6/mysql-test/r/trigger-grant.result 2006-05-15 00:51:00 +04:00 +++ 1.7/mysql-test/r/trigger-grant.result 2007-03-08 14:31:30 +03:00 @@ -81,9 +81,9 @@ INSERT INTO t1 VALUES(6); ERROR 42000: Access denied; you need the SUPER privilege for this operation SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode Definer -trg1 INSERT t1 SET @new_sum = 0 BEFORE NULL mysqltest_inv@localhost -trg2 INSERT t1 SET @new_sum = 0 AFTER NULL mysqltest_nonexs@localhost +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col +trg1 INSERT t1 SET @new_sum = 0 BEFORE NULL mysqltest_inv@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg2 INSERT t1 SET @new_sum = 0 AFTER NULL mysqltest_nonexs@localhost latin1 latin1_swedish_ci latin1_swedish_ci DROP TRIGGER trg1; DROP TRIGGER trg2; CREATE TRIGGER trg1 BEFORE INSERT ON t1 @@ -113,12 +113,12 @@ Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER -NULL mysqltest_db1 trg1 INSERT NULL mysqltest_db1 t1 0 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW NULL -NULL mysqltest_db1 trg2 INSERT NULL mysqltest_db1 t1 0 NULL SET @a = 2 ROW AFTER NULL NULL OLD NEW NULL @ -NULL mysqltest_db1 trg3 UPDATE NULL mysqltest_db1 t1 0 NULL SET @a = 3 ROW BEFORE NULL NULL OLD NEW NULL @abc@def@@ -NULL mysqltest_db1 trg4 UPDATE NULL mysqltest_db1 t1 0 NULL SET @a = 4 ROW AFTER NULL NULL OLD NEW NULL @hostname -NULL mysqltest_db1 trg5 DELETE NULL mysqltest_db1 t1 0 NULL SET @a = 5 ROW BEFORE NULL NULL OLD NEW NULL @abcdef@@@hostname +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CLIENT_CS CONNECTION_COL DATABASE_COL +NULL mysqltest_db1 trg1 INSERT NULL mysqltest_db1 t1 0 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW NULL latin1 latin1_swedish_ci latin1_swedish_ci +NULL mysqltest_db1 trg2 INSERT NULL mysqltest_db1 t1 0 NULL SET @a = 2 ROW AFTER NULL NULL OLD NEW NULL @ latin1 latin1_swedish_ci latin1_swedish_ci +NULL mysqltest_db1 trg3 UPDATE NULL mysqltest_db1 t1 0 NULL SET @a = 3 ROW BEFORE NULL NULL OLD NEW NULL @abc@def@@ latin1 latin1_swedish_ci latin1_swedish_ci +NULL mysqltest_db1 trg4 UPDATE NULL mysqltest_db1 t1 0 NULL SET @a = 4 ROW AFTER NULL NULL OLD NEW NULL @hostname latin1 latin1_swedish_ci latin1_swedish_ci +NULL mysqltest_db1 trg5 DELETE NULL mysqltest_db1 t1 0 NULL SET @a = 5 ROW BEFORE NULL NULL OLD NEW NULL @abcdef@@@hostname latin1 latin1_swedish_ci latin1_swedish_ci ---> connection: default DROP USER mysqltest_dfn@localhost; --- 1.11/mysql-test/r/lowercase_view.result 2005-09-14 13:24:10 +04:00 +++ 1.12/mysql-test/r/lowercase_view.result 2007-03-08 14:31:29 +03:00 @@ -6,8 +6,8 @@ create table TaB (Field int); create view ViE as select * from TAb; show create table VIe; -View Create View -vie CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vie` AS select `tab`.`Field` AS `Field` from `tab` +View Create View Client_cs Connection_col Database_col +vie CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vie` AS select `tab`.`Field` AS `Field` from `tab` latin1 latin1_swedish_ci latin1_swedish_ci drop database MySQLTest; use test; create table t1Aa (col1 int); @@ -118,8 +118,8 @@ create table t1Aa (col1 int); create view v1Aa as select col1 from t1Aa as AaA; show create view v1AA; -View Create View -v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA` +View Create View Client_cs Connection_col Database_col +v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1AA; select Aaa.col1 from t1Aa as AaA; col1 @@ -127,7 +127,7 @@ drop view v1AA; create view v1Aa as select AaA.col1 from t1Aa as AaA; show create view v1AA; -View Create View -v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA` +View Create View Client_cs Connection_col Database_col +v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1AA; drop table t1Aa; --- 1.8/mysql-test/r/rpl_view.result 2006-11-03 14:27:32 +03:00 +++ 1.9/mysql-test/r/rpl_view.result 2007-03-08 14:31:29 +03:00 @@ -99,8 +99,8 @@ a int(11) YES NULL b decimal(32,0) YES NULL show create table v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,sum(`t1`.`b`) AS `b` from `t1` group by `t1`.`a` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,sum(`t1`.`b`) AS `b` from `t1` group by `t1`.`a` latin1 latin1_swedish_ci latin1_swedish_ci select * from v1; a b 1 6 --- 1.186/mysql-test/r/view.result 2007-01-11 21:52:55 +03:00 +++ 1.187/mysql-test/r/view.result 2007-03-08 14:31:30 +03:00 @@ -34,11 +34,11 @@ 6 11 show create table v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci show create view t1; ERROR HY000: 'test.t1' is not VIEW drop table t1; @@ -57,8 +57,8 @@ Note 1003 select (`test`.`t1`.`b` + 1) AS `c` from `test`.`t1` create algorithm=temptable view v2 (c) as select b+1 from t1; show create view v2; -View Create View -v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t1`.`b` + 1) AS `c` from `t1` +View Create View Client_cs Connection_col Database_col +v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci select c from v2; c 3 @@ -567,8 +567,8 @@ create table t1 ("a*b" int); create view v1 as select "a*b" from t1; show create view v1; -View Create View -v1 CREATE VIEW "v1" AS select "t1"."a*b" AS "a*b" from "t1" +View Create View Client_cs Connection_col Database_col +v1 CREATE VIEW "v1" AS select "t1"."a*b" AS "a*b" from "t1" latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; drop table t1; set sql_mode=default; @@ -669,8 +669,8 @@ drop table t1; CREATE VIEW v1 (f1,f2,f3,f4) AS SELECT connection_id(), pi(), current_user(), version(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select connection_id() AS `f1`,pi() AS `f2`,current_user() AS `f3`,version() AS `f4` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select connection_id() AS `f1`,pi() AS `f2`,current_user() AS `f3`,version() AS `f4` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; create table t1 (s1 int); create table t2 (s2 int); @@ -703,14 +703,14 @@ create view v1 as select a from t1; create view v2 as select a from t2 where a in (select a from v1); show create view v2; -View Create View -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where `t2`.`a` in (select `v1`.`a` AS `a` from `v1`) +View Create View Client_cs Connection_col Database_col +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where `t2`.`a` in (select `v1`.`a` AS `a` from `v1`) latin1 latin1_swedish_ci latin1_swedish_ci drop view v2, v1; drop table t1, t2; CREATE VIEW `v 1` AS select 5 AS `5`; show create view `v 1`; -View Create View -v 1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v 1` AS select 5 AS `5` +View Create View Client_cs Connection_col Database_col +v 1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v 1` AS select 5 AS `5` latin1 latin1_swedish_ci latin1_swedish_ci drop view `v 1`; create database mysqltest; create table mysqltest.t1 (a int, b int); @@ -777,15 +777,15 @@ a b 1 1 show create view v3; -View Create View -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `v1`.`col1` AS `a`,`v2`.`col1` AS `b` from (`v1` join `v2`) where (`v1`.`col1` = `v2`.`col1`) +View Create View Client_cs Connection_col Database_col +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `v1`.`col1` AS `a`,`v2`.`col1` AS `b` from (`v1` join `v2`) where (`v1`.`col1` = `v2`.`col1`) latin1 latin1_swedish_ci latin1_swedish_ci drop view v3, v2, v1; drop table t2, t1; create function `f``1` () returns int return 5; create view v1 as select test.`f``1` (); show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`f``1`() AS `test.``f````1`` ()` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`f``1`() AS `test.``f````1`` ()` latin1 latin1_swedish_ci latin1_swedish_ci select * from v1; test.`f``1` () 5 @@ -801,11 +801,11 @@ create table t2 (col1 char collate latin1_german2_ci); create view v2 as select col1 collate latin1_german1_ci from t2; show create view v2; -View Create View -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t2`.`col1` collate latin1_german1_ci) AS `col1 collate latin1_german1_ci` from `t2` +View Create View Client_cs Connection_col Database_col +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t2`.`col1` collate latin1_german1_ci) AS `col1 collate latin1_german1_ci` from `t2` latin1 latin1_swedish_ci latin1_swedish_ci show create view v2; -View Create View -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t2`.`col1` collate latin1_german1_ci) AS `col1 collate latin1_german1_ci` from `t2` +View Create View Client_cs Connection_col Database_col +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t2`.`col1` collate latin1_german1_ci) AS `col1 collate latin1_german1_ci` from `t2` latin1 latin1_swedish_ci latin1_swedish_ci drop view v2; drop table t2; create table t1 (a int); @@ -831,17 +831,24 @@ drop table t1; create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 99999999999999999999999999999999999999999999999999999 AS `col1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 99999999999999999999999999999999999999999999999999999 AS `col1` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; -create table t (c char); -create view v as select c from t; -insert into v values (''); -select * from v; -c - -drop view v; -drop table t; +SET @old_cs_client = @@character_set_client; +SET @old_cs_results = @@character_set_results; +SET @old_cs_connection = @@character_set_connection; +SET names utf8; +create table tü (cü char); +create view vü as select cü from tü; +insert into vü values ('ü'); +select * from vü; +cü +ü +drop view vü; +drop table tü; +SET character_set_client = @old_cs_client; +SET character_set_results = @old_cs_results; +SET character_set_connection = @old_cs_connection; create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); create view v1(c) as select a+1 from t1 where b >= 4; @@ -852,8 +859,8 @@ drop table t1; create view v1 as select cast(1 as char(3)); show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1 as char(3) charset latin1) AS `cast(1 as char(3))` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1 as char(3) charset latin1) AS `cast(1 as char(3))` latin1 latin1_swedish_ci latin1_swedish_ci select * from v1; cast(1 as char(3)) 1 @@ -1183,20 +1190,20 @@ create table t1 (a int); create view v1 as select * from t1; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci alter algorithm=undefined view v1 as select * from t1 with check option; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` WITH CASCADED CHECK OPTION +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` WITH CASCADED CHECK OPTION latin1 latin1_swedish_ci latin1_swedish_ci alter algorithm=merge view v1 as select * from t1 with cascaded check option; show create view v1; -View Create View -v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` WITH CASCADED CHECK OPTION +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` WITH CASCADED CHECK OPTION latin1 latin1_swedish_ci latin1_swedish_ci alter algorithm=temptable view v1 as select * from t1; show create view v1; -View Create View -v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; drop table t1; create table t1 (s1 int); @@ -1867,25 +1874,25 @@ create table t2 (b timestamp default now()); create view v1 as select a,b,t1.a < now() from t1,t2 where t1.a < now(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,(`t1`.`a` < now()) AS `t1.a < now()` from (`t1` join `t2`) where (`t1`.`a` < now()) +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,(`t1`.`a` < now()) AS `t1.a < now()` from (`t1` join `t2`) where (`t1`.`a` < now()) latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; drop table t1, t2; CREATE TABLE t1 ( a varchar(50) ); CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = CURRENT_USER(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = current_user()) +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = current_user()) latin1 latin1_swedish_ci latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = VERSION(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = version()) +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = version()) latin1 latin1_swedish_ci latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = DATABASE(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = database()) +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = database()) latin1 latin1_swedish_ci latin1_swedish_ci DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (col1 time); @@ -1975,8 +1982,8 @@ create table t1 (s1 int); create view v1 as select var_samp(s1) from t1; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select var_samp(`t1`.`s1`) AS `var_samp(s1)` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select var_samp(`t1`.`s1`) AS `var_samp(s1)` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; drop table t1; set sql_mode='strict_all_tables'; @@ -2232,16 +2239,16 @@ INSERT INTO t1 VALUES ('2005-09-06'); CREATE VIEW v1 AS SELECT DAYNAME(date) FROM t1; SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select dayname(`t1`.`date`) AS `DAYNAME(date)` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select dayname(`t1`.`date`) AS `DAYNAME(date)` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci CREATE VIEW v2 AS SELECT DAYOFWEEK(date) FROM t1; SHOW CREATE VIEW v2; -View Create View -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select dayofweek(`t1`.`date`) AS `DAYOFWEEK(date)` from `t1` +View Create View Client_cs Connection_col Database_col +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select dayofweek(`t1`.`date`) AS `DAYOFWEEK(date)` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci CREATE VIEW v3 AS SELECT WEEKDAY(date) FROM t1; SHOW CREATE VIEW v3; -View Create View -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select weekday(`t1`.`date`) AS `WEEKDAY(date)` from `t1` +View Create View Client_cs Connection_col Database_col +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select weekday(`t1`.`date`) AS `WEEKDAY(date)` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci SELECT DAYNAME('2005-09-06'); DAYNAME('2005-09-06') Tuesday @@ -2396,13 +2403,13 @@ DROP VIEW v1; create definer = current_user() sql security invoker view v1 as select 1; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; create definer = current_user sql security invoker view v1 as select 1; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; create table t1 (id INT, primary key(id)); insert into t1 values (1),(2); @@ -2431,8 +2438,8 @@ // call p1(); show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; drop procedure p1; CREATE VIEW v1 AS SELECT 42 AS Meaning; @@ -2537,8 +2544,8 @@ show create view v1; drop view v1; // -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`id` AS `id` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`id` AS `id` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci create table t1(f1 int, f2 int); create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb .f1 and ta.f2=tb.f2; @@ -2654,8 +2661,8 @@ SELECT id, date(d) + INTERVAL TIME_TO_SEC(d) SECOND AS t, COUNT(*) FROM t1 GROUP BY id, t; SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id`,(cast(`t1`.`d` as date) + interval time_to_sec(`t1`.`d`) second) AS `t`,count(0) AS `COUNT(*)` from `t1` group by `t1`.`id`,(cast(`t1`.`d` as date) + interval time_to_sec(`t1`.`d`) second) +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id`,(cast(`t1`.`d` as date) + interval time_to_sec(`t1`.`d`) second) AS `t`,count(0) AS `COUNT(*)` from `t1` group by `t1`.`id`,(cast(`t1`.`d` as date) + interval time_to_sec(`t1`.`d`) second) latin1 latin1_swedish_ci latin1_swedish_ci SELECT * FROM v1; id t COUNT(*) DROP VIEW v1; @@ -2682,8 +2689,8 @@ SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(now()) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75) +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(now()) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75) latin1 latin1_swedish_ci latin1_swedish_ci SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; Age 43 @@ -2810,12 +2817,12 @@ CREATE TABLE t1 (x INT, y INT); CREATE ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci ALTER VIEW v1 AS SELECT x, y FROM t1; SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x`,`t1`.`y` AS `y` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x`,`t1`.`y` AS `y` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (s1 char); @@ -2875,8 +2882,8 @@ create table t1 (f1 datetime); create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` where (`t1`.`f1` between now() and (now() + interval 1 minute)) +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` where (`t1`.`f1` between now() and (now() + interval 1 minute)) latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; drop table t1; DROP TABLE IF EXISTS t1; @@ -2952,8 +2959,8 @@ SHOW WARNINGS; Level Code Message SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) AS `MAX(t.ver)` from `t2` `t` where (`t`.`org` = `t2`.`org`)))))) +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) AS `MAX(t.ver)` from `t2` `t` where (`t`.`org` = `t2`.`org`)))))) latin1 latin1_swedish_ci latin1_swedish_ci DROP VIEW v1; DROP TABLE t1, t2; DROP FUNCTION IF EXISTS f1; @@ -3020,7 +3027,7 @@ TheEnd TheEnd SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd` latin1 latin1_swedish_ci latin1_swedish_ci DROP VIEW v1; End of 5.0 tests. --- 1.9/mysql-test/r/skip_grants.result 2006-03-10 14:40:12 +03:00 +++ 1.10/mysql-test/r/skip_grants.result 2007-03-08 14:31:29 +03:00 @@ -35,16 +35,16 @@ CREATE DEFINER=a@'' FUNCTION f3() RETURNS INT RETURN 3; SHOW CREATE VIEW v3; -View Create View -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`a`@`` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`c` AS `c` from `t1` +View Create View Client_cs Connection_col Database_col +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`a`@`` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`c` AS `c` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE PROCEDURE p3; -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col p3 CREATE DEFINER=`a`@`` PROCEDURE `p3`() -SELECT 3 +SELECT 3 latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE FUNCTION f3; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col f3 CREATE DEFINER=`a`@`` FUNCTION `f3`() RETURNS int(11) -RETURN 3 +RETURN 3 latin1 latin1_swedish_ci latin1_swedish_ci DROP TRIGGER t1_bi; DROP TRIGGER ti_ai; DROP TRIGGER ti_bu; --- 1.171/mysql-test/t/view.test 2006-12-19 15:32:01 +03:00 +++ 1.172/mysql-test/t/view.test 2007-03-08 14:31:30 +03:00 @@ -748,12 +748,23 @@ # # VIEWs with national characters # -create table t (c char); -create view v as select c from t; -insert into v values (''); -select * from v; -drop view v; -drop table t; + +SET @old_cs_client = @@character_set_client; +SET @old_cs_results = @@character_set_results; +SET @old_cs_connection = @@character_set_connection; + +SET names utf8; + +create table tü (cü char); +create view vü as select cü from tü; +insert into vü values ('ü'); +select * from vü; +drop view vü; +drop table tü; + +SET character_set_client = @old_cs_client; +SET character_set_results = @old_cs_results; +SET character_set_connection = @old_cs_connection; # # problem with used_tables() of outer reference resolved in VIEW @@ -2974,5 +2985,6 @@ SHOW CREATE VIEW v1; DROP VIEW v1; + --echo End of 5.0 tests. --- 1.102/sql/sql_view.cc 2007-01-18 13:04:22 +03:00 +++ 1.103/sql/sql_view.cc 2007-03-08 14:31:30 +03:00 @@ -597,8 +597,8 @@ /* index of revision number in following table */ static const int revision_number_position= 8; -/* index of last required parameter for making view */ -static const int required_view_parameters= 10; +/* number of required parameters for making view */ +static const int required_view_parameters= 15; /* number of backups */ static const int num_view_backups= 3; @@ -645,6 +645,15 @@ {{(char*) STRING_WITH_LEN("source")}, my_offsetof(TABLE_LIST, source), FILE_OPTIONS_ESTRING}, + {{(char*) STRING_WITH_LEN("client_cs_name")}, + my_offsetof(TABLE_LIST, view_client_cs_name), + FILE_OPTIONS_STRING}, + {{(char*) STRING_WITH_LEN("connection_cl_name")}, + my_offsetof(TABLE_LIST, view_connection_cl_name), + FILE_OPTIONS_STRING}, + {{(char*) STRING_WITH_LEN("db_cl_name")}, + my_offsetof(TABLE_LIST, view_db_cl_name), + FILE_OPTIONS_STRING}, {{NullS, 0}, 0, FILE_OPTIONS_STRING} }; @@ -672,7 +681,7 @@ { LEX *lex= thd->lex; char buff[4096]; - String str(buff,(uint32) sizeof(buff), system_charset_info); + String str(buff,(uint32) sizeof(buff), thd->charset()); char md5[MD5_BUFF_LENGTH]; bool can_be_merged; char dir_buff[FN_REFLEN], file_buff[FN_REFLEN]; @@ -801,7 +810,26 @@ } } } + loop_out: + + /* Set view definition context. */ + + view->view_def_ctx.client_cs= thd->charset(); + view->view_def_ctx.connection_cl= thd->variables.collation_connection; + view->view_def_ctx.db_cl= thd->variables.collation_database; + + /* Set character set names in order to save view definition file. */ + + set_lex_string(&view->view_client_cs_name, + view->view_def_ctx.client_cs->csname); + + set_lex_string(&view->view_connection_cl_name, + view->view_def_ctx.connection_cl->name); + + set_lex_string(&view->view_db_cl_name, + view->view_def_ctx.db_cl->name); + /* Check that table of main select do not used in subqueries. @@ -862,6 +890,7 @@ TABLE_LIST *top_view= table->top_table(); int res; bool result; + DBUG_ENTER("mysql_make_view"); DBUG_PRINT("info", ("table: 0x%lx (%s)", (ulong) table, table->table_name)); @@ -962,6 +991,75 @@ /*TODO: md5 test here and warning if it is differ */ /* + Initialize view definition context by character set names loaded from + the view definition file. Use UTF8 character set if view definition + file is of old version and does not contain the character set names. + */ + + if (!table->view_client_cs_name.str || + !table->view_connection_cl_name.str || + !table->view_db_cl_name.str) + { + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_VIEW_NO_DEF_CONTEXT, + ER(ER_VIEW_NO_DEF_CONTEXT), + (const char *) table->db, + (const char *) table->table_name); + + table->view_def_ctx.client_cs= system_charset_info; + table->view_def_ctx.connection_cl= system_charset_info; + table->view_def_ctx.db_cl= system_charset_info; + } + else + { + table->view_def_ctx.client_cs= + get_charset_by_csname(table->view_client_cs_name.str, + MY_CS_PRIMARY, MYF(0)); + + if (!table->view_def_ctx.client_cs) + { + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_VIEW_UNKNOWN_CLIENT_CHARSET, + ER(ER_VIEW_UNKNOWN_CLIENT_CHARSET), + (const char *) table->view_client_cs_name.str, + (const char *) table->db, + (const char *) table->table_name); + + table->view_def_ctx.client_cs= system_charset_info; + } + + table->view_def_ctx.connection_cl= get_charset_by_name( + table->view_connection_cl_name.str, MYF(0)); + + if (!table->view_def_ctx.connection_cl) + { + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_VIEW_UNKNOWN_CONNECTION_CHARSET, + ER(ER_VIEW_UNKNOWN_CONNECTION_CHARSET), + (const char *) table->view_connection_cl_name.str, + (const char *) table->db, + (const char *) table->table_name); + + table->view_def_ctx.connection_cl= system_charset_info; + } + + table->view_def_ctx.db_cl= get_charset_by_name( + table->view_db_cl_name.str, MYF(0)); + + if (!table->view_def_ctx.db_cl) + { + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_VIEW_UNKNOWN_DB_CHARSET, + ER(ER_VIEW_UNKNOWN_DB_CHARSET), + (const char *) table->view_db_cl_name.str, + (const char *) table->db, + (const char *) table->table_name); + + table->view_def_ctx.db_cl= system_charset_info; + } + } + + /* TODO: TABLE mem root should be used here when VIEW will be stored in TABLE cache @@ -972,7 +1070,12 @@ view_select= &lex->select_lex; view_select->select_number= ++thd->select_number; { - ulong save_mode= thd->variables.sql_mode; + st_query_ctx saved_view_def_ctx; + ulong save_mode; + + /* Switch SQL mode. */ + + save_mode= thd->variables.sql_mode; /* switch off modes which can prevent normal parsing of VIEW - MODE_REAL_AS_FLOAT affect only CREATE TABLE parsing + MODE_PIPES_AS_CONCAT affect expression parsing @@ -999,12 +1102,23 @@ */ thd->variables.sql_mode&= ~(MODE_PIPES_AS_CONCAT | MODE_ANSI_QUOTES | MODE_IGNORE_SPACE | MODE_NO_BACKSLASH_ESCAPES); - CHARSET_INFO *save_cs= thd->variables.character_set_client; - thd->variables.character_set_client= system_charset_info; + + /* Switch query context. */ + + save_current_query_ctx(thd, &saved_view_def_ctx); + switch_query_ctx(thd, &table->view_def_ctx); + + /* Parse the query. */ + res= MYSQLparse((void *)thd); - thd->variables.character_set_client= save_cs; + + /* Restore environment. */ + + switch_query_ctx(thd, &saved_view_def_ctx); + thd->variables.sql_mode= save_mode; } + if (!res && !thd->is_fatal_error) { TABLE_LIST *view_tables= lex->query_tables; --- 1.50/mysql-test/r/trigger.result 2006-11-14 01:38:34 +03:00 +++ 1.51/mysql-test/r/trigger.result 2007-03-08 14:31:30 +03:00 @@ -600,9 +600,9 @@ @a 10 show triggers; -Trigger Event Table Statement Timing Created sql_mode Definer -t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI root@localhost -t1_af INSERT t1 set @a=10 AFTER # root@localhost +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col +t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +t1_af INSERT t1 set @a=10 AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci drop table t1; set sql_mode="traditional"; create table t1 (a date); @@ -622,8 +622,8 @@ `a` date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show triggers; -Trigger Event Table Statement Timing Created sql_mode Definer -t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # root@localhost +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col +t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci drop table t1; create table t1 (id int); create trigger t1_ai after insert on t1 for each row reset query cache; @@ -1278,4 +1278,75 @@ 2 b 3 c drop table t1; +DROP TABLE IF EXISTS t1; +SET @old_cs_client = @@character_set_client; +SET @old_cs_results = @@character_set_results; +SET @old_cs_connection = @@character_set_connection; +SET @@character_set_client= utf8; +SET @@character_set_results= utf8; +SET @@character_set_connection= utf8; +SET @@collation_connection= utf8_unicode_ci; +CREATE TABLE t1( +n INT, +cs_name VARCHAR(32), +col_name VARCHAR(32), +cs_client VARCHAR(32), +cs_connection VARCHAR(32), +cs_server VARCHAR(32)); +INSERT INTO t1 VALUES( +1, +CHARSET('xxx'), +COLLATION('xxx'), +@@character_set_client, +@@character_set_connection, +@@character_set_server); +SELECT * FROM t1; +n cs_name col_name cs_client cs_connection cs_server +1 utf8 utf8_unicode_ci utf8 utf8 latin1 +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 +FOR EACH ROW +BEGIN +SET NEW.cs_name = CHARSET('a'); +SET NEW.col_name = COLLATION('a'); +SET NEW.cs_client = @@character_set_client; +SET NEW.cs_connection = @@character_set_connection; +SET NEW.cs_server = @@character_set_server; +END| +SET @@character_set_client= koi8r; +SET @@character_set_results= koi8r; +SET @@character_set_connection= koi8r; +SET @@collation_connection= koi8r_bin; +INSERT INTO t1 VALUES( +2, +CHARSET('xxx'), +COLLATION('xxx'), +@@character_set_client, +@@character_set_connection, +@@character_set_server); +SELECT * FROM t1; +n cs_name col_name cs_client cs_connection cs_server +1 utf8 utf8_unicode_ci utf8 utf8 latin1 +2 utf8 utf8_unicode_ci utf8 utf8 latin1 +SET @@character_set_client= cp1251; +SET @@character_set_results= cp1251; +SET @@character_set_connection= cp1251; +SET @@collation_connection= cp1251_general_cs; +ALTER TABLE t1 ADD COLUMN fake INT; +ALTER TABLE t1 DROP COLUMN fake; +INSERT INTO t1 VALUES( +3, +CHARSET('xxx'), +COLLATION('xxx'), +@@character_set_client, +@@character_set_connection, +@@character_set_server); +SELECT * FROM t1; +n cs_name col_name cs_client cs_connection cs_server +1 utf8 utf8_unicode_ci utf8 utf8 latin1 +2 utf8 utf8_unicode_ci utf8 utf8 latin1 +3 utf8 utf8_unicode_ci utf8 utf8 latin1 +DROP TABLE t1; +SET character_set_client = @old_cs_client; +SET character_set_results = @old_cs_results; +SET character_set_connection = @old_cs_connection; End of 5.0 tests --- 1.58/mysql-test/t/trigger.test 2007-01-11 17:43:42 +03:00 +++ 1.59/mysql-test/t/trigger.test 2007-03-08 14:31:30 +03:00 @@ -1554,4 +1554,113 @@ drop table t1; +# +# BUG#25221: Character set of string constant is ignored +# + +# Prepare environment (set character set to UTF8 just to avoid ambiguity with +# initial settings). + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +SET @old_cs_client = @@character_set_client; +SET @old_cs_results = @@character_set_results; +SET @old_cs_connection = @@character_set_connection; + +SET @@character_set_client= utf8; +SET @@character_set_results= utf8; +SET @@character_set_connection= utf8; +SET @@collation_connection= utf8_unicode_ci; + +CREATE TABLE t1( + n INT, + cs_name VARCHAR(32), + col_name VARCHAR(32), + cs_client VARCHAR(32), + cs_connection VARCHAR(32), + cs_server VARCHAR(32)); + +# Put the example record. + +INSERT INTO t1 VALUES( + 1, + CHARSET('xxx'), + COLLATION('xxx'), + @@character_set_client, + @@character_set_connection, + @@character_set_server); + +SELECT * FROM t1; + +# Create trigger with a text literal. From now on CHARSET(<text literal>) must +# return utf8. + +delimiter |; + +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 + FOR EACH ROW + BEGIN + SET NEW.cs_name = CHARSET('a'); + SET NEW.col_name = COLLATION('a'); + SET NEW.cs_client = @@character_set_client; + SET NEW.cs_connection = @@character_set_connection; + SET NEW.cs_server = @@character_set_server; + END| + +delimiter ;| + +# Set character set to another one (let it be koi8r), initiate the first +# trigger compilation, execute the trigger and analyze the results. + +SET @@character_set_client= koi8r; +SET @@character_set_results= koi8r; +SET @@character_set_connection= koi8r; +SET @@collation_connection= koi8r_bin; + +INSERT INTO t1 VALUES( + 2, + CHARSET('xxx'), + COLLATION('xxx'), + @@character_set_client, + @@character_set_connection, + @@character_set_server); + +SELECT * FROM t1; + +# Set character set to another one (cp1251). + +SET @@character_set_client= cp1251; +SET @@character_set_results= cp1251; +SET @@character_set_connection= cp1251; +SET @@collation_connection= cp1251_general_cs; + +# Flush trigger cache (invalidate the trigger to forget its executable code). +# ALTER TABLE statement is used only for flushing purposes. + +ALTER TABLE t1 ADD COLUMN fake INT; +ALTER TABLE t1 DROP COLUMN fake; + +# Initiate the second trigger compilation. Execute the trigger and analyze +# the results. + +INSERT INTO t1 VALUES( + 3, + CHARSET('xxx'), + COLLATION('xxx'), + @@character_set_client, + @@character_set_connection, + @@character_set_server); + +SELECT * FROM t1; + +# That's all - cleanup. + +DROP TABLE t1; + +SET character_set_client = @old_cs_client; +SET character_set_results = @old_cs_results; +SET character_set_connection = @old_cs_connection; + --echo End of 5.0 tests --- 1.61/sql/sql_trigger.cc 2006-12-23 22:04:27 +03:00 +++ 1.62/sql/sql_trigger.cc 2007-03-08 14:31:30 +03:00 @@ -20,6 +20,28 @@ #include "sql_trigger.h" #include "parse_file.h" +template <class T> +inline T *alloc_type(MEM_ROOT *m) +{ + return (T *) alloc_root(m, sizeof (T)); +} + +/* + NOTE: Since alloc_type() is declared as inline, alloc_root() calls should + be inlined by the compiler. So, implementation of alloc_root() is not + needed. However, let's put the implementation in object file just in case + of stupid MS or other old compilers. +*/ + +template LEX_STRING *alloc_type<LEX_STRING>(MEM_ROOT *m); +template ulonglong *alloc_type<ulonglong>(MEM_ROOT *m); +template st_query_ctx *alloc_type<st_query_ctx>(MEM_ROOT *m); + +inline LEX_STRING *alloc_lex_string(MEM_ROOT *m) +{ + return alloc_type<LEX_STRING>(m); +} + static const LEX_STRING triggers_file_type= {(char *) STRING_WITH_LEN("TRIGGERS")}; @@ -34,20 +56,35 @@ static File_option triggers_file_parameters[]= { { - {(char *) STRING_WITH_LEN("triggers") }, + { (char *) STRING_WITH_LEN("triggers") }, my_offsetof(class Table_triggers_list, definitions_list), FILE_OPTIONS_STRLIST }, { - {(char *) STRING_WITH_LEN("sql_modes") }, + { (char *) STRING_WITH_LEN("sql_modes") }, my_offsetof(class Table_triggers_list, definition_modes_list), FILE_OPTIONS_ULLLIST }, { - {(char *) STRING_WITH_LEN("definers") }, + { (char *) STRING_WITH_LEN("definers") }, my_offsetof(class Table_triggers_list, definers_list), FILE_OPTIONS_STRLIST }, + { + { (char *) STRING_WITH_LEN("client_cs_names") }, + my_offsetof(class Table_triggers_list, client_cs_names), + FILE_OPTIONS_STRLIST + }, + { + { (char *) STRING_WITH_LEN("connection_cl_names") }, + my_offsetof(class Table_triggers_list, connection_cl_names), + FILE_OPTIONS_STRLIST + }, + { + { (char *) STRING_WITH_LEN("db_cl_names") }, + my_offsetof(class Table_triggers_list, db_cl_names), + FILE_OPTIONS_STRLIST + }, { { 0, 0 }, 0, FILE_OPTIONS_STRING } }; @@ -64,7 +101,7 @@ .trg file. */ -static const int TRG_NUM_REQUIRED_PARAMETERS= 4; +static const int TRG_NUM_REQUIRED_PARAMETERS= 6; /* Structure representing contents of .TRN file which are used to support @@ -106,10 +143,6 @@ }; -static int -add_table_for_trigger(THD *thd, sp_name *trig, bool if_exists, - TABLE_LIST ** table); - class Handle_old_incorrect_sql_modes_hook: public Unknown_key_hook { private: @@ -122,6 +155,13 @@ MEM_ROOT *mem_root, char *end); }; + +static bool add_table_for_trigger(THD *thd, + sp_name *trigger_name, + bool continue_if_not_exist, + TABLE_LIST **table); + + class Handle_old_incorrect_trigger_table_hook: public Unknown_key_hook { public: @@ -363,6 +403,9 @@ LEX_STRING *trg_definer; Item_trigger_field *trg_field; struct st_trigname trigname; + LEX_STRING *trg_client_cs_name; + LEX_STRING *trg_connection_cl_name; + LEX_STRING *trg_db_cl_name; /* Trigger must be in the same schema as target table. */ @@ -494,16 +537,26 @@ QQ: Hmm... probably we should not care about setting up active thread mem_root too. */ - if (!(trg_def= (LEX_STRING *)alloc_root(&table->mem_root, - sizeof(LEX_STRING))) || + if (!(trg_def= alloc_lex_string(&table->mem_root)) || definitions_list.push_back(trg_def, &table->mem_root) || - !(trg_sql_mode= (ulonglong*)alloc_root(&table->mem_root, - sizeof(ulonglong))) || + + !(trg_sql_mode= alloc_type<ulonglong>(&table->mem_root)) || definition_modes_list.push_back(trg_sql_mode, &table->mem_root) || - !(trg_definer= (LEX_STRING*) alloc_root(&table->mem_root, - sizeof(LEX_STRING))) || - definers_list.push_back(trg_definer, &table->mem_root)) + + !(trg_definer= alloc_lex_string(&table->mem_root)) || + definers_list.push_back(trg_definer, &table->mem_root) || + + !(trg_client_cs_name= alloc_lex_string(&table->mem_root)) || + client_cs_names.push_back(trg_client_cs_name, &table->mem_root) || + + !(trg_connection_cl_name= alloc_lex_string(&table->mem_root)) || + connection_cl_names.push_back(trg_connection_cl_name, &table->mem_root) || + + !(trg_db_cl_name= alloc_lex_string(&table->mem_root)) || + db_cl_names.push_back(trg_db_cl_name, &table->mem_root)) + { goto err_with_cleanup; + } *trg_sql_mode= thd->variables.sql_mode; @@ -546,6 +599,21 @@ } /* + Fill character set information: + - client character set contains charset info only; + - connection collation contains pair {character set, collation}; + - database collation contains pair {character set, collation}; + */ + + set_lex_string(trg_client_cs_name, thd->charset()->csname); + + set_lex_string(trg_connection_cl_name, + thd->variables.collation_connection->name); + + set_lex_string(trg_db_cl_name, + thd->variables.collation_database->name); + + /* Create well-formed trigger definition query. Original query is not appropriated, because definer-clause can be not truncated. */ @@ -683,14 +751,20 @@ bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables, String *stmt_query) { - LEX *lex= thd->lex; + const char *sp_name= thd->lex->spname->m_name.str; // alias + LEX_STRING *name; - List_iterator_fast<LEX_STRING> it_name(names_list); - List_iterator<LEX_STRING> it_def(definitions_list); - List_iterator<ulonglong> it_mod(definition_modes_list); - List_iterator<LEX_STRING> it_definer(definers_list); char path[FN_REFLEN]; + List_iterator_fast<LEX_STRING> it_name(names_list); + + List_iterator<ulonglong> it_mod(definition_modes_list); + List_iterator<LEX_STRING> it_def(definitions_list); + List_iterator<LEX_STRING> it_definer(definers_list); + List_iterator<LEX_STRING> it_client_cs_name(client_cs_names); + List_iterator<LEX_STRING> it_connection_cl_name(connection_cl_names); + List_iterator<LEX_STRING> it_db_cl_name(db_cl_names); + stmt_query->append(thd->query, thd->query_length); while ((name= it_name++)) @@ -698,9 +772,11 @@ it_def++; it_mod++; it_definer++; + it_client_cs_name++; + it_connection_cl_name++; + it_db_cl_name++; - if (my_strcasecmp(table_alias_charset, lex->spname->m_name.str, - name->str) == 0) + if (my_strcasecmp(table_alias_charset, sp_name, name->str) == 0) { /* Again we don't care much about other things required for @@ -709,6 +785,9 @@ it_def.remove(); it_mod.remove(); it_definer.remove(); + it_client_cs_name.remove(); + it_connection_cl_name.remove(); + it_db_cl_name.remove(); if (definitions_list.is_empty()) { @@ -727,7 +806,7 @@ return 1; } - if (rm_trigname_file(path, tables->db, lex->spname->m_name.str)) + if (rm_trigname_file(path, tables->db, sp_name)) return 1; return 0; } @@ -867,9 +946,13 @@ we should initialize the list for safety: - sql_modes; - definers; + - character sets (client, connection, database); */ triggers->definition_modes_list.empty(); triggers->definers_list.empty(); + triggers->client_cs_names.empty(); + triggers->connection_cl_names.empty(); + triggers->db_cl_names.empty(); if (parser->parse((gptr)triggers, &table->mem_root, triggers_file_parameters, @@ -890,8 +973,7 @@ We use one mode (current) for all triggers, because we have not information about mode in old format. */ - if (!(trg_sql_mode= (ulonglong*)alloc_root(&table->mem_root, - sizeof(ulonglong)))) + if (!(trg_sql_mode= alloc_type<ulonglong>(&table->mem_root))) { DBUG_RETURN(1); // EOM } @@ -920,8 +1002,7 @@ LEX_STRING *trg_definer; - if (! (trg_definer= (LEX_STRING*)alloc_root(&table->mem_root, - sizeof(LEX_STRING)))) + if (!(trg_definer= alloc_lex_string(&table->mem_root))) DBUG_RETURN(1); // EOM trg_definer->str= (char*) ""; @@ -939,10 +1020,82 @@ it.rewind(); } + if (!triggers->definitions_list.is_empty() && + (triggers->client_cs_names.is_empty() || + triggers->connection_cl_names.is_empty() || + triggers->db_cl_names.is_empty())) + { + /* + It is old file format => we should fill lists of character sets. + */ + + LEX_STRING *trg_client_cs_name; + LEX_STRING *trg_connection_cl_name; + LEX_STRING *trg_db_cl_name; + + if (!triggers->client_cs_names.is_empty() || + !triggers->connection_cl_names.is_empty() || + !triggers->db_cl_names.is_empty()) + { + my_error(ER_TRG_CORRUPTED_FILE, MYF(0), + (const char *) db, + (const char *) table_name); + + DBUG_RETURN(1); // EOM + } + + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRG_NO_DEF_CONTEXT, + ER(ER_TRG_NO_DEF_CONTEXT), + (const char*) db, + (const char*) table_name); + + if (!(trg_client_cs_name= alloc_lex_string(&table->mem_root)) || + !(trg_connection_cl_name= alloc_lex_string(&table->mem_root)) || + !(trg_db_cl_name= alloc_lex_string(&table->mem_root))) + { + DBUG_RETURN(1); // EOM + } + + /* + Backward compatibility: assume that the query is in the current + character set. + */ + + set_lex_string(trg_client_cs_name, + thd->variables.character_set_client->csname); + + set_lex_string(trg_connection_cl_name, + thd->variables.collation_connection->name); + + set_lex_string(trg_db_cl_name, + thd->variables.collation_database->name); + + while (it++) + { + // XXX: length of lines will be fixed in the polishing patch. + + if (triggers->client_cs_names.push_back(trg_client_cs_name, &table->mem_root) || + triggers->connection_cl_names.push_back(trg_connection_cl_name, &table->mem_root) || + triggers->db_cl_names.push_back(trg_db_cl_name, &table->mem_root)) + { + DBUG_RETURN(1); // EOM + } + } + + it.rewind(); + } + DBUG_ASSERT(triggers->definition_modes_list.elements == triggers->definitions_list.elements); DBUG_ASSERT(triggers->definers_list.elements == triggers->definitions_list.elements); + DBUG_ASSERT(triggers->client_cs_names.elements == + triggers->definitions_list.elements); + DBUG_ASSERT(triggers->connection_cl_names.elements == + triggers->definitions_list.elements); + DBUG_ASSERT(triggers->db_cl_names.elements == + triggers->definitions_list.elements); table->triggers= triggers; @@ -966,6 +1119,9 @@ List_iterator_fast<ulonglong> itm(triggers->definition_modes_list); List_iterator_fast<LEX_STRING> it_definer(triggers->definers_list); + List_iterator_fast<LEX_STRING> it_client_cs_name(triggers->client_cs_names); + List_iterator_fast<LEX_STRING> it_connection_cl_name(triggers->connection_cl_names); + List_iterator_fast<LEX_STRING> it_db_cl_name(triggers->db_cl_names); LEX *old_lex= thd->lex, lex; sp_rcontext *save_spcont= thd->spcont; ulong save_sql_mode= thd->variables.sql_mode; @@ -979,13 +1135,78 @@ while ((trg_create_str= it++)) { trg_sql_mode= itm++; - LEX_STRING *trg_definer= it_definer++; thd->variables.sql_mode= (ulong)*trg_sql_mode; lex_start(thd, (uchar*)trg_create_str->str, trg_create_str->length); thd->spcont= 0; - if (MYSQLparse((void *)thd) || thd->is_fatal_error) + + LEX_STRING *trg_definer= it_definer++; + LEX_STRING *trg_client_cs_name= it_client_cs_name++; + LEX_STRING *trg_connection_cl_name= it_connection_cl_name++; + LEX_STRING *trg_db_cl_name= it_db_cl_name++; + + CHARSET_INFO *trg_client_cs= + get_charset_by_csname(trg_client_cs_name->str, + MY_CS_PRIMARY, MYF(0)); + + CHARSET_INFO *trg_connection_cl= + get_charset_by_name(trg_connection_cl_name->str, MYF(0)); + + CHARSET_INFO *trg_db_cl= + get_charset_by_name(trg_db_cl_name->str, MYF(0)); + + if (!trg_client_cs) + { + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_TRG_UNKNOWN_CLIENT_CHARSET, + ER(ER_TRG_UNKNOWN_CLIENT_CHARSET), + (const char *) trg_client_cs_name->str, + (const char *) db, + (const char *) table_name); + + DBUG_RETURN(1); + } + + if (!trg_connection_cl) + { + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_TRG_UNKNOWN_CONNECTION_CHARSET, + ER(ER_TRG_UNKNOWN_CONNECTION_CHARSET), + (const char *) trg_connection_cl_name->str, + (const char *) db, + (const char *) table_name); + + DBUG_RETURN(1); + } + + if (!trg_db_cl) + { + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_TRG_UNKNOWN_DB_CHARSET, + ER(ER_TRG_UNKNOWN_DB_CHARSET), + (const char *) trg_db_cl_name->str, + (const char *) db, + (const char *) table_name); + + DBUG_RETURN(1); + } + + st_query_ctx saved_def_ctx; + st_query_ctx *def_ctx= alloc_type<st_query_ctx>(&table->mem_root); + + def_ctx->client_cs= trg_client_cs; + def_ctx->connection_cl= trg_connection_cl; + def_ctx->db_cl= trg_db_cl; + + save_current_query_ctx(thd, &saved_def_ctx); + switch_query_ctx(thd, def_ctx); + + int parse_status= MYSQLparse((void *)thd); + + switch_query_ctx(thd, &saved_def_ctx); + + if (parse_status || thd->is_fatal_error) { /* Free lex associated resources. @@ -997,8 +1218,11 @@ lex.sphead->set_info(0, 0, &lex.sp_chistics, (ulong) *trg_sql_mode); - triggers->bodies[lex.trg_chistics.event] - [lex.trg_chistics.action_time]= lex.sphead; + int event= lex.trg_chistics.event; + int action_time= lex.trg_chistics.action_time; + + lex.sphead->m_defstr_ctx= *def_ctx; + triggers->bodies[event][action_time]= lex.sphead; if (!trg_definer->length) { @@ -1034,8 +1258,7 @@ &table->mem_root)) goto err_with_lex_cleanup; - if (!(on_table_name= (LEX_STRING*) alloc_root(&table->mem_root, - sizeof(LEX_STRING)))) + if (!(on_table_name= alloc_lex_string(&table->mem_root))) goto err_with_lex_cleanup; *on_table_name= lex.ident; if (triggers->on_table_names_list.push_back(on_table_name, &table->mem_root)) @@ -1077,7 +1300,7 @@ trg_field; trg_field= trg_field->next_trg_field) { - trg_field->setup_field(thd, table, + trg_field->setup_field(thd, table, &triggers->subject_table_grants[lex.trg_chistics.event] [lex.trg_chistics.action_time]); } @@ -1139,7 +1362,10 @@ LEX_STRING *trigger_name, LEX_STRING *trigger_stmt, ulong *sql_mode, - LEX_STRING *definer) + LEX_STRING *definer, + LEX_STRING *client_cs_name, + LEX_STRING *connection_cl_name, + LEX_STRING *db_cl_name) { sp_head *body; DBUG_ENTER("get_trigger_info"); @@ -1160,89 +1386,201 @@ body->m_definer_host.str, NullS) - definer->str; } + set_lex_string(client_cs_name, + bodies[event][time_type]->m_defstr_ctx.client_cs->csname); + + set_lex_string(connection_cl_name, + bodies[event][time_type]->m_defstr_ctx.connection_cl->name); + + set_lex_string(db_cl_name, + bodies[event][time_type]->m_defstr_ctx.db_cl->name); + DBUG_RETURN(0); } DBUG_RETURN(1); } +void Table_triggers_list::get_trigger_info(THD *thd, + int trigger_idx, + LEX_STRING *trigger_name, + ulonglong *sql_mode, + LEX_STRING *sql_original_stmt, + LEX_STRING *client_cs_name, + LEX_STRING *connection_cl_name, + LEX_STRING *db_cl_name) +{ + List_iterator_fast<LEX_STRING> it_trigger_name(names_list); + List_iterator_fast<ulonglong> it_sql_mode(definition_modes_list); + List_iterator_fast<LEX_STRING> it_sql_orig_stmt(definitions_list); + List_iterator_fast<LEX_STRING> it_client_cs_name(client_cs_names); + List_iterator_fast<LEX_STRING> it_connection_cl_name(connection_cl_names); + List_iterator_fast<LEX_STRING> it_db_cl_name(db_cl_names); + + for (int i = 0; i < trigger_idx - 1; ++i) + { + it_trigger_name.next_fast(); + it_sql_mode.next_fast(); + it_sql_orig_stmt.next_fast(); + + it_client_cs_name.next_fast(); + it_connection_cl_name.next_fast(); + it_db_cl_name.next_fast(); + } + + *trigger_name= *(it_trigger_name++); + *sql_mode= *(it_sql_mode++); + *sql_original_stmt= *(it_sql_orig_stmt++); + + *client_cs_name= *(it_client_cs_name++); + *connection_cl_name= *(it_connection_cl_name++); + *db_cl_name= *(it_db_cl_name++); +} + + +int Table_triggers_list::find_trigger_by_name(const LEX_STRING *trg_name) +{ + List_iterator_fast<LEX_STRING> it(names_list); + + for (int i = 0; ; ++i) + { + LEX_STRING *cur_name= it++; + + if (!cur_name) + return -1; + + if (!strcmp(cur_name->str, trg_name->str)) + return i; + } +} + + +bool get_trn_path_for_trigger(THD *thd, + const sp_name *trg_name, + LEX_STRING *trn_path) +{ + DBUG_ENTER("get_trn_path_for_trigger"); + + /* Construct path to the TRN-file. */ + + strxnmov(trn_path->str, FN_REFLEN, + mysql_data_home, "/", + trg_name->m_db.str, "/", + trg_name->m_name.str, trigname_file_ext, + NullS); + trn_path->length= unpack_filename(trn_path->str, trn_path->str); + + /* Check if the TRN-file exists. */ + + return access(trn_path->str, F_OK) != 0; +} + + +bool get_table_name_for_trigger(THD *thd, + const sp_name *trg_name, + const LEX_STRING *trn_path, + LEX_STRING *tbl_name) +{ + File_parser *parser; + struct st_trigname trn_data; + + Handle_old_incorrect_trigger_table_hook trigger_table_hook( + trn_path->str, + &trn_data.trigger_table); + + DBUG_ENTER("get_table_name_for_trigger"); + + /* Parse the TRN-file. */ + + if (!(parser= sql_parse_prepare(trn_path, thd->mem_root, TRUE))) + DBUG_RETURN(TRUE); + + if (!is_equal(&trigname_file_type, parser->type())) + { + my_error(ER_WRONG_OBJECT, MYF(0), + trg_name->m_name.str, + trigname_file_ext + 1, + "TRIGGERNAME"); + + DBUG_RETURN(TRUE); + } + + if (parser->parse((gptr) &trn_data, thd->mem_root, + trigname_file_parameters, 1, + &trigger_table_hook)) + DBUG_RETURN(TRUE); + + /* Copy trigger table name. */ + + *tbl_name= trn_data.trigger_table; + + /* That's all. */ + + DBUG_RETURN(FALSE); +} + + /* Find trigger's table from trigger identifier and add it to the statement table list. SYNOPSIS - mysql_table_for_trigger() - thd - current thread context - trig - identifier for trigger - if_exists - treat a not existing trigger as a warning if TRUE - table - pointer to TABLE_LIST object for the table trigger (output) + add_table_for_trigger() + thd [IN] current thread context + trigger_name [IN] identifier for trigger + continue_if_not_exist [IN] treat a not existing trigger as a warning + if TRUE + table [OUT] pointer to TABLE_LIST object for the + table trigger RETURN VALUE - 0 Success - 1 Error + FALSE Success + TRUE Error */ -static int -add_table_for_trigger(THD *thd, sp_name *trig, bool if_exists, - TABLE_LIST **table) +static bool add_table_for_trigger(THD *thd, + sp_name *trg_name, + bool continue_if_not_exist, + TABLE_LIST **table) { LEX *lex= thd->lex; - char path_buff[FN_REFLEN]; - LEX_STRING path; - File_parser *parser; - struct st_trigname trigname; - Handle_old_incorrect_trigger_table_hook trigger_table_hook( - path_buff, &trigname.trigger_table); - - DBUG_ENTER("add_table_for_trigger"); - DBUG_ASSERT(table != NULL); - strxnmov(path_buff, FN_REFLEN, mysql_data_home, "/", trig->m_db.str, "/", - trig->m_name.str, trigname_file_ext, NullS); - path.length= unpack_filename(path_buff, path_buff); - path.str= path_buff; + char trn_path_buff[FN_REFLEN]; + LEX_STRING trn_path= { trn_path_buff, 0 }; - if (access(path_buff, F_OK)) + LEX_STRING tbl_name; + + DBUG_ENTER("add_table_for_trigger"); + + if (get_trn_path_for_trigger(thd, trg_name, &trn_path)) { - if (if_exists) + if (continue_if_not_exist) { push_warning_printf(thd, - MYSQL_ERROR::WARN_LEVEL_NOTE, - ER_TRG_DOES_NOT_EXIST, - ER(ER_TRG_DOES_NOT_EXIST)); + MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_TRG_DOES_NOT_EXIST, + ER(ER_TRG_DOES_NOT_EXIST)); + *table= NULL; - DBUG_RETURN(0); + + DBUG_RETURN(FALSE); } my_error(ER_TRG_DOES_NOT_EXIST, MYF(0)); - DBUG_RETURN(1); - } - - if (!(parser= sql_parse_prepare(&path, thd->mem_root, 1))) - DBUG_RETURN(1); - - if (!is_equal(&trigname_file_type, parser->type())) - { - my_error(ER_WRONG_OBJECT, MYF(0), trig->m_name.str, trigname_file_ext+1, - "TRIGGERNAME"); - DBUG_RETURN(1); + DBUG_RETURN(TRUE); } - if (parser->parse((gptr)&trigname, thd->mem_root, - trigname_file_parameters, 1, - &trigger_table_hook)) - DBUG_RETURN(1); + if (get_table_name_for_trigger(thd, trg_name, &trn_path, &tbl_name)) + DBUG_RETURN(TRUE); /* We need to reset statement table list to be PS/SP friendly. */ lex->query_tables= 0; lex->query_tables_last= &lex->query_tables; - *table= sp_add_to_query_tables(thd, lex, trig->m_db.str, - trigname.trigger_table.str, TL_IGNORE); - if (! *table) - DBUG_RETURN(1); + *table= sp_add_to_query_tables(thd, lex, trg_name->m_db.str, + tbl_name.str, TL_IGNORE); - DBUG_RETURN(0); + DBUG_RETURN(*table ? FALSE : TRUE); } @@ -1535,30 +1873,33 @@ trg_action_time_type time_type, bool old_row_is_record1) { - bool err_status= FALSE; - sp_head *sp_trigger= bodies[event][time_type]; + bool err_status; + Sub_statement_state statement_state; - if (sp_trigger) + if (!bodies[event][time_type]) + return FALSE; + + if (old_row_is_record1) + { + old_field= record1_field; + new_field= table->field; + } + else { - Sub_statement_state statement_state; + new_field= record1_field; + old_field= table->field; + } - if (old_row_is_record1) - { - old_field= record1_field; - new_field= table->field; - } - else - { - new_field= record1_field; - old_field= table->field; - } + thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER); - thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER); - err_status= sp_trigger->execute_trigger - (thd, table->s->db, table->s->table_name, - &subject_table_grants[event][time_type]); - thd->restore_sub_statement_state(&statement_state); - } + err_status= + bodies[event][time_type]->execute_trigger( + thd, + table->s->db, + table->s->table_name, + &subject_table_grants[event][time_type]); + + thd->restore_sub_statement_state(&statement_state); return err_status; } --- 1.23/sql/sql_trigger.h 2006-12-23 22:04:27 +03:00 +++ 1.24/sql/sql_trigger.h 2007-03-08 14:31:30 +03:00 @@ -17,7 +17,7 @@ /* This class holds all information about triggers of table. - QQ: Will it be merged into TABLE in future ? + QQ: Will it be merged into TABLE in the future ? */ class Table_triggers_list: public Sql_alloc @@ -82,6 +82,14 @@ List<LEX_STRING> definers_list; + /* Character set context, used for parsing and executing triggers. */ + + List<LEX_STRING> client_cs_names; + List<LEX_STRING> connection_cl_names; + List<LEX_STRING> db_cl_names; + + /* End of character ser context. */ + Table_triggers_list(TABLE *table_arg): record1_field(0), table(table_arg) { @@ -96,11 +104,26 @@ bool process_triggers(THD *thd, trg_event_type event, trg_action_time_type time_type, bool old_row_is_record1); + bool get_trigger_info(THD *thd, trg_event_type event, trg_action_time_type time_type, LEX_STRING *trigger_name, LEX_STRING *trigger_stmt, ulong *sql_mode, - LEX_STRING *definer); + LEX_STRING *definer, + LEX_STRING *client_cs_name, + LEX_STRING *connection_cl_name, + LEX_STRING *db_cl_name); + + void get_trigger_info(THD *thd, + int trigger_idx, + LEX_STRING *trigger_name, + ulonglong *sql_mode, + LEX_STRING *sql_original_stmt, + LEX_STRING *client_cs_name, + LEX_STRING *connection_cl_name, + LEX_STRING *db_cl_name); + + int find_trigger_by_name(const LEX_STRING *trigger_name); static bool check_n_load(THD *thd, const char *db, const char *table_name, TABLE *table, bool names_only); --- 1.22/mysql-test/r/rpl_sp.result 2007-01-09 00:01:04 +03:00 +++ 1.23/mysql-test/r/rpl_sp.result 2007-03-08 14:31:29 +03:00 @@ -17,21 +17,21 @@ insert into t1 values (unix_timestamp()); end| select * from mysql.proc where name='foo' and db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment charset_client collation_connection collation_database mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); -end root@localhost # # +end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci select * from mysql.proc where name='foo' and db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment charset_client collation_connection collation_database mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); -end root@localhost # # +end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci set timestamp=1000000000; call foo(); select * from t1; @@ -115,15 +115,15 @@ a 20 select * from mysql.proc where name="foo4" and db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment charset_client collation_connection collation_database mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES DEFINER begin insert into t2 values(20),(20); -end root@localhost # # +end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci drop procedure foo4; select * from mysql.proc where name="foo4" and db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment charset_client collation_connection collation_database select * from mysql.proc where name="foo4" and db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment charset_client collation_connection collation_database drop procedure foo; drop procedure foo2; drop procedure foo3; @@ -202,16 +202,16 @@ fn3() 0 select * from mysql.proc where db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment charset_client collation_connection collation_database mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); -end root@localhost # # +end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); -end zedjzlcsjhd@localhost # # +end zedjzlcsjhd@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin return 0; -end root@localhost # # +end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci select * from t1; a 1000000000 @@ -220,16 +220,16 @@ a 1000000000 select * from mysql.proc where db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment charset_client collation_connection collation_database mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); -end root@localhost # # +end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); -end zedjzlcsjhd@localhost # # +end zedjzlcsjhd@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin return 0; -end root@localhost # # +end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci delete from t2; alter table t2 add unique (a); drop function fn1; @@ -337,26 +337,26 @@ ---> Checking on master... SHOW CREATE PROCEDURE p1; -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() -SET @a = 1 +SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE FUNCTION f1; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) -RETURN 0 +RETURN 0 latin1 latin1_swedish_ci latin1_swedish_ci ---> Synchronizing slave with master... ---> connection: master ---> Checking on slave... SHOW CREATE PROCEDURE p1; -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() -SET @a = 1 +SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE FUNCTION f1; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) -RETURN 0 +RETURN 0 latin1 latin1_swedish_ci latin1_swedish_ci ---> connection: master --- 1.117/mysql-test/r/information_schema.result 2006-12-01 19:56:55 +03:00 +++ 1.118/mysql-test/r/information_schema.result 2007-03-08 14:31:29 +03:00 @@ -256,11 +256,11 @@ SQL CONTAINS SQL NULL SQL CONTAINS SQL int(11) show procedure status; -Db Name Type Definer Modified Created Security_type Comment -test sel2 PROCEDURE root@localhost # # DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test sel2 PROCEDURE root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci show function status; -Db Name Type Definer Modified Created Security_type Comment -test sub1 FUNCTION root@localhost # # DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test sub1 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci select a.ROUTINE_NAME from information_schema.ROUTINES a, information_schema.SCHEMATA b where a.ROUTINE_SCHEMA = b.SCHEMA_NAME; @@ -309,26 +309,26 @@ sub1 NULL sub2 return i+1 show create procedure sel2; -Procedure sql_mode Create Procedure -sel2 NULL +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +sel2 NULL latin1 latin1_swedish_ci latin1_swedish_ci show create function sub1; -Function sql_mode Create Function -sub1 NULL +Function sql_mode Create Function Client_cs Connection_col Database_col +sub1 NULL latin1 latin1_swedish_ci latin1_swedish_ci show create function sub2; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col sub2 CREATE DEFINER=`mysqltest_1`@`localhost` FUNCTION `sub2`(i int) RETURNS int(11) -return i+1 +return i+1 latin1 latin1_swedish_ci latin1_swedish_ci show function status like "sub2"; -Db Name Type Definer Modified Created Security_type Comment -test sub2 FUNCTION mysqltest_1@localhost # # DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test sub2 FUNCTION mysqltest_1@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci drop function sub2; show create procedure sel2; -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col sel2 CREATE DEFINER=`root`@`localhost` PROCEDURE `sel2`() begin select * from t1; select * from t2; -end +end latin1 latin1_swedish_ci latin1_swedish_ci create view v0 (c) as select schema_name from information_schema.schemata; select * from v0; c @@ -368,12 +368,12 @@ show keys from v4; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment select * from information_schema.views where TABLE_NAME like "v%"; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE -NULL test v0 /* ALGORITHM=UNDEFINED */ select `schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata` NONE NO root@localhost DEFINER -NULL test v1 /* ALGORITHM=UNDEFINED */ select `tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where (`tables`.`TABLE_NAME` = _utf8'v1') NONE NO root@localhost DEFINER -NULL test v2 /* ALGORITHM=UNDEFINED */ select `columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where (`columns`.`TABLE_NAME` = _utf8'v2') NONE NO root@localhost DEFINER -NULL test v3 /* ALGORITHM=UNDEFINED */ select `character_sets`.`CHARACTER_SET_NAME` AS `c` from `information_schema`.`character_sets` where (`character_sets`.`CHARACTER_SET_NAME` like _utf8'latin1%') NONE NO root@localhost DEFINER -NULL test v4 /* ALGORITHM=UNDEFINED */ select `collations`.`COLLATION_NAME` AS `c` from `information_schema`.`collations` where (`collations`.`COLLATION_NAME` like _utf8'latin1%') NONE NO root@localhost DEFINER +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CLIENT_CS CONNECTION_COL DATABASE_COL +NULL test v0 /* ALGORITHM=UNDEFINED */ select `schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +NULL test v1 /* ALGORITHM=UNDEFINED */ select `tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where (`tables`.`TABLE_NAME` = _utf8'v1') NONE NO root@localhost DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +NULL test v2 /* ALGORITHM=UNDEFINED */ select `columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where (`columns`.`TABLE_NAME` = _utf8'v2') NONE NO root@localhost DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +NULL test v3 /* ALGORITHM=UNDEFINED */ select `character_sets`.`CHARACTER_SET_NAME` AS `c` from `information_schema`.`character_sets` where (`character_sets`.`CHARACTER_SET_NAME` like _utf8'latin1%') NONE NO root@localhost DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +NULL test v4 /* ALGORITHM=UNDEFINED */ select `collations`.`COLLATION_NAME` AS `c` from `information_schema`.`collations` where (`collations`.`COLLATION_NAME` like _utf8'latin1%') NONE NO root@localhost DEFINER latin1 latin1_swedish_ci latin1_swedish_ci drop view v0, v1, v2, v3, v4; create table t1 (a int); grant select,update,insert on t1 to mysqltest_1@localhost; @@ -463,10 +463,10 @@ create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION; create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION; select * from information_schema.views; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE -NULL test v1 /* ALGORITHM=UNDEFINED */ select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER -NULL test v2 /* ALGORITHM=UNDEFINED */ select `test`.`t1`.`a` AS `c` from `test`.`t1` LOCAL YES root@localhost DEFINER -NULL test v3 /* ALGORITHM=UNDEFINED */ select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CLIENT_CS CONNECTION_COL DATABASE_COL +NULL test v1 /* ALGORITHM=UNDEFINED */ select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +NULL test v2 /* ALGORITHM=UNDEFINED */ select `test`.`t1`.`a` AS `c` from `test`.`t1` LOCAL YES root@localhost DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +NULL test v3 /* ALGORITHM=UNDEFINED */ select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER latin1 latin1_swedish_ci latin1_swedish_ci grant select (a) on test.t1 to joe@localhost with grant option; select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE @@ -567,6 +567,9 @@ proc modified timestamp proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE') proc comment char(64) +proc charset_client char(32) +proc collation_connection char(32) +proc collation_database char(32) drop table t115; create procedure p108 () begin declare c cursor for select data_type from information_schema.columns; open c; open c; end;// @@ -681,13 +684,13 @@ where table_schema='test'; constraint_name show create view v2; -View Create View -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `test`.`t1`.`f1` AS `c` from `t1` +View Create View Client_cs Connection_col Database_col +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `test`.`t1`.`f1` AS `c` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci Warnings: Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them show create table v3; -View Create View -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `test`.`sub1`(1) AS `c` +View Create View Client_cs Connection_col Database_col +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `test`.`sub1`(1) AS `c` latin1 latin1_swedish_ci latin1_swedish_ci Warnings: Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them drop view v2; @@ -823,39 +826,39 @@ end if; end| show triggers; -Trigger Event Table Statement Timing Created sql_mode Definer +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col trg1 INSERT t1 begin if new.j > 10 then set new.j := 10; end if; -end BEFORE NULL root@localhost +end BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg2 UPDATE t1 begin if old.i % 2 = 0 then set new.j := -1; end if; -end BEFORE NULL root@localhost +end BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg3 UPDATE t1 begin if new.j = -1 then set @fired:= "Yes"; end if; -end AFTER NULL root@localhost +end AFTER NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci select * from information_schema.triggers; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CLIENT_CS CONNECTION_COL DATABASE_COL NULL test trg1 INSERT NULL test t1 0 NULL begin if new.j > 10 then set new.j := 10; end if; -end ROW BEFORE NULL NULL OLD NEW NULL root@localhost +end ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci NULL test trg2 UPDATE NULL test t1 0 NULL begin if old.i % 2 = 0 then set new.j := -1; end if; -end ROW BEFORE NULL NULL OLD NEW NULL root@localhost +end ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci NULL test trg3 UPDATE NULL test t1 0 NULL begin if new.j = -1 then set @fired:= "Yes"; end if; -end ROW AFTER NULL NULL OLD NEW NULL root@localhost +end ROW AFTER NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci drop trigger trg1; drop trigger trg2; drop trigger trg3; @@ -1107,7 +1110,7 @@ use mysql; INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL', 'NO','DEFINER','','','BEGIN\r\n \r\nEND','root@%','2006-03-02 18:40:03', -'2006-03-02 18:40:03','',''); +'2006-03-02 18:40:03','','', 'latin1', 'latin1', 'latin1'); select routine_name from information_schema.routines; routine_name @@ -1120,9 +1123,9 @@ sql security definer view v2 as select 1; select * from information_schema.views where table_name='v1' or table_name='v2'; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE -NULL test v1 NONE YES root@localhost DEFINER -NULL test v2 /* ALGORITHM=UNDEFINED */ select 1 AS `1` NONE NO mysqltest_1@localhost DEFINER +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CLIENT_CS CONNECTION_COL DATABASE_COL +NULL test v1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +NULL test v2 /* ALGORITHM=UNDEFINED */ select 1 AS `1` NONE NO mysqltest_1@localhost DEFINER latin1 latin1_swedish_ci latin1_swedish_ci drop view v1, v2; drop table t1; drop user mysqltest_1@localhost; @@ -1147,23 +1150,23 @@ f1 RETURN @a + 1 p1 SET @a= 1 SHOW CREATE PROCEDURE p1; -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() -SET @a= 1 +SET @a= 1 latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE FUNCTION f1; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) -RETURN @a + 1 +RETURN @a + 1 latin1 latin1_swedish_ci latin1_swedish_ci SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION f1 NULL p1 NULL SHOW CREATE PROCEDURE p1; -Procedure sql_mode Create Procedure -p1 NULL +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +p1 NULL latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE FUNCTION f1; -Function sql_mode Create Function -f1 NULL +Function sql_mode Create Function Client_cs Connection_col Database_col +f1 NULL latin1 latin1_swedish_ci latin1_swedish_ci CALL p1(); SELECT f1(); f1() --- 1.88/mysql-test/t/information_schema.test 2006-12-01 19:56:55 +03:00 +++ 1.89/mysql-test/t/information_schema.test 2007-03-08 14:31:30 +03:00 @@ -819,7 +819,7 @@ use mysql; INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL', 'NO','DEFINER','','','BEGIN\r\n \r\nEND','root@%','2006-03-02 18:40:03', -'2006-03-02 18:40:03','',''); +'2006-03-02 18:40:03','','', 'latin1', 'latin1', 'latin1'); select routine_name from information_schema.routines; delete from proc where name=''; use test; --- 1.9/mysql-test/r/information_schema_db.result 2006-08-08 11:50:00 +04:00 +++ 1.10/mysql-test/r/information_schema_db.result 2007-03-08 14:31:29 +03:00 @@ -123,8 +123,8 @@ Field Type Null Key Default Extra f1 char(4) YES NULL show create view v2; -View Create View -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v2` AS select `v1`.`f1` AS `f1` from `testdb_1`.`v1` +View Create View Client_cs Connection_col Database_col +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v2` AS select `v1`.`f1` AS `f1` from `testdb_1`.`v1` latin1 latin1_swedish_ci latin1_swedish_ci show create view testdb_1.v1; ERROR 42000: SHOW VIEW command denied to user 'testdb_2'@'localhost' for table 'v1' select table_name from information_schema.columns a --- 1.35/mysql-test/r/sql_mode.result 2006-04-27 16:27:46 +04:00 +++ 1.36/mysql-test/r/sql_mode.result 2007-03-08 14:31:30 +03:00 @@ -444,37 +444,37 @@ SET @@SQL_MODE=''; create function `foo` () returns int return 5; show create function `foo`; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col foo CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11) -return 5 +return 5 latin1 latin1_swedish_ci latin1_swedish_ci SET @@SQL_MODE='ANSI_QUOTES'; show create function `foo`; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col foo CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11) -return 5 +return 5 latin1 latin1_swedish_ci latin1_swedish_ci drop function `foo`; create function `foo` () returns int return 5; show create function `foo`; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col foo ANSI_QUOTES CREATE DEFINER="root"@"localhost" FUNCTION "foo"() RETURNS int(11) -return 5 +return 5 latin1 latin1_swedish_ci latin1_swedish_ci SET @@SQL_MODE=''; show create function `foo`; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col foo ANSI_QUOTES CREATE DEFINER="root"@"localhost" FUNCTION "foo"() RETURNS int(11) -return 5 +return 5 latin1 latin1_swedish_ci latin1_swedish_ci drop function `foo`; SET @@SQL_MODE=''; create table t1 (a int); create table t2 (a int); create view v1 as select a from t1; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci SET @@SQL_MODE='ANSI_QUOTES'; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER="root"@"localhost" SQL SECURITY DEFINER VIEW "v1" AS select "t1"."a" AS "a" from "t1" +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER="root"@"localhost" SQL SECURITY DEFINER VIEW "v1" AS select "t1"."a" AS "a" from "t1" latin1 latin1_swedish_ci latin1_swedish_ci create view v2 as select a from t2 where a in (select a from v1); drop view v2, v1; drop table t1, t2; --- 1.22/mysql-test/r/view_grant.result 2007-01-18 13:06:07 +03:00 +++ 1.23/mysql-test/r/view_grant.result 2007-03-08 14:31:30 +03:00 @@ -25,8 +25,8 @@ create view v2 as select * from mysqltest.t2; ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 't2' show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` latin1 latin1_swedish_ci latin1_swedish_ci grant create view,drop,select on test.* to mysqltest_1@localhost; use test; alter view v1 as select * from mysqltest.t1; @@ -125,28 +125,28 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found show create view mysqltest.v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v1` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v1` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` latin1 latin1_swedish_ci latin1_swedish_ci explain select c from mysqltest.v2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table show create view mysqltest.v2; -View Create View -v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v2` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` +View Create View Client_cs Connection_col Database_col +v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v2` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` latin1 latin1_swedish_ci latin1_swedish_ci explain select c from mysqltest.v3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found show create view mysqltest.v3; -View Create View -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v3` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` +View Create View Client_cs Connection_col Database_col +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v3` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` latin1 latin1_swedish_ci latin1_swedish_ci explain select c from mysqltest.v4; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table show create view mysqltest.v4; -View Create View -v4 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v4` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` +View Create View Client_cs Connection_col Database_col +v4 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v4` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` latin1 latin1_swedish_ci latin1_swedish_ci revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; drop database mysqltest; @@ -315,8 +315,8 @@ grant create view,select on test.* to mysqltest_1@localhost; create view v1 as select * from mysqltest.t1; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` latin1 latin1_swedish_ci latin1_swedish_ci revoke select on mysqltest.t1 from mysqltest_1@localhost; select * from v1; ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them @@ -483,15 +483,15 @@ use test; create view v1 as select 42; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` latin1 latin1_swedish_ci latin1_swedish_ci select definer into @v1def1 from information_schema.views where table_schema = 'test' and table_name='v1'; drop view v1; create definer=`test14256`@`%` view v1 as select 42; show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` latin1 latin1_swedish_ci latin1_swedish_ci select definer into @v1def2 from information_schema.views where table_schema = 'test' and table_name='v1'; drop view v1; @@ -507,8 +507,8 @@ CREATE TABLE t1 (i INT); CREATE VIEW v1 AS SELECT * FROM t1; SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; use mysqltest; LOCK TABLES v1 READ; @@ -526,11 +526,11 @@ Warnings: Note 1449 There is no 'some_user'@'localhost' registered show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci latin1_swedish_ci show create view v2; -View Create View -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select 1 AS `1` +View Create View Client_cs Connection_col Database_col +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select 1 AS `1` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; drop view v2; CREATE DATABASE mysqltest1; @@ -609,8 +609,8 @@ Warnings: Note 1449 There is no 'no-such-user'@'localhost' registered SHOW CREATE VIEW v; -View Create View -v CREATE ALGORITHM=UNDEFINED DEFINER=`no-such-user`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t1`.`a` AS `a` from `t1` +View Create View Client_cs Connection_col Database_col +v CREATE ALGORITHM=UNDEFINED DEFINER=`no-such-user`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci Warnings: Note 1449 There is no 'no-such-user'@'localhost' registered SELECT * FROM v; @@ -626,14 +626,14 @@ CREATE VIEW view1 AS SELECT * FROM t1; SHOW CREATE VIEW view1; -View Create View -view1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `t1`.`f1` AS `f1` from `t1` +View Create View Client_cs Connection_col Database_col +view1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci CREATE VIEW view2 AS SELECT * FROM view1; # Here comes a suspicious warning SHOW CREATE VIEW view2; -View Create View -view2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select `view1`.`f1` AS `f1` from `view1` +View Create View Client_cs Connection_col Database_col +view2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select `view1`.`f1` AS `f1` from `view1` latin1 latin1_swedish_ci latin1_swedish_ci # But the view view2 is usable SELECT * FROM view2; f1 --- 1.14/mysql-test/lib/init_db.sql 2005-10-14 15:35:31 +04:00 +++ 1.15/mysql-test/lib/init_db.sql 2007-03-08 14:31:29 +03:00 @@ -555,5 +555,8 @@ 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, + charset_client char(32) collate utf8_bin, + collation_connection char(32) collate utf8_bin, + collation_database char(32) collate utf8_bin, PRIMARY KEY (db,name,type) ) character set utf8 comment='Stored Procedures'; --- 1.11/mysql-test/r/rpl_ddl.result 2006-03-02 15:18:45 +03:00 +++ 1.12/mysql-test/r/rpl_ddl.result 2007-03-08 14:31:29 +03:00 @@ -1128,6 +1128,9 @@ Created # Security_type DEFINER Comment +Client_cs latin1 +Connection_col latin1_swedish_ci +Database_col latin1_swedish_ci -------- switch to slave ------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 @@ -1138,6 +1141,9 @@ Created # Security_type DEFINER Comment +Client_cs latin1 +Connection_col latin1_swedish_ci +Database_col latin1_swedish_ci ######## ALTER PROCEDURE p1 COMMENT "I have been altered" ######## @@ -1194,6 +1200,9 @@ Created # Security_type DEFINER Comment I have been altered +Client_cs latin1 +Connection_col latin1_swedish_ci +Database_col latin1_swedish_ci -------- switch to slave ------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 @@ -1204,6 +1213,9 @@ Created # Security_type DEFINER Comment I have been altered +Client_cs latin1 +Connection_col latin1_swedish_ci +Database_col latin1_swedish_ci ######## DROP PROCEDURE p1 ######## @@ -1302,13 +1314,13 @@ -------- switch to master ------- SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci -------- switch to slave ------- SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci ######## ALTER VIEW v1 AS select f1 from t1 ######## @@ -1357,13 +1369,13 @@ -------- switch to master ------- SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci -------- switch to slave ------- SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci latin1_swedish_ci ######## DROP VIEW IF EXISTS v1 ######## @@ -1465,13 +1477,13 @@ -------- switch to master ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode Definer -trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col +trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -------- switch to slave ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode Definer -trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col +trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci ######## DROP TRIGGER trg1 ######## @@ -1520,11 +1532,11 @@ -------- switch to master ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode Definer +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col -------- switch to slave ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode Definer +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col ######## CREATE USER user1@localhost ######## --- 1.30/mysql-test/r/func_in.result 2006-12-08 22:37:59 +03:00 +++ 1.31/mysql-test/r/func_in.result 2007-03-08 14:31:29 +03:00 @@ -225,8 +225,8 @@ 46 CREATE VIEW v1 AS SELECT * FROM t1 WHERE a NOT IN (45); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` <> 45) +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` <> 45) latin1 latin1_swedish_ci latin1_swedish_ci SELECT * FROM v1; a 44 --- 1.83/mysql-test/r/show_check.result 2006-11-28 01:47:19 +03:00 +++ 1.84/mysql-test/r/show_check.result 2007-03-08 14:31:29 +03:00 @@ -570,48 +570,48 @@ DROP PROCEDURE IF EXISTS p1; CREATE VIEW v1 AS SELECT 1; SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` binary binary latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_CACHE 1; SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache 1 AS `1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache 1 AS `1` binary binary latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_NO_CACHE 1; SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache 1 AS `1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache 1 AS `1` binary binary latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT NOW(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select now() AS `NOW()` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select now() AS `NOW()` binary binary latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_CACHE NOW(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache now() AS `NOW()` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache now() AS `NOW()` binary binary latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_NO_CACHE NOW(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()` binary binary latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE NOW(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()` binary binary latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_NO_CACHE SQL_CACHE NOW(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()` binary binary latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE SQL_CACHE NOW(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()` binary binary latin1_swedish_ci DROP VIEW v1; CREATE PROCEDURE p1() BEGIN @@ -622,8 +622,8 @@ END | CALL p1(); SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache 1 AS `1` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache 1 AS `1` binary binary latin1_swedish_ci DROP PROCEDURE p1; DROP VIEW v1; SHOW TABLES FROM no_such_database; --- 1.21/mysql-test/r/temp_table.result 2006-09-29 12:30:28 +04:00 +++ 1.22/mysql-test/r/temp_table.result 2007-03-08 14:31:30 +03:00 @@ -111,8 +111,8 @@ `A` varchar(19) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create view v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'This is view' AS `A` +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'This is view' AS `A` latin1 latin1_swedish_ci latin1_swedish_ci drop view v1; select * from v1; A --- 1.60/mysql-test/r/grant.result 2006-08-31 12:33:57 +04:00 +++ 1.61/mysql-test/r/grant.result 2007-03-08 14:31:29 +03:00 @@ -893,11 +893,11 @@ SHOW CREATE TABLE mysqltest2.v_yn; ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn' SHOW CREATE TABLE mysqltest2.v_ny; -View Create View -v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` +View Create View Client_cs Connection_col Database_col +v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE VIEW mysqltest2.v_ny; -View Create View -v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` +View Create View Client_cs Connection_col Database_col +v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE TABLE mysqltest3.t_nn; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't_nn' SHOW CREATE VIEW mysqltest3.t_nn; @@ -914,17 +914,17 @@ SHOW CREATE VIEW mysqltest2.t_nn; ERROR HY000: 'mysqltest2.t_nn' is not VIEW SHOW CREATE VIEW mysqltest2.v_yy; -View Create View -v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55) +View Create View Client_cs Connection_col Database_col +v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55) latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE TABLE mysqltest2.v_yy; -View Create View -v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55) +View Create View Client_cs Connection_col Database_col +v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55) latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE TABLE mysqltest2.v_nn; -View Create View -v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn` +View Create View Client_cs Connection_col Database_col +v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn` latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE VIEW mysqltest2.v_nn; -View Create View -v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn` +View Create View Client_cs Connection_col Database_col +v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn` latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE TABLE mysqltest2.t_nn; Table Create Table t_nn CREATE TABLE `t_nn` ( --- 1.11/mysql-test/r/rpl_trigger.result 2006-11-14 01:38:34 +03:00 +++ 1.12/mysql-test/r/rpl_trigger.result 2007-03-08 14:31:29 +03:00 @@ -865,8 +865,8 @@ t1 t2 SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode Definer -trg1 INSERT t1 INSERT INTO t2 VALUES(CURRENT_USER()) AFTER NULL +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col +trg1 INSERT t1 INSERT INTO t2 VALUES(CURRENT_USER()) AFTER NULL latin1 latin1_swedish_ci latin1_swedish_ci SELECT * FROM t1; c 1 @@ -892,7 +892,7 @@ SHOW TABLES LIKE 't_'; Tables_in_test (t_) SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode Definer +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col RESET MASTER; START SLAVE; --- 1.39/scripts/mysql_fix_privilege_tables.sql 2006-11-29 14:04:25 +03:00 +++ 1.40/scripts/mysql_fix_privilege_tables.sql 2007-03-08 14:31:30 +03:00 @@ -507,6 +507,9 @@ 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, + charset_client char(32) collate utf8_bin, + collation_connection char(32) collate utf8_bin, + collation_database char(32) collate utf8_bin, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 @@ -564,7 +567,13 @@ MODIFY definer char(77) collate utf8_bin DEFAULT '' NOT NULL, MODIFY comment - char(64) collate utf8_bin DEFAULT '' NOT NULL; + char(64) collate utf8_bin DEFAULT '' NOT NULL, + MODIFY charset_client + char(32) collate utf8_bin DEFAULT NULL, + MODIFY collation_connection + char(32) collate utf8_bin DEFAULT NULL, + MODIFY collation_database + char(32) collate utf8_bin DEFAULT NULL; # Activate the new, possible modified privilege tables # This should not be needed, but gives us some extra testing that the above --- 1.5/mysql-test/r/sp-destruct.result 2006-05-12 20:58:48 +04:00 +++ 1.6/mysql-test/r/sp-destruct.result 2007-03-08 14:31:29 +03:00 @@ -78,6 +78,6 @@ drop function bug14233_2; drop procedure bug14233_3; show procedure status; -Db Name Type Definer Modified Created Security_type Comment +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col show function status; -Db Name Type Definer Modified Created Security_type Comment +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col --- 1.110/mysql-test/r/sp-error.result 2007-01-23 15:03:46 +03:00 +++ 1.111/mysql-test/r/sp-error.result 2007-03-08 14:31:29 +03:00 @@ -682,8 +682,8 @@ begin end| show procedure status like 'bug17015%'| -Db Name Type Definer Modified Created Security_type Comment -test bug17015_0123456789012345678901234567890123456789012345678901234 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test bug17015_0123456789012345678901234567890123456789012345678901234 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci drop procedure bug17015_0123456789012345678901234567890123456789012345678901234| drop procedure if exists bug10969| create procedure bug10969() @@ -1087,8 +1087,8 @@ ERROR 3D000: No database selected use mysqltest2; show procedure status; -Db Name Type Definer Modified Created Security_type Comment -mysqltest2 p1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +mysqltest2 p1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci drop database mysqltest2; use test; DROP FUNCTION IF EXISTS bug13012| @@ -1178,8 +1178,8 @@ 1 1 show procedure status; -Db Name Type Definer Modified Created Security_type Comment -test bug15658 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test bug15658 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci drop procedure ` bug15658`; drop function if exists bug14270; drop table if exists t1; --- 1.31/mysql-test/r/sp-security.result 2006-07-13 17:12:27 +04:00 +++ 1.32/mysql-test/r/sp-security.result 2007-03-08 14:31:29 +03:00 @@ -11,12 +11,12 @@ create procedure stamp(i int) insert into db1_secret.t1 values (user(), i); show procedure status like 'stamp'; -Db Name Type Definer Modified Created Security_type Comment -db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci create function db() returns varchar(64) return database(); show function status like 'db'; -Db Name Type Definer Modified Created Security_type Comment -db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci call stamp(1); select * from t1; u i @@ -55,12 +55,12 @@ anon@localhost 3 alter procedure stamp sql security invoker; show procedure status like 'stamp'; -Db Name Type Definer Modified Created Security_type Comment -db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER latin1 latin1_swedish_ci latin1_swedish_ci alter function db sql security invoker; show function status like 'db'; -Db Name Type Definer Modified Created Security_type Comment -db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER latin1 latin1_swedish_ci latin1_swedish_ci call stamp(4); select * from t1; u i @@ -358,21 +358,21 @@ ---> connection: con1root use mysqltest; SHOW CREATE PROCEDURE wl2897_p1; -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col wl2897_p1 CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`() -SELECT 1 +SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE PROCEDURE wl2897_p3; -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col wl2897_p3 CREATE DEFINER=`a @ b @ c`@`localhost` PROCEDURE `wl2897_p3`() -SELECT 3 +SELECT 3 latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE FUNCTION wl2897_f1; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col wl2897_f1 CREATE DEFINER=`mysqltest_2`@`localhost` FUNCTION `wl2897_f1`() RETURNS int(11) -RETURN 1 +RETURN 1 latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE FUNCTION wl2897_f3; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col wl2897_f3 CREATE DEFINER=`a @ b @ c`@`localhost` FUNCTION `wl2897_f3`() RETURNS int(11) -RETURN 3 +RETURN 3 latin1 latin1_swedish_ci latin1_swedish_ci DROP USER mysqltest_1@localhost; DROP USER mysqltest_2@localhost; DROP DATABASE mysqltest; @@ -436,14 +436,14 @@ SELECT a; END // SHOW CREATE PROCEDURE test.sp19857; -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col sp19857 CREATE DEFINER=`user19857`@`localhost` PROCEDURE `sp19857`() DETERMINISTIC BEGIN DECLARE a INT; SET a=1; SELECT a; -END +END latin1 latin1_swedish_ci latin1_swedish_ci DROP PROCEDURE IF EXISTS test.sp19857; ---> connection: root --- 1.218/mysql-test/r/sp.result 2007-01-18 20:05:03 +03:00 +++ 1.219/mysql-test/r/sp.result 2007-03-08 14:31:30 +03:00 @@ -787,11 +787,11 @@ comment 'Characteristics procedure test' insert into t1 values ("chistics", 1)| show create procedure chistics| -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col chistics CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`() MODIFIES SQL DATA COMMENT 'Characteristics procedure test' -insert into t1 values ("chistics", 1) +insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci call chistics()| select * from t1| id data @@ -799,12 +799,12 @@ delete from t1| alter procedure chistics sql security invoker| show create procedure chistics| -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col chistics CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`() MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'Characteristics procedure test' -insert into t1 values ("chistics", 1) +insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci drop procedure chistics| drop function if exists chistics| create function chistics() returns int @@ -814,12 +814,12 @@ comment 'Characteristics procedure test' return 42| show create function chistics| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col chistics CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11) DETERMINISTIC SQL SECURITY INVOKER COMMENT 'Characteristics procedure test' -return 42 +return 42 latin1 latin1_swedish_ci latin1_swedish_ci select chistics()| chistics() 42 @@ -827,13 +827,13 @@ no sql comment 'Characteristics function test'| show create function chistics| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col chistics CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11) NO SQL DETERMINISTIC SQL SECURITY INVOKER COMMENT 'Characteristics function test' -return 42 +return 42 latin1 latin1_swedish_ci latin1_swedish_ci drop function chistics| insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)| set @@sql_mode = 'ANSI'| @@ -1219,12 +1219,12 @@ 20 2432902008176640000 drop table t3| show function status like '%f%'| -Db Name Type Definer Modified Created Security_type Comment -test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci drop procedure ifac| drop function fac| show function status like '%f%'| -Db Name Type Definer Modified Created Security_type Comment +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col drop table if exists t3| create table t3 ( i int unsigned not null primary key, @@ -1286,7 +1286,7 @@ end while; end| show create procedure opp| -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col opp CREATE DEFINER=`root`@`localhost` PROCEDURE `opp`(n bigint unsigned, out pp bool) begin declare r double; @@ -1312,11 +1312,11 @@ end; end if; end loop; -end +end latin1 latin1_swedish_ci latin1_swedish_ci show procedure status like '%p%'| -Db Name Type Definer Modified Created Security_type Comment -test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci call ip(200)| select * from t3 where i=45 or i=100 or i=199| i p @@ -1327,7 +1327,7 @@ drop procedure opp| drop procedure ip| show procedure status like '%p%'| -Db Name Type Definer Modified Created Security_type Comment +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col drop table if exists t3| create table t3 ( f bigint unsigned not null )| drop procedure if exists fib| @@ -1379,19 +1379,19 @@ comment "111111111111" sql security invoker insert into test.t1 values (x, y)| show procedure status like 'bar'| -Db Name Type Definer Modified Created Security_type Comment -test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER 111111111111 +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER 111111111111 latin1 latin1_swedish_ci latin1_swedish_ci alter procedure bar comment "2222222222" sql security definer| alter procedure bar comment "3333333333"| alter procedure bar| show create procedure bar| -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col bar CREATE DEFINER=`root`@`localhost` PROCEDURE `bar`(x char(16), y int) COMMENT '3333333333' -insert into test.t1 values (x, y) +insert into test.t1 values (x, y) latin1 latin1_swedish_ci latin1_swedish_ci show procedure status like 'bar'| -Db Name Type Definer Modified Created Security_type Comment -test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER 3333333333 +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER 3333333333 latin1 latin1_swedish_ci latin1_swedish_ci drop procedure bar| drop procedure if exists p1| create procedure p1 () @@ -1956,24 +1956,24 @@ end| create function bug2267_4() returns int return 100| call bug2267_1()| -Db Name Type Definer Modified Created Security_type Comment -test bug2267_1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_2 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_3 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_4 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test bug2267_1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +test bug2267_2 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +test bug2267_3 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +test bug2267_4 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci call bug2267_2()| -Db Name Type Definer Modified Created Security_type Comment -test bug2267_4 FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +Db Name Type Definer Modified Created Security_type Comment Client_cs Connection_col Database_col +test bug2267_4 FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci call bug2267_3()| -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col bug2267_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2267_1`() begin show procedure status; -end +end latin1 latin1_swedish_ci latin1_swedish_ci call bug2267_4()| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug2267_4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2267_4`() RETURNS int(11) -return 100 +return 100 latin1 latin1_swedish_ci latin1_swedish_ci drop procedure bug2267_1| drop procedure bug2267_2| drop procedure bug2267_3| @@ -2303,22 +2303,22 @@ return x || y$ set @@sql_mode = ''| show create procedure bug2564_1| -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col bug2564_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2564_1`() COMMENT 'Joe''s procedure' -insert into `t1` values ("foo", 1) +insert into `t1` values ("foo", 1) latin1 latin1_swedish_ci latin1_swedish_ci show create procedure bug2564_2| -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col bug2564_2 ANSI_QUOTES CREATE DEFINER="root"@"localhost" PROCEDURE "bug2564_2"() -insert into "t1" values ('foo', 1) +insert into "t1" values ('foo', 1) latin1 latin1_swedish_ci latin1_swedish_ci show create function bug2564_3| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug2564_3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2564_3`(x int, y int) RETURNS int(11) -return x || y +return x || y latin1 latin1_swedish_ci latin1_swedish_ci show create function bug2564_4| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI CREATE DEFINER="root"@"localhost" FUNCTION "bug2564_4"(x int, y int) RETURNS int(11) -return x || y +return x || y latin1 latin1_swedish_ci latin1_swedish_ci drop procedure bug2564_1| drop procedure bug2564_2| drop function bug2564_3| @@ -3984,11 +3984,11 @@ return 42; end */;; show create function bug14723;; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug14723 CREATE DEFINER=`root`@`localhost` FUNCTION `bug14723`() RETURNS bigint(20) main_loop: begin return 42; -end +end latin1 latin1_swedish_ci latin1_swedish_ci select bug14723();; bug14723() 42 @@ -3997,11 +3997,11 @@ select 42; end */;; show create procedure bug14723;; -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col bug14723 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug14723`() main_loop: begin select 42; -end +end latin1 latin1_swedish_ci latin1_swedish_ci call bug14723();; 42 42 @@ -5081,21 +5081,21 @@ CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r RETURN ""| SHOW CREATE FUNCTION bug16211_f1| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8 -RETURN "" +RETURN "" latin1 latin1_swedish_ci utf8_general_ci SHOW CREATE FUNCTION bug16211_f2| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r -RETURN "" +RETURN "" latin1 latin1_swedish_ci utf8_general_ci SHOW CREATE FUNCTION mysqltest2.bug16211_f3| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8 -RETURN "" +RETURN "" latin1 latin1_swedish_ci utf8_general_ci SHOW CREATE FUNCTION mysqltest2.bug16211_f4| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r -RETURN "" +RETURN "" latin1 latin1_swedish_ci utf8_general_ci SELECT dtd_identifier FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| @@ -5131,21 +5131,21 @@ ALTER DATABASE mysqltest1 CHARACTER SET cp1251| ALTER DATABASE mysqltest2 CHARACTER SET cp1251| SHOW CREATE FUNCTION bug16211_f1| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8 -RETURN "" +RETURN "" latin1 latin1_swedish_ci utf8_general_ci SHOW CREATE FUNCTION bug16211_f2| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r -RETURN "" +RETURN "" latin1 latin1_swedish_ci utf8_general_ci SHOW CREATE FUNCTION mysqltest2.bug16211_f3| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8 -RETURN "" +RETURN "" latin1 latin1_swedish_ci utf8_general_ci SHOW CREATE FUNCTION mysqltest2.bug16211_f4| -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r -RETURN "" +RETURN "" latin1 latin1_swedish_ci utf8_general_ci SELECT dtd_identifier FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| @@ -5390,9 +5390,9 @@ drop procedure if exists bug21416| create procedure bug21416() show create procedure bug21416| call bug21416()| -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col bug21416 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21416`() -show create procedure bug21416 +show create procedure bug21416 latin1 latin1_swedish_ci latin1_swedish_ci drop procedure bug21416| DROP PROCEDURE IF EXISTS bug21414| CREATE PROCEDURE bug21414() SELECT 1| @@ -5405,7 +5405,7 @@ set names utf8| drop database if exists това_е_дълго_име_за_база_данни_нали| create database това_е_дълго_име_за_база_данни_нали| -INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','')| +INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','', 33, 33, 8)| call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()| ERROR HY000: Failed to load routine това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) drop database това_е_дълго_име_за_база_данни_нали| @@ -5626,6 +5626,53 @@ Called B drop procedure proc_21462_a| drop procedure proc_21462_b| +DROP PROCEDURE IF EXISTS bug25212| + +---> connection: bug25212_con1 +SET @@character_set_client= utf8; +SET @@character_set_results= utf8; +SET @@character_set_connection= utf8; +SET @@collation_connection= utf8_unicode_ci; +CREATE PROCEDURE bug25212() +SELECT +CHARSET('string literal'), +COLLATION('string_literal'), +@@character_set_connection, +@@character_set_client, +@@character_set_server| + +---> connection: bug25212_con2 +SET @@character_set_client= cp1251; +SET @@character_set_results= cp1251; +SET @@character_set_connection= cp1251; +SET @@collation_connection= cp1251_general_cs; +CALL bug25212()| +CHARSET('string literal') COLLATION('string_literal') @@character_set_connection @@character_set_client @@character_set_server +utf8 utf8_unicode_ci utf8 utf8 latin1 +SET @@character_set_client= koi8r; +SET @@character_set_results= koi8r; +SET @@character_set_connection= koi8r; +SET @@collation_connection= koi8r_bin; +CALL bug25212()| +CHARSET('string literal') COLLATION('string_literal') @@character_set_connection @@character_set_client @@character_set_server +utf8 utf8_unicode_ci utf8 utf8 latin1 + +---> connection: bug25212_con3 +SET @@character_set_client= koi8r; +SET @@character_set_results= koi8r; +SET @@character_set_connection= koi8r; +SET @@collation_connection= koi8r_bin; +CALL bug25212()| +CHARSET('string literal') COLLATION('string_literal') @@character_set_connection @@character_set_client @@character_set_server +utf8 utf8_unicode_ci utf8 utf8 latin1 +SET @@character_set_client= cp1251; +SET @@character_set_results= cp1251; +SET @@character_set_connection= cp1251; +SET @@collation_connection= cp1251_general_cs; +CALL bug25212()| +CHARSET('string literal') COLLATION('string_literal') @@character_set_connection @@character_set_client @@character_set_server +utf8 utf8_unicode_ci utf8 utf8 latin1 +DROP PROCEDURE bug25212| drop table if exists t3| drop procedure if exists proc_bug19733| create table t3 (s1 int)| @@ -5648,6 +5695,7 @@ DROP TABLE IF EXISTS bug23760_log| DROP PROCEDURE IF EXISTS bug23760_update_log| DROP PROCEDURE IF EXISTS bug23760_test_row_count| +DROP PROCEDURE IF EXISTS bug23760_test_row_count2| DROP FUNCTION IF EXISTS bug23760_rc_test| CREATE TABLE bug23760 ( id INT NOT NULL AUTO_INCREMENT , @@ -5724,6 +5772,7 @@ DROP TABLE bug23760, bug23760_log| DROP PROCEDURE bug23760_update_log| DROP PROCEDURE bug23760_test_row_count| +DROP PROCEDURE bug23760_test_row_count2| DROP FUNCTION bug23760_rc_test| DROP PROCEDURE IF EXISTS bug24117| DROP TABLE IF EXISTS t3| @@ -5741,4 +5790,118 @@ CALL bug24117()| DROP PROCEDURE bug24117| DROP TABLE t3| +DROP DATABASE IF EXISTS mysqltest1| +DROP DATABASE IF EXISTS mysqltest2| +CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci| +CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci| +use mysqltest1| +CREATE PROCEDURE bug21249_p1() +BEGIN +DECLARE v1 CHAR(10); +SELECT CHARSET(v1), COLLATION(v1); +END| +CREATE PROCEDURE bug21249_p2() +BEGIN +DECLARE v1 CHAR(10) CHARACTER SET utf8; +SELECT CHARSET(v1), COLLATION(v1); +END| +CREATE PROCEDURE mysqltest2.bug21249_p3() +BEGIN +DECLARE v1 CHAR(10); +SELECT CHARSET(v1), COLLATION(v1); +END| +CREATE PROCEDURE mysqltest2.bug21249_p4() +BEGIN +DECLARE v1 CHAR(10) CHARACTER SET utf8; +SELECT CHARSET(v1), COLLATION(v1); +END| +SHOW CREATE PROCEDURE bug21249_p1| +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +bug21249_p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21249_p1`() +BEGIN +DECLARE v1 CHAR(10); +SELECT CHARSET(v1), COLLATION(v1); +END cp1251 cp1251_general_cs utf8_unicode_ci +SHOW CREATE PROCEDURE bug21249_p2| +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +bug21249_p2 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21249_p2`() +BEGIN +DECLARE v1 CHAR(10) CHARACTER SET utf8; +SELECT CHARSET(v1), COLLATION(v1); +END cp1251 cp1251_general_cs utf8_unicode_ci +SHOW CREATE PROCEDURE mysqltest2.bug21249_p3| +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +bug21249_p3 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21249_p3`() +BEGIN +DECLARE v1 CHAR(10); +SELECT CHARSET(v1), COLLATION(v1); +END cp1251 cp1251_general_cs utf8_unicode_ci +SHOW CREATE PROCEDURE mysqltest2.bug21249_p4| +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +bug21249_p4 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21249_p4`() +BEGIN +DECLARE v1 CHAR(10) CHARACTER SET utf8; +SELECT CHARSET(v1), COLLATION(v1); +END cp1251 cp1251_general_cs utf8_unicode_ci +CALL bug21249_p1| +CHARSET(v1) COLLATION(v1) +utf8 utf8_unicode_ci +CALL bug21249_p2| +CHARSET(v1) COLLATION(v1) +utf8 utf8_general_ci +CALL mysqltest2.bug21249_p3| +CHARSET(v1) COLLATION(v1) +utf8 utf8_unicode_ci +CALL mysqltest2.bug21249_p4| +CHARSET(v1) COLLATION(v1) +utf8 utf8_general_ci +ALTER DATABASE mysqltest1 CHARACTER SET cp1251 COLLATE cp1251_general_cs| +ALTER DATABASE mysqltest2 CHARACTER SET cp1251 COLLATE cp1251_general_cs| + +---> connection: bug21249_con +SHOW CREATE PROCEDURE bug21249_p1| +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +bug21249_p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21249_p1`() +BEGIN +DECLARE v1 CHAR(10); +SELECT CHARSET(v1), COLLATION(v1); +END cp1251 cp1251_general_cs utf8_unicode_ci +SHOW CREATE PROCEDURE bug21249_p2| +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +bug21249_p2 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21249_p2`() +BEGIN +DECLARE v1 CHAR(10) CHARACTER SET utf8; +SELECT CHARSET(v1), COLLATION(v1); +END cp1251 cp1251_general_cs utf8_unicode_ci +SHOW CREATE PROCEDURE mysqltest2.bug21249_p3| +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +bug21249_p3 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21249_p3`() +BEGIN +DECLARE v1 CHAR(10); +SELECT CHARSET(v1), COLLATION(v1); +END cp1251 cp1251_general_cs utf8_unicode_ci +SHOW CREATE PROCEDURE mysqltest2.bug21249_p4| +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +bug21249_p4 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21249_p4`() +BEGIN +DECLARE v1 CHAR(10) CHARACTER SET utf8; +SELECT CHARSET(v1), COLLATION(v1); +END cp1251 cp1251_general_cs utf8_unicode_ci +CALL bug21249_p1| +CHARSET(v1) COLLATION(v1) +cp1251 cp1251_general_cs +CALL bug21249_p2| +CHARSET(v1) COLLATION(v1) +utf8 utf8_general_ci +CALL mysqltest2.bug21249_p3| +CHARSET(v1) COLLATION(v1) +cp1251 cp1251_general_cs +CALL mysqltest2.bug21249_p4| +CHARSET(v1) COLLATION(v1) +utf8 utf8_general_ci + +---> connection: default +use test| +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| drop table t1,t2; --- 1.208/mysql-test/t/sp.test 2007-01-18 20:05:03 +03:00 +++ 1.209/mysql-test/t/sp.test 2007-03-08 14:31:30 +03:00 @@ -6350,7 +6350,7 @@ drop database if exists това_е_дълго_име_за_база_данни_нали| --enable_warnings create database това_е_дълго_име_за_база_данни_нали| -INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','')| +INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','', 33, 33, 8)| --error ER_SP_PROC_TABLE_CORRUPT call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()| drop database това_е_дълго_име_за_база_данни_нали| @@ -6589,6 +6589,83 @@ # +# BUG#25212: Character set of string constant is ignored +# + +# Prepare. + +--disable_warnings +DROP PROCEDURE IF EXISTS bug25212| +--enable_warnings + +# Create procedure. + +--connect (bug25212_con1,localhost,root,,) +--echo +--echo ---> connection: bug25212_con1 + +SET @@character_set_client= utf8; +SET @@character_set_results= utf8; +SET @@character_set_connection= utf8; +SET @@collation_connection= utf8_unicode_ci; + +CREATE PROCEDURE bug25212() + SELECT + CHARSET('string literal'), + COLLATION('string_literal'), + @@character_set_connection, + @@character_set_client, + @@character_set_server| + +# Make a separate connection, set some character set and execute the stored +# routine. + +--connect (bug25212_con2,localhost,root,,) +--echo +--echo ---> connection: bug25212_con2 + +SET @@character_set_client= cp1251; +SET @@character_set_results= cp1251; +SET @@character_set_connection= cp1251; +SET @@collation_connection= cp1251_general_cs; + +CALL bug25212()| + +SET @@character_set_client= koi8r; +SET @@character_set_results= koi8r; +SET @@character_set_connection= koi8r; +SET @@collation_connection= koi8r_bin; + +CALL bug25212()| + +# Make another connection, set another character set and execute the stored +# routine again. + +--connect (bug25212_con3,localhost,root,,) +--echo +--echo ---> connection: bug25212_con3 + +SET @@character_set_client= koi8r; +SET @@character_set_results= koi8r; +SET @@character_set_connection= koi8r; +SET @@collation_connection= koi8r_bin; + +CALL bug25212()| + +SET @@character_set_client= cp1251; +SET @@character_set_results= cp1251; +SET @@character_set_connection= cp1251; +SET @@collation_connection= cp1251_general_cs; + +CALL bug25212()| + +# Cleanup. + +DROP PROCEDURE bug25212| + + + +# # Bug#19733 "Repeated alter, or repeated create/drop, fails" # Check that CREATE/DROP INDEX is re-execution friendly. # @@ -6626,6 +6703,7 @@ DROP TABLE IF EXISTS bug23760_log| DROP PROCEDURE IF EXISTS bug23760_update_log| DROP PROCEDURE IF EXISTS bug23760_test_row_count| +DROP PROCEDURE IF EXISTS bug23760_test_row_count2| DROP FUNCTION IF EXISTS bug23760_rc_test| --enable_warnings CREATE TABLE bug23760 ( @@ -6688,6 +6766,7 @@ DROP TABLE bug23760, bug23760_log| DROP PROCEDURE bug23760_update_log| DROP PROCEDURE bug23760_test_row_count| +DROP PROCEDURE bug23760_test_row_count2| DROP FUNCTION bug23760_rc_test| # @@ -6713,6 +6792,112 @@ CALL bug24117()| DROP PROCEDURE bug24117| DROP TABLE t3| + + +# +# BUG#21249: Character set of SP-var can be ignored. +# + +# Prepare: create database with fixed, pre-defined character set. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1| +DROP DATABASE IF EXISTS mysqltest2| +--enable_warnings + +CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci| +CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci| + +# Test case: + +use mysqltest1| + +# - Create two stored routines -- with and without explicit +# CHARSET-clause for SP-variable; + +CREATE PROCEDURE bug21249_p1() +BEGIN + DECLARE v1 CHAR(10); + + SELECT CHARSET(v1), COLLATION(v1); +END| + +CREATE PROCEDURE bug21249_p2() +BEGIN + DECLARE v1 CHAR(10) CHARACTER SET utf8; + + SELECT CHARSET(v1), COLLATION(v1); +END| + +CREATE PROCEDURE mysqltest2.bug21249_p3() +BEGIN + DECLARE v1 CHAR(10); + + SELECT CHARSET(v1), COLLATION(v1); +END| + +CREATE PROCEDURE mysqltest2.bug21249_p4() +BEGIN + DECLARE v1 CHAR(10) CHARACTER SET utf8; + + SELECT CHARSET(v1), COLLATION(v1); +END| + +# - Check that CHARSET-clause is specified for the second routine +# only; + +SHOW CREATE PROCEDURE bug21249_p1| +SHOW CREATE PROCEDURE bug21249_p2| + +SHOW CREATE PROCEDURE mysqltest2.bug21249_p3| +SHOW CREATE PROCEDURE mysqltest2.bug21249_p4| + +CALL bug21249_p1| +CALL bug21249_p2| + +CALL mysqltest2.bug21249_p3| +CALL mysqltest2.bug21249_p4| + +# - Alter database character set. + +ALTER DATABASE mysqltest1 CHARACTER SET cp1251 COLLATE cp1251_general_cs| +ALTER DATABASE mysqltest2 CHARACTER SET cp1251 COLLATE cp1251_general_cs| + +# - Change connection to flush SP-cache. + +--connect (bug21249_con,localhost,root,,mysqltest1); +--echo +--echo ---> connection: bug21249_con + +# - Check that CHARSET-clause has not changed. + +SHOW CREATE PROCEDURE bug21249_p1| +SHOW CREATE PROCEDURE bug21249_p2| + +SHOW CREATE PROCEDURE mysqltest2.bug21249_p3| +SHOW CREATE PROCEDURE mysqltest2.bug21249_p4| + +CALL bug21249_p1| +CALL bug21249_p2| + +CALL mysqltest2.bug21249_p3| +CALL mysqltest2.bug21249_p4| + +# - Restore connection. + +--connection default +--echo +--echo ---> connection: default + +--disconnect bug21249_con + +# Cleanup. + +use test| + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + # # NOTE: The delimiter is `|`, and not `;`. It is changed to `;` --- 1.125/sql/sp.cc 2006-12-23 22:04:26 +03:00 +++ 1.126/sql/sp.cc 2007-03-08 14:31:30 +03:00 @@ -36,7 +36,8 @@ db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp, ulong sql_mode, const char *params, const char *returns, const char *body, st_sp_chistics &chistics, - const char *definer, longlong created, longlong modified); + const char *definer, longlong created, longlong modified, + const st_query_ctx *def_ctx); /* * @@ -62,6 +63,9 @@ MYSQL_PROC_FIELD_MODIFIED, MYSQL_PROC_FIELD_SQL_MODE, MYSQL_PROC_FIELD_COMMENT, + MYSQL_PROC_FIELD_CLIENT_CHARSET, + MYSQL_PROC_FIELD_CONNECTION_COLLATION, + MYSQL_PROC_FIELD_DB_COLLATION, MYSQL_PROC_FIELD_COUNT }; @@ -225,6 +229,84 @@ } +enum enum_retrive_cs_mode +{ + GET_CHARSET_ONLY, + GET_CHARSET_AND_COLLATION +}; + + +static bool +db_retrieve_cs(THD *thd, + const sp_name *name, + Field *field, + CHARSET_INFO **cs, + CHARSET_INFO *default_cs, + enum_retrive_cs_mode mode) +{ + char cs_name_buf[MY_CS_NAME_SIZE]; + String cs_name(cs_name_buf, sizeof (cs_name_buf), system_charset_info); + + if (field->is_null()) + { + push_warning_printf(thd, + MYSQL_ERROR::WARN_LEVEL_WARN, + ER_SR_NO_DEF_CONTEXT, + ER(ER_SR_NO_DEF_CONTEXT), + (const char *) thd->strmake(name->m_db.str, name->m_db.length), + (const char *) thd->strmake(name->m_name.str, name->m_name.length), + (const char *) field->field_name); + + *cs= default_cs; + + return TRUE; + } + + field->val_str(&cs_name, &cs_name); + + if (!cs_name.length()) + { + push_warning_printf(thd, + MYSQL_ERROR::WARN_LEVEL_WARN, + ER_SR_INVALID_CHARSET, + ER(ER_SR_INVALID_CHARSET), + (const char *) cs_name.c_ptr(), + (const char *) thd->strmake(name->m_db.str, name->m_db.length), + (const char *) thd->strmake(name->m_name.str, name->m_name.length), + (const char *) field->field_name); + + *cs= default_cs; + + return TRUE; + } + + if (mode == GET_CHARSET_ONLY) + *cs= get_charset_by_csname(cs_name.c_ptr(), MY_CS_PRIMARY, MYF(0)); + else if (mode == GET_CHARSET_AND_COLLATION) + *cs= get_charset_by_name(cs_name.c_ptr(), MYF(0)); + else + DBUG_ASSERT(FALSE); + + if (!(*cs)) + { + push_warning_printf(thd, + MYSQL_ERROR::WARN_LEVEL_WARN, + ER_SR_UNKNOWN_CHARSET, + ER(ER_SR_UNKNOWN_CHARSET), + (const char *) cs_name.c_ptr(), + (const char *) thd->strmake(name->m_db.str, name->m_db.length), + (const char *) thd->strmake(name->m_name.str, name->m_name.length), + (const char *) field->field_name); + + *cs= default_cs; + + return TRUE; + } + + return FALSE; +} + + /* Find routine definition in mysql.proc table and create corresponding sp_head object for it. @@ -262,6 +344,8 @@ String str(buff, sizeof(buff), &my_charset_bin); ulong sql_mode; Open_tables_state open_tables_state_backup; + st_query_ctx def_ctx; + DBUG_ENTER("db_find_routine"); DBUG_PRINT("enter", ("type: %d name: %.*s", type, name->m_name.length, name->m_name.str)); @@ -362,13 +446,32 @@ chistics.comment.str= ptr; chistics.comment.length= length; + db_retrieve_cs(thd, name, + table->field[MYSQL_PROC_FIELD_CLIENT_CHARSET], + &def_ctx.client_cs, + thd->variables.character_set_client, + GET_CHARSET_ONLY); + + db_retrieve_cs(thd, name, + table->field[MYSQL_PROC_FIELD_CONNECTION_COLLATION], + &def_ctx.connection_cl, + thd->variables.collation_connection, + GET_CHARSET_AND_COLLATION); + + db_retrieve_cs(thd, name, + table->field[MYSQL_PROC_FIELD_DB_COLLATION], + &def_ctx.db_cl, + thd->variables.collation_database, + GET_CHARSET_AND_COLLATION); + close_proc_table(thd, &open_tables_state_backup); table= 0; ret= db_load_routine(thd, type, name, sphp, sql_mode, params, returns, body, chistics, - definer, created, modified); - + definer, created, modified, + &def_ctx); + done: if (table) close_proc_table(thd, &open_tables_state_backup); @@ -380,7 +483,8 @@ db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp, ulong sql_mode, const char *params, const char *returns, const char *body, st_sp_chistics &chistics, - const char *definer, longlong created, longlong modified) + const char *definer, longlong created, longlong modified, + const st_query_ctx *def_ctx) { LEX *old_lex= thd->lex, newlex; String defstr; @@ -411,7 +515,7 @@ definer_user_name.str, &definer_user_name.length, definer_host_name.str, &definer_host_name.length); - defstr.set_charset(system_charset_info); + defstr.set_charset(def_ctx->client_cs); /* We have to add DEFINER clause and provide proper routine characterstics in @@ -437,7 +541,17 @@ lex_start(thd, (uchar*)defstr.c_ptr(), defstr.length()); thd->spcont= 0; - if (MYSQLparse(thd) || thd->is_fatal_error || newlex.sphead == NULL) + + st_query_ctx saved_def_ctx; + + save_current_query_ctx(thd, &saved_def_ctx); + switch_query_ctx(thd, def_ctx); + + ret= MYSQLparse(thd); + + switch_query_ctx(thd, &saved_def_ctx); + + if (ret || thd->is_fatal_error || newlex.sphead == NULL) { sp_head *sp= newlex.sphead; @@ -453,6 +567,7 @@ *sphp= newlex.sphead; (*sphp)->set_definer(&definer_user_name, &definer_host_name); (*sphp)->set_info(created, modified, &chistics, sql_mode); + (*sphp)->m_defstr_ctx= *def_ctx; (*sphp)->optimize(); } end: @@ -594,6 +709,24 @@ } } + table->field[MYSQL_PROC_FIELD_CLIENT_CHARSET]->set_notnull(); + table->field[MYSQL_PROC_FIELD_CLIENT_CHARSET]->store( + thd->charset()->csname, + strlen(thd->charset()->csname), + system_charset_info); + + table->field[MYSQL_PROC_FIELD_CONNECTION_COLLATION]->set_notnull(); + table->field[MYSQL_PROC_FIELD_CONNECTION_COLLATION]->store( + thd->variables.collation_connection->name, + strlen(thd->variables.collation_connection->name), + system_charset_info); + + table->field[MYSQL_PROC_FIELD_DB_COLLATION]->set_notnull(); + table->field[MYSQL_PROC_FIELD_DB_COLLATION]->store( + thd->variables.collation_database->name, + strlen(thd->variables.collation_database->name), + system_charset_info); + ret= SP_OK; if (table->file->write_row(table->record[0])) ret= SP_WRITE_ROW_FAILED; @@ -712,15 +845,18 @@ static struct st_used_field init_fields[]= { - { "Db", NAME_LEN, MYSQL_TYPE_STRING, 0}, - { "Name", NAME_LEN, MYSQL_TYPE_STRING, 0}, - { "Type", 9, MYSQL_TYPE_STRING, 0}, - { "Definer", 77, MYSQL_TYPE_STRING, 0}, - { "Modified", 0, MYSQL_TYPE_TIMESTAMP, 0}, - { "Created", 0, MYSQL_TYPE_TIMESTAMP, 0}, - { "Security_type", 1, MYSQL_TYPE_STRING, 0}, - { "Comment", NAME_LEN, MYSQL_TYPE_STRING, 0}, - { 0, 0, MYSQL_TYPE_STRING, 0} + { "Db", NAME_LEN, MYSQL_TYPE_STRING, 0}, + { "Name", NAME_LEN, MYSQL_TYPE_STRING, 0}, + { "Type", 9, MYSQL_TYPE_STRING, 0}, + { "Definer", USER_HOST_BUFF_SIZE, MYSQL_TYPE_STRING, 0}, + { "Modified", 0, MYSQL_TYPE_TIMESTAMP, 0}, + { "Created", 0, MYSQL_TYPE_TIMESTAMP, 0}, + { "Security_type", 1, MYSQL_TYPE_STRING, 0}, + { "Comment", NAME_LEN, MYSQL_TYPE_STRING, 0}, + { "Client_cs", 0, MYSQL_TYPE_SHORT, 0}, + { "Connection_col", 0, MYSQL_TYPE_SHORT, 0}, + { "Database_col", 0, MYSQL_TYPE_SHORT, 0}, + { 0, 0, MYSQL_TYPE_STRING, 0} }; @@ -1022,7 +1158,8 @@ if (db_load_routine(thd, type, name, &new_sp, sp->m_sql_mode, sp->m_params.str, returns, sp->m_body.str, *sp->m_chistics, definer, - sp->m_created, sp->m_modified) == SP_OK) + sp->m_created, sp->m_modified, + &sp->m_defstr_ctx) == SP_OK) { sp->m_last_cached_sp->m_next_cached_sp= new_sp; new_sp->m_recursion_level= level; @@ -1172,23 +1309,40 @@ int -sp_show_create_procedure(THD *thd, sp_name *name) +sp_show_create_routine(THD *thd, sp_name *name, int routine_type) { int ret= SP_KEY_NOT_FOUND; sp_head *sp; - DBUG_ENTER("sp_show_create_procedure"); + sp_cache *cache = routine_type == TYPE_ENUM_PROCEDURE ? + thd->sp_proc_cache : thd->sp_func_cache; + + DBUG_ENTER("sp_show_create_routine"); DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); - /* - Increase the recursion limit for this statement. SHOW CREATE PROCEDURE - does not do actual recursion. - */ - thd->variables.max_sp_recursion_depth++; - if ((sp= sp_find_routine(thd, TYPE_ENUM_PROCEDURE, name, - &thd->sp_proc_cache, FALSE))) - ret= sp->show_create_procedure(thd); + DBUG_ASSERT(routine_type == TYPE_ENUM_PROCEDURE || + routine_type == TYPE_ENUM_FUNCTION); + + if (routine_type == TYPE_ENUM_PROCEDURE) + { + /* + SHOW CREATE PROCEDURE may require two instances of one sp_head + object when SHOW CREATE PROCEDURE is called for the procedure that + is being executed. Basically, there is no actual recursion, so we + increase the recursion limit for this statement (kind of hack). + + SHOW CREATE FUNCTION does not require this because SHOW CREATE + statements are prohibitted within stored functions. + */ + + thd->variables.max_sp_recursion_depth++; + } + + if ((sp= sp_find_routine(thd, routine_type, name, &cache, FALSE))) + ret= sp->show_create_routine(thd, routine_type); + + if (routine_type == TYPE_ENUM_PROCEDURE) + thd->variables.max_sp_recursion_depth--; - thd->variables.max_sp_recursion_depth--; DBUG_RETURN(ret); } @@ -1245,24 +1399,6 @@ if (!ret) sp_cache_invalidate(); DBUG_RETURN(ret); -} - - -int -sp_show_create_function(THD *thd, sp_name *name) -{ - sp_head *sp; - DBUG_ENTER("sp_show_create_function"); - DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); - - if ((sp= sp_find_routine(thd, TYPE_ENUM_FUNCTION, name, - &thd->sp_func_cache, FALSE))) - { - int ret= sp->show_create_function(thd); - - DBUG_RETURN(ret); - } - DBUG_RETURN(SP_KEY_NOT_FOUND); } --- 1.35/sql/sp.h 2006-12-23 22:04:26 +03:00 +++ 1.36/sql/sp.h 2007-03-08 14:31:30 +03:00 @@ -55,9 +55,6 @@ sp_update_procedure(THD *thd, sp_name *name, st_sp_chistics *chistics); int -sp_show_create_procedure(THD *thd, sp_name *name); - -int sp_show_status_procedure(THD *thd, const char *wild); int @@ -70,7 +67,7 @@ sp_update_function(THD *thd, sp_name *name, st_sp_chistics *chistics); int -sp_show_create_function(THD *thd, sp_name *name); +sp_show_create_routine(THD *thd, sp_name *name, int routine_type); int sp_show_status_function(THD *thd, const char *wild); --- 1.230/sql/sp_head.cc 2007-01-17 21:30:01 +03:00 +++ 1.231/sql/sp_head.cc 2007-03-08 14:31:30 +03:00 @@ -452,6 +452,10 @@ m_return_field_def.charset = NULL; + m_defstr_ctx.client_cs= NULL; + m_defstr_ctx.connection_cl= NULL; + m_defstr_ctx.db_cl= NULL; + extern byte * sp_table_key(const byte *ptr, uint *plen, my_bool first); DBUG_ENTER("sp_head::sp_head"); @@ -925,6 +929,8 @@ Item_change_list old_change_list; String old_packet; + st_query_ctx saved_def_ctx; + /* Use some extra margin for possible SP recursion and functions */ if (check_stack_overrun(thd, 8 * STACK_MIN_SIZE, (char*)&old_packet)) DBUG_RETURN(TRUE); @@ -1008,6 +1014,11 @@ */ thd->spcont->callers_arena= &backup_arena; + /* Switch query context. */ + + save_current_query_ctx(thd, &saved_def_ctx); + switch_query_ctx(thd, &m_defstr_ctx); + do { sp_instr *i; @@ -1092,6 +1103,12 @@ } } while (!err_status && !thd->killed); + /* Restore query context. */ + + switch_query_ctx(thd, &saved_def_ctx); + + /* Restore arena. */ + thd->restore_active_arena(&execute_arena, &backup_arena); thd->spcont->pop_all_cursors(); // To avoid memory leaks after an error @@ -2027,55 +2044,6 @@ } -int -sp_head::show_create_procedure(THD *thd) -{ - Protocol *protocol= thd->protocol; - char buff[2048]; - String buffer(buff, sizeof(buff), system_charset_info); - int res; - List<Item> field_list; - byte *sql_mode_str; - ulong sql_mode_len; - bool full_access; - DBUG_ENTER("sp_head::show_create_procedure"); - DBUG_PRINT("info", ("procedure %s", m_name.str)); - - LINT_INIT(sql_mode_str); - LINT_INIT(sql_mode_len); - - if (check_show_routine_access(thd, this, &full_access)) - DBUG_RETURN(1); - - sql_mode_str= - sys_var_thd_sql_mode::symbolic_mode_representation(thd, - m_sql_mode, - &sql_mode_len); - field_list.push_back(new Item_empty_string("Procedure", NAME_LEN)); - field_list.push_back(new Item_empty_string("sql_mode", sql_mode_len)); - // 1024 is for not to confuse old clients - Item_empty_string *definition= - new Item_empty_string("Create Procedure", max(buffer.length(),1024)); - definition->maybe_null= TRUE; - field_list.push_back(definition); - - if (protocol->send_fields(&field_list, Protocol::SEND_NUM_ROWS | - Protocol::SEND_EOF)) - DBUG_RETURN(1); - protocol->prepare_for_resend(); - protocol->store(m_name.str, m_name.length, system_charset_info); - protocol->store((char*) sql_mode_str, sql_mode_len, system_charset_info); - if (full_access) - protocol->store(m_defstr.str, m_defstr.length, system_charset_info); - else - protocol->store_null(); - res= protocol->write(); - send_eof(thd); - - DBUG_RETURN(res); -} - - /* Add instruction to SP @@ -2099,50 +2067,86 @@ } -int -sp_head::show_create_function(THD *thd) +bool sp_head::show_create_routine(THD *thd, int routine_type) { - Protocol *protocol= thd->protocol; - char buff[2048]; - String buffer(buff, sizeof(buff), system_charset_info); - int res; - List<Item> field_list; - byte *sql_mode_str; - ulong sql_mode_len; + const char *col1_caption= routine_type == TYPE_ENUM_PROCEDURE ? + "Procedure" : "Function"; + + const char *col3_caption= routine_type == TYPE_ENUM_PROCEDURE ? + "Create Procedure" : "Create Function"; + + int ret_code; + + Protocol *p= thd->protocol; + List<Item> fields; + + char *sql_mode_str; + ulong sql_mode_length; + bool full_access; - DBUG_ENTER("sp_head::show_create_function"); - DBUG_PRINT("info", ("procedure %s", m_name.str)); - LINT_INIT(sql_mode_str); - LINT_INIT(sql_mode_len); + + DBUG_ENTER("sp_head::show_create_routine"); + DBUG_PRINT("info", ("routine %s", m_name.str)); + + DBUG_ASSERT(routine_type == TYPE_ENUM_PROCEDURE || + routine_type == TYPE_ENUM_FUNCTION); if (check_show_routine_access(thd, this, &full_access)) - DBUG_RETURN(1); + DBUG_RETURN(TRUE); sql_mode_str= - sys_var_thd_sql_mode::symbolic_mode_representation(thd, - m_sql_mode, - &sql_mode_len); - field_list.push_back(new Item_empty_string("Function",NAME_LEN)); - field_list.push_back(new Item_empty_string("sql_mode", sql_mode_len)); - Item_empty_string *definition= - new Item_empty_string("Create Function", max(buffer.length(),1024)); - definition->maybe_null= TRUE; - field_list.push_back(definition); + (char *) sys_var_thd_sql_mode::symbolic_mode_representation( + thd, m_sql_mode, &sql_mode_length); + + /* Send header. */ + + fields.push_back(new Item_empty_string(col1_caption, NAME_LEN)); + fields.push_back(new Item_empty_string("sql_mode", sql_mode_length)); + + { + /* + NOTE: SQL statement field must be not less than 1024 in order not to + confuse old clients. + */ + + Item_empty_string *stmt_fld= + new Item_empty_string(col3_caption, + max(m_defstr.length, 1024)); + + stmt_fld->maybe_null= TRUE; + + fields.push_back(stmt_fld); + } + + fields.push_back(new Item_empty_string("Client_cs", MY_CS_NAME_SIZE)); + fields.push_back(new Item_empty_string("Connection_col", MY_CS_NAME_SIZE)); + fields.push_back(new Item_empty_string("Database_col", MY_CS_NAME_SIZE)); + + if (p->send_fields(&fields, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) + DBUG_RETURN(TRUE); + + /* Send data. */ + + p->prepare_for_resend(); + + p->store(m_name.str, m_name.length, system_charset_info); + p->store(sql_mode_str, sql_mode_length, system_charset_info); - if (protocol->send_fields(&field_list, - Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) - DBUG_RETURN(1); - protocol->prepare_for_resend(); - protocol->store(m_name.str, m_name.length, system_charset_info); - protocol->store((char*) sql_mode_str, sql_mode_len, system_charset_info); if (full_access) - protocol->store(m_defstr.str, m_defstr.length, system_charset_info); + p->store(m_defstr.str, m_defstr.length, &my_charset_bin); else - protocol->store_null(); - res= protocol->write(); - send_eof(thd); + p->store_null(); - DBUG_RETURN(res); + p->store(m_defstr_ctx.client_cs->csname, system_charset_info); + p->store(m_defstr_ctx.connection_cl->name, system_charset_info); + p->store(m_defstr_ctx.db_cl->name, system_charset_info); + + ret_code= p->write(); + + if (!ret_code) + send_eof(thd); + + DBUG_RETURN(ret_code); } @@ -2306,7 +2310,9 @@ if ((res= protocol->write())) break; } - send_eof(thd); + + if (!res) + send_eof(thd); DBUG_RETURN(res); } --- 1.90/sql/sp_head.h 2007-01-11 21:52:55 +03:00 +++ 1.91/sql/sp_head.h 2007-03-08 14:31:30 +03:00 @@ -137,6 +137,9 @@ LEX_STRING m_defstr; LEX_STRING m_definer_user; LEX_STRING m_definer_host; + + st_query_ctx m_defstr_ctx; + longlong m_created; longlong m_modified; /* Recursion level of the current SP instance. The levels are numbered from 0 */ @@ -221,11 +224,8 @@ bool execute_procedure(THD *thd, List<Item> *args); - int - show_create_procedure(THD *thd); - - int - show_create_function(THD *thd); + bool + show_create_routine(THD *thd, int routine_type); void add_instr(sp_instr *instr); --- 1.119/mysql-test/r/mysqldump.result 2006-12-09 01:33:35 +03:00 +++ 1.120/mysql-test/r/mysqldump.result 2007-03-08 14:31:29 +03:00 @@ -1903,10 +1903,22 @@ ) */; /*!50001 DROP TABLE IF EXISTS `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where (`t2`.`a` like _latin1'a%') */ /*!50002 WITH CASCADED CHECK OPTION */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -1982,9 +1994,21 @@ ) */; /*!50001 DROP TABLE IF EXISTS `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v1` AS select `t1`.`a` AS `a` from `t1` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -2041,10 +2065,22 @@ ) */; /*!50001 DROP TABLE IF EXISTS `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where (`t2`.`a` like _latin1'a%') */ /*!50002 WITH CASCADED CHECK OPTION */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -2151,19 +2187,55 @@ ) */; /*!50001 DROP TABLE IF EXISTS `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v1` AS select `v3`.`a` AS `a`,`v3`.`b` AS `b`,`v3`.`c` AS `c` from `v3` where (`v3`.`b` in (1,2,3,4,5,6,7)) */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; /*!50001 DROP TABLE IF EXISTS `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v2` AS select `v3`.`a` AS `a` from (`v3` join `v1`) where ((`v1`.`a` = `v3`.`a`) and (`v3`.`b` = 3)) limit 1 */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; /*!50001 DROP TABLE IF EXISTS `v3`*/; /*!50001 DROP VIEW IF EXISTS `v3`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v3` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from `t1` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -2206,21 +2278,21 @@ end| set sql_mode=default| show triggers like "t1"; -Trigger Event Table Statement Timing Created sql_mode Definer +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col trg1 INSERT t1 begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; -end BEFORE 0000-00-00 00:00:00 root@localhost +end BEFORE 0000-00-00 00:00:00 root@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg2 UPDATE t1 begin if old.a % 2 = 0 then set new.b := 12; end if; -end BEFORE 0000-00-00 00:00:00 root@localhost +end BEFORE 0000-00-00 00:00:00 root@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg3 UPDATE t1 begin if new.a = -1 then set @fired:= "Yes"; end if; -end AFTER 0000-00-00 00:00:00 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost +end AFTER 0000-00-00 00:00:00 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost latin1 latin1_swedish_ci latin1_swedish_ci INSERT INTO t1 (a) VALUES (1),(2),(3),(22); update t1 set a = 4 where a=3; @@ -2249,31 +2321,73 @@ INSERT INTO `t1` VALUES (1,NULL),(2,NULL),(4,NULL),(11,NULL); /*!40000 ALTER TABLE `t1` ENABLE KEYS */; UNLOCK TABLES; - -/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; +/*!50003 SET @saved_sql_mode = @@sql_mode */; +/*!50003 SET @saved_cs_client = @@character_set_client */; +/*!50003 SET @saved_cs_results = @@character_set_results */; +/*!50003 SET @saved_col_connection = @@collation_connection */; +/*!50003 SET @saved_col_database = @@collation_database */; +/*!50003 SET sql_mode = '' */; +/*!50003 SET character_set_client = latin1 */; +/*!50003 SET character_set_results = latin1 */; +/*!50003 SET collation_connection = latin1_swedish_ci */; +/*!50003 SET collation_database = latin1_swedish_ci */; DELIMITER ;; -/*!50003 SET SESSION SQL_MODE="" */;; -/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `trg1` BEFORE INSERT ON `t1` FOR EACH ROW begin +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger trg1 before insert on t1 for each row +begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; end */;; - -/*!50003 SET SESSION SQL_MODE="" */;; -/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `trg2` BEFORE UPDATE ON `t1` FOR EACH ROW begin +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */; +/*!50003 SET character_set_client = @saved_cs_client */; +/*!50003 SET character_set_results = @saved_cs_results */; +/*!50003 SET collation_connection = @saved_col_connection */; +/*!50003 SET collation_database = @saved_col_database */; +/*!50003 SET @saved_sql_mode = @@sql_mode */; +/*!50003 SET @saved_cs_client = @@character_set_client */; +/*!50003 SET @saved_cs_results = @@character_set_results */; +/*!50003 SET @saved_col_connection = @@collation_connection */; +/*!50003 SET @saved_col_database = @@collation_database */; +/*!50003 SET sql_mode = '' */; +/*!50003 SET character_set_client = latin1 */; +/*!50003 SET character_set_results = latin1 */; +/*!50003 SET collation_connection = latin1_swedish_ci */; +/*!50003 SET collation_database = latin1_swedish_ci */; +DELIMITER ;; +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger trg2 before update on t1 for each row begin if old.a % 2 = 0 then set new.b := 12; end if; end */;; - -/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER" */;; -/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `trg3` AFTER UPDATE ON `t1` FOR EACH ROW begin +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */; +/*!50003 SET character_set_client = @saved_cs_client */; +/*!50003 SET character_set_results = @saved_cs_results */; +/*!50003 SET collation_connection = @saved_col_connection */; +/*!50003 SET collation_database = @saved_col_database */; +/*!50003 SET @saved_sql_mode = @@sql_mode */; +/*!50003 SET @saved_cs_client = @@character_set_client */; +/*!50003 SET @saved_cs_results = @@character_set_results */; +/*!50003 SET @saved_col_connection = @@collation_connection */; +/*!50003 SET @saved_col_database = @@collation_database */; +/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER' */; +/*!50003 SET character_set_client = latin1 */; +/*!50003 SET character_set_results = latin1 */; +/*!50003 SET collation_connection = latin1_swedish_ci */; +/*!50003 SET collation_database = latin1_swedish_ci */; +DELIMITER ;; +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger trg3 after update on t1 for each row +begin if new.a = -1 then set @fired:= "Yes"; end if; end */;; - DELIMITER ; -/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */; +/*!50003 SET sql_mode = @saved_sql_mode */; +/*!50003 SET character_set_client = @saved_cs_client */; +/*!50003 SET character_set_results = @saved_cs_results */; +/*!50003 SET collation_connection = @saved_col_connection */; +/*!50003 SET collation_database = @saved_col_database */; DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2` ( `a` int(11) default NULL @@ -2283,18 +2397,29 @@ /*!40000 ALTER TABLE `t2` DISABLE KEYS */; /*!40000 ALTER TABLE `t2` ENABLE KEYS */; UNLOCK TABLES; - -/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; +/*!50003 SET @saved_sql_mode = @@sql_mode */; +/*!50003 SET @saved_cs_client = @@character_set_client */; +/*!50003 SET @saved_cs_results = @@character_set_results */; +/*!50003 SET @saved_col_connection = @@collation_connection */; +/*!50003 SET @saved_col_database = @@collation_database */; +/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER' */; +/*!50003 SET character_set_client = latin1 */; +/*!50003 SET character_set_results = latin1 */; +/*!50003 SET collation_connection = latin1_swedish_ci */; +/*!50003 SET collation_database = latin1_swedish_ci */; DELIMITER ;; -/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER" */;; -/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `trg4` BEFORE INSERT ON `t2` FOR EACH ROW begin +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger trg4 before insert on t2 for each row +begin if new.a > 10 then set @fired:= "No"; end if; end */;; - DELIMITER ; -/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */; +/*!50003 SET sql_mode = @saved_sql_mode */; +/*!50003 SET character_set_client = @saved_cs_client */; +/*!50003 SET character_set_results = @saved_cs_results */; +/*!50003 SET collation_connection = @saved_col_connection */; +/*!50003 SET collation_database = @saved_col_database */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -2356,26 +2481,26 @@ t1 t2 show triggers; -Trigger Event Table Statement Timing Created sql_mode Definer +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col trg1 INSERT t1 begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; -end BEFORE # root@localhost +end BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg2 UPDATE t1 begin if old.a % 2 = 0 then set new.b := 12; end if; -end BEFORE # root@localhost +end BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg3 UPDATE t1 begin if new.a = -1 then set @fired:= "Yes"; end if; -end AFTER # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost +end AFTER # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg4 INSERT t2 begin if new.a > 10 then set @fired:= "No"; end if; -end BEFORE # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost +end BEFORE # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost latin1 latin1_swedish_ci latin1_swedish_ci DROP TABLE t1, t2; # # Bugs #9136, #12917: problems with --defaults-extra-file option @@ -2404,9 +2529,9 @@ a2 1 SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode Definer +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col testref INSERT test1 BEGIN -INSERT INTO test2 SET a2 = NEW.a1; END BEFORE NULL root@localhost +INSERT INTO test2 SET a2 = NEW.a1; END BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci SELECT * FROM `test1`; a1 1 @@ -2469,35 +2594,100 @@ UNLOCK TABLES; DELIMITER ;; /*!50003 DROP FUNCTION IF EXISTS `bug9056_func1` */;; -/*!50003 SET SESSION SQL_MODE=""*/;; +/*!50003 SET @saved_sql_mode = @@sql_mode */;; +/*!50003 SET @saved_cs_client = @@character_set_client */;; +/*!50003 SET @saved_cs_results = @@character_set_results */;; +/*!50003 SET @saved_col_connection = @@collation_connection */;; +/*!50003 SET @saved_col_database = @@collation_database */;; +/*!50003 SET sql_mode = '' */;; +/*!50003 SET character_set_client = latin1 */;; +/*!50003 SET character_set_results = latin1 */;; +/*!50003 SET collation_connection = latin1_swedish_ci */;; +/*!50003 SET collation_database = latin1_swedish_ci */;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11) RETURN a+b */;; -/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; +/*!50003 SET sql_mode = @saved_sql_mode */;; +/*!50003 SET character_set_client = @saved_cs_client */;; +/*!50003 SET character_set_results = @saved_cs_results */;; +/*!50003 SET collation_connection = @saved_col_connection */;; +/*!50003 SET collation_database = @saved_col_database */;; /*!50003 DROP FUNCTION IF EXISTS `bug9056_func2` */;; -/*!50003 SET SESSION SQL_MODE=""*/;; +/*!50003 SET @saved_sql_mode = @@sql_mode */;; +/*!50003 SET @saved_cs_client = @@character_set_client */;; +/*!50003 SET @saved_cs_results = @@character_set_results */;; +/*!50003 SET @saved_col_connection = @@collation_connection */;; +/*!50003 SET @saved_col_database = @@collation_database */;; +/*!50003 SET sql_mode = '' */;; +/*!50003 SET character_set_client = latin1 */;; +/*!50003 SET character_set_results = latin1 */;; +/*!50003 SET collation_connection = latin1_swedish_ci */;; +/*!50003 SET collation_database = latin1_swedish_ci */;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `bug9056_func2`(f1 char binary) RETURNS char(1) CHARSET latin1 begin set f1= concat( 'hello', f1 ); return f1; end */;; -/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; +/*!50003 SET sql_mode = @saved_sql_mode */;; +/*!50003 SET character_set_client = @saved_cs_client */;; +/*!50003 SET character_set_results = @saved_cs_results */;; +/*!50003 SET collation_connection = @saved_col_connection */;; +/*!50003 SET collation_database = @saved_col_database */;; /*!50003 DROP PROCEDURE IF EXISTS `a'b` */;; -/*!50003 SET SESSION SQL_MODE="REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI"*/;; +/*!50003 SET @saved_sql_mode = @@sql_mode */;; +/*!50003 SET @saved_cs_client = @@character_set_client */;; +/*!50003 SET @saved_cs_results = @@character_set_results */;; +/*!50003 SET @saved_col_connection = @@collation_connection */;; +/*!50003 SET @saved_col_database = @@collation_database */;; +/*!50003 SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI' */;; +/*!50003 SET character_set_client = latin1 */;; +/*!50003 SET character_set_results = latin1 */;; +/*!50003 SET collation_connection = latin1_swedish_ci */;; +/*!50003 SET collation_database = latin1_swedish_ci */;; /*!50003 CREATE*/ /*!50020 DEFINER="root"@"localhost"*/ /*!50003 PROCEDURE "a'b"() select 1 */;; -/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; +/*!50003 SET sql_mode = @saved_sql_mode */;; +/*!50003 SET character_set_client = @saved_cs_client */;; +/*!50003 SET character_set_results = @saved_cs_results */;; +/*!50003 SET collation_connection = @saved_col_connection */;; +/*!50003 SET collation_database = @saved_col_database */;; /*!50003 DROP PROCEDURE IF EXISTS `bug9056_proc1` */;; -/*!50003 SET SESSION SQL_MODE=""*/;; +/*!50003 SET @saved_sql_mode = @@sql_mode */;; +/*!50003 SET @saved_cs_client = @@character_set_client */;; +/*!50003 SET @saved_cs_results = @@character_set_results */;; +/*!50003 SET @saved_col_connection = @@collation_connection */;; +/*!50003 SET @saved_col_database = @@collation_database */;; +/*!50003 SET sql_mode = '' */;; +/*!50003 SET character_set_client = latin1 */;; +/*!50003 SET character_set_results = latin1 */;; +/*!50003 SET collation_connection = latin1_swedish_ci */;; +/*!50003 SET collation_database = latin1_swedish_ci */;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `bug9056_proc1`(IN a INT, IN b INT, OUT c INT) BEGIN SELECT a+b INTO c; end */;; -/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; +/*!50003 SET sql_mode = @saved_sql_mode */;; +/*!50003 SET character_set_client = @saved_cs_client */;; +/*!50003 SET character_set_results = @saved_cs_results */;; +/*!50003 SET collation_connection = @saved_col_connection */;; +/*!50003 SET collation_database = @saved_col_database */;; /*!50003 DROP PROCEDURE IF EXISTS `bug9056_proc2` */;; -/*!50003 SET SESSION SQL_MODE=""*/;; +/*!50003 SET @saved_sql_mode = @@sql_mode */;; +/*!50003 SET @saved_cs_client = @@character_set_client */;; +/*!50003 SET @saved_cs_results = @@character_set_results */;; +/*!50003 SET @saved_col_connection = @@collation_connection */;; +/*!50003 SET @saved_col_database = @@collation_database */;; +/*!50003 SET sql_mode = '' */;; +/*!50003 SET character_set_client = latin1 */;; +/*!50003 SET character_set_results = latin1 */;; +/*!50003 SET collation_connection = latin1_swedish_ci */;; +/*!50003 SET collation_database = latin1_swedish_ci */;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `bug9056_proc2`(OUT a INT) BEGIN select sum(id) from t1 into a; END */;; -/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; +/*!50003 SET sql_mode = @saved_sql_mode */;; +/*!50003 SET character_set_client = @saved_cs_client */;; +/*!50003 SET character_set_results = @saved_cs_results */;; +/*!50003 SET collation_connection = @saved_col_connection */;; +/*!50003 SET collation_database = @saved_col_database */;; DELIMITER ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; @@ -2641,15 +2831,25 @@ INSERT INTO "t1 test" VALUES (1),(2),(3); /*!40000 ALTER TABLE "t1 test" ENABLE KEYS */; UNLOCK TABLES; - -/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; +/*!50003 SET @saved_sql_mode = @@sql_mode */; +/*!50003 SET @saved_cs_client = @@character_set_client */; +/*!50003 SET @saved_cs_results = @@character_set_results */; +/*!50003 SET @saved_col_connection = @@collation_connection */; +/*!50003 SET @saved_col_database = @@collation_database */; +/*!50003 SET sql_mode = '' */; +/*!50003 SET character_set_client = latin1 */; +/*!50003 SET character_set_results = latin1 */; +/*!50003 SET collation_connection = latin1_swedish_ci */; +/*!50003 SET collation_database = latin1_swedish_ci */; DELIMITER ;; -/*!50003 SET SESSION SQL_MODE="" */;; -/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `test trig` BEFORE INSERT ON `t1 test` FOR EACH ROW BEGIN +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `test trig` BEFORE INSERT ON `t1 test` FOR EACH ROW BEGIN INSERT INTO `t2 test` SET a2 = NEW.a1; END */;; - DELIMITER ; -/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */; +/*!50003 SET sql_mode = @saved_sql_mode */; +/*!50003 SET character_set_client = @saved_cs_client */; +/*!50003 SET character_set_results = @saved_cs_results */; +/*!50003 SET collation_connection = @saved_col_connection */; +/*!50003 SET collation_database = @saved_col_database */; DROP TABLE IF EXISTS "t2 test"; CREATE TABLE "t2 test" ( "a2" int(11) default NULL @@ -2738,19 +2938,55 @@ USE `test`; /*!50001 DROP TABLE IF EXISTS `v0`*/; /*!50001 DROP VIEW IF EXISTS `v0`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v0` AS select `v1`.`a` AS `a`,`v1`.`b` AS `b`,`v1`.`c` AS `c` from `v1` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; /*!50001 DROP TABLE IF EXISTS `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from `t1` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; /*!50001 DROP TABLE IF EXISTS `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v2` AS select `v0`.`a` AS `a`,`v0`.`b` AS `b`,`v0`.`c` AS `c` from `v0` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -2802,16 +3038,28 @@ /*!40000 ALTER TABLE `t1` DISABLE KEYS */; /*!40000 ALTER TABLE `t1` ENABLE KEYS */; UNLOCK TABLES; - -/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; +/*!50003 SET @saved_sql_mode = @@sql_mode */; +/*!50003 SET @saved_cs_client = @@character_set_client */; +/*!50003 SET @saved_cs_results = @@character_set_results */; +/*!50003 SET @saved_col_connection = @@collation_connection */; +/*!50003 SET @saved_col_database = @@collation_database */; +/*!50003 SET sql_mode = 'IGNORE_SPACE' */; +/*!50003 SET character_set_client = latin1 */; +/*!50003 SET character_set_results = latin1 */; +/*!50003 SET collation_connection = latin1_swedish_ci */; +/*!50003 SET collation_database = latin1_swedish_ci */; DELIMITER ;; -/*!50003 SET SESSION SQL_MODE="IGNORE_SPACE" */;; -/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `tr1` BEFORE INSERT ON `t1` FOR EACH ROW BEGIN +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr1 BEFORE INSERT ON t1 +FOR EACH ROW +BEGIN SET new.a = 0; END */;; - DELIMITER ; -/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */; +/*!50003 SET sql_mode = @saved_sql_mode */; +/*!50003 SET character_set_client = @saved_cs_client */; +/*!50003 SET character_set_results = @saved_cs_results */; +/*!50003 SET collation_connection = @saved_col_connection */; +/*!50003 SET collation_database = @saved_col_database */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -2939,12 +3187,12 @@ drop trigger tr2; drop table t1, t2; show triggers; -Trigger Event Table Statement Timing Created sql_mode Definer +Trigger Event Table Statement Timing Created sql_mode Definer Client_cs Connection_col Database_col tr1 INSERT t1 set -new.created=now() BEFORE # root@localhost +new.created=now() BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci tr2 INSERT t1 begin insert into t2 set b=new.a and created=new.created; -end AFTER # root@localhost +end AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci drop trigger tr1; drop trigger tr2; drop table t1, t2; @@ -2957,14 +3205,38 @@ create view v1 as select qty, price, qty*price as value from t; create view v2 as select qty from v1; mysqldump { +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v1` AS select `t`.`qty` AS `qty`,`t`.`price` AS `price`,(`t`.`qty` * `t`.`price`) AS `value` from `t` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; } mysqldump { +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v2` AS select `v1`.`qty` AS `qty` from `v1` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; } mysqldump drop view v1; @@ -2979,13 +3251,13 @@ /*!50003 CREATE PROCEDURE `p`() select 42 */| show create function f; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col f CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS bigint(20) -return 42 +return 42 latin1 latin1_swedish_ci latin1_swedish_ci show create procedure p; -Procedure sql_mode Create Procedure +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col p CREATE DEFINER=`root`@`localhost` PROCEDURE `p`() -select 42 +select 42 latin1 latin1_swedish_ci latin1_swedish_ci drop function f; drop procedure p; # @@ -3047,9 +3319,21 @@ USE `mysqldump_test_db`; /*!50001 DROP TABLE IF EXISTS `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v1` AS select `t1`.`id` AS `id` from `t1` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -3092,9 +3376,21 @@ USE `mysqldump_tables`; USE `mysqldump_views`; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET @saved_col_database = @@collation_database */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 SET collation_database = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `mysqldump_views`.`nasishnasifu` AS select `mysqldump_tables`.`basetable`.`id` AS `id` from `mysqldump_tables`.`basetable` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 SET collation_database = @saved_col_database */; drop view nasishnasifu; drop database mysqldump_views; drop table mysqldump_tables.basetable; @@ -3217,6 +3513,66 @@ </database> </mysqldump> DROP TABLE t1; -# -# End of 5.0 tests -# +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS t1; +SET @old_cs_client = @@character_set_client; +SET @old_cs_results = @@character_set_results; +SET @old_cs_connection = @@character_set_connection; +SET names utf8; +CREATE TABLE t1(абв INT); +INSERT INTO t1 VALUES(1); +CREATE VIEW v1 AS SELECT 'абв', абв FROM t1; +CREATE PROCEDURE p1() SELECT 'абв', абв FROM t1; +CREATE FUNCTION f1() RETURNS CHAR(10) CHARSET utf8 RETURN 'абв'; +SHOW CREATE VIEW v1; +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _utf8'абв' AS `My_exp_абв`,`t1`.`абв` AS `абв` from `t1` utf8 utf8_general_ci latin1_swedish_ci +SHOW CREATE PROCEDURE p1; +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +SELECT 'абв', абв FROM t1 utf8 utf8_general_ci latin1_swedish_ci +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function Client_cs Connection_col Database_col +f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS char(10) CHARSET utf8 +RETURN 'абв' utf8 utf8_general_ci latin1_swedish_ci +SELECT * FROM v1; +My_exp_абв абв +абв 1 +CALL p1(); +абв абв +абв 1 +SELECT f1(); +f1() +абв +DROP VIEW v1; +DROP PROCEDURE p1; +DROP FUNCTION f1; +SHOW CREATE VIEW v1; +View Create View Client_cs Connection_col Database_col +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _utf8'абв' AS `My_exp_абв`,`t1`.`абв` AS `абв` from `t1` utf8 utf8_general_ci latin1_swedish_ci +SHOW CREATE PROCEDURE p1; +Procedure sql_mode Create Procedure Client_cs Connection_col Database_col +p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +SELECT 'абв', абв FROM t1 utf8 utf8_general_ci latin1_swedish_ci +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function Client_cs Connection_col Database_col +f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS char(10) CHARSET utf8 +RETURN 'абв' utf8 utf8_general_ci latin1_swedish_ci +SELECT * FROM v1; +My_exp_абв абв +абв 1 +CALL p1(); +абв абв +абв 1 +SELECT f1(); +f1() +абв +SET character_set_client = @old_cs_client; +SET character_set_results = @old_cs_results; +SET character_set_connection = @old_cs_connection; +DROP VIEW v1; +DROP TABLE t1; +DROP PROCEDURE p1; +DROP FUNCTION f1; --- 1.108/mysql-test/t/mysqldump.test 2006-12-09 01:33:35 +03:00 +++ 1.109/mysql-test/t/mysqldump.test 2007-03-08 14:31:30 +03:00 @@ -1427,6 +1427,80 @@ DROP TABLE t1; ---echo # ---echo # End of 5.0 tests ---echo # + +# +# BUG#16291: mysqldump corrupts string-constants with non-ascii-chars +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# Switch locale to UTF8, because the CREATE-VIEW-statetement is in UTF8. + +SET @old_cs_client = @@character_set_client; +SET @old_cs_results = @@character_set_results; +SET @old_cs_connection = @@character_set_connection; + +SET names utf8; + +# Create objects with non-latin1 symbols. + +CREATE TABLE t1(абв INT); +INSERT INTO t1 VALUES(1); + +CREATE VIEW v1 AS SELECT 'абв', абв FROM t1; + +CREATE PROCEDURE p1() SELECT 'абв', абв FROM t1; + +CREATE FUNCTION f1() RETURNS CHAR(10) CHARSET utf8 RETURN 'абв'; + +# Check what we have created. + +SHOW CREATE VIEW v1; +SHOW CREATE PROCEDURE p1; +SHOW CREATE FUNCTION f1; + +SELECT * FROM v1; + +CALL p1(); + +SELECT f1(); + +# Dump the view using the KOI8-R client character set. + +--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --default-character-set=koi8r --routines test > $MYSQLTEST_VARDIR/tmp/bug16291.sql + +# Drop and re-create the view. + +DROP VIEW v1; +DROP PROCEDURE p1; +DROP FUNCTION f1; + +--exec $MYSQL --character-sets-dir=$CHARSETSDIR test < $MYSQLTEST_VARDIR/tmp/bug16291.sql + +# Check again what we've got. + +SHOW CREATE VIEW v1; +SHOW CREATE PROCEDURE p1; +SHOW CREATE FUNCTION f1; + +SELECT * FROM v1; + +CALL p1(); + +SELECT f1(); + +# Clean up: reset locale and drop the view. + +SET character_set_client = @old_cs_client; +SET character_set_results = @old_cs_results; +SET character_set_connection = @old_cs_connection; + +DROP VIEW v1; +DROP TABLE t1; +DROP PROCEDURE p1; +DROP FUNCTION f1; --- 1.38/mysql-test/r/gis.result 2006-12-07 14:22:40 +03:00 +++ 1.39/mysql-test/r/gis.result 2007-03-08 14:31:29 +03:00 @@ -697,9 +697,9 @@ drop procedure if exists fn3; create function fn3 () returns point return GeomFromText("point(1 1)"); show create function fn3; -Function sql_mode Create Function +Function sql_mode Create Function Client_cs Connection_col Database_col fn3 CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point -return GeomFromText("point(1 1)") +return GeomFromText("point(1 1)") latin1 latin1_swedish_ci latin1_swedish_ci select astext(fn3()); astext(fn3()) POINT(1 1) --- 1.29/mysql-test/r/system_mysql_db.result 2005-10-13 18:12:37 +04:00 +++ 1.30/mysql-test/r/system_mysql_db.result 2007-03-08 14:31:30 +03:00 @@ -178,6 +178,9 @@ `modified` timestamp NOT NULL default '0000-00-00 00:00:00', `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE') NOT NULL default '', `comment` char(64) character set utf8 collate utf8_bin NOT NULL default '', + `charset_client` char(32) character set utf8 collate utf8_bin default NULL, + `collation_connection` char(32) character set utf8 collate utf8_bin default NULL, + `collation_database` char(32) character set utf8 collate utf8_bin default NULL, PRIMARY KEY (`db`,`name`,`type`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures' show tables; --- 1.29/scripts/mysql_create_system_tables.sh 2006-12-30 20:25:24 +03:00 +++ 1.30/scripts/mysql_create_system_tables.sh 2007-03-08 14:31:30 +03:00 @@ -732,6 +732,9 @@ c_p="$c_p 'HIGH_NOT_PRECEDENCE'" c_p="$c_p ) DEFAULT '' NOT NULL," c_p="$c_p comment char(64) collate utf8_bin DEFAULT '' NOT NULL," + c_p="$c_p charset_client char(32) collate utf8_bin," + c_p="$c_p collation_connection char(32) collate utf8_bin," + c_p="$c_p collation_database char(32) collate utf8_bin," c_p="$c_p PRIMARY KEY (db,name,type)" c_p="$c_p ) engine=MyISAM" c_p="$c_p character set utf8"
| Thread | ||
|---|---|---|
| • bk commit into 5.0 tree (anozdrin:1.2389) BUG#11986 | Alexander Nozdrin | 8 Mar |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 2.177 sec. using MySQL 5.1.14-beta-log