diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/ps_1general.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/ps_2myisam.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/ps_3innodb.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/ps_4heap.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/ps_5merge.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/ps_6bdb.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/ps_7ndb.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/show_check.result | 443 | ||||
| -rw-r--r-- | mysql-test/r/sp-dynamic.result | 18 | ||||
| -rw-r--r-- | mysql-test/r/sp-error.result | 4 | ||||
| -rw-r--r-- | mysql-test/t/ps_1general.test | 4 | ||||
| -rw-r--r-- | mysql-test/t/show_check.test | 248 | ||||
| -rw-r--r-- | mysql-test/t/sp-dynamic.test | 12 | ||||
| -rw-r--r-- | mysql-test/t/sp-error.test | 10 |
14 files changed, 740 insertions, 19 deletions
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index df4ec793325..71f4bcbdf38 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -396,6 +396,8 @@ prepare stmt1 from ' execute stmt2 ' ; ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt1 from ' deallocate prepare never_prepared ' ; ERROR HY000: This command is not supported in the prepared statement protocol yet +prepare stmt1 from 'alter view v1 as select 2'; +ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt4 from ' use test ' ; ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt3 from ' create database mysqltest '; @@ -466,7 +468,7 @@ def table 253 64 2 Y 0 31 8 def type 253 10 3 Y 0 31 8 def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 -def key_len 253 4096 0 Y 128 31 63 +def key_len 253 1365 0 Y 0 31 8 def ref 253 1024 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 14 N 1 31 8 @@ -482,7 +484,7 @@ def table 253 64 2 Y 0 31 8 def type 253 10 5 Y 0 31 8 def possible_keys 253 4096 7 Y 0 31 8 def key 253 64 7 Y 0 31 8 -def key_len 253 4096 1 Y 128 31 63 +def key_len 253 1365 1 Y 0 31 8 def ref 253 1024 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 27 N 1 31 8 diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 06941cf6497..7ccb41e6294 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -1158,7 +1158,7 @@ def table 253 64 2 Y 0 31 8 def type 253 10 3 Y 0 31 8 def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 -def key_len 253 4096 0 Y 128 31 63 +def key_len 253 1365 0 Y 0 31 8 def ref 253 1024 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 0 N 1 31 8 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 235660cbbaf..21d2b23f27b 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -1158,7 +1158,7 @@ def table 253 64 2 Y 0 31 8 def type 253 10 3 Y 0 31 8 def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 -def key_len 253 4096 0 Y 128 31 63 +def key_len 253 1365 0 Y 0 31 8 def ref 253 1024 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 0 N 1 31 8 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index bf77ec7acf2..6b31e95c6c8 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -1159,7 +1159,7 @@ def table 253 64 2 Y 0 31 8 def type 253 10 3 Y 0 31 8 def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 -def key_len 253 4096 0 Y 128 31 63 +def key_len 253 1365 0 Y 0 31 8 def ref 253 1024 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 0 N 1 31 8 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index f6aa716d932..c7b20b774bd 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -1201,7 +1201,7 @@ def table 253 64 2 Y 0 31 8 def type 253 10 3 Y 0 31 8 def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 -def key_len 253 4096 0 Y 128 31 63 +def key_len 253 1365 0 Y 0 31 8 def ref 253 1024 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 0 N 1 31 8 @@ -4222,7 +4222,7 @@ def table 253 64 2 Y 0 31 8 def type 253 10 3 Y 0 31 8 def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 -def key_len 253 4096 0 Y 128 31 63 +def key_len 253 1365 0 Y 0 31 8 def ref 253 1024 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 0 N 1 31 8 diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result index 2c8b40e8818..5e97d5cf179 100644 --- a/mysql-test/r/ps_6bdb.result +++ b/mysql-test/r/ps_6bdb.result @@ -1158,7 +1158,7 @@ def table 253 64 2 Y 0 31 8 def type 253 10 3 Y 0 31 8 def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 -def key_len 253 4096 0 Y 128 31 63 +def key_len 253 1365 0 Y 0 31 8 def ref 253 1024 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 0 N 1 31 8 diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result index a0bbeed34ee..7ca18edc9cc 100644 --- a/mysql-test/r/ps_7ndb.result +++ b/mysql-test/r/ps_7ndb.result @@ -1158,7 +1158,7 @@ def table 253 64 2 Y 0 31 8 def type 253 10 3 Y 0 31 8 def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 -def key_len 253 4096 0 Y 128 31 63 +def key_len 253 1365 0 Y 0 31 8 def ref 253 1024 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 0 N 1 31 8 diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 0dfb7c95f9a..e2450c98202 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -7,60 +7,157 @@ delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mys flush privileges; create table t1 (a int not null primary key, b int not null,c int not null, key(b,c)); insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4); +-- Here we enable metadata just to check that the collation of the +-- resultset is non-binary for string type. This should be changed +-- after Bug#29394 is implemented. check table t1 fast; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Table 253 42 7 Y 0 31 8 +def Op 253 3 5 Y 0 31 8 +def Msg_type 253 3 6 Y 0 31 8 +def Msg_text 253 85 27 Y 0 31 8 Table Op Msg_type Msg_text test.t1 check status Table is already up to date check table t1 fast; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Table 253 42 7 Y 0 31 8 +def Op 253 3 5 Y 0 31 8 +def Msg_type 253 3 6 Y 0 31 8 +def Msg_text 253 85 27 Y 0 31 8 Table Op Msg_type Msg_text test.t1 check status Table is already up to date check table t1 changed; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Table 253 42 7 Y 0 31 8 +def Op 253 3 5 Y 0 31 8 +def Msg_type 253 3 6 Y 0 31 8 +def Msg_text 253 85 2 Y 0 31 8 Table Op Msg_type Msg_text test.t1 check status OK insert into t1 values (5,5,5); check table t1 changed; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Table 253 42 7 Y 0 31 8 +def Op 253 3 5 Y 0 31 8 +def Msg_type 253 3 6 Y 0 31 8 +def Msg_text 253 85 2 Y 0 31 8 Table Op Msg_type Msg_text test.t1 check status OK check table t1 medium; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Table 253 42 7 Y 0 31 8 +def Op 253 3 5 Y 0 31 8 +def Msg_type 253 3 6 Y 0 31 8 +def Msg_text 253 85 2 Y 0 31 8 Table Op Msg_type Msg_text test.t1 check status OK check table t1 extended; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Table 253 42 7 Y 0 31 8 +def Op 253 3 5 Y 0 31 8 +def Msg_type 253 3 6 Y 0 31 8 +def Msg_text 253 85 2 Y 0 31 8 Table Op Msg_type Msg_text test.t1 check status OK show index from t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def STATISTICS TABLE_NAME Table 253 64 2 N 1 0 8 +def STATISTICS NON_UNIQUE Non_unique 8 1 1 N 32769 0 63 +def STATISTICS INDEX_NAME Key_name 253 64 7 N 1 0 8 +def STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 32769 0 63 +def STATISTICS COLUMN_NAME Column_name 253 64 1 N 1 0 8 +def STATISTICS COLLATION Collation 253 1 1 Y 0 0 8 +def STATISTICS CARDINALITY Cardinality 8 21 1 Y 32768 0 63 +def STATISTICS SUB_PART Sub_part 8 3 0 Y 32768 0 63 +def STATISTICS PACKED Packed 253 10 0 Y 0 0 8 +def STATISTICS NULLABLE Null 253 3 0 N 1 0 8 +def STATISTICS INDEX_TYPE Index_type 253 16 5 N 1 0 8 +def STATISTICS COMMENT Comment 253 16 0 Y 0 0 8 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 a A 5 NULL NULL BTREE t1 1 b 1 b A 1 NULL NULL BTREE t1 1 b 2 c A 5 NULL NULL BTREE insert into t1 values (5,5,5); ERROR 23000: Duplicate entry '5' for key 1 +-- Here we enable metadata just to check that the collation of the +-- resultset is non-binary for string type. This should be changed +-- after Bug#29394 is implemented. optimize table t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Table 253 42 7 Y 0 31 8 +def Op 253 3 8 Y 0 31 8 +def Msg_type 253 3 6 Y 0 31 8 +def Msg_text 253 85 2 Y 0 31 8 Table Op Msg_type Msg_text test.t1 optimize status OK optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status Table is already up to date drop table t1; +-- Here we enable metadata just to check that the collation of the +-- resultset is non-binary for string type. This should be changed +-- after Bug#29394 is implemented. show variables like "wait_timeout%"; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def VARIABLES Variable_name Variable_name 253 80 12 N 1 0 8 +def VARIABLES Value Value 253 512 5 N 1 0 8 Variable_name Value wait_timeout 28800 show variables like "WAIT_timeout%"; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def VARIABLES Variable_name Variable_name 253 80 12 N 1 0 8 +def VARIABLES Value Value 253 512 5 N 1 0 8 Variable_name Value wait_timeout 28800 show variables like "this_doesn't_exists%"; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def VARIABLES Variable_name Variable_name 253 80 0 N 1 0 8 +def VARIABLES Value Value 253 512 0 N 1 0 8 Variable_name Value show table status from test like "this_doesn't_exists%"; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def TABLES TABLE_NAME Name 253 64 0 N 1 0 8 +def TABLES ENGINE Engine 253 64 0 Y 0 0 8 +def TABLES VERSION Version 8 21 0 Y 32768 0 63 +def TABLES ROW_FORMAT Row_format 253 10 0 Y 0 0 8 +def TABLES TABLE_ROWS Rows 8 21 0 Y 32768 0 63 +def TABLES AVG_ROW_LENGTH Avg_row_length 8 21 0 Y 32768 0 63 +def TABLES DATA_LENGTH Data_length 8 21 0 Y 32768 0 63 +def TABLES MAX_DATA_LENGTH Max_data_length 8 21 0 Y 32768 0 63 +def TABLES INDEX_LENGTH Index_length 8 21 0 Y 32768 0 63 +def TABLES DATA_FREE Data_free 8 21 0 Y 32768 0 63 +def TABLES AUTO_INCREMENT Auto_increment 8 21 0 Y 32768 0 63 +def TABLES CREATE_TIME Create_time 12 19 0 Y 128 0 63 +def TABLES UPDATE_TIME Update_time 12 19 0 Y 128 0 63 +def TABLES CHECK_TIME Check_time 12 19 0 Y 128 0 63 +def TABLES TABLE_COLLATION Collation 253 64 0 Y 0 0 8 +def TABLES CHECKSUM Checksum 8 21 0 Y 32768 0 63 +def TABLES CREATE_OPTIONS Create_options 253 255 0 Y 0 0 8 +def TABLES TABLE_COMMENT Comment 253 80 0 N 1 0 8 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment show databases; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def SCHEMATA SCHEMA_NAME Database 253 64 18 N 1 0 8 Database information_schema mysql test show databases like "test%"; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def SCHEMATA SCHEMA_NAME Database (test%) 253 64 4 N 1 0 8 Database (test%) test create table t1 (f1 int not null, f2 int not null, f3 int not null, f4 int not null, primary key(f1,f2,f3,f4)); insert into t1 values (1,1,1,0),(1,1,2,0),(1,1,3,0),(1,2,1,0),(1,2,2,0),(1,2,3,0),(1,3,1,0),(1,3,2,0),(1,3,3,0),(1,1,1,1),(1,1,2,1),(1,1,3,1),(1,2,1,1),(1,2,2,1),(1,2,3,1),(1,3,1,1),(1,3,2,1),(1,3,3,1); +-- Here we enable metadata just to check that the collation of the +-- resultset is non-binary for string type. This should be changed +-- after Bug#29394 is implemented. analyze table t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Table 253 42 7 Y 0 31 8 +def Op 253 3 7 Y 0 31 8 +def Msg_type 253 3 6 Y 0 31 8 +def Msg_text 253 85 2 Y 0 31 8 Table Op Msg_type Msg_text test.t1 analyze status OK show index from t1; @@ -69,7 +166,15 @@ t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE +-- Here we enable metadata just to check that the collation of the +-- resultset is non-binary for string type. This should be changed +-- after Bug#29394 is implemented. repair table t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Table 253 42 7 Y 0 31 8 +def Op 253 3 6 Y 0 31 8 +def Msg_type 253 3 6 Y 0 31 8 +def Msg_text 253 85 2 Y 0 31 8 Table Op Msg_type Msg_text test.t1 repair status OK show index from t1; @@ -645,4 +750,342 @@ select 1 from information_schema.tables limit 1; show status like 'slow_queries'; Variable_name Value Slow_queries 1 +DROP DATABASE IF EXISTS mysqltest1; +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +CREATE DATABASE mysqltest1; +CREATE TABLE t1(c INT NOT NULL PRIMARY KEY); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; +CREATE VIEW v1 AS SELECT 1; +CREATE PROCEDURE p1() SELECT 1; +CREATE FUNCTION f1() RETURNS INT RETURN 1; +set names utf8; +-- Here we enable metadata just to check that the collation of the +-- resultset is non-binary for string type. This should be changed +-- after Bug#29394 is implemented. +---------------------------------------------------------------- +SHOW CHARACTER SET LIKE 'utf8'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def CHARACTER_SETS CHARACTER_SET_NAME Charset 253 192 4 N 1 0 33 +def CHARACTER_SETS DESCRIPTION Description 253 180 13 N 1 0 33 +def CHARACTER_SETS DEFAULT_COLLATE_NAME Default collation 253 192 15 N 1 0 33 +def CHARACTER_SETS MAXLEN Maxlen 8 3 1 N 32769 0 63 +Charset Description Default collation Maxlen +utf8 UTF-8 Unicode utf8_general_ci 3 +---------------------------------------------------------------- +SHOW COLLATION LIKE 'latin1_bin'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def COLLATIONS COLLATION_NAME Collation 253 192 10 N 1 0 33 +def COLLATIONS CHARACTER_SET_NAME Charset 253 192 6 N 1 0 33 +def COLLATIONS ID Id 8 11 2 N 32769 0 63 +def COLLATIONS IS_DEFAULT Default 253 9 0 N 1 0 33 +def COLLATIONS IS_COMPILED Compiled 253 9 3 N 1 0 33 +def COLLATIONS SORTLEN Sortlen 8 3 1 N 32769 0 63 +Collation Charset Id Default Compiled Sortlen +latin1_bin latin1 47 Yes 1 +---------------------------------------------------------------- +SHOW CREATE DATABASE mysqltest1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Database 253 63 10 N 1 31 33 +def Create Database 253 1023 69 N 1 31 33 +Database Create Database +mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET latin1 */ +---------------------------------------------------------------- +SHOW DATABASES LIKE 'mysqltest1'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def SCHEMATA SCHEMA_NAME Database (mysqltest1) 253 192 10 N 1 0 33 +Database (mysqltest1) +mysqltest1 +---------------------------------------------------------------- +SHOW CREATE TABLE t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Table 253 63 2 N 1 31 33 +def Create Table 253 1023 103 N 1 31 33 +Table Create Table +t1 CREATE TABLE `t1` ( + `c` int(11) NOT NULL, + PRIMARY KEY (`c`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +---------------------------------------------------------------- +SHOW INDEX FROM t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def STATISTICS TABLE_NAME Table 253 192 2 N 1 0 33 +def STATISTICS NON_UNIQUE Non_unique 8 1 1 N 32769 0 63 +def STATISTICS INDEX_NAME Key_name 253 192 7 N 1 0 33 +def STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 32769 0 63 +def STATISTICS COLUMN_NAME Column_name 253 192 1 N 1 0 33 +def STATISTICS COLLATION Collation 253 3 1 Y 0 0 33 +def STATISTICS CARDINALITY Cardinality 8 21 1 Y 32768 0 63 +def STATISTICS SUB_PART Sub_part 8 3 0 Y 32768 0 63 +def STATISTICS PACKED Packed 253 30 0 Y 0 0 33 +def STATISTICS NULLABLE Null 253 9 0 N 1 0 33 +def STATISTICS INDEX_TYPE Index_type 253 48 5 N 1 0 33 +def STATISTICS COMMENT Comment 253 48 0 Y 0 0 33 +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 c A 0 NULL NULL BTREE +---------------------------------------------------------------- +SELECT +TABLE_CATALOG, +TABLE_SCHEMA, +TABLE_NAME, +TABLE_TYPE, +ENGINE, +ROW_FORMAT, +TABLE_COLLATION, +CREATE_OPTIONS, +TABLE_COMMENT +FROM INFORMATION_SCHEMA.TABLES +WHERE table_name = 't1'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def TABLES TABLE_CATALOG TABLE_CATALOG 253 1536 0 Y 0 0 33 +def TABLES TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 +def TABLES TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 +def TABLES TABLE_TYPE TABLE_TYPE 253 192 10 N 1 0 33 +def TABLES ENGINE ENGINE 253 192 6 Y 0 0 33 +def TABLES ROW_FORMAT ROW_FORMAT 253 30 5 Y 0 0 33 +def TABLES TABLE_COLLATION TABLE_COLLATION 253 192 17 Y 0 0 33 +def TABLES CREATE_OPTIONS CREATE_OPTIONS 253 765 0 Y 0 0 33 +def TABLES TABLE_COMMENT TABLE_COMMENT 253 240 0 N 1 0 33 +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE ROW_FORMAT TABLE_COLLATION CREATE_OPTIONS TABLE_COMMENT +NULL test t1 BASE TABLE MyISAM Fixed latin1_swedish_ci +---------------------------------------------------------------- +SELECT +TABLE_CATALOG, +TABLE_SCHEMA, +TABLE_NAME, +COLUMN_NAME, +COLUMN_DEFAULT, +IS_NULLABLE, +DATA_TYPE, +CHARACTER_SET_NAME, +COLLATION_NAME, +COLUMN_TYPE, +COLUMN_KEY, +EXTRA, +PRIVILEGES, +COLUMN_COMMENT +FROM INFORMATION_SCHEMA.COLUMNS +WHERE table_name = 't1'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def COLUMNS TABLE_CATALOG TABLE_CATALOG 253 1536 0 Y 0 0 33 +def COLUMNS TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 +def COLUMNS TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 +def COLUMNS COLUMN_NAME COLUMN_NAME 253 192 1 N 1 0 33 +def COLUMNS COLUMN_DEFAULT COLUMN_DEFAULT 252 589815 0 Y 16 0 33 +def COLUMNS IS_NULLABLE IS_NULLABLE 253 9 2 N 1 0 33 +def COLUMNS DATA_TYPE DATA_TYPE 253 192 3 N 1 0 33 +def COLUMNS CHARACTER_SET_NAME CHARACTER_SET_NAME 253 192 0 Y 0 0 33 +def COLUMNS COLLATION_NAME COLLATION_NAME 253 192 0 Y 0 0 33 +def COLUMNS COLUMN_TYPE COLUMN_TYPE 252 589815 7 N 17 0 33 +def COLUMNS COLUMN_KEY COLUMN_KEY 253 9 3 N 1 0 33 +def COLUMNS EXTRA EXTRA 253 60 0 N 1 0 33 +def COLUMNS PRIVILEGES PRIVILEGES 253 240 31 N 1 0 33 +def COLUMNS COLUMN_COMMENT COLUMN_COMMENT 253 765 0 N 1 0 33 +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT +NULL test t1 c NULL NO int NULL NULL int(11) PRI select,insert,update,references +---------------------------------------------------------------- +SHOW TABLES LIKE 't1'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def TABLE_NAMES TABLE_NAME Tables_in_test (t1) 253 192 2 N 1 0 33 +Tables_in_test (t1) +t1 +---------------------------------------------------------------- +SHOW COLUMNS FROM t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def COLUMNS COLUMN_NAME Field 253 192 1 N 1 0 33 +def COLUMNS COLUMN_TYPE Type 252 589815 7 N 17 0 33 +def COLUMNS IS_NULLABLE Null 253 9 2 N 1 0 33 +def COLUMNS COLUMN_KEY Key 253 9 3 N 1 0 33 +def COLUMNS COLUMN_DEFAULT Default 252 589815 0 Y 16 0 33 +def COLUMNS EXTRA Extra 253 60 0 N 1 0 33 +Field Type Null Key Default Extra +c int(11) NO PRI +---------------------------------------------------------------- +SHOW TRIGGERS LIKE 't1'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def TRIGGERS TRIGGER_NAME Trigger 253 192 5 N 1 0 33 +def TRIGGERS EVENT_MANIPULATION Event 253 18 6 N 1 0 33 +def TRIGGERS EVENT_OBJECT_TABLE Table 253 192 2 N 1 0 33 +def TRIGGERS ACTION_STATEMENT Statement 252 589815 10 N 17 0 33 +def TRIGGERS ACTION_TIMING Timing 253 18 6 N 1 0 33 +def TRIGGERS CREATED Created 12 19 0 Y 128 0 63 +def TRIGGERS SQL_MODE sql_mode 252 589815 0 N 17 0 33 +def TRIGGERS DEFINER Definer 252 589815 14 N 17 0 33 +Trigger Event Table Statement Timing Created sql_mode Definer +t1_bi INSERT t1 SET @a = 1 BEFORE NULL root@localhost +---------------------------------------------------------------- +SELECT +TRIGGER_CATALOG, +TRIGGER_SCHEMA, +TRIGGER_NAME, +EVENT_MANIPULATION, +EVENT_OBJECT_CATALOG, +EVENT_OBJECT_SCHEMA, +EVENT_OBJECT_TABLE, +ACTION_CONDITION, +ACTION_STATEMENT, +ACTION_ORIENTATION, +ACTION_TIMING, +ACTION_REFERENCE_OLD_TABLE, +ACTION_REFERENCE_NEW_TABLE, +ACTION_REFERENCE_OLD_ROW, +ACTION_REFERENCE_NEW_ROW, +SQL_MODE, +DEFINER +FROM INFORMATION_SCHEMA.TRIGGERS +WHERE trigger_name = 't1_bi'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def TRIGGERS TRIGGER_CATALOG TRIGGER_CATALOG 253 1536 0 Y 0 0 33 +def TRIGGERS TRIGGER_SCHEMA TRIGGER_SCHEMA 253 192 4 N 1 0 33 +def TRIGGERS TRIGGER_NAME TRIGGER_NAME 253 192 5 N 1 0 33 +def TRIGGERS EVENT_MANIPULATION EVENT_MANIPULATION 253 18 6 N 1 0 33 +def TRIGGERS EVENT_OBJECT_CATALOG EVENT_OBJECT_CATALOG 253 1536 0 Y 0 0 33 +def TRIGGERS EVENT_OBJECT_SCHEMA EVENT_OBJECT_SCHEMA 253 192 4 N 1 0 33 +def TRIGGERS EVENT_OBJECT_TABLE EVENT_OBJECT_TABLE 253 192 2 N 1 0 33 +def TRIGGERS ACTION_CONDITION ACTION_CONDITION 252 589815 0 Y 16 0 33 +def TRIGGERS ACTION_STATEMENT ACTION_STATEMENT 252 589815 10 N 17 0 33 +def TRIGGERS ACTION_ORIENTATION ACTION_ORIENTATION 253 27 3 N 1 0 33 +def TRIGGERS ACTION_TIMING ACTION_TIMING 253 18 6 N 1 0 33 +def TRIGGERS ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_OLD_TABLE 253 192 0 Y 0 0 33 +def TRIGGERS ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_NEW_TABLE 253 192 0 Y 0 0 33 +def TRIGGERS ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_OLD_ROW 253 9 3 N 1 0 33 +def TRIGGERS ACTION_REFERENCE_NEW_ROW ACTION_REFERENCE_NEW_ROW 253 9 3 N 1 0 33 +def TRIGGERS SQL_MODE SQL_MODE 252 589815 0 N 17 0 33 +def TRIGGERS DEFINER DEFINER 252 589815 14 N 17 0 33 +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW SQL_MODE DEFINER +NULL test t1_bi INSERT NULL test t1 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW root@localhost +---------------------------------------------------------------- +SHOW CREATE VIEW v1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def View 253 63 2 N 1 31 33 +def Create View 253 1023 103 N 1 31 33 +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` +---------------------------------------------------------------- +SELECT * +FROM INFORMATION_SCHEMA.VIEWS +WHERE table_name = 'v1'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def VIEWS TABLE_CATALOG TABLE_CATALOG 253 1536 0 Y 0 0 33 +def VIEWS TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 +def VIEWS TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 +def VIEWS VIEW_DEFINITION VIEW_DEFINITION 252 589815 41 N 17 0 33 +def VIEWS CHECK_OPTION CHECK_OPTION 253 24 4 N 1 0 33 +def VIEWS IS_UPDATABLE IS_UPDATABLE 253 9 2 N 1 0 33 +def VIEWS DEFINER DEFINER 253 231 14 N 1 0 33 +def VIEWS SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE +NULL test v1 /* ALGORITHM=UNDEFINED */ select 1 AS `1` NONE NO root@localhost DEFINER +---------------------------------------------------------------- +SHOW CREATE PROCEDURE p1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Procedure 253 63 2 N 1 31 33 +def sql_mode 253 0 0 N 1 31 33 +def Create Procedure 253 2046 59 Y 0 31 33 +Procedure sql_mode Create Procedure +p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +SELECT 1 +---------------------------------------------------------------- +SELECT +SPECIFIC_NAME, +ROUTINE_CATALOG, +ROUTINE_SCHEMA, +ROUTINE_NAME, +ROUTINE_TYPE, +DTD_IDENTIFIER, +ROUTINE_BODY, +ROUTINE_DEFINITION, +EXTERNAL_NAME, +EXTERNAL_LANGUAGE, +PARAMETER_STYLE, +IS_DETERMINISTIC, +SQL_DATA_ACCESS, +SQL_PATH, +SECURITY_TYPE, +SQL_MODE, +ROUTINE_COMMENT, +DEFINER +FROM INFORMATION_SCHEMA.ROUTINES +WHERE routine_name = 'p1'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def ROUTINES SPECIFIC_NAME SPECIFIC_NAME 253 192 2 N 1 0 33 +def ROUTINES ROUTINE_CATALOG ROUTINE_CATALOG 253 1536 0 Y 0 0 33 +def ROUTINES ROUTINE_SCHEMA ROUTINE_SCHEMA 253 192 4 N 1 0 33 +def ROUTINES ROUTINE_NAME ROUTINE_NAME 253 192 2 N 1 0 33 +def ROUTINES ROUTINE_TYPE ROUTINE_TYPE 253 27 9 N 1 0 33 +def ROUTINES DTD_IDENTIFIER DTD_IDENTIFIER 253 192 0 Y 0 0 33 +def ROUTINES ROUTINE_BODY ROUTINE_BODY 253 24 3 N 1 0 33 +def ROUTINES ROUTINE_DEFINITION ROUTINE_DEFINITION 252 589815 8 Y 16 0 33 +def ROUTINES EXTERNAL_NAME EXTERNAL_NAME 253 192 0 Y 0 0 33 +def ROUTINES EXTERNAL_LANGUAGE EXTERNAL_LANGUAGE 253 192 0 Y 0 0 33 +def ROUTINES PARAMETER_STYLE PARAMETER_STYLE 253 24 3 N 1 0 33 +def ROUTINES IS_DETERMINISTIC IS_DETERMINISTIC 253 9 2 N 1 0 33 +def ROUTINES SQL_DATA_ACCESS SQL_DATA_ACCESS 253 192 12 N 1 0 33 +def ROUTINES SQL_PATH SQL_PATH 253 192 0 Y 0 0 33 +def ROUTINES SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 +def ROUTINES SQL_MODE SQL_MODE 252 589815 0 N 17 0 33 +def ROUTINES ROUTINE_COMMENT ROUTINE_COMMENT 253 192 0 N 1 0 33 +def ROUTINES DEFINER DEFINER 253 231 14 N 1 0 33 +SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE SQL_MODE ROUTINE_COMMENT DEFINER +p1 NULL test p1 PROCEDURE NULL SQL SELECT 1 NULL NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost +---------------------------------------------------------------- +SHOW CREATE FUNCTION f1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def Function 253 63 2 N 1 31 33 +def sql_mode 253 0 0 N 1 31 33 +def Create Function 253 2046 74 Y 0 31 33 +Function sql_mode Create Function +f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) +RETURN 1 +---------------------------------------------------------------- +SELECT +SPECIFIC_NAME, +ROUTINE_CATALOG, +ROUTINE_SCHEMA, +ROUTINE_NAME, +ROUTINE_TYPE, +DTD_IDENTIFIER, +ROUTINE_BODY, +ROUTINE_DEFINITION, +EXTERNAL_NAME, +EXTERNAL_LANGUAGE, +PARAMETER_STYLE, +IS_DETERMINISTIC, +SQL_DATA_ACCESS, +SQL_PATH, +SECURITY_TYPE, +SQL_MODE, +ROUTINE_COMMENT, +DEFINER +FROM INFORMATION_SCHEMA.ROUTINES +WHERE routine_name = 'f1'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def ROUTINES SPECIFIC_NAME SPECIFIC_NAME 253 192 2 N 1 0 33 +def ROUTINES ROUTINE_CATALOG ROUTINE_CATALOG 253 1536 0 Y 0 0 33 +def ROUTINES ROUTINE_SCHEMA ROUTINE_SCHEMA 253 192 4 N 1 0 33 +def ROUTINES ROUTINE_NAME ROUTINE_NAME 253 192 2 N 1 0 33 +def ROUTINES ROUTINE_TYPE ROUTINE_TYPE 253 27 8 N 1 0 33 +def ROUTINES DTD_IDENTIFIER DTD_IDENTIFIER 253 192 7 Y 0 0 33 +def ROUTINES ROUTINE_BODY ROUTINE_BODY 253 24 3 N 1 0 33 +def ROUTINES ROUTINE_DEFINITION ROUTINE_DEFINITION 252 589815 8 Y 16 0 33 +def ROUTINES EXTERNAL_NAME EXTERNAL_NAME 253 192 0 Y 0 0 33 +def ROUTINES EXTERNAL_LANGUAGE EXTERNAL_LANGUAGE 253 192 0 Y 0 0 33 +def ROUTINES PARAMETER_STYLE PARAMETER_STYLE 253 24 3 N 1 0 33 +def ROUTINES IS_DETERMINISTIC IS_DETERMINISTIC 253 9 2 N 1 0 33 +def ROUTINES SQL_DATA_ACCESS SQL_DATA_ACCESS 253 192 12 N 1 0 33 +def ROUTINES SQL_PATH SQL_PATH 253 192 0 Y 0 0 33 +def ROUTINES SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 +def ROUTINES SQL_MODE SQL_MODE 252 589815 0 N 17 0 33 +def ROUTINES ROUTINE_COMMENT ROUTINE_COMMENT 253 192 0 N 1 0 33 +def ROUTINES DEFINER DEFINER 253 231 14 N 1 0 33 +SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE SQL_MODE ROUTINE_COMMENT DEFINER +f1 NULL test f1 FUNCTION int(11) SQL RETURN 1 NULL NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost +---------------------------------------------------------------- +DROP DATABASE mysqltest1; +DROP TABLE t1; +DROP VIEW v1; +DROP PROCEDURE p1; +DROP FUNCTION f1; End of 5.0 tests diff --git a/mysql-test/r/sp-dynamic.result b/mysql-test/r/sp-dynamic.result index d9d5706cded..0f50bcc97cd 100644 --- a/mysql-test/r/sp-dynamic.result +++ b/mysql-test/r/sp-dynamic.result @@ -87,6 +87,10 @@ prepare stmt from "create table t1 (a int)"; execute stmt; insert into t1 (a) values (1); select * from t1; +prepare stmt_alter from "alter table t1 add (b int)"; +execute stmt_alter; +insert into t1 (a,b) values (2,1); +deallocate prepare stmt_alter; deallocate prepare stmt; deallocate prepare stmt_drop; end| @@ -245,6 +249,9 @@ a 1 drop procedure p1| drop table if exists t1| +drop table if exists t2| +Warnings: +Note 1051 Unknown table 't2' create table t1 (id integer primary key auto_increment, stmt_text char(35), status varchar(20))| insert into t1 (stmt_text) values @@ -255,7 +262,10 @@ insert into t1 (stmt_text) values ("help help"), ("show databases"), ("show tables"), ("show table status"), ("show open tables"), ("show storage engines"), ("insert into t1 (id) values (1)"), ("update t1 set status=''"), -("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar")| +("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar"), +("create view v1 as select 1"), ("alter view v1 as select 2"), +("drop view v1"),("create table t2 (a int)"),("alter table t2 add (b int)"), +("drop table t2")| create procedure p1() begin declare v_stmt_text varchar(255); @@ -305,6 +315,12 @@ id stmt_text status 20 truncate t1 supported 21 call p1() supported 22 foo bar syntax error +23 create view v1 as select 1 supported +24 alter view v1 as select 2 not supported +25 drop view v1 supported +26 create table t2 (a int) supported +27 alter table t2 add (b int) supported +28 drop table t2 supported drop procedure p1| drop table t1| prepare stmt from 'select 1'| diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 7a2f812cde4..bd0640b2b14 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -982,9 +982,9 @@ ERROR HY000: Explicit or implicit commit is not allowed in stored function or tr CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END | ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +ERROR 0A000: ALTER VIEW is not allowed in stored procedures CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +ERROR 0A000: ALTER VIEW is not allowed in stored procedures CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END | ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END | diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test index 2e7fea2ff3d..952931f2ad1 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -423,6 +423,10 @@ prepare stmt1 from ' execute stmt2 ' ; --error ER_UNSUPPORTED_PS prepare stmt1 from ' deallocate prepare never_prepared ' ; +## We don't support alter view as prepared statements +--error ER_UNSUPPORTED_PS +prepare stmt1 from 'alter view v1 as select 2'; + ## switch the database connection --error 1295 prepare stmt4 from ' use test ' ; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 369ae9bff65..1db40b0c17c 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -18,6 +18,12 @@ flush privileges; create table t1 (a int not null primary key, b int not null,c int not null, key(b,c)); insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4); + +--echo -- Here we enable metadata just to check that the collation of the +--echo -- resultset is non-binary for string type. This should be changed +--echo -- after Bug#29394 is implemented. + +--enable_metadata check table t1 fast; check table t1 fast; check table t1 changed; @@ -26,28 +32,58 @@ check table t1 changed; check table t1 medium; check table t1 extended; show index from t1; +--disable_metadata --error 1062 insert into t1 values (5,5,5); + +--echo -- Here we enable metadata just to check that the collation of the +--echo -- resultset is non-binary for string type. This should be changed +--echo -- after Bug#29394 is implemented. + +--enable_metadata optimize table t1; +--disable_metadata optimize table t1; drop table t1; #show variables; + +--echo -- Here we enable metadata just to check that the collation of the +--echo -- resultset is non-binary for string type. This should be changed +--echo -- after Bug#29394 is implemented. + +--enable_metadata show variables like "wait_timeout%"; show variables like "WAIT_timeout%"; show variables like "this_doesn't_exists%"; show table status from test like "this_doesn't_exists%"; show databases; show databases like "test%"; +--disable_metadata # # Check of show index # create table t1 (f1 int not null, f2 int not null, f3 int not null, f4 int not null, primary key(f1,f2,f3,f4)); insert into t1 values (1,1,1,0),(1,1,2,0),(1,1,3,0),(1,2,1,0),(1,2,2,0),(1,2,3,0),(1,3,1,0),(1,3,2,0),(1,3,3,0),(1,1,1,1),(1,1,2,1),(1,1,3,1),(1,2,1,1),(1,2,2,1),(1,2,3,1),(1,3,1,1),(1,3,2,1),(1,3,3,1); + +--echo -- Here we enable metadata just to check that the collation of the +--echo -- resultset is non-binary for string type. This should be changed +--echo -- after Bug#29394 is implemented. + +--enable_metadata analyze table t1; +--disable_metadata show index from t1; + +--echo -- Here we enable metadata just to check that the collation of the +--echo -- resultset is non-binary for string type. This should be changed +--echo -- after Bug#29394 is implemented. + +--enable_metadata + repair table t1; +--disable_metadata show index from t1; drop table t1; @@ -518,4 +554,216 @@ show status like 'slow_queries'; select 1 from information_schema.tables limit 1; show status like 'slow_queries'; +# +# BUG#10491: Server returns data as charset binary SHOW CREATE TABLE or SELECT +# FROM I_S. +# + +# Ensure that all needed objects are dropped. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# Create objects. + +CREATE DATABASE mysqltest1; + +CREATE TABLE t1(c INT NOT NULL PRIMARY KEY); + +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; + +CREATE VIEW v1 AS SELECT 1; + +CREATE PROCEDURE p1() SELECT 1; + +CREATE FUNCTION f1() RETURNS INT RETURN 1; + + +# Test. + +set names utf8; + +--echo -- Here we enable metadata just to check that the collation of the +--echo -- resultset is non-binary for string type. This should be changed +--echo -- after Bug#29394 is implemented. + +--enable_metadata + +--echo ---------------------------------------------------------------- + +SHOW CHARACTER SET LIKE 'utf8'; + +--echo ---------------------------------------------------------------- + +SHOW COLLATION LIKE 'latin1_bin'; + +--echo ---------------------------------------------------------------- + +SHOW CREATE DATABASE mysqltest1; + +--echo ---------------------------------------------------------------- + +SHOW DATABASES LIKE 'mysqltest1'; + +--echo ---------------------------------------------------------------- + +SHOW CREATE TABLE t1; + +--echo ---------------------------------------------------------------- + +SHOW INDEX FROM t1; + +--echo ---------------------------------------------------------------- + +SELECT + TABLE_CATALOG, + TABLE_SCHEMA, + TABLE_NAME, + TABLE_TYPE, + ENGINE, + ROW_FORMAT, + TABLE_COLLATION, + CREATE_OPTIONS, + TABLE_COMMENT +FROM INFORMATION_SCHEMA.TABLES +WHERE table_name = 't1'; + +--echo ---------------------------------------------------------------- + +SELECT + TABLE_CATALOG, + TABLE_SCHEMA, + TABLE_NAME, + COLUMN_NAME, + COLUMN_DEFAULT, + IS_NULLABLE, + DATA_TYPE, + CHARACTER_SET_NAME, + COLLATION_NAME, + COLUMN_TYPE, + COLUMN_KEY, + EXTRA, + PRIVILEGES, + COLUMN_COMMENT +FROM INFORMATION_SCHEMA.COLUMNS +WHERE table_name = 't1'; + +--echo ---------------------------------------------------------------- + +SHOW TABLES LIKE 't1'; + +--echo ---------------------------------------------------------------- + +SHOW COLUMNS FROM t1; + +--echo ---------------------------------------------------------------- + +SHOW TRIGGERS LIKE 't1'; + +--echo ---------------------------------------------------------------- + +SELECT + TRIGGER_CATALOG, + TRIGGER_SCHEMA, + TRIGGER_NAME, + EVENT_MANIPULATION, + EVENT_OBJECT_CATALOG, + EVENT_OBJECT_SCHEMA, + EVENT_OBJECT_TABLE, + ACTION_CONDITION, + ACTION_STATEMENT, + ACTION_ORIENTATION, + ACTION_TIMING, + ACTION_REFERENCE_OLD_TABLE, + ACTION_REFERENCE_NEW_TABLE, + ACTION_REFERENCE_OLD_ROW, + ACTION_REFERENCE_NEW_ROW, + SQL_MODE, + DEFINER +FROM INFORMATION_SCHEMA.TRIGGERS +WHERE trigger_name = 't1_bi'; + +--echo ---------------------------------------------------------------- + +SHOW CREATE VIEW v1; + +--echo ---------------------------------------------------------------- + +SELECT * +FROM INFORMATION_SCHEMA.VIEWS +WHERE table_name = 'v1'; + +--echo ---------------------------------------------------------------- + +SHOW CREATE PROCEDURE p1; + +--echo ---------------------------------------------------------------- + +SELECT + SPECIFIC_NAME, + ROUTINE_CATALOG, + ROUTINE_SCHEMA, + ROUTINE_NAME, + ROUTINE_TYPE, + DTD_IDENTIFIER, + ROUTINE_BODY, + ROUTINE_DEFINITION, + EXTERNAL_NAME, + EXTERNAL_LANGUAGE, + PARAMETER_STYLE, + IS_DETERMINISTIC, + SQL_DATA_ACCESS, + SQL_PATH, + SECURITY_TYPE, + SQL_MODE, + ROUTINE_COMMENT, + DEFINER +FROM INFORMATION_SCHEMA.ROUTINES +WHERE routine_name = 'p1'; + +--echo ---------------------------------------------------------------- + +SHOW CREATE FUNCTION f1; + +--echo ---------------------------------------------------------------- + +SELECT + SPECIFIC_NAME, + ROUTINE_CATALOG, + ROUTINE_SCHEMA, + ROUTINE_NAME, + ROUTINE_TYPE, + DTD_IDENTIFIER, + ROUTINE_BODY, + ROUTINE_DEFINITION, + EXTERNAL_NAME, + EXTERNAL_LANGUAGE, + PARAMETER_STYLE, + IS_DETERMINISTIC, + SQL_DATA_ACCESS, + SQL_PATH, + SECURITY_TYPE, + SQL_MODE, + ROUTINE_COMMENT, + DEFINER +FROM INFORMATION_SCHEMA.ROUTINES +WHERE routine_name = 'f1'; + +--echo ---------------------------------------------------------------- + +--disable_metadata + +# Cleanup. + +DROP DATABASE mysqltest1; +DROP TABLE t1; +DROP VIEW v1; +DROP PROCEDURE p1; +DROP FUNCTION f1; + --echo End of 5.0 tests diff --git a/mysql-test/t/sp-dynamic.test b/mysql-test/t/sp-dynamic.test index 6546a5ab548..e6f4aae96ac 100644 --- a/mysql-test/t/sp-dynamic.test +++ b/mysql-test/t/sp-dynamic.test @@ -85,7 +85,7 @@ call p1()| call p1()| drop procedure p1| # -# D. Create/Drop a table (a DDL that issues a commit) in Dynamic SQL. +# D. Create/Drop/Alter a table (a DDL that issues a commit) in Dynamic SQL. # (should work ok). # create procedure p1() @@ -96,6 +96,10 @@ begin execute stmt; insert into t1 (a) values (1); select * from t1; + prepare stmt_alter from "alter table t1 add (b int)"; + execute stmt_alter; + insert into t1 (a,b) values (2,1); + deallocate prepare stmt_alter; deallocate prepare stmt; deallocate prepare stmt_drop; end| @@ -239,6 +243,7 @@ drop procedure p1| # K. Use of continue handlers with Dynamic SQL. # drop table if exists t1| +drop table if exists t2| create table t1 (id integer primary key auto_increment, stmt_text char(35), status varchar(20))| insert into t1 (stmt_text) values @@ -249,7 +254,10 @@ insert into t1 (stmt_text) values ("help help"), ("show databases"), ("show tables"), ("show table status"), ("show open tables"), ("show storage engines"), ("insert into t1 (id) values (1)"), ("update t1 set status=''"), - ("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar")| + ("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar"), + ("create view v1 as select 1"), ("alter view v1 as select 2"), + ("drop view v1"),("create table t2 (a int)"),("alter table t2 add (b int)"), + ("drop table t2")| create procedure p1() begin declare v_stmt_text varchar(255); diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index ec91be13ba0..240cda67edc 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1087,12 +1087,12 @@ delimiter ;| # # BUG 12490 (Packets out of order if calling HELP CONTENTS from Stored Procedure) # ---error 1314 +--error ER_SP_BADSTATEMENT CREATE PROCEDURE BUG_12490() HELP CONTENTS; ---error 1314 +--error ER_SP_BADSTATEMENT CREATE FUNCTION BUG_12490() RETURNS INT HELP CONTENTS; CREATE TABLE t_bug_12490(a int); ---error 1314 +--error ER_SP_BADSTATEMENT CREATE TRIGGER BUG_12490 BEFORE UPDATE ON t_bug_12490 FOR EACH ROW HELP CONTENTS; DROP TABLE t_bug_12490; @@ -1397,9 +1397,9 @@ CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as sele -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END | --- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +-- error ER_SP_BADSTATEMENT CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END | --- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +-- error ER_SP_BADSTATEMENT CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG |