|
1
by brian
clean slate |
1 |
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
|
2 |
select * from information_schema.SCHEMATA where schema_name > 'm';
|
|
3 |
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
|
|
|
383.1.16
by Brian Aker
Force client communication into UTF8 |
4 |
NULL mysql utf8 utf8_general_ci NULL
|
5 |
NULL test utf8 utf8_general_ci NULL
|
|
|
1
by brian
clean slate |
6 |
select schema_name from information_schema.schemata;
|
7 |
schema_name
|
|
8 |
information_schema
|
|
9 |
mysql
|
|
10 |
test
|
|
11 |
show databases like 't%';
|
|
12 |
Database (t%)
|
|
13 |
test
|
|
14 |
show databases;
|
|
15 |
Database
|
|
16 |
information_schema
|
|
17 |
mysql
|
|
18 |
test
|
|
19 |
create database mysqltest;
|
|
20 |
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
|
|
21 |
create table test.t2(a int);
|
|
22 |
create table t3(a int, KEY a_data (a));
|
|
23 |
create table mysqltest.t4(a int);
|
|
24 |
create table t5 (id int auto_increment primary key);
|
|
25 |
insert into t5 values (10);
|
|
26 |
select table_name from information_schema.TABLES
|
|
27 |
where table_schema = "mysqltest" and table_name like "t%";
|
|
28 |
table_name
|
|
29 |
t1
|
|
30 |
t4
|
|
31 |
select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
|
|
32 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT
|
|
|
201
by Brian Aker
Convert default engine to Innodb |
33 |
NULL mysqltest t1 1 mysqltest string_data 1 b A 0 NULL NULL YES BTREE
|
|
1
by brian
clean slate |
34 |
show tables like 't%';
|
35 |
Tables_in_test (t%)
|
|
36 |
t2
|
|
37 |
t3
|
|
38 |
t5
|
|
39 |
show full columns from t3 like "a%";
|
|
40 |
Field Type Collation Null Key Default Extra Privileges Comment
|
|
|
520.4.13
by Monty Taylor
Cleaned up two remaining test cases. |
41 |
a int NULL YES MUL NULL #
|
|
1
by brian
clean slate |
42 |
select * from information_schema.COLUMNS where table_name="t1"
|
43 |
and column_name= "a";
|
|
44 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT STORAGE FORMAT
|
|
|
221
by Brian Aker
First pass of removing length types for ints. |
45 |
NULL mysqltest t1 a 1 NULL YES int NULL NULL 10 0 NULL NULL int Default Default
|
|
1
by brian
clean slate |
46 |
select table_name, column_name, privileges from information_schema.columns
|
47 |
where table_schema = 'mysqltest' and table_name = 't1';
|
|
48 |
table_name column_name privileges
|
|
49 |
t1 a
|
|
50 |
t1 b
|
|
51 |
show columns from mysqltest.t1;
|
|
52 |
Field Type Null Key Default Extra
|
|
|
221
by Brian Aker
First pass of removing length types for ints. |
53 |
a int YES NULL
|
|
1
by brian
clean slate |
54 |
b varchar(30) YES MUL NULL
|
55 |
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
|
|
56 |
drop database mysqltest;
|
|
57 |
select * from information_schema.CHARACTER_SETS
|
|
58 |
where CHARACTER_SET_NAME like 'latin1%';
|
|
59 |
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN
|
|
60 |
select * from information_schema.COLLATIONS
|
|
61 |
where COLLATION_NAME like 'latin1%';
|
|
62 |
COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN
|
|
63 |
select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
|
|
64 |
where COLLATION_NAME like 'latin1%';
|
|
65 |
COLLATION_NAME CHARACTER_SET_NAME
|
|
66 |
select * from information_schema.table_names;
|
|
67 |
ERROR 42S02: Unknown table 'table_names' in information_schema
|
|
68 |
select column_type from information_schema.columns
|
|
69 |
where table_schema="information_schema" and table_name="COLUMNS" and
|
|
70 |
(column_name="character_set_name" or column_name="collation_name");
|
|
71 |
column_type
|
|
72 |
varchar(64)
|
|
73 |
varchar(64)
|
|
74 |
select TABLE_ROWS from information_schema.tables where
|
|
75 |
table_schema="information_schema" and table_name="COLUMNS";
|
|
76 |
TABLE_ROWS
|
|
77 |
NULL
|
|
78 |
select table_type from information_schema.tables
|
|
79 |
where table_schema="mysql" and table_name="user";
|
|
80 |
table_type
|
|
81 |
show global status like "Threads_running";
|
|
82 |
Variable_name Value
|
|
83 |
Threads_running #
|
|
84 |
select table_schema,table_name, column_name from
|
|
85 |
information_schema.columns
|
|
86 |
where data_type = 'longtext';
|
|
87 |
table_schema table_name column_name
|
|
88 |
select table_name, column_name, data_type from information_schema.columns
|
|
89 |
where data_type = 'datetime';
|
|
90 |
table_name column_name data_type
|
|
91 |
TABLES CREATE_TIME datetime
|
|
92 |
TABLES UPDATE_TIME datetime
|
|
93 |
TABLES CHECK_TIME datetime
|
|
|
520.4.1
by Monty Taylor
Imported InnoDB plugin with changes. |
94 |
INNODB_TRX trx_started datetime
|
95 |
INNODB_TRX trx_wait_started datetime
|
|
|
1
by brian
clean slate |
96 |
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
|
97 |
WHERE NOT EXISTS
|
|
98 |
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
|
|
99 |
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
|
|
100 |
AND A.TABLE_NAME = B.TABLE_NAME);
|
|
101 |
COUNT(*)
|
|
102 |
0
|
|
103 |
create table t1
|
|
104 |
( x_bigint BIGINT,
|
|
105 |
x_integer INTEGER,
|
|
|
396
by Brian Aker
Cleanup tiny and small int. |
106 |
x_int int,
|
|
1
by brian
clean slate |
107 |
x_decimal DECIMAL(5,3),
|
108 |
x_numeric NUMERIC(5,3),
|
|
109 |
x_real REAL,
|
|
110 |
x_float FLOAT,
|
|
111 |
x_double_precision DOUBLE PRECISION );
|
|
112 |
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
|
|
113 |
FROM INFORMATION_SCHEMA.COLUMNS
|
|
114 |
WHERE TABLE_NAME= 't1';
|
|
115 |
COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
|
|
116 |
x_bigint NULL NULL
|
|
117 |
x_integer NULL NULL
|
|
|
396
by Brian Aker
Cleanup tiny and small int. |
118 |
x_int NULL NULL
|
|
1
by brian
clean slate |
119 |
x_decimal NULL NULL
|
120 |
x_numeric NULL NULL
|
|
121 |
x_real NULL NULL
|
|
122 |
x_float NULL NULL
|
|
123 |
x_double_precision NULL NULL
|
|
124 |
drop table t1;
|
|
125 |
SELECT table_schema, count(*) FROM information_schema.TABLES
|
|
126 |
WHERE table_name NOT LIKE 'ndb_%' AND table_name NOT LIKE 'falcon%' GROUP BY TABLE_SCHEMA;
|
|
127 |
table_schema count(*)
|
|
|
520.4.1
by Monty Taylor
Imported InnoDB plugin with changes. |
128 |
information_schema 23
|
|
1
by brian
clean slate |
129 |
show create database information_schema;
|
130 |
Database Create Database
|
|
|
352.2.1
by Harrison Fisk
Fix for bugs 259843 and 256482 |
131 |
information_schema CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */
|
|
1
by brian
clean slate |
132 |
create table t1(f1 LONGBLOB, f2 LONGTEXT);
|
133 |
select column_name,data_type,CHARACTER_OCTET_LENGTH,
|
|
134 |
CHARACTER_MAXIMUM_LENGTH
|
|
135 |
from information_schema.columns
|
|
136 |
where table_name='t1';
|
|
137 |
column_name data_type CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH
|
|
|
221
by Brian Aker
First pass of removing length types for ints. |
138 |
f1 blob 4294967295 4294967295
|
139 |
f2 text 4294967295 4294967295
|
|
|
1
by brian
clean slate |
140 |
drop table t1;
|
|
396
by Brian Aker
Cleanup tiny and small int. |
141 |
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
|
142 |
f5 BIGINT, f6 int, f7 int);
|
|
|
1
by brian
clean slate |
143 |
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
|
144 |
from information_schema.columns
|
|
145 |
where table_name='t1';
|
|
146 |
column_name NUMERIC_PRECISION NUMERIC_SCALE
|
|
|
396
by Brian Aker
Cleanup tiny and small int. |
147 |
f1 10 0
|
148 |
f2 10 0
|
|
|
67
by Brian Aker
First pass for removing mediumint (3 byte INT type). |
149 |
f3 19 0
|
|
1
by brian
clean slate |
150 |
f4 10 0
|
151 |
f5 19 0
|
|
|
396
by Brian Aker
Cleanup tiny and small int. |
152 |
f6 10 0
|
153 |
f7 10 0
|
|
|
1
by brian
clean slate |
154 |
drop table t1;
|
155 |
create table t1 (a int not null, b int);
|
|
156 |
use information_schema;
|
|
157 |
select column_name, column_default from columns
|
|
158 |
where table_schema='test' and table_name='t1';
|
|
159 |
column_name column_default
|
|
160 |
a NULL
|
|
161 |
b NULL
|
|
162 |
use test;
|
|
163 |
show columns from t1;
|
|
164 |
Field Type Null Key Default Extra
|
|
|
221
by Brian Aker
First pass of removing length types for ints. |
165 |
a int NO NULL
|
166 |
b int YES NULL
|
|
|
1
by brian
clean slate |
167 |
drop table t1;
|
168 |
alter database information_schema;
|
|
|
629.2.6
by Monty
Updated test output with new and improved error messages. |
169 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
|
|
1
by brian
clean slate |
170 |
drop database information_schema;
|
171 |
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
|
|
172 |
drop table information_schema.tables;
|
|
173 |
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
|
|
174 |
alter table information_schema.tables;
|
|
175 |
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
|
|
176 |
use information_schema;
|
|
177 |
create temporary table schemata(f1 char(10));
|
|
178 |
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
|
|
179 |
use test;
|
|
180 |
create table t1(id int);
|
|
181 |
insert into t1(id) values (1);
|
|
182 |
select 1 from (select 1 from test.t1) a;
|
|
183 |
1
|
|
184 |
1
|
|
185 |
use information_schema;
|
|
186 |
select 1 from (select 1 from test.t1) a;
|
|
187 |
1
|
|
188 |
1
|
|
189 |
use test;
|
|
190 |
drop table t1;
|
|
191 |
create temporary table t1(f1 int, index(f1));
|
|
192 |
show columns from t1;
|
|
193 |
Field Type Null Key Default Extra
|
|
|
221
by Brian Aker
First pass of removing length types for ints. |
194 |
f1 int YES MUL NULL
|
|
1
by brian
clean slate |
195 |
describe t1;
|
196 |
Field Type Null Key Default Extra
|
|
|
221
by Brian Aker
First pass of removing length types for ints. |
197 |
f1 int YES MUL NULL
|
|
1
by brian
clean slate |
198 |
show indexes from t1;
|
199 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
|
|
|
201
by Brian Aker
Convert default engine to Innodb |
200 |
t1 1 f1 1 f1 A 0 NULL NULL YES BTREE
|
|
1
by brian
clean slate |
201 |
drop table t1;
|
|
233
by Brian Aker
Fix to remove binary/nchar |
202 |
create table t1(f1 varbinary(32), f2 varbinary(64));
|
|
1
by brian
clean slate |
203 |
select character_maximum_length, character_octet_length
|
204 |
from information_schema.columns where table_name='t1';
|
|
205 |
character_maximum_length character_octet_length
|
|
206 |
32 32
|
|
207 |
64 64
|
|
208 |
drop table t1;
|
|
209 |
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
|
|
210 |
from information_schema.columns where
|
|
211 |
table_schema='information_schema' and
|
|
212 |
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
|
|
213 |
or column_type = 'varchar(27)')
|
|
214 |
group by column_type order by num;
|
|
215 |
column_type group_concat(table_schema, '.', table_name) num
|
|
|
177.4.6
by Mark Atwood
updated test result for information_schema because of changes in SHOW PLUGINS |
216 |
varchar(20) information_schema.PLUGINS 1
|
|
1
by brian
clean slate |
217 |
varchar(27) information_schema.COLUMNS 1
|
|
377.1.4
by Brian Aker
Big, fat, UTF-8 patch. This fixes some of the oddities around only one |
218 |
create table t1(f1 char(1) not null, f2 char(9) not null);
|
|
1
by brian
clean slate |
219 |
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
|
220 |
information_schema.columns where table_schema='test' and table_name = 't1';
|
|
221 |
CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
|
|
|
383.1.16
by Brian Aker
Force client communication into UTF8 |
222 |
1 4
|
223 |
9 36
|
|
|
1
by brian
clean slate |
224 |
drop table t1;
|
225 |
set @a:= '.';
|
|
226 |
create table t1(f1 char(5));
|
|
227 |
create table t2(f1 char(5));
|
|
228 |
select concat(@a, table_name), @a, table_name
|
|
229 |
from information_schema.tables where table_schema = 'test';
|
|
230 |
concat(@a, table_name) @a table_name
|
|
231 |
.t1 . t1
|
|
232 |
.t2 . t2
|
|
233 |
drop table t1,t2;
|
|
234 |
SELECT t.table_name, c1.column_name
|
|
235 |
FROM information_schema.tables t
|
|
236 |
INNER JOIN
|
|
237 |
information_schema.columns c1
|
|
238 |
ON t.table_schema = c1.table_schema AND
|
|
239 |
t.table_name = c1.table_name
|
|
240 |
WHERE t.table_schema = 'information_schema' AND
|
|
241 |
c1.ordinal_position =
|
|
242 |
( SELECT COALESCE(MIN(c2.ordinal_position),1)
|
|
243 |
FROM information_schema.columns c2
|
|
244 |
WHERE c2.table_schema = t.table_schema AND
|
|
245 |
c2.table_name = t.table_name AND
|
|
246 |
c2.column_name LIKE '%SCHEMA%'
|
|
247 |
)
|
|
248 |
AND t.table_name NOT LIKE 'falcon%';
|
|
249 |
table_name column_name
|
|
250 |
CHARACTER_SETS CHARACTER_SET_NAME
|
|
251 |
COLLATIONS COLLATION_NAME
|
|
252 |
COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME
|
|
253 |
COLUMNS TABLE_SCHEMA
|
|
254 |
GLOBAL_STATUS VARIABLE_NAME
|
|
255 |
GLOBAL_VARIABLES VARIABLE_NAME
|
|
256 |
KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
|
|
257 |
PLUGINS PLUGIN_NAME
|
|
258 |
PROCESSLIST ID
|
|
259 |
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
|
|
260 |
SCHEMATA SCHEMA_NAME
|
|
261 |
SESSION_STATUS VARIABLE_NAME
|
|
262 |
SESSION_VARIABLES VARIABLE_NAME
|
|
263 |
STATISTICS TABLE_SCHEMA
|
|
264 |
TABLES TABLE_SCHEMA
|
|
265 |
TABLE_CONSTRAINTS CONSTRAINT_SCHEMA
|
|
|
520.4.1
by Monty Taylor
Imported InnoDB plugin with changes. |
266 |
INNODB_CMP_RESET page_size
|
267 |
INNODB_TRX trx_id
|
|
268 |
INNODB_CMPMEM_RESET page_size
|
|
269 |
INNODB_LOCK_WAITS requesting_trx_id
|
|
270 |
INNODB_CMPMEM page_size
|
|
271 |
INNODB_CMP page_size
|
|
272 |
INNODB_LOCKS lock_id
|
|
|
1
by brian
clean slate |
273 |
SELECT t.table_name, c1.column_name
|
274 |
FROM information_schema.tables t
|
|
275 |
INNER JOIN
|
|
276 |
information_schema.columns c1
|
|
277 |
ON t.table_schema = c1.table_schema AND
|
|
278 |
t.table_name = c1.table_name
|
|
279 |
WHERE t.table_schema = 'information_schema' AND
|
|
280 |
c1.ordinal_position =
|
|
281 |
( SELECT COALESCE(MIN(c2.ordinal_position),1)
|
|
282 |
FROM information_schema.columns c2
|
|
283 |
WHERE c2.table_schema = 'information_schema' AND
|
|
284 |
c2.table_name = t.table_name AND
|
|
285 |
c2.column_name LIKE '%SCHEMA%'
|
|
286 |
)
|
|
287 |
AND t.table_name NOT LIKE 'falcon%';
|
|
288 |
table_name column_name
|
|
289 |
CHARACTER_SETS CHARACTER_SET_NAME
|
|
290 |
COLLATIONS COLLATION_NAME
|
|
291 |
COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME
|
|
292 |
COLUMNS TABLE_SCHEMA
|
|
293 |
GLOBAL_STATUS VARIABLE_NAME
|
|
294 |
GLOBAL_VARIABLES VARIABLE_NAME
|
|
295 |
KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
|
|
296 |
PLUGINS PLUGIN_NAME
|
|
297 |
PROCESSLIST ID
|
|
298 |
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
|
|
299 |
SCHEMATA SCHEMA_NAME
|
|
300 |
SESSION_STATUS VARIABLE_NAME
|
|
301 |
SESSION_VARIABLES VARIABLE_NAME
|
|
302 |
STATISTICS TABLE_SCHEMA
|
|
303 |
TABLES TABLE_SCHEMA
|
|
304 |
TABLE_CONSTRAINTS CONSTRAINT_SCHEMA
|
|
|
520.4.1
by Monty Taylor
Imported InnoDB plugin with changes. |
305 |
INNODB_CMP_RESET page_size
|
306 |
INNODB_TRX trx_id
|
|
307 |
INNODB_CMPMEM_RESET page_size
|
|
308 |
INNODB_LOCK_WAITS requesting_trx_id
|
|
309 |
INNODB_CMPMEM page_size
|
|
310 |
INNODB_CMP page_size
|
|
311 |
INNODB_LOCKS lock_id
|
|
|
1
by brian
clean slate |
312 |
SELECT MAX(table_name) FROM information_schema.tables;
|
313 |
MAX(table_name)
|
|
|
82
by Brian Aker
Clean up install, we no longer have system tables. |
314 |
TABLE_CONSTRAINTS
|
|
1
by brian
clean slate |
315 |
SELECT table_name from information_schema.tables
|
316 |
WHERE table_name=(SELECT MAX(table_name)
|
|
317 |
FROM information_schema.tables);
|
|
318 |
table_name
|
|
|
82
by Brian Aker
Clean up install, we no longer have system tables. |
319 |
TABLE_CONSTRAINTS
|
|
223
by Brian Aker
Cleanup int() work. |
320 |
create table t1 (f1 int);
|
321 |
create table t2 (f1 int, f2 int);
|
|
|
1
by brian
clean slate |
322 |
select table_name from information_schema.tables
|
323 |
where table_schema = 'test' and table_name not in
|
|
324 |
(select table_name from information_schema.columns
|
|
325 |
where table_schema = 'test' and column_name = 'f3');
|
|
326 |
table_name
|
|
327 |
t1
|
|
328 |
t2
|
|
329 |
drop table t1,t2;
|
|
330 |
select 1 as f1 from information_schema.tables where "CHARACTER_SETS"=
|
|
331 |
(select cast(table_name as char) from information_schema.tables
|
|
332 |
order by table_name limit 1) limit 1;
|
|
333 |
f1
|
|
334 |
1
|
|
335 |
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
|
|
336 |
count(*) as num1
|
|
337 |
from information_schema.tables t
|
|
338 |
inner join information_schema.columns c1
|
|
339 |
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
|
|
340 |
where t.table_schema = 'information_schema' AND
|
|
341 |
t.table_name not like 'falcon%' AND
|
|
342 |
c1.ordinal_position =
|
|
343 |
(select isnull(c2.column_type) -
|
|
344 |
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
|
|
345 |
count(*) as num
|
|
346 |
from information_schema.columns c2 where
|
|
347 |
c2.table_schema='information_schema' and
|
|
348 |
(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
|
|
349 |
group by c2.column_type order by num limit 1)
|
|
350 |
group by t.table_name order by num1, t.table_name;
|
|
351 |
table_name group_concat(t.table_schema, '.', t.table_name) num1
|
|
352 |
CHARACTER_SETS information_schema.CHARACTER_SETS 1
|
|
353 |
COLLATIONS information_schema.COLLATIONS 1
|
|
|
177.4.6
by Mark Atwood
updated test result for information_schema because of changes in SHOW PLUGINS |
354 |
COLLATION_CHARACTER_SET_APPLICABILITY information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 1
|
|
1
by brian
clean slate |
355 |
COLUMNS information_schema.COLUMNS 1
|
|
177.4.6
by Mark Atwood
updated test result for information_schema because of changes in SHOW PLUGINS |
356 |
GLOBAL_STATUS information_schema.GLOBAL_STATUS 1
|
357 |
GLOBAL_VARIABLES information_schema.GLOBAL_VARIABLES 1
|
|
|
520.4.1
by Monty Taylor
Imported InnoDB plugin with changes. |
358 |
INNODB_CMP information_schema.INNODB_CMP 1
|
359 |
INNODB_CMPMEM information_schema.INNODB_CMPMEM 1
|
|
360 |
INNODB_CMPMEM_RESET information_schema.INNODB_CMPMEM_RESET 1
|
|
361 |
INNODB_CMP_RESET information_schema.INNODB_CMP_RESET 1
|
|
362 |
INNODB_LOCKS information_schema.INNODB_LOCKS 1
|
|
363 |
INNODB_LOCK_WAITS information_schema.INNODB_LOCK_WAITS 1
|
|
364 |
INNODB_TRX information_schema.INNODB_TRX 1
|
|
|
1
by brian
clean slate |
365 |
KEY_COLUMN_USAGE information_schema.KEY_COLUMN_USAGE 1
|
366 |
PLUGINS information_schema.PLUGINS 1
|
|
367 |
PROCESSLIST information_schema.PROCESSLIST 1
|
|
368 |
REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS 1
|
|
369 |
SCHEMATA information_schema.SCHEMATA 1
|
|
|
177.4.6
by Mark Atwood
updated test result for information_schema because of changes in SHOW PLUGINS |
370 |
SESSION_STATUS information_schema.SESSION_STATUS 1
|
371 |
SESSION_VARIABLES information_schema.SESSION_VARIABLES 1
|
|
|
1
by brian
clean slate |
372 |
STATISTICS information_schema.STATISTICS 1
|
373 |
TABLES information_schema.TABLES 1
|
|
374 |
TABLE_CONSTRAINTS information_schema.TABLE_CONSTRAINTS 1
|
|
375 |
alter database;
|
|
|
629.2.6
by Monty
Updated test output with new and improved error messages. |
376 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
|
|
1
by brian
clean slate |
377 |
alter database test;
|
|
629.2.6
by Monty
Updated test output with new and improved error messages. |
378 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
|
|
1
by brian
clean slate |
379 |
create table t1 (
|
380 |
f1 varchar(50),
|
|
381 |
f2 varchar(50) not null,
|
|
382 |
f3 varchar(50) default '',
|
|
383 |
f4 varchar(50) default NULL,
|
|
384 |
f5 bigint not null,
|
|
385 |
f6 bigint not null default 10,
|
|
386 |
f7 datetime not null,
|
|
387 |
f8 datetime default '2006-01-01'
|
|
388 |
);
|
|
389 |
select column_default from information_schema.columns where table_name= 't1';
|
|
390 |
column_default
|
|
391 |
NULL
|
|
392 |
NULL
|
|
393 |
NULL
|
|
394 |
NULL
|
|
395 |
NULL
|
|
396 |
NULL
|
|
397 |
NULL
|
|
398 |
NULL
|
|
399 |
show columns from t1;
|
|
400 |
Field Type Null Key Default Extra
|
|
401 |
f1 varchar(50) YES NULL
|
|
402 |
f2 varchar(50) NO NULL
|
|
403 |
f3 varchar(50) YES NULL
|
|
404 |
f4 varchar(50) YES NULL
|
|
|
221
by Brian Aker
First pass of removing length types for ints. |
405 |
f5 bigint NO NULL
|
406 |
f6 bigint NO NULL
|
|
|
1
by brian
clean slate |
407 |
f7 datetime NO NULL
|
408 |
f8 datetime YES NULL
|
|
409 |
drop table t1;
|
|
410 |
show fields from information_schema.table_names;
|
|
411 |
ERROR 42S02: Unknown table 'table_names' in information_schema
|
|
412 |
show keys from information_schema.table_names;
|
|
413 |
ERROR 42S02: Unknown table 'table_names' in information_schema
|
|
414 |
SET max_heap_table_size = DEFAULT;
|
|
415 |
USE test;
|
|
416 |
End of 5.0 tests.
|
|
417 |
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
|
|
418 |
WHERE SCHEMA_NAME ='information_schema';
|
|
419 |
SCHEMA_NAME
|
|
420 |
information_schema
|
|
421 |
SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
|
|
422 |
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
|
|
423 |
TABLE_COLLATION
|
|
424 |
select * from information_schema.columns where table_schema = NULL;
|
|
425 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT STORAGE FORMAT
|
|
426 |
select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;
|
|
427 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT STORAGE FORMAT
|
|
428 |
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
|
|
429 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
|
|
430 |
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
|
|
431 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
|
|
432 |
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
|
|
433 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
|
|
434 |
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
|
|
435 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
|
|
436 |
select * from information_schema.schemata where schema_name = NULL;
|
|
437 |
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
|
|
438 |
select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
|
|
439 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT
|
|
440 |
select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;
|
|
441 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT
|
|
442 |
select * from information_schema.tables where table_schema = NULL;
|
|
443 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
|
|
444 |
select * from information_schema.tables where table_catalog = NULL;
|
|
445 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
|
|
446 |
select * from information_schema.tables where table_name = NULL;
|
|
447 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
|
|
448 |
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
|
|
449 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
|
|
450 |
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
|
|
451 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
|