diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/ddl_i18n_koi8r.test | 1112 | ||||
| -rw-r--r-- | mysql-test/t/ddl_i18n_utf8.test | 1112 | ||||
| -rw-r--r-- | mysql-test/t/events.test | 25 | ||||
| -rw-r--r-- | mysql-test/t/information_schema.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/show_check.test | 51 | ||||
| -rw-r--r-- | mysql-test/t/sp-destruct.test | 15 | ||||
| -rw-r--r-- | mysql-test/t/sp.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 10 |
8 files changed, 2321 insertions, 8 deletions
diff --git a/mysql-test/t/ddl_i18n_koi8r.test b/mysql-test/t/ddl_i18n_koi8r.test new file mode 100644 index 00000000000..c3475c98428 --- /dev/null +++ b/mysql-test/t/ddl_i18n_koi8r.test @@ -0,0 +1,1112 @@ +# Objects to test: +# - stored procedures/functions; +# - triggers; +# - events; +# - views; +# +# For stored routines: +# - create a database with collation utf8_unicode_ci; +# - create an object, which +# - contains SP-var with explicit CHARSET-clause; +# - contains SP-var without CHARSET-clause; +# - contains text constant; +# - has localized routine/parameter names; +# - check: +# - execute; +# - SHOW CREATE output; +# - SHOW output; +# - SELECT FROM INFORMATION_SCHEMA output; +# - alter database character set; +# - change connection collation; +# - check again; +# - dump definition using mysqldump; +# - drop object; +# - restore object; +# + +########################################################################### +# +# NOTE: this file contains text in UTF8 and KOI8-R encodings. +# +########################################################################### + +--source include/have_utf8.inc +--source include/have_cp866.inc +--source include/have_cp1251.inc +--source include/have_koi8r.inc + +########################################################################### + +set names koi8r; +delimiter |; + +########################################################################### +# +# * Views. +# +########################################################################### + +--echo +--echo ------------------------------------------------------------------- +--echo Views +--echo ------------------------------------------------------------------- +--echo + +# +# Preparation: +# + +# - Create database with fixed, pre-defined character set. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1| +--enable_warnings + +CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci| + +use mysqltest1| + +CREATE TABLE t1( INT)| +INSERT INTO t1 VALUES(1)| + +# - Create views; + +--echo + +CREATE VIEW v1 AS + SELECT '' AS c1, AS c2 + FROM t1| + +--echo + +CREATE VIEW v2 AS SELECT _utf8'тест' as c1| + +--echo + +# +# First-round checks. +# + +--source include/ddl_i18n.check_views.inc + +# +# Change running environment (alter database character set, change session +# variables). +# + +--echo +--echo + +ALTER DATABASE mysqltest1 COLLATE cp866_general_ci| + +# +# Second-round checks: +# + +# - Change connection to flush cache; + +--connect (con2,localhost,root,,) +--echo +--echo ---> connection: con2 + +# - Switch environment variables and trigger loading views; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +--disable_result_log +SELECT * FROM mysqltest1.v1| +SELECT * FROM mysqltest1.v2| +--enable_result_log + +use mysqltest1| + +# - Restore environment; + +set names koi8r| + +# - Check! + +--source include/ddl_i18n.check_views.inc + +# +# Check mysqldump. +# + +# - Dump mysqltest1; + +--echo +--echo ---> Dumping mysqltest1 to ddl_i18n_koi8r.views.mysqltest1.sql + +--exec $MYSQL_DUMP --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.views.mysqltest1.sql + +# - Clean mysqltest1; + +--echo +--echo + +DROP DATABASE mysqltest1| + +# - Restore mysqltest1; + +--echo +--echo + +--echo ---> Restoring mysqltest1... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.views.mysqltest1.sql + +# +# Third-round checks. +# + +# - Change connection to flush cache; + +--connect (con3,localhost,root,,) +--echo +--echo ---> connection: con3 + +# - Switch environment variables and trigger loading stored procedures; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +--disable_result_log +SELECT * FROM mysqltest1.v1| +SELECT * FROM mysqltest1.v2| +--enable_result_log + +use mysqltest1| + +# - Restore environment; + +set names koi8r| + +# - Check! + +--source include/ddl_i18n.check_views.inc + +# +# Cleanup. +# + +--connection default +--echo +--echo ---> connection: default + +--disconnect con2 +--disconnect con3 + +use test| + +DROP DATABASE mysqltest1| + +########################################################################### +# +# * Stored procedures/functions. +# +########################################################################### + +--echo +--echo ------------------------------------------------------------------- +--echo Stored procedures/functions +--echo ------------------------------------------------------------------- +--echo + +# +# Preparation: +# + +# - 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| + +use mysqltest1| + +# - Create two stored routines -- with and without explicit +# CHARSET-clause for SP-variable; +# + +--echo + +# - Procedure p1 + +CREATE PROCEDURE p1( + INOUT 1 CHAR(10), + OUT 2 CHAR(10)) +BEGIN + DECLARE 1 CHAR(10); + + SELECT + COLLATION(1) AS c1, + COLLATION(1) AS c2, + COLLATION(2) AS c3; + + SELECT + COLLATION('') AS c4, + COLLATION(_koi8r '') AS c5, + COLLATION(_utf8 'текст') AS c6, + @@collation_connection AS c7, + @@character_set_client AS c8; + + SET 1 = 'a'; + SET 2 = 'b'; +END| + +--echo + +# - Procedure p2 + +CREATE PROCEDURE p2( + INOUT 1 CHAR(10) CHARACTER SET utf8, + OUT 2 CHAR(10) CHARACTER SET utf8) +BEGIN + DECLARE 1 CHAR(10) CHARACTER SET utf8; + + SELECT + COLLATION(1) AS c1, + COLLATION(1) AS c2, + COLLATION(2) AS c3; + + SELECT + COLLATION('') AS c4, + COLLATION(_koi8r '') AS c5, + COLLATION(_utf8 'текст') AS c6, + @@collation_connection AS c7, + @@character_set_client AS c8; + + SET 1 = 'a'; + SET 2 = 'b'; +END| + +--echo + +# - Procedure p3 + +CREATE PROCEDURE mysqltest2.p3( + INOUT 1 CHAR(10), + OUT 2 CHAR(10)) +BEGIN + DECLARE 1 CHAR(10); + + SELECT + COLLATION(1) AS c1, + COLLATION(1) AS c2, + COLLATION(2) AS c3; + + SELECT + COLLATION('') AS c4, + COLLATION(_koi8r '') AS c5, + COLLATION(_utf8 'текст') AS c6, + @@collation_connection AS c7, + @@character_set_client AS c8; + + SET 1 = 'a'; + SET 2 = 'b'; +END| + +--echo + +# - Procedure p4 + +CREATE PROCEDURE mysqltest2.p4( + INOUT 1 CHAR(10) CHARACTER SET utf8, + OUT 2 CHAR(10) CHARACTER SET utf8) +BEGIN + DECLARE 1 CHAR(10) CHARACTER SET utf8; + + SELECT + COLLATION(1) AS c1, + COLLATION(1) AS c2, + COLLATION(2) AS c3; + + SELECT + COLLATION('') AS c4, + COLLATION(_koi8r '') AS c5, + COLLATION(_utf8 'текст') AS c6, + @@collation_connection AS c7, + @@character_set_client AS c8; + + SET 1 = 'a'; + SET 2 = 'b'; +END| + +# +# First-round checks. +# + +--source include/ddl_i18n.check_sp.inc + +# +# Change running environment (alter database character set, change session +# variables). +# + +--echo +--echo + +ALTER DATABASE mysqltest1 COLLATE cp866_general_ci| +ALTER DATABASE mysqltest2 COLLATE cp866_general_ci| + +# +# Second-round checks: +# + +# - Change connection to flush SP-cache; + +--connect (con2,localhost,root,,mysqltest1) +--echo +--echo ---> connection: con2 + +# - Switch environment variables and trigger loading stored procedures; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +CALL p1(@a, @b)| +CALL p2(@a, @b)| +CALL mysqltest2.p3(@a, @b)| +CALL mysqltest2.p4(@a, @b)| + +# - Restore environment; + +set names koi8r| + +# - Check! + +--source include/ddl_i18n.check_sp.inc + +# +# Check mysqldump. +# + +# - Dump mysqltest1, mysqltest2; + +--echo +--echo ---> Dump of mysqltest1 + +--exec $MYSQL_DUMP --compact --routines --databases mysqltest1 + +--echo +--echo ---> Dumping mysqltest1 to ddl_i18n_koi8r.sp.mysqltest1.sql + +--exec $MYSQL_DUMP --compact --routines --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.sp.mysqltest1.sql + +--echo +--echo ---> Dump of mysqltest2 + +--exec $MYSQL_DUMP --compact --routines --databases mysqltest2 + +--echo +--echo ---> Dumping mysqltest2 to ddl_i18n_koi8r.sp.mysqltest2.sql + +--exec $MYSQL_DUMP --compact --routines --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.sp.mysqltest2.sql + +# - Clean mysqltest1, mysqltest2; + +--echo +--echo + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + +# - Restore mysqltest1; + +--echo +--echo + +--echo ---> Restoring mysqltest1... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.sp.mysqltest1.sql + +--echo ---> Restoring mysqltest2... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.sp.mysqltest2.sql + +# +# Third-round checks. +# + +# - Change connection to flush SP-cache; + +--connect (con3,localhost,root,,mysqltest1) +--echo +--echo ---> connection: con3 + +# - Switch environment variables and trigger loading stored procedures; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +CALL p1(@a, @b)| +CALL p2(@a, @b)| +CALL mysqltest2.p3(@a, @b)| +CALL mysqltest2.p4(@a, @b)| + +# - Restore environment; + +set names koi8r| + +# - Check! + +--source include/ddl_i18n.check_sp.inc + +# +# Cleanup. +# + +--connection default +--echo +--echo ---> connection: default + +--disconnect con2 +--disconnect con3 + +use test| + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + +########################################################################### +# +# * Triggers. +# +########################################################################### + +--echo +--echo ------------------------------------------------------------------- +--echo Triggers +--echo ------------------------------------------------------------------- +--echo + +# +# Preparation: +# + +# - 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| + +use mysqltest1| + +# - Create tables for triggers; + +CREATE TABLE t1(c INT)| +CREATE TABLE mysqltest2.t1(c INT)| + +# - Create log tables; + +CREATE TABLE log(msg VARCHAR(255))| +CREATE TABLE mysqltest2.log(msg VARCHAR(255))| + + +# - Create triggers -- with and without explicit CHARSET-clause for +# SP-variable; +# + +--echo + +# - Trigger trg1 + +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE 1 CHAR(10); + + INSERT INTO log VALUES(COLLATION(1)); + INSERT INTO log VALUES(COLLATION('')); + INSERT INTO log VALUES(COLLATION(_koi8r '')); + INSERT INTO log VALUES(COLLATION(_utf8 'текст')); + INSERT INTO log VALUES(@@collation_connection); + INSERT INTO log VALUES(@@character_set_client); + + SET @a1 = ''; + SET @a1 = _koi8r ''; + SET @a2 = _utf8 'текст'; +END| + +--echo + +# - Trigger trg2 + +CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE 1 CHAR(10) CHARACTER SET utf8; + + INSERT INTO log VALUES(COLLATION(1)); + INSERT INTO log VALUES(COLLATION('')); + INSERT INTO log VALUES(COLLATION(_koi8r '')); + INSERT INTO log VALUES(COLLATION(_utf8 'текст')); + INSERT INTO log VALUES(@@collation_connection); + INSERT INTO log VALUES(@@character_set_client); + + SET @b1 = ''; + SET @b1 = _koi8r ''; + SET @b2 = _utf8 'текст'; +END| + +--echo + +# - Trigger trg3 + +CREATE TRIGGER mysqltest2.trg3 BEFORE INSERT ON mysqltest2.t1 FOR EACH ROW +BEGIN + DECLARE 1 CHAR(10); + + INSERT INTO log VALUES(COLLATION(1)); + INSERT INTO log VALUES(COLLATION('')); + INSERT INTO log VALUES(COLLATION(_koi8r '')); + INSERT INTO log VALUES(COLLATION(_utf8 'текст')); + INSERT INTO log VALUES(@@collation_connection); + INSERT INTO log VALUES(@@character_set_client); + + SET @a1 = ''; + SET @a1 = _koi8r ''; + SET @a2 = _utf8 'текст'; +END| + +--echo + +# - Trigger trg4 + +CREATE TRIGGER mysqltest2.trg4 AFTER INSERT ON mysqltest2.t1 FOR EACH ROW +BEGIN + DECLARE 1 CHAR(10) CHARACTER SET utf8; + + INSERT INTO log VALUES(COLLATION(1)); + INSERT INTO log VALUES(COLLATION('')); + INSERT INTO log VALUES(COLLATION(_koi8r '')); + INSERT INTO log VALUES(COLLATION(_utf8 'текст')); + INSERT INTO log VALUES(@@collation_connection); + INSERT INTO log VALUES(@@character_set_client); + + SET @b1 = ''; + SET @b1 = _koi8r ''; + SET @b2 = _utf8 'текст'; +END| + +--echo + +# +# First-round checks. +# + +--source include/ddl_i18n.check_triggers.inc + +# +# Change running environment (alter database character set, change session +# variables). +# + +--echo +--echo + +ALTER DATABASE mysqltest1 COLLATE cp866_general_ci| +ALTER DATABASE mysqltest2 COLLATE cp866_general_ci| + +# +# Second-round checks: +# + +# - Flush table cache; + +ALTER TABLE t1 ADD COLUMN fake INT| +ALTER TABLE t1 DROP COLUMN fake| + +ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT| +ALTER TABLE mysqltest2.t1 DROP COLUMN fake| + +# - Switch environment variables and initiate loading of triggers +# (connect using NULL database); + +--connect (con2,localhost,root,,) +--echo +--echo ---> connection: con2 + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +INSERT INTO mysqltest1.t1 VALUES(0)| +INSERT INTO mysqltest2.t1 VALUES(0)| + +DELETE FROM mysqltest1.log| +DELETE FROM mysqltest2.log| + +# - Restore environment; + +set names koi8r| + +use mysqltest1| + +# - Check! + +--source include/ddl_i18n.check_triggers.inc + +# +# Check mysqldump. +# + +# - Dump mysqltest1, mysqltest2; + +--echo +--echo ---> Dump of mysqltest1 + +--exec $MYSQL_DUMP --compact --triggers --databases mysqltest1 + +--echo +--echo ---> Dumping mysqltest1 to ddl_i18n_koi8r.triggers.mysqltest1.sql + +--exec $MYSQL_DUMP --compact --triggers --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.triggers.mysqltest1.sql + +--echo +--echo ---> Dump of mysqltest2 + +--exec $MYSQL_DUMP --compact --triggers --databases mysqltest2 + +--echo +--echo ---> Dumping mysqltest2 to ddl_i18n_koi8r.triggers.mysqltest2.sql + +--exec $MYSQL_DUMP --compact --triggers --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.triggers.mysqltest2.sql + +# - Clean mysqltest1, mysqltest2; + +--echo +--echo + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + +# - Restore mysqltest1; + +--echo +--echo + +--echo ---> Restoring mysqltest1... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.triggers.mysqltest1.sql + +--echo ---> Restoring mysqltest2... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.triggers.mysqltest2.sql + +# +# Third-round checks. +# + +# - Flush table cache; + +ALTER TABLE mysqltest1.t1 ADD COLUMN fake INT| +ALTER TABLE mysqltest1.t1 DROP COLUMN fake| + +ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT| +ALTER TABLE mysqltest2.t1 DROP COLUMN fake| + +# - Switch environment variables and initiate loading of triggers +# (connect using NULL database); + +--connect (con3,localhost,root,,) +--echo +--echo ---> connection: con3 + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +INSERT INTO mysqltest1.t1 VALUES(0)| +INSERT INTO mysqltest2.t1 VALUES(0)| + +DELETE FROM mysqltest1.log| +DELETE FROM mysqltest2.log| + +# - Restore environment; + +set names koi8r| + +use mysqltest1| + +# - Check! + +--source include/ddl_i18n.check_triggers.inc + +# +# Cleanup. +# + +--connection default +--echo +--echo ---> connection: default + +--disconnect con2 +--disconnect con3 + +use test| + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + +########################################################################### +# +# * Events +# +# We don't have EXECUTE EVENT so far, so this test is limited. It checks that +# event with non-latin1 symbols can be created, dumped, restored and SHOW +# statements work properly. +# +########################################################################### + +--echo +--echo ------------------------------------------------------------------- +--echo Events +--echo ------------------------------------------------------------------- +--echo + +# +# Preparation: +# + +# - 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| + +use mysqltest1| + +# - Create two stored routines -- with and without explicit +# CHARSET-clause for SP-variable; +# + +--echo + +# - Event ev1 + +CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO +BEGIN + DECLARE 1 CHAR(10); + + SELECT + COLLATION(1) AS c1, + COLLATION('') AS c2, + COLLATION(_koi8r '') AS c3, + COLLATION(_utf8 'текст') AS c4, + @@collation_connection AS c5, + @@character_set_client AS c6; +END| + +--echo + +# - Event ev2 + +CREATE EVENT ev2 ON SCHEDULE AT '2030-01-01 00:00:00' DO +BEGIN + DECLARE 1 CHAR(10) CHARACTER SET utf8; + + SELECT + COLLATION(1) AS c1, + COLLATION('') AS c2, + COLLATION(_koi8r '') AS c3, + COLLATION(_utf8 'текст') AS c4, + @@collation_connection AS c5, + @@character_set_client AS c6; +END| + +--echo + +# - Event ev3 + +CREATE EVENT mysqltest2.ev3 ON SCHEDULE AT '2030-01-01 00:00:00' DO +BEGIN + DECLARE 1 CHAR(10) CHARACTER SET utf8; + + SELECT + COLLATION(1) AS c1, + COLLATION('') AS c2, + COLLATION(_koi8r '') AS c3, + COLLATION(_utf8 'текст') AS c4, + @@collation_connection AS c5, + @@character_set_client AS c6; +END| + +--echo + +# - Event ev4 + +CREATE EVENT mysqltest2.ev4 ON SCHEDULE AT '2030-01-01 00:00:00' DO +BEGIN + DECLARE 1 CHAR(10) CHARACTER SET utf8; + + SELECT + COLLATION(1) AS c1, + COLLATION('') AS c2, + COLLATION(_koi8r '') AS c3, + COLLATION(_utf8 'текст') AS c4, + @@collation_connection AS c5, + @@character_set_client AS c6; +END| + +--echo + + +# +# First-round checks. +# + +--source include/ddl_i18n.check_events.inc + +# +# Change running environment (alter database character set, change session +# variables). +# + +--echo +--echo + +ALTER DATABASE mysqltest1 COLLATE cp866_general_ci| +ALTER DATABASE mysqltest2 COLLATE cp866_general_ci| + +# +# Second-round checks: +# + +# - Change connection to flush cache; + +--connect (con2,localhost,root,,mysqltest1) +--echo +--echo ---> connection: con2 + +# - Switch environment variables and trigger loading stored procedures; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +--disable_result_log +SHOW CREATE EVENT ev1| +SHOW CREATE EVENT ev2| +SHOW CREATE EVENT mysqltest2.ev3| +SHOW CREATE EVENT mysqltest2.ev4| +--enable_result_log + +# - Restore environment; + +set names koi8r| + +# - Check! + +--source include/ddl_i18n.check_events.inc + +# +# Check mysqldump. +# + +# - Dump mysqltest1, mysqltest2; + +--echo +--echo ---> Dump of mysqltest1 + +--exec $MYSQL_DUMP --compact --events --databases mysqltest1 + +--echo +--echo ---> Dumping mysqltest1 to ddl_i18n_koi8r.events.mysqltest1.sql + +--exec $MYSQL_DUMP --compact --events --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.events.mysqltest1.sql + +--echo +--echo ---> Dump of mysqltest2 + +--exec $MYSQL_DUMP --compact --events --databases mysqltest2 + +--echo +--echo ---> Dumping mysqltest2 to ddl_i18n_koi8r.events.mysqltest2.sql + +--exec $MYSQL_DUMP --compact --events --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.events.mysqltest2.sql + +# - Clean mysqltest1, mysqltest2; + +--echo +--echo + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + +# - Restore mysqltest1; + +--echo +--echo + +--echo ---> Restoring mysqltest1... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.events.mysqltest1.sql + +--echo ---> Restoring mysqltest2... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.events.mysqltest2.sql + +# +# Third-round checks. +# + +# - Change connection to flush cache; + +--connect (con3,localhost,root,,mysqltest1) +--echo +--echo ---> connection: con3 + +# - Switch environment variables and trigger loading stored procedures; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +--disable_result_log +SHOW CREATE EVENT ev1| +SHOW CREATE EVENT ev2| +SHOW CREATE EVENT mysqltest2.ev3| +SHOW CREATE EVENT mysqltest2.ev4| +--enable_result_log + +# - Restore environment; + +set names koi8r| + +# - Check! + +--source include/ddl_i18n.check_events.inc + +########################################################################### +# +# * DDL statements inside stored routine. +# +# Here we check that DDL statements use actual database collation even if they +# are called from stored routine. +# +########################################################################### + +--echo +--echo ------------------------------------------------------------------- +--echo DDL statements within stored routine. +--echo ------------------------------------------------------------------- +--echo + +# +# Preparation: +# + +# - 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| + +use mysqltest1| + +# - Create procedures; + +--echo + +CREATE PROCEDURE p1() +BEGIN + CREATE TABLE t1(col1 VARCHAR(10)); + SHOW CREATE TABLE t1; +END| + +--echo + +CREATE PROCEDURE mysqltest2.p2() +BEGIN + CREATE TABLE t2(col1 VARCHAR(10)); + SHOW CREATE TABLE t2; +END| + +--echo + +# +# First-round checks. +# + +CALL p1()| + +--echo + +SHOW CREATE TABLE t1| + +--echo +--echo + +CALL mysqltest2.p2()| + +--echo + +SHOW CREATE TABLE mysqltest2.t2| + +# +# Alter database. +# + +--echo + +ALTER DATABASE mysqltest1 COLLATE cp1251_general_cs| +ALTER DATABASE mysqltest2 COLLATE cp1251_general_cs| + +DROP TABLE t1| +DROP TABLE mysqltest2.t2| + +--echo + +# +# Second-round checks. +# + +CALL p1()| + +--echo + +SHOW CREATE TABLE t1| + +--echo +--echo + +CALL mysqltest2.p2()| + +--echo + +SHOW CREATE TABLE mysqltest2.t2| + +########################################################################### +# +# That's it. +# +########################################################################### + +# +# Cleanup. +# + +--connection default +--echo +--echo ---> connection: default + +--disconnect con2 +--disconnect con3 + +use test| + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| diff --git a/mysql-test/t/ddl_i18n_utf8.test b/mysql-test/t/ddl_i18n_utf8.test new file mode 100644 index 00000000000..8fbf819a1b0 --- /dev/null +++ b/mysql-test/t/ddl_i18n_utf8.test @@ -0,0 +1,1112 @@ +# Objects to test: +# - stored procedures/functions; +# - triggers; +# - events; +# - views; +# +# For stored routines: +# - create a database with collation utf8_unicode_ci; +# - create an object, which +# - contains SP-var with explicit CHARSET-clause; +# - contains SP-var without CHARSET-clause; +# - contains text constant; +# - has localized routine/parameter names; +# - check: +# - execute; +# - SHOW CREATE output; +# - SHOW output; +# - SELECT FROM INFORMATION_SCHEMA output; +# - alter database character set; +# - change connection collation; +# - check again; +# - dump definition using mysqldump; +# - drop object; +# - restore object; +# + +########################################################################### +# +# NOTE: this file contains text in UTF8 and KOI8-R encodings. +# +########################################################################### + +--source include/have_utf8.inc +--source include/have_cp866.inc +--source include/have_cp1251.inc +--source include/have_koi8r.inc + +########################################################################### + +set names utf8; +delimiter |; + +########################################################################### +# +# * Views. +# +########################################################################### + +--echo +--echo ------------------------------------------------------------------- +--echo Views +--echo ------------------------------------------------------------------- +--echo + +# +# Preparation: +# + +# - Create database with fixed, pre-defined character set. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1| +--enable_warnings + +CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci| + +use mysqltest1| + +CREATE TABLE t1(кол INT)| +INSERT INTO t1 VALUES(1)| + +# - Create views; + +--echo + +CREATE VIEW v1 AS + SELECT 'тест' AS c1, кол AS c2 + FROM t1| + +--echo + +CREATE VIEW v2 AS SELECT _koi8r'' as c1| + +--echo + +# +# First-round checks. +# + +--source include/ddl_i18n.check_views.inc + +# +# Change running environment (alter database character set, change session +# variables). +# + +--echo +--echo + +ALTER DATABASE mysqltest1 COLLATE cp866_general_ci| + +# +# Second-round checks: +# + +# - Change connection to flush cache; + +--connect (con2,localhost,root,,) +--echo +--echo ---> connection: con2 + +# - Switch environment variables and trigger loading views; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +--disable_result_log +SELECT * FROM mysqltest1.v1| +SELECT * FROM mysqltest1.v2| +--enable_result_log + +use mysqltest1| + +# - Restore environment; + +set names utf8| + +# - Check! + +--source include/ddl_i18n.check_views.inc + +# +# Check mysqldump. +# + +# - Dump mysqltest1; + +--echo +--echo ---> Dumping mysqltest1 to ddl_i18n_utf8views.mysqltest1.sql + +--exec $MYSQL_DUMP --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8views.mysqltest1.sql + +# - Clean mysqltest1; + +--echo +--echo + +DROP DATABASE mysqltest1| + +# - Restore mysqltest1; + +--echo +--echo + +--echo ---> Restoring mysqltest1... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8views.mysqltest1.sql + +# +# Third-round checks. +# + +# - Change connection to flush cache; + +--connect (con3,localhost,root,,) +--echo +--echo ---> connection: con3 + +# - Switch environment variables and trigger loading stored procedures; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +--disable_result_log +SELECT * FROM mysqltest1.v1| +SELECT * FROM mysqltest1.v2| +--enable_result_log + +use mysqltest1| + +# - Restore environment; + +set names utf8| + +# - Check! + +--source include/ddl_i18n.check_views.inc + +# +# Cleanup. +# + +--connection default +--echo +--echo ---> connection: default + +--disconnect con2 +--disconnect con3 + +use test| + +DROP DATABASE mysqltest1| + +########################################################################### +# +# * Stored procedures/functions. +# +########################################################################### + +--echo +--echo ------------------------------------------------------------------- +--echo Stored procedures/functions +--echo ------------------------------------------------------------------- +--echo + +# +# Preparation: +# + +# - 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| + +use mysqltest1| + +# - Create two stored routines -- with and without explicit +# CHARSET-clause for SP-variable; +# + +--echo + +# - Procedure p1 + +CREATE PROCEDURE p1( + INOUT парам1 CHAR(10), + OUT парам2 CHAR(10)) +BEGIN + DECLARE перем1 CHAR(10); + + SELECT + COLLATION(перем1) AS c1, + COLLATION(парам1) AS c2, + COLLATION(парам2) AS c3; + + SELECT + COLLATION('текст') AS c4, + COLLATION(_utf8 'текст') AS c5, + COLLATION(_koi8r '') AS c6, + @@collation_connection AS c7, + @@character_set_client AS c8; + + SET парам1 = 'a'; + SET парам2 = 'b'; +END| + +--echo + +# - Procedure p2 + +CREATE PROCEDURE p2( + INOUT парам1 CHAR(10) CHARACTER SET utf8, + OUT парам2 CHAR(10) CHARACTER SET utf8) +BEGIN + DECLARE перем1 CHAR(10) CHARACTER SET utf8; + + SELECT + COLLATION(перем1) AS c1, + COLLATION(парам1) AS c2, + COLLATION(парам2) AS c3; + + SELECT + COLLATION('текст') AS c4, + COLLATION(_utf8 'текст') AS c5, + COLLATION(_koi8r '') AS c6, + @@collation_connection AS c7, + @@character_set_client AS c8; + + SET парам1 = 'a'; + SET парам2 = 'b'; +END| + +--echo + +# - Procedure p3 + +CREATE PROCEDURE mysqltest2.p3( + INOUT парам1 CHAR(10), + OUT парам2 CHAR(10)) +BEGIN + DECLARE перем1 CHAR(10); + + SELECT + COLLATION(перем1) AS c1, + COLLATION(парам1) AS c2, + COLLATION(парам2) AS c3; + + SELECT + COLLATION('текст') AS c4, + COLLATION(_utf8 'текст') AS c5, + COLLATION(_koi8r '') AS c6, + @@collation_connection AS c7, + @@character_set_client AS c8; + + SET парам1 = 'a'; + SET парам2 = 'b'; +END| + +--echo + +# - Procedure p4 + +CREATE PROCEDURE mysqltest2.p4( + INOUT парам1 CHAR(10) CHARACTER SET utf8, + OUT парам2 CHAR(10) CHARACTER SET utf8) +BEGIN + DECLARE перем1 CHAR(10) CHARACTER SET utf8; + + SELECT + COLLATION(перем1) AS c1, + COLLATION(парам1) AS c2, + COLLATION(парам2) AS c3; + + SELECT + COLLATION('текст') AS c4, + COLLATION(_utf8 'текст') AS c5, + COLLATION(_koi8r '') AS c6, + @@collation_connection AS c7, + @@character_set_client AS c8; + + SET парам1 = 'a'; + SET парам2 = 'b'; +END| + +# +# First-round checks. +# + +--source include/ddl_i18n.check_sp.inc + +# +# Change running environment (alter database character set, change session +# variables). +# + +--echo +--echo + +ALTER DATABASE mysqltest1 COLLATE cp866_general_ci| +ALTER DATABASE mysqltest2 COLLATE cp866_general_ci| + +# +# Second-round checks: +# + +# - Change connection to flush SP-cache; + +--connect (con2,localhost,root,,mysqltest1) +--echo +--echo ---> connection: con2 + +# - Switch environment variables and trigger loading stored procedures; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +CALL p1(@a, @b)| +CALL p2(@a, @b)| +CALL mysqltest2.p3(@a, @b)| +CALL mysqltest2.p4(@a, @b)| + +# - Restore environment; + +set names utf8| + +# - Check! + +--source include/ddl_i18n.check_sp.inc + +# +# Check mysqldump. +# + +# - Dump mysqltest1, mysqltest2; + +--echo +--echo ---> Dump of mysqltest1 + +--exec $MYSQL_DUMP --compact --routines --databases mysqltest1 + +--echo +--echo ---> Dumping mysqltest1 to ddl_i18n_utf8sp.mysqltest1.sql + +--exec $MYSQL_DUMP --compact --routines --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8sp.mysqltest1.sql + +--echo +--echo ---> Dump of mysqltest2 + +--exec $MYSQL_DUMP --compact --routines --databases mysqltest2 + +--echo +--echo ---> Dumping mysqltest2 to ddl_i18n_utf8sp.mysqltest2.sql + +--exec $MYSQL_DUMP --compact --routines --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8sp.mysqltest2.sql + +# - Clean mysqltest1, mysqltest2; + +--echo +--echo + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + +# - Restore mysqltest1; + +--echo +--echo + +--echo ---> Restoring mysqltest1... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8sp.mysqltest1.sql + +--echo ---> Restoring mysqltest2... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8sp.mysqltest2.sql + +# +# Third-round checks. +# + +# - Change connection to flush SP-cache; + +--connect (con3,localhost,root,,mysqltest1) +--echo +--echo ---> connection: con3 + +# - Switch environment variables and trigger loading stored procedures; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +CALL p1(@a, @b)| +CALL p2(@a, @b)| +CALL mysqltest2.p3(@a, @b)| +CALL mysqltest2.p4(@a, @b)| + +# - Restore environment; + +set names utf8| + +# - Check! + +--source include/ddl_i18n.check_sp.inc + +# +# Cleanup. +# + +--connection default +--echo +--echo ---> connection: default + +--disconnect con2 +--disconnect con3 + +use test| + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + +########################################################################### +# +# * Triggers. +# +########################################################################### + +--echo +--echo ------------------------------------------------------------------- +--echo Triggers +--echo ------------------------------------------------------------------- +--echo + +# +# Preparation: +# + +# - 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| + +use mysqltest1| + +# - Create tables for triggers; + +CREATE TABLE t1(c INT)| +CREATE TABLE mysqltest2.t1(c INT)| + +# - Create log tables; + +CREATE TABLE log(msg VARCHAR(255))| +CREATE TABLE mysqltest2.log(msg VARCHAR(255))| + + +# - Create triggers -- with and without explicit CHARSET-clause for +# SP-variable; +# + +--echo + +# - Trigger trg1 + +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE перем1 CHAR(10); + + INSERT INTO log VALUES(COLLATION(перем1)); + INSERT INTO log VALUES(COLLATION('текст')); + INSERT INTO log VALUES(COLLATION(_utf8 'текст')); + INSERT INTO log VALUES(COLLATION(_koi8r '')); + INSERT INTO log VALUES(@@collation_connection); + INSERT INTO log VALUES(@@character_set_client); + + SET @a1 = 'текст'; + SET @a2 = _utf8 'текст'; + SET @a3 = _koi8r ''; +END| + +--echo + +# - Trigger trg2 + +CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE перем1 CHAR(10) CHARACTER SET utf8; + + INSERT INTO log VALUES(COLLATION(перем1)); + INSERT INTO log VALUES(COLLATION('текст')); + INSERT INTO log VALUES(COLLATION(_utf8 'текст')); + INSERT INTO log VALUES(COLLATION(_koi8r '')); + INSERT INTO log VALUES(@@collation_connection); + INSERT INTO log VALUES(@@character_set_client); + + SET @b1 = 'текст'; + SET @b2 = _utf8 'текст'; + SET @b3 = _koi8r ''; +END| + +--echo + +# - Trigger trg3 + +CREATE TRIGGER mysqltest2.trg3 BEFORE INSERT ON mysqltest2.t1 FOR EACH ROW +BEGIN + DECLARE перем1 CHAR(10); + + INSERT INTO log VALUES(COLLATION(перем1)); + INSERT INTO log VALUES(COLLATION('текст')); + INSERT INTO log VALUES(COLLATION(_utf8 'текст')); + INSERT INTO log VALUES(COLLATION(_koi8r '')); + INSERT INTO log VALUES(@@collation_connection); + INSERT INTO log VALUES(@@character_set_client); + + SET @a1 = 'текст'; + SET @a2 = _utf8 'текст'; + SET @a3 = _koi8r ''; +END| + +--echo + +# - Trigger trg4 + +CREATE TRIGGER mysqltest2.trg4 AFTER INSERT ON mysqltest2.t1 FOR EACH ROW +BEGIN + DECLARE перем1 CHAR(10) CHARACTER SET utf8; + + INSERT INTO log VALUES(COLLATION(перем1)); + INSERT INTO log VALUES(COLLATION('текст')); + INSERT INTO log VALUES(COLLATION(_utf8 'текст')); + INSERT INTO log VALUES(COLLATION(_koi8r '')); + INSERT INTO log VALUES(@@collation_connection); + INSERT INTO log VALUES(@@character_set_client); + + SET @b1 = 'текст'; + SET @b2 = _utf8 'текст'; + SET @b3 = _koi8r ''; +END| + +--echo + +# +# First-round checks. +# + +--source include/ddl_i18n.check_triggers.inc + +# +# Change running environment (alter database character set, change session +# variables). +# + +--echo +--echo + +ALTER DATABASE mysqltest1 COLLATE cp866_general_ci| +ALTER DATABASE mysqltest2 COLLATE cp866_general_ci| + +# +# Second-round checks: +# + +# - Flush table cache; + +ALTER TABLE t1 ADD COLUMN fake INT| +ALTER TABLE t1 DROP COLUMN fake| + +ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT| +ALTER TABLE mysqltest2.t1 DROP COLUMN fake| + +# - Switch environment variables and initiate loading of triggers +# (connect using NULL database); + +--connect (con2,localhost,root,,) +--echo +--echo ---> connection: con2 + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +INSERT INTO mysqltest1.t1 VALUES(0)| +INSERT INTO mysqltest2.t1 VALUES(0)| + +DELETE FROM mysqltest1.log| +DELETE FROM mysqltest2.log| + +# - Restore environment; + +set names utf8| + +use mysqltest1| + +# - Check! + +--source include/ddl_i18n.check_triggers.inc + +# +# Check mysqldump. +# + +# - Dump mysqltest1, mysqltest2; + +--echo +--echo ---> Dump of mysqltest1 + +--exec $MYSQL_DUMP --compact --triggers --databases mysqltest1 + +--echo +--echo ---> Dumping mysqltest1 to ddl_i18n_utf8triggers.mysqltest1.sql + +--exec $MYSQL_DUMP --compact --triggers --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8triggers.mysqltest1.sql + +--echo +--echo ---> Dump of mysqltest2 + +--exec $MYSQL_DUMP --compact --triggers --databases mysqltest2 + +--echo +--echo ---> Dumping mysqltest2 to ddl_i18n_utf8triggers.mysqltest2.sql + +--exec $MYSQL_DUMP --compact --triggers --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8triggers.mysqltest2.sql + +# - Clean mysqltest1, mysqltest2; + +--echo +--echo + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + +# - Restore mysqltest1; + +--echo +--echo + +--echo ---> Restoring mysqltest1... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8triggers.mysqltest1.sql + +--echo ---> Restoring mysqltest2... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8triggers.mysqltest2.sql + +# +# Third-round checks. +# + +# - Flush table cache; + +ALTER TABLE mysqltest1.t1 ADD COLUMN fake INT| +ALTER TABLE mysqltest1.t1 DROP COLUMN fake| + +ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT| +ALTER TABLE mysqltest2.t1 DROP COLUMN fake| + +# - Switch environment variables and initiate loading of triggers +# (connect using NULL database); + +--connect (con3,localhost,root,,) +--echo +--echo ---> connection: con3 + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +INSERT INTO mysqltest1.t1 VALUES(0)| +INSERT INTO mysqltest2.t1 VALUES(0)| + +DELETE FROM mysqltest1.log| +DELETE FROM mysqltest2.log| + +# - Restore environment; + +set names utf8| + +use mysqltest1| + +# - Check! + +--source include/ddl_i18n.check_triggers.inc + +# +# Cleanup. +# + +--connection default +--echo +--echo ---> connection: default + +--disconnect con2 +--disconnect con3 + +use test| + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + +########################################################################### +# +# * Events +# +# We don't have EXECUTE EVENT so far, so this test is limited. It checks that +# event with non-latin1 symbols can be created, dumped, restored and SHOW +# statements work properly. +# +########################################################################### + +--echo +--echo ------------------------------------------------------------------- +--echo Events +--echo ------------------------------------------------------------------- +--echo + +# +# Preparation: +# + +# - 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| + +use mysqltest1| + +# - Create two stored routines -- with and without explicit +# CHARSET-clause for SP-variable; +# + +--echo + +# - Event ev1 + +CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO +BEGIN + DECLARE перем1 CHAR(10); + + SELECT + COLLATION(перем1) AS c1, + COLLATION('текст') AS c2, + COLLATION(_utf8 'текст') AS c3, + COLLATION(_koi8r '') AS c4, + @@collation_connection AS c5, + @@character_set_client AS c6; +END| + +--echo + +# - Event ev2 + +CREATE EVENT ev2 ON SCHEDULE AT '2030-01-01 00:00:00' DO +BEGIN + DECLARE перем1 CHAR(10) CHARACTER SET utf8; + + SELECT + COLLATION(перем1) AS c1, + COLLATION('текст') AS c2, + COLLATION(_utf8 'текст') AS c3, + COLLATION(_koi8r '') AS c4, + @@collation_connection AS c5, + @@character_set_client AS c6; +END| + +--echo + +# - Event ev3 + +CREATE EVENT mysqltest2.ev3 ON SCHEDULE AT '2030-01-01 00:00:00' DO +BEGIN + DECLARE перем1 CHAR(10) CHARACTER SET utf8; + + SELECT + COLLATION(перем1) AS c1, + COLLATION('текст') AS c2, + COLLATION(_utf8 'текст') AS c3, + COLLATION(_koi8r '') AS c4, + @@collation_connection AS c5, + @@character_set_client AS c6; +END| + +--echo + +# - Event ev4 + +CREATE EVENT mysqltest2.ev4 ON SCHEDULE AT '2030-01-01 00:00:00' DO +BEGIN + DECLARE перем1 CHAR(10) CHARACTER SET utf8; + + SELECT + COLLATION(перем1) AS c1, + COLLATION('текст') AS c2, + COLLATION(_utf8 'текст') AS c3, + COLLATION(_koi8r '') AS c4, + @@collation_connection AS c5, + @@character_set_client AS c6; +END| + +--echo + + +# +# First-round checks. +# + +--source include/ddl_i18n.check_events.inc + +# +# Change running environment (alter database character set, change session +# variables). +# + +--echo +--echo + +ALTER DATABASE mysqltest1 COLLATE cp866_general_ci| +ALTER DATABASE mysqltest2 COLLATE cp866_general_ci| + +# +# Second-round checks: +# + +# - Change connection to flush cache; + +--connect (con2,localhost,root,,mysqltest1) +--echo +--echo ---> connection: con2 + +# - Switch environment variables and trigger loading stored procedures; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +--disable_result_log +SHOW CREATE EVENT ev1| +SHOW CREATE EVENT ev2| +SHOW CREATE EVENT mysqltest2.ev3| +SHOW CREATE EVENT mysqltest2.ev4| +--enable_result_log + +# - Restore environment; + +set names utf8| + +# - Check! + +--source include/ddl_i18n.check_events.inc + +# +# Check mysqldump. +# + +# - Dump mysqltest1, mysqltest2; + +--echo +--echo ---> Dump of mysqltest1 + +--exec $MYSQL_DUMP --compact --events --databases mysqltest1 + +--echo +--echo ---> Dumping mysqltest1 to ddl_i18n_utf8events.mysqltest1.sql + +--exec $MYSQL_DUMP --compact --events --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8events.mysqltest1.sql + +--echo +--echo ---> Dump of mysqltest2 + +--exec $MYSQL_DUMP --compact --events --databases mysqltest2 + +--echo +--echo ---> Dumping mysqltest2 to ddl_i18n_utf8events.mysqltest2.sql + +--exec $MYSQL_DUMP --compact --events --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8events.mysqltest2.sql + +# - Clean mysqltest1, mysqltest2; + +--echo +--echo + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + +# - Restore mysqltest1; + +--echo +--echo + +--echo ---> Restoring mysqltest1... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8events.mysqltest1.sql + +--echo ---> Restoring mysqltest2... +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8events.mysqltest2.sql + +# +# Third-round checks. +# + +# - Change connection to flush cache; + +--connect (con3,localhost,root,,mysqltest1) +--echo +--echo ---> connection: con3 + +# - Switch environment variables and trigger loading stored procedures; + +SET @@character_set_client= cp1251| +SET @@character_set_results= cp1251| +SET @@collation_connection= cp1251_general_ci| + +--disable_result_log +SHOW CREATE EVENT ev1| +SHOW CREATE EVENT ev2| +SHOW CREATE EVENT mysqltest2.ev3| +SHOW CREATE EVENT mysqltest2.ev4| +--enable_result_log + +# - Restore environment; + +set names utf8| + +# - Check! + +--source include/ddl_i18n.check_events.inc + +########################################################################### +# +# * DDL statements inside stored routine. +# +# Here we check that DDL statements use actual database collation even if they +# are called from stored routine. +# +########################################################################### + +--echo +--echo ------------------------------------------------------------------- +--echo DDL statements within stored routine. +--echo ------------------------------------------------------------------- +--echo + +# +# Preparation: +# + +# - 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| + +use mysqltest1| + +# - Create procedures; + +--echo + +CREATE PROCEDURE p1() +BEGIN + CREATE TABLE t1(col1 VARCHAR(10)); + SHOW CREATE TABLE t1; +END| + +--echo + +CREATE PROCEDURE mysqltest2.p2() +BEGIN + CREATE TABLE t2(col1 VARCHAR(10)); + SHOW CREATE TABLE t2; +END| + +--echo + +# +# First-round checks. +# + +CALL p1()| + +--echo + +SHOW CREATE TABLE t1| + +--echo +--echo + +CALL mysqltest2.p2()| + +--echo + +SHOW CREATE TABLE mysqltest2.t2| + +# +# Alter database. +# + +--echo + +ALTER DATABASE mysqltest1 COLLATE cp1251_general_cs| +ALTER DATABASE mysqltest2 COLLATE cp1251_general_cs| + +DROP TABLE t1| +DROP TABLE mysqltest2.t2| + +--echo + +# +# Second-round checks. +# + +CALL p1()| + +--echo + +SHOW CREATE TABLE t1| + +--echo +--echo + +CALL mysqltest2.p2()| + +--echo + +SHOW CREATE TABLE mysqltest2.t2| + +########################################################################### +# +# That's it. +# +########################################################################### + +# +# Cleanup. +# + +--connection default +--echo +--echo ---> connection: default + +--disconnect con2 +--disconnect con3 + +use test| + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| diff --git a/mysql-test/t/events.test b/mysql-test/t/events.test index dcb591352a8..d7232705b81 100644 --- a/mysql-test/t/events.test +++ b/mysql-test/t/events.test @@ -185,7 +185,30 @@ set names cp1251; create event 21 on schedule every '50:23:59:95' day_second COMMENT ' 1251 ' do select 1; --replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT 21; -insert into mysql.event (db, name, body, definer, interval_value, interval_field, originator) values (database(), "root22", "select 1", user(), 100, "SECOND_MICROSECOND", 1); +insert into mysql.event ( + db, + name, + body, + definer, + interval_value, + interval_field, + originator, + character_set_client, + collation_connection, + db_collation, + body_utf8) +values ( + database(), + "root22", + "select 1", + user(), + 100, + "SECOND_MICROSECOND", + 1, + 'utf8', + 'utf8_general_ci', + 'utf8_general_ci', + 'select 1'); --error ER_NOT_SUPPORTED_YET show create event root22; --error ER_NOT_SUPPORTED_YET diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 755419cbf52..9babeddbae2 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -825,7 +825,7 @@ drop table t1; 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','','','utf8','utf8_general_ci','utf8_general_ci','n/a'); select routine_name from information_schema.routines; delete from proc where name=''; use test; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index af2c044ee10..bca2ce7101a 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -599,4 +599,55 @@ set names latin1; --error ER_NO_SUCH_TABLE,ER_FILE_NOT_FOUND show columns from `#mysql50#????????`; +# +# SHOW CREATE TRIGGER test. +# + +# Prepare. + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE TABLE t1(c1 INT); + +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; + +# Test. + +SHOW CREATE TRIGGER t1_bi; + +CREATE PROCEDURE p1() SHOW CREATE TRIGGER t1_bi; + +CALL p1(); +CALL p1(); +CALL p1(); +CALL p1(); +CALL p1(); +CALL p1(); +CALL p1(); +CALL p1(); +CALL p1(); +CALL p1(); + +PREPARE stmt1 FROM 'SHOW CREATE TRIGGER t1_bi'; + +EXECUTE stmt1; +EXECUTE stmt1; +EXECUTE stmt1; +EXECUTE stmt1; +EXECUTE stmt1; +EXECUTE stmt1; +EXECUTE stmt1; +EXECUTE stmt1; +EXECUTE stmt1; +EXECUTE stmt1; + +# Cleanup. + +DROP TABLE t1; +DROP PROCEDURE p1; +DEALLOCATE PREPARE stmt1; + --echo End of 5.1 tests diff --git a/mysql-test/t/sp-destruct.test b/mysql-test/t/sp-destruct.test index df07091d2de..04a581ab45f 100644 --- a/mysql-test/t/sp-destruct.test +++ b/mysql-test/t/sp-destruct.test @@ -94,26 +94,33 @@ insert into mysql.proc ( db, name, type, specific_name, language, sql_data_access, is_deterministic, security_type, param_list, returns, body, definer, created, modified, - sql_mode, comment + sql_mode, comment, character_set_client, collation_connection, db_collation, + body_utf8 ) values ( 'test', 'bug14233_1', 'FUNCTION', 'bug14233_1', 'SQL', 'READS_SQL_DATA', 'NO', 'DEFINER', '', 'int(10)', 'select count(*) from mysql.user', - 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '' + 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '', + '', '', '', + 'select count(*) from mysql.user' ), ( 'test', 'bug14233_2', 'FUNCTION', 'bug14233_2', 'SQL', 'READS_SQL_DATA', 'NO', 'DEFINER', '', 'int(10)', 'begin declare x int; select count(*) into x from mysql.user; end', - 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '' + 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '', + '', '', '', + 'begin declare x int; select count(*) into x from mysql.user; end' ), ( 'test', 'bug14233_3', 'PROCEDURE', 'bug14233_3', 'SQL', 'READS_SQL_DATA','NO', 'DEFINER', '', '', 'alksj wpsj sa ^#!@ ', - 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '' + 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '', + '', '', '', + 'alksj wpsj sa ^#!@ ' ); --error ER_SP_PROC_TABLE_CORRUPT diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 29e759d7881..f5bad05a40a 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -6331,7 +6331,7 @@ set names utf8| 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(),'','', 'utf8', 'utf8_general_ci', 'utf8_general_ci', 'n/a')| --error ER_SP_PROC_TABLE_CORRUPT call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()| drop database това_е_дълго_име_за_база_данни_нали| diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index c441c7b5efc..ee807a1ae25 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -752,6 +752,11 @@ drop view v1; # # VIEWs with national characters # + +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ü; @@ -759,7 +764,10 @@ insert into vü values ('ü'); select * from vü; drop view vü; drop table tü; -set names latin1; + +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 |