Debian Codegraph @

mysql-test/suite/funcs_1/r/is_tables_memory.result

raw
1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2 DROP DATABASE IF EXISTS test1;
3 DROP DATABASE IF EXISTS test2;
4 CREATE DATABASE test1 charset latin1;
5 CREATE DATABASE test2 charset latin1;
6 CREATE TABLE test1.t1 (f1 VARCHAR(20)) ENGINE = <engine_to_be_used>;
7 CREATE TABLE test1.t2 (f1 VARCHAR(20)) ENGINE = <engine_to_be_used>;
8 CREATE TABLE test2.t1 (f1 VARCHAR(20)) ENGINE = <engine_to_be_used>;
9 SELECT *,
10 LEFT( table_comment,
11 IF(INSTR(table_comment,'TMP_TABLE_ENGINE free') = 0
12 AND INSTR(table_comment,'number_of_replicas') = 0,
13 LENGTH(table_comment),
14 INSTR(table_comment,'TMP_TABLE_ENGINE free')
15 + INSTR(table_comment,'number_of_replicas') - 1))
16 AS "user_comment",
17 '-----------------------------------------------------' AS "Separator"
18 FROM information_schema.tables
19 WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'
20 ORDER BY table_schema,table_name COLLATE utf8_general_ci;
21 TABLE_CATALOG def
22 TABLE_SCHEMA test1
23 TABLE_NAME t1
24 TABLE_TYPE BASE TABLE
25 ENGINE MEMORY
26 VERSION 10
27 ROW_FORMAT Fixed
28 TABLE_ROWS #TBLR#
29 AVG_ROW_LENGTH #ARL#
30 DATA_LENGTH #DL#
31 MAX_DATA_LENGTH #MDL#
32 INDEX_LENGTH #IL#
33 DATA_FREE #DF#
34 AUTO_INCREMENT #AI#
35 CREATE_TIME #CRT#
36 UPDATE_TIME #UT#
37 CHECK_TIME #CT#
38 TABLE_COLLATION latin1_swedish_ci
39 CHECKSUM NULL
40 CREATE_OPTIONS #CO#
41 TABLE_COMMENT #TC#
42 user_comment
43 Separator -----------------------------------------------------
44 TABLE_CATALOG def
45 TABLE_SCHEMA test1
46 TABLE_NAME t2
47 TABLE_TYPE BASE TABLE
48 ENGINE MEMORY
49 VERSION 10
50 ROW_FORMAT Fixed
51 TABLE_ROWS #TBLR#
52 AVG_ROW_LENGTH #ARL#
53 DATA_LENGTH #DL#
54 MAX_DATA_LENGTH #MDL#
55 INDEX_LENGTH #IL#
56 DATA_FREE #DF#
57 AUTO_INCREMENT #AI#
58 CREATE_TIME #CRT#
59 UPDATE_TIME #UT#
60 CHECK_TIME #CT#
61 TABLE_COLLATION latin1_swedish_ci
62 CHECKSUM NULL
63 CREATE_OPTIONS #CO#
64 TABLE_COMMENT #TC#
65 user_comment
66 Separator -----------------------------------------------------
67 TABLE_CATALOG def
68 TABLE_SCHEMA test2
69 TABLE_NAME t1
70 TABLE_TYPE BASE TABLE
71 ENGINE MEMORY
72 VERSION 10
73 ROW_FORMAT Fixed
74 TABLE_ROWS #TBLR#
75 AVG_ROW_LENGTH #ARL#
76 DATA_LENGTH #DL#
77 MAX_DATA_LENGTH #MDL#
78 INDEX_LENGTH #IL#
79 DATA_FREE #DF#
80 AUTO_INCREMENT #AI#
81 CREATE_TIME #CRT#
82 UPDATE_TIME #UT#
83 CHECK_TIME #CT#
84 TABLE_COLLATION latin1_swedish_ci
85 CHECKSUM NULL
86 CREATE_OPTIONS #CO#
87 TABLE_COMMENT #TC#
88 user_comment
89 Separator -----------------------------------------------------
90 SHOW TABLES FROM test1;
91 Tables_in_test1
92 t1
93 t2
94 SHOW TABLES FROM test2;
95 Tables_in_test2
96 t1
97 DROP USER testuser1@localhost;
98 CREATE USER testuser1@localhost;
99 GRANT SELECT ON test1.* TO testuser1@localhost;
100 # Establish connection testuser1 (user=testuser1)
101 SELECT *,
102 LEFT( table_comment,
103 IF(INSTR(table_comment,'TMP_TABLE_ENGINE free') = 0
104 AND INSTR(table_comment,'number_of_replicas') = 0,
105 LENGTH(table_comment),
106 INSTR(table_comment,'TMP_TABLE_ENGINE free')
107 + INSTR(table_comment,'number_of_replicas') - 1))
108 AS "user_comment",
109 '-----------------------------------------------------' AS "Separator"
110 FROM information_schema.tables
111 WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'
112 ORDER BY table_schema,table_name COLLATE utf8_general_ci;
113 TABLE_CATALOG def
114 TABLE_SCHEMA test1
115 TABLE_NAME t1
116 TABLE_TYPE BASE TABLE
117 ENGINE MEMORY
118 VERSION 10
119 ROW_FORMAT Fixed
120 TABLE_ROWS #TBLR#
121 AVG_ROW_LENGTH #ARL#
122 DATA_LENGTH #DL#
123 MAX_DATA_LENGTH #MDL#
124 INDEX_LENGTH #IL#
125 DATA_FREE #DF#
126 AUTO_INCREMENT #AI#
127 CREATE_TIME #CRT#
128 UPDATE_TIME #UT#
129 CHECK_TIME #CT#
130 TABLE_COLLATION latin1_swedish_ci
131 CHECKSUM NULL
132 CREATE_OPTIONS #CO#
133 TABLE_COMMENT #TC#
134 user_comment
135 Separator -----------------------------------------------------
136 TABLE_CATALOG def
137 TABLE_SCHEMA test1
138 TABLE_NAME t2
139 TABLE_TYPE BASE TABLE
140 ENGINE MEMORY
141 VERSION 10
142 ROW_FORMAT Fixed
143 TABLE_ROWS #TBLR#
144 AVG_ROW_LENGTH #ARL#
145 DATA_LENGTH #DL#
146 MAX_DATA_LENGTH #MDL#
147 INDEX_LENGTH #IL#
148 DATA_FREE #DF#
149 AUTO_INCREMENT #AI#
150 CREATE_TIME #CRT#
151 UPDATE_TIME #UT#
152 CHECK_TIME #CT#
153 TABLE_COLLATION latin1_swedish_ci
154 CHECKSUM NULL
155 CREATE_OPTIONS #CO#
156 TABLE_COMMENT #TC#
157 user_comment
158 Separator -----------------------------------------------------
159 SHOW TABLES FROM test1;
160 Tables_in_test1
161 t1
162 t2
163 SHOW TABLES FROM test2;
164 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'test2'
165 # Switch to connection default and close connection testuser1
166 DROP USER testuser1@localhost;
167 DROP DATABASE test1;
168 DROP DATABASE test2;