summaryrefslogtreecommitdiff
path: root/mysql-test/main/information_schema.result
blob: 0ce5cbea37d72d66333485b33b9a80f599186a0c (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
set global sql_mode="";
set local sql_mode="";
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
DROP VIEW IF EXISTS v1;
show variables where variable_name like "skip_show_database";
Variable_name	Value
skip_show_database	OFF
grant select, update, execute on test.* to mysqltest_2@localhost;
grant select, update on test.* to mysqltest_1@localhost;
create user mysqltest_3@localhost;
create user mysqltest_3;
select * from information_schema.SCHEMATA where schema_name > 'm';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
def	mtr	latin1	latin1_swedish_ci	NULL	
def	mysql	latin1	latin1_swedish_ci	NULL	
def	performance_schema	utf8mb3	utf8mb3_general_ci	NULL	
def	sys	utf8mb3	utf8mb3_general_ci	NULL	
def	test	latin1	latin1_swedish_ci	NULL	
select schema_name from information_schema.schemata;
schema_name
information_schema
mtr
mysql
performance_schema
sys
test
show databases like 't%';
Database (t%)
test
show databases;
Database
information_schema
mtr
mysql
performance_schema
sys
test
show databases where `database` = 't%';
Database
create database mysqltest;
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
create table test.t2(a int);
create table t3(a int, KEY a_data (a));
create table mysqltest.t4(a int);
create table t5 (id int auto_increment primary key);
insert into t5 values (10);
create view v1 (c) as
SELECT table_name FROM information_schema.TABLES
WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND
table_name not like 'innodb_%' AND
table_name not like 'xtradb_%';
select * from v1;
c
ALL_PLUGINS
APPLICABLE_ROLES
CHARACTER_SETS
CHECK_CONSTRAINTS
CLIENT_STATISTICS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
ENABLED_ROLES
ENGINES
EVENTS
FILES
GEOMETRY_COLUMNS
GLOBAL_STATUS
GLOBAL_VARIABLES
INDEX_STATISTICS
KEYWORDS
KEY_CACHES
KEY_COLUMN_USAGE
OPTIMIZER_TRACE
PARAMETERS
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
SPATIAL_REF_SYS
SQL_FUNCTIONS
STATISTICS
SYSTEM_VARIABLES
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLE_STATISTICS
TRIGGERS
USER_PRIVILEGES
USER_STATISTICS
VIEWS
column_stats
columns_priv
db
event
func
general_log
global_priv
gtid_slave_pos
help_category
help_keyword
help_relation
help_topic
index_stats
plugin
proc
procs_priv
proxies_priv
roles_mapping
servers
slow_log
t1
t2
t3
t4
t5
table_stats
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
transaction_registry
user
v1
select c,table_name from v1 
inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
TABLES	TABLES
TABLESPACES	TABLESPACES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_STATISTICS	TABLE_STATISTICS
TRIGGERS	TRIGGERS
t1	t1
t2	t2
t3	t3
t4	t4
t5	t5
table_stats	table_stats
tables_priv	tables_priv
time_zone	time_zone
time_zone_leap_second	time_zone_leap_second
time_zone_name	time_zone_name
time_zone_transition	time_zone_transition
time_zone_transition_type	time_zone_transition_type
transaction_registry	transaction_registry
select c,table_name from v1 
left join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
TABLES	TABLES
TABLESPACES	TABLESPACES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_STATISTICS	TABLE_STATISTICS
TRIGGERS	TRIGGERS
t1	t1
t2	t2
t3	t3
t4	t4
t5	t5
table_stats	table_stats
tables_priv	tables_priv
time_zone	time_zone
time_zone_leap_second	time_zone_leap_second
time_zone_name	time_zone_name
time_zone_transition	time_zone_transition
time_zone_transition_type	time_zone_transition_type
transaction_registry	transaction_registry
select c, v2.table_name from v1
right join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
TABLES	TABLES
TABLESPACES	TABLESPACES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_STATISTICS	TABLE_STATISTICS
TRIGGERS	TRIGGERS
t1	t1
t2	t2
t3	t3
t4	t4
t5	t5
table_stats	table_stats
tables_priv	tables_priv
time_zone	time_zone
time_zone_leap_second	time_zone_leap_second
time_zone_name	time_zone_name
time_zone_transition	time_zone_transition
time_zone_transition_type	time_zone_transition_type
transaction_registry	transaction_registry
select table_name from information_schema.TABLES
where table_schema = "mysqltest" and table_name like "t%";
table_name
t1
t4
select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
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	IGNORED
def	mysqltest	t1	1	mysqltest	string_data	1	b	A	NULL	NULL	NULL	YES	BTREE			NO
show keys from t3 where Key_name = "a_data";
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
t3	1	a_data	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
show tables like 't%';
Tables_in_test (t%)
t2
t3
t5
show table status;
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	Max_index_length	Temporary
t2	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL			#	N
t3	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL			#	N
t5	MyISAM	10	Fixed	1	7	7	#	2048	0	11	#	#	NULL	latin1_swedish_ci	NULL			#	N
v1	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	NULL	NULL	#	#	NULL	NULL	NULL	NULL	VIEW	#	NULL
show full columns from t3 like "a%";
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
a	int(11)	NULL	YES	MUL	NULL		select,insert,update,references	
show full columns from mysql.db like "Insert%";
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
Insert_priv	enum('N','Y')	utf8mb3_general_ci	NO		N		select,insert,update,references	
show full columns from v1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
c	varchar(64)	utf8mb3_general_ci	NO		NULL		select,insert,update,references	
select * from information_schema.COLUMNS where table_name="t1"
and column_name= "a";
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	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
def	mysqltest	t1	a	1	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references		NEVER	NULL
show columns from mysqltest.t1 where field like "%a%";
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
create view mysqltest.v1 (c) as select a from mysqltest.t1;
grant select (a) on mysqltest.t1 to mysqltest_2@localhost;
grant select on mysqltest.v1 to mysqltest_3;
connect  user3,localhost,mysqltest_2,,;
connection user3;
select table_name, column_name, privileges from information_schema.columns
where table_schema = 'mysqltest' and table_name = 't1';
table_name	column_name	privileges
t1	a	select
show columns from mysqltest.t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
connect  user4,localhost,mysqltest_3,,mysqltest;
connection user4;
select table_name, column_name, privileges from information_schema.columns
where table_schema = 'mysqltest' and table_name = 'v1';
table_name	column_name	privileges
v1	c	select
explain select * from v1;
ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
connection default;
disconnect user4;
drop view v1, mysqltest.v1;
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
drop database mysqltest;
select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like 'latin1%';
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	cp1252 West European	1
SHOW CHARACTER SET LIKE 'latin1%';
Charset	Description	Default collation	Maxlen
latin1	cp1252 West European	latin1_swedish_ci	1
SHOW CHARACTER SET WHERE charset like 'latin1%';
Charset	Description	Default collation	Maxlen
latin1	cp1252 West European	latin1_swedish_ci	1
select * from information_schema.COLLATIONS
where COLLATION_NAME like 'latin1%';
COLLATION_NAME	CHARACTER_SET_NAME	ID	IS_DEFAULT	IS_COMPILED	SORTLEN
latin1_german1_ci	latin1	5		#	1
latin1_swedish_ci	latin1	8	Yes	#	1
latin1_danish_ci	latin1	15		#	1
latin1_german2_ci	latin1	31		#	2
latin1_bin	latin1	47		#	1
latin1_general_ci	latin1	48		#	1
latin1_general_cs	latin1	49		#	1
latin1_spanish_ci	latin1	94		#	1
latin1_swedish_nopad_ci	latin1	1032		#	1
latin1_nopad_bin	latin1	1071		#	1
SHOW COLLATION LIKE 'latin1%';
Collation	Charset	Id	Default	Compiled	Sortlen
latin1_german1_ci	latin1	5		#	1
latin1_swedish_ci	latin1	8	Yes	#	1
latin1_danish_ci	latin1	15		#	1
latin1_german2_ci	latin1	31		#	2
latin1_bin	latin1	47		#	1
latin1_general_ci	latin1	48		#	1
latin1_general_cs	latin1	49		#	1
latin1_spanish_ci	latin1	94		#	1
latin1_swedish_nopad_ci	latin1	1032		#	1
latin1_nopad_bin	latin1	1071		#	1
SHOW COLLATION WHERE collation like 'latin1%';
Collation	Charset	Id	Default	Compiled	Sortlen
latin1_german1_ci	latin1	5		#	1
latin1_swedish_ci	latin1	8	Yes	#	1
latin1_danish_ci	latin1	15		#	1
latin1_german2_ci	latin1	31		#	2
latin1_bin	latin1	47		#	1
latin1_general_ci	latin1	48		#	1
latin1_general_cs	latin1	49		#	1
latin1_spanish_ci	latin1	94		#	1
latin1_swedish_nopad_ci	latin1	1032		#	1
latin1_nopad_bin	latin1	1071		#	1
select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
where COLLATION_NAME like 'latin1%';
COLLATION_NAME	CHARACTER_SET_NAME
latin1_german1_ci	latin1
latin1_swedish_ci	latin1
latin1_danish_ci	latin1
latin1_german2_ci	latin1
latin1_bin	latin1
latin1_general_ci	latin1
latin1_general_cs	latin1
latin1_spanish_ci	latin1
latin1_swedish_nopad_ci	latin1
latin1_nopad_bin	latin1
drop procedure if exists sel2;
drop function if exists sub1;
drop function if exists sub2;
create function sub1(i int) returns int
return i+1;
create procedure sel2()
begin
select * from t1;
select * from t2;
end|
select parameter_style, sql_data_access, dtd_identifier
from information_schema.routines where routine_schema='test';
parameter_style	sql_data_access	dtd_identifier
SQL	CONTAINS SQL	NULL
SQL	CONTAINS SQL	int(11)
show procedure status where db='test';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	sel2	PROCEDURE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
show function status where db='test';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	sub1	FUNCTION	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
select a.ROUTINE_NAME from information_schema.ROUTINES a,
information_schema.SCHEMATA b where
a.ROUTINE_SCHEMA = b.SCHEMA_NAME AND b.SCHEMA_NAME='test';
ROUTINE_NAME
sel2
sub1
explain select a.ROUTINE_NAME from information_schema.ROUTINES a,
information_schema.SCHEMATA b where
a.ROUTINE_SCHEMA = b.SCHEMA_NAME;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	NULL	
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	NULL	Using where; Using join buffer (flat, BNL join)
select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a,
mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) AND a.ROUTINE_SCHEMA='test' order by 1;
ROUTINE_NAME	name
sel2	sel2
sub1	sub1
select count(*) from information_schema.ROUTINES where routine_schema='test';
count(*)
2
create view v1 as select routine_schema, routine_name from information_schema.routines where routine_schema='test'
order by routine_schema, routine_name;
select * from v1;
routine_schema	routine_name
test	sel2
test	sub1
drop view v1;
connect  user1,localhost,mysqltest_1,,;
connection user1;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES WHERE ROUTINE_SCHEMA <> 'sys';
ROUTINE_NAME	ROUTINE_DEFINITION
show create function sub1;
ERROR 42000: FUNCTION sub1 does not exist
connection user3;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES WHERE ROUTINE_SCHEMA <> 'sys';
ROUTINE_NAME	ROUTINE_DEFINITION
sel2	NULL
sub1	NULL
connection default;
grant all privileges on test.* to mysqltest_1@localhost;
connect  user2,localhost,mysqltest_1,,;
connection user2;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES WHERE ROUTINE_SCHEMA <> 'sys';
ROUTINE_NAME	ROUTINE_DEFINITION
sel2	NULL
sub1	NULL
create function sub2(i int) returns int
return i+1;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES WHERE ROUTINE_SCHEMA <> 'sys';
ROUTINE_NAME	ROUTINE_DEFINITION
sel2	NULL
sub1	NULL
sub2	return i+1
show create procedure sel2;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sel2		NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
show create function sub1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
sub1		NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
show create function sub2;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
sub2		CREATE DEFINER=`mysqltest_1`@`localhost` FUNCTION `sub2`(i int) RETURNS int(11)
return i+1	latin1	latin1_swedish_ci	latin1_swedish_ci
show function status like "sub2";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	sub2	FUNCTION	mysqltest_1@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
connection default;
disconnect user1;
disconnect user3;
drop function sub2;
show create procedure sel2;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sel2		CREATE DEFINER=`root`@`localhost` PROCEDURE `sel2`()
begin
select * from t1;
select * from t2;
end	latin1	latin1_swedish_ci	latin1_swedish_ci
create view v0 (c) as select schema_name from information_schema.schemata;
select * from v0;
c
information_schema
mtr
mysql
performance_schema
sys
test
explain select * from v0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	NULL	
create view v1 (c) as select table_name from information_schema.tables
where table_name="v1";
select * from v1;
c
v1
create view v2 (c) as select column_name from information_schema.columns
where table_name="v2";
select * from v2;
c
c
create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets
where CHARACTER_SET_NAME like "latin1%";
select * from v3;
c
latin1
create view v4 (c) as select COLLATION_NAME from information_schema.collations
where COLLATION_NAME like "latin1%";
select * from v4;
c
latin1_german1_ci
latin1_swedish_ci
latin1_danish_ci
latin1_german2_ci
latin1_bin
latin1_general_ci
latin1_general_cs
latin1_spanish_ci
latin1_swedish_nopad_ci
latin1_nopad_bin
show keys from v4;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
select * from information_schema.views where TABLE_NAME like "v%" AND TABLE_SCHEMA <> 'sys';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	ALGORITHM
def	test	v0	select `information_schema`.`schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata`	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v1	select `information_schema`.`tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where `information_schema`.`tables`.`TABLE_NAME` = 'v1'	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v2	select `information_schema`.`columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where `information_schema`.`columns`.`TABLE_NAME` = 'v2'	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v3	select `information_schema`.`character_sets`.`CHARACTER_SET_NAME` AS `c` from `information_schema`.`character_sets` where `information_schema`.`character_sets`.`CHARACTER_SET_NAME` like 'latin1%'	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v4	select `information_schema`.`collations`.`COLLATION_NAME` AS `c` from `information_schema`.`collations` where `information_schema`.`collations`.`COLLATION_NAME` like 'latin1%'	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
drop view v0, v1, v2, v3, v4;
create table t1 (a int);
grant select,update,insert on t1 to mysqltest_1@localhost;
grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
grant all on test.* to mysqltest_1@localhost with grant option;
select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	def	USAGE	NO
select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	def	test	SELECT	YES
'mysqltest_1'@'localhost'	def	test	INSERT	YES
'mysqltest_1'@'localhost'	def	test	UPDATE	YES
'mysqltest_1'@'localhost'	def	test	DELETE	YES
'mysqltest_1'@'localhost'	def	test	CREATE	YES
'mysqltest_1'@'localhost'	def	test	DROP	YES
'mysqltest_1'@'localhost'	def	test	REFERENCES	YES
'mysqltest_1'@'localhost'	def	test	INDEX	YES
'mysqltest_1'@'localhost'	def	test	ALTER	YES
'mysqltest_1'@'localhost'	def	test	CREATE TEMPORARY TABLES	YES
'mysqltest_1'@'localhost'	def	test	LOCK TABLES	YES
'mysqltest_1'@'localhost'	def	test	EXECUTE	YES
'mysqltest_1'@'localhost'	def	test	CREATE VIEW	YES
'mysqltest_1'@'localhost'	def	test	SHOW VIEW	YES
'mysqltest_1'@'localhost'	def	test	CREATE ROUTINE	YES
'mysqltest_1'@'localhost'	def	test	ALTER ROUTINE	YES
'mysqltest_1'@'localhost'	def	test	EVENT	YES
'mysqltest_1'@'localhost'	def	test	TRIGGER	YES
'mysqltest_1'@'localhost'	def	test	DELETE HISTORY	YES
select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	def	test	t1	SELECT	NO
'mysqltest_1'@'localhost'	def	test	t1	INSERT	NO
'mysqltest_1'@'localhost'	def	test	t1	UPDATE	NO
select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	def	test	t1	a	SELECT	NO
'mysqltest_1'@'localhost'	def	test	t1	a	INSERT	NO
'mysqltest_1'@'localhost'	def	test	t1	a	UPDATE	NO
'mysqltest_1'@'localhost'	def	test	t1	a	REFERENCES	NO
delete from mysql.user where user like 'mysqltest%';
delete from mysql.db where user like 'mysqltest%';
delete from mysql.tables_priv where user like 'mysqltest%';
delete from mysql.columns_priv where user like 'mysqltest%';
flush privileges;
drop table t1;
create table t1 (a int null, primary key(a));
alter table t1 add constraint constraint_1 unique (a);
alter table t1 add constraint unique key_1(a);
Warnings:
Note	1831	Duplicate index `key_1`. This is deprecated and will be disallowed in a future release
alter table t1 add constraint constraint_2 unique key_2(a);
Warnings:
Note	1831	Duplicate index `key_2`. This is deprecated and will be disallowed in a future release
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `constraint_1` (`a`),
  UNIQUE KEY `key_1` (`a`),
  UNIQUE KEY `key_2` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from information_schema.TABLE_CONSTRAINTS where
TABLE_SCHEMA= "test";
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
def	test	PRIMARY	test	t1	PRIMARY KEY
def	test	constraint_1	test	t1	UNIQUE
def	test	key_1	test	t1	UNIQUE
def	test	key_2	test	t1	UNIQUE
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_SCHEMA= "test";
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
def	test	PRIMARY	def	test	t1	a	1	NULL	NULL	NULL	NULL
def	test	constraint_1	def	test	t1	a	1	NULL	NULL	NULL	NULL
def	test	key_1	def	test	t1	a	1	NULL	NULL	NULL	NULL
def	test	key_2	def	test	t1	a	1	NULL	NULL	NULL	NULL
connection user2;
select table_name from information_schema.TABLES where table_schema like "test%";
table_name
t1
select table_name,column_name from information_schema.COLUMNS where table_schema like "test%";
table_name	column_name
t1	a
select ROUTINE_NAME from information_schema.ROUTINES;
ROUTINE_NAME
sel2
sub1
disconnect user2;
connection default;
delete from mysql.user where user='mysqltest_1';
drop table t1;
drop procedure sel2;
drop function sub1;
create table t1(a int);
create view v1 (c) as select a from t1 with check option;
create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION;
create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION;
select * from information_schema.views where table_schema <> 'sys';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	ALGORITHM
def	mysql	user	select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `mysql`.`global_priv`	NONE	YES	mariadb.sys@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v1	select `test`.`t1`.`a` AS `c` from `test`.`t1`	CASCADED	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v2	select `test`.`t1`.`a` AS `c` from `test`.`t1`	LOCAL	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v3	select `test`.`t1`.`a` AS `c` from `test`.`t1`	CASCADED	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
grant select (a) on test.t1 to joe@localhost with grant option;
select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'joe'@'localhost'	def	test	t1	a	SELECT	YES
select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mariadb.sys'@'localhost'	def	mysql	global_priv	SELECT	NO
'mariadb.sys'@'localhost'	def	mysql	global_priv	DELETE	NO
drop view v1, v2, v3;
drop table t1;
delete from mysql.user where user='joe';
delete from mysql.db where user='joe';
delete from mysql.tables_priv where user='joe';
delete from mysql.columns_priv where user='joe';
flush privileges;
create table t1 (a int not null auto_increment,b int, primary key (a));
insert into t1 values (1,1),(NULL,3),(NULL,4);
select AUTO_INCREMENT from information_schema.tables where table_name = 't1';
AUTO_INCREMENT
4
drop table t1;
create table t1 (s1 int);
insert into t1 values (0),(9),(0);
select s1 from t1 where s1 in (select version from
information_schema.tables) union select version from
information_schema.tables;
s1
10
11
NULL
drop table t1;
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
Table	Create Table
CHARACTER_SETS	CREATE TEMPORARY TABLE `CHARACTER_SETS` (
  `CHARACTER_SET_NAME` varchar(32) NOT NULL,
  `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL,
  `DESCRIPTION` varchar(60) NOT NULL,
  `MAXLEN` bigint(3) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3
set names latin2;
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
Table	Create Table
CHARACTER_SETS	CREATE TEMPORARY TABLE `CHARACTER_SETS` (
  `CHARACTER_SET_NAME` varchar(32) NOT NULL,
  `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL,
  `DESCRIPTION` varchar(60) NOT NULL,
  `MAXLEN` bigint(3) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3
set names latin1;
create table t1 select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like "latin1";
select * from t1;
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	cp1252 West European	1
alter table t1 default character set utf8;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `CHARACTER_SET_NAME` varchar(32) NOT NULL,
  `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL,
  `DESCRIPTION` varchar(60) NOT NULL,
  `MAXLEN` bigint(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3
drop table t1;
create view v1 as select * from information_schema.TABLES;
drop view v1;
create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2),
d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3),
i DOUBLE);
select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
from information_schema.columns where table_name= 't1';
COLUMN_NAME	COLUMN_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE
a	decimal(5,3)	NULL	NULL	5	3
b	decimal(5,1)	NULL	NULL	5	1
c	float(5,2)	NULL	NULL	5	2
d	decimal(6,4)	NULL	NULL	6	4
e	float	NULL	NULL	12	NULL
f	decimal(6,3)	NULL	NULL	6	3
g	int(11)	NULL	NULL	10	0
h	double(10,3)	NULL	NULL	10	3
i	double	NULL	NULL	22	NULL
drop table t1;
create table t115 as select table_name, column_name, column_type
from information_schema.columns where table_name = 'proc';
select * from t115;
table_name	column_name	column_type
proc	db	char(64)
proc	name	char(64)
proc	type	enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')
proc	specific_name	char(64)
proc	language	enum('SQL')
proc	sql_data_access	enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA')
proc	is_deterministic	enum('YES','NO')
proc	security_type	enum('INVOKER','DEFINER')
proc	param_list	blob
proc	returns	longblob
proc	body	longblob
proc	definer	varchar(384)
proc	created	timestamp
proc	modified	timestamp
proc	sql_mode	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL')
proc	comment	text
proc	character_set_client	char(32)
proc	collation_connection	char(32)
proc	db_collation	char(32)
proc	body_utf8	longblob
proc	aggregate	enum('NONE','GROUP')
drop table t115;
create procedure p108 () begin declare c cursor for select data_type
from information_schema.columns;  open c; open c; end;//
call p108()//
ERROR 24000: Cursor is already open
drop procedure p108;
create view v1 as select A1.table_name from information_schema.TABLES A1
where table_name= "user";
select * from v1;
table_name
user
drop view v1;
create view vo as select 'a' union select 'a';
show index from vo;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
select * from information_schema.TABLE_CONSTRAINTS where
TABLE_NAME= "vo";
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_NAME= "vo";
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
drop view vo;
select TABLE_NAME,TABLE_TYPE,ENGINE
from information_schema.tables
where table_schema='information_schema' limit 2;
TABLE_NAME	TABLE_TYPE	ENGINE
ALL_PLUGINS	SYSTEM VIEW	Aria
APPLICABLE_ROLES	SYSTEM VIEW	MEMORY
show tables from information_schema like "T%";
Tables_in_information_schema (T%)
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLE_STATISTICS
TRIGGERS
create database information_schema;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
use information_schema;
show full tables like "T%";
Tables_in_information_schema (T%)	Table_type
TABLES	SYSTEM VIEW
TABLESPACES	SYSTEM VIEW
TABLE_CONSTRAINTS	SYSTEM VIEW
TABLE_PRIVILEGES	SYSTEM VIEW
TABLE_STATISTICS	SYSTEM VIEW
TRIGGERS	SYSTEM VIEW
create table t1(a int);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
use test;
show tables;
Tables_in_test
use information_schema;
show tables like "T%";
Tables_in_information_schema (T%)
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLE_STATISTICS
TRIGGERS
select table_name from tables where table_name='user';
table_name
user
select column_name, privileges from columns
where table_name='user' and column_name like '%o%';
column_name	privileges
Host	select,insert,update,references
Password	select,insert,update,references
Drop_priv	select,insert,update,references
Reload_priv	select,insert,update,references
Shutdown_priv	select,insert,update,references
Process_priv	select,insert,update,references
Show_db_priv	select,insert,update,references
Lock_tables_priv	select,insert,update,references
Show_view_priv	select,insert,update,references
Create_routine_priv	select,insert,update,references
Alter_routine_priv	select,insert,update,references
Delete_history_priv	select,insert,update,references
max_questions	select,insert,update,references
max_connections	select,insert,update,references
max_user_connections	select,insert,update,references
authentication_string	select,insert,update,references
password_expired	select,insert,update,references
is_role	select,insert,update,references
default_role	select,insert,update,references
use test;
create function sub1(i int) returns int
return i+1;
create table t1(f1 int);
create view v2 (c) as select f1 from t1;
create view v3 (c) as select sub1(1);
create table t4(f1 int, KEY f1_key (f1));
drop table t1;
drop function sub1;
select table_name from information_schema.views
where table_schema='test';
table_name
v2
v3
select table_name from information_schema.views
where table_schema='test';
table_name
v2
v3
select column_name from information_schema.columns
where table_schema='test' and table_name='t4';
column_name
f1
select column_name from information_schema.columns
where table_schema='test' and table_name='v2';
column_name
Warnings:
Warning	1356	View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
select column_name from information_schema.columns
where table_schema='test' and table_name='v3';
column_name
Warnings:
Warning	1356	View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
select index_name from information_schema.statistics where table_schema='test';
index_name
f1_key
select constraint_name from information_schema.table_constraints
where table_schema='test';
constraint_name
show create view v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `test`.`t1`.`f1` AS `c` from `t1`	latin1	latin1_swedish_ci
Warnings:
Warning	1356	View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
show create table v3;
View	Create View	character_set_client	collation_connection
v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `sub1`(1) AS `c`	latin1	latin1_swedish_ci
Warnings:
Warning	1356	View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
drop view v2;
drop view v3;
drop table t4;
select * from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
select column_type from information_schema.columns
where table_schema="information_schema" and table_name="COLUMNS" and
(column_name="character_set_name" or column_name="collation_name");
column_type
varchar(32)
varchar(32)
select TABLE_ROWS from information_schema.tables where
table_schema="information_schema" and table_name="COLUMNS";
TABLE_ROWS
NULL
select table_type from information_schema.tables
where table_schema="mysql" and table_name="user";
table_type
VIEW
show open tables where `table` like "user";
Database	Table	In_use	Name_locked
mysql	user	0	0
show status where variable_name like "%database%";
Variable_name	Value
Acl_database_grants	2
Com_show_databases	3
show variables where variable_name like "skip_show_databas";
Variable_name	Value
show global status like "Threads_running";
Variable_name	Value
Threads_running	#
create table t1(f1 int);
create table t2(f2 int);
create view v1 as select * from t1, t2;
set @got_val= (select count(*) from information_schema.columns);
drop view v1;
drop table t1, t2;
use test;
CREATE TABLE t_crashme ( f1 BIGINT);
CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1;
CREATE VIEW a2 AS SELECT t_CRASHME FROM a1;
count(*)
68
drop view a2, a1;
drop table t_crashme;
select table_schema,table_name, column_name from
information_schema.columns
where data_type = 'longtext' and table_schema != 'performance_schema'
order by binary table_name, ordinal_position;
table_schema	table_name	column_name
information_schema	ALL_PLUGINS	PLUGIN_DESCRIPTION
information_schema	CHECK_CONSTRAINTS	CHECK_CLAUSE
information_schema	COLUMNS	COLUMN_DEFAULT
information_schema	COLUMNS	COLUMN_TYPE
information_schema	COLUMNS	GENERATION_EXPRESSION
information_schema	EVENTS	EVENT_DEFINITION
information_schema	OPTIMIZER_TRACE	QUERY
information_schema	OPTIMIZER_TRACE	TRACE
information_schema	PARAMETERS	DTD_IDENTIFIER
information_schema	PARTITIONS	PARTITION_EXPRESSION
information_schema	PARTITIONS	SUBPARTITION_EXPRESSION
information_schema	PARTITIONS	PARTITION_DESCRIPTION
information_schema	PLUGINS	PLUGIN_DESCRIPTION
information_schema	PROCESSLIST	INFO
information_schema	ROUTINES	DTD_IDENTIFIER
information_schema	ROUTINES	ROUTINE_DEFINITION
information_schema	ROUTINES	ROUTINE_COMMENT
information_schema	SYSTEM_VARIABLES	ENUM_VALUE_LIST
information_schema	TRIGGERS	ACTION_CONDITION
information_schema	TRIGGERS	ACTION_STATEMENT
information_schema	VIEWS	VIEW_DEFINITION
mysql	global_priv	Priv
sys	innodb_lock_waits	waiting_query
sys	innodb_lock_waits	blocking_query
sys	processlist	current_statement
sys	processlist	last_statement
sys	schema_auto_increment_columns	column_type
sys	schema_table_lock_waits	waiting_query
sys	session	current_statement
sys	session	last_statement
sys	statement_analysis	query
sys	statements_with_errors_or_warnings	query
sys	statements_with_full_table_scans	query
sys	statements_with_runtimes_in_95th_percentile	query
sys	statements_with_sorting	query
sys	statements_with_temp_tables	query
mysql	user	Password
mysql	user	ssl_cipher
mysql	user	x509_issuer
mysql	user	x509_subject
mysql	user	plugin
mysql	user	authentication_string
mysql	user	default_role
sys	x$processlist	current_statement
sys	x$processlist	last_statement
sys	x$schema_table_lock_waits	waiting_query
sys	x$session	current_statement
sys	x$session	last_statement
sys	x$statement_analysis	query
sys	x$statements_with_errors_or_warnings	query
sys	x$statements_with_full_table_scans	query
sys	x$statements_with_runtimes_in_95th_percentile	query
sys	x$statements_with_sorting	query
sys	x$statements_with_temp_tables	query
select table_name, column_name, data_type from information_schema.columns
where data_type = 'datetime' and table_name not like 'innodb_%'
order by binary table_name, ordinal_position;
table_name	column_name	data_type
EVENTS	EXECUTE_AT	datetime
EVENTS	STARTS	datetime
EVENTS	ENDS	datetime
EVENTS	CREATED	datetime
EVENTS	LAST_ALTERED	datetime
EVENTS	LAST_EXECUTED	datetime
FILES	CREATION_TIME	datetime
FILES	LAST_UPDATE_TIME	datetime
FILES	LAST_ACCESS_TIME	datetime
FILES	CREATE_TIME	datetime
FILES	UPDATE_TIME	datetime
FILES	CHECK_TIME	datetime
PARTITIONS	CREATE_TIME	datetime
PARTITIONS	UPDATE_TIME	datetime
PARTITIONS	CHECK_TIME	datetime
ROUTINES	CREATED	datetime
ROUTINES	LAST_ALTERED	datetime
TABLES	CREATE_TIME	datetime
TABLES	UPDATE_TIME	datetime
TABLES	CHECK_TIME	datetime
TRIGGERS	CREATED	datetime
event	execute_at	datetime
event	last_executed	datetime
event	starts	datetime
event	ends	datetime
x$innodb_lock_waits	wait_started	datetime
x$innodb_lock_waits	waiting_trx_started	datetime
x$innodb_lock_waits	blocking_trx_started	datetime
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
WHERE NOT EXISTS
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.TABLE_NAME = B.TABLE_NAME);
COUNT(*)
0
create table t1
( x_bigint BIGINT,
x_integer INTEGER,
x_smallint SMALLINT,
x_decimal DECIMAL(5,3),
x_numeric NUMERIC(5,3),
x_real REAL,
x_float FLOAT,
x_double_precision DOUBLE PRECISION );
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= 't1';
COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH
x_bigint	NULL	NULL
x_integer	NULL	NULL
x_smallint	NULL	NULL
x_decimal	NULL	NULL
x_numeric	NULL	NULL
x_real	NULL	NULL
x_float	NULL	NULL
x_double_precision	NULL	NULL
drop table t1;
grant select on test.* to mysqltest_4@localhost;
connect  user10261,localhost,mysqltest_4,,;
connection user10261;
SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME='TABLE_NAME' and table_name not like 'innodb%';
TABLE_NAME	COLUMN_NAME	PRIVILEGES
CHECK_CONSTRAINTS	TABLE_NAME	select
COLUMNS	TABLE_NAME	select
COLUMN_PRIVILEGES	TABLE_NAME	select
FILES	TABLE_NAME	select
INDEX_STATISTICS	TABLE_NAME	select
KEY_COLUMN_USAGE	TABLE_NAME	select
PARTITIONS	TABLE_NAME	select
REFERENTIAL_CONSTRAINTS	TABLE_NAME	select
STATISTICS	TABLE_NAME	select
TABLES	TABLE_NAME	select
TABLE_CONSTRAINTS	TABLE_NAME	select
TABLE_PRIVILEGES	TABLE_NAME	select
TABLE_STATISTICS	TABLE_NAME	select
VIEWS	TABLE_NAME	select
connection default;
disconnect user10261;
delete from mysql.user where user='mysqltest_4';
delete from mysql.db where user='mysqltest_4';
flush privileges;
create table t1 (i int, j int);
create trigger trg1 before insert on t1 for each row
begin
if new.j > 10 then
set new.j := 10;
end if;
end|
create trigger trg2 before update on t1 for each row
begin
if old.i % 2 = 0 then
set new.j := -1;
end if;
end|
create trigger trg3 after update on t1 for each row
begin
if new.j = -1 then
set @fired:= "Yes";
end if;
end|
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
trg1	INSERT	t1	begin
if new.j > 10 then
set new.j := 10;
end if;
end	BEFORE	#		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
trg2	UPDATE	t1	begin
if old.i % 2 = 0 then
set new.j := -1;
end if;
end	BEFORE	#		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
trg3	UPDATE	t1	begin
if new.j = -1 then
set @fired:= "Yes";
end if;
end	AFTER	#		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest');
TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
def	test	trg1	INSERT	def	test	t1	1	NULL	begin
if new.j > 10 then
set new.j := 10;
end if;
end	ROW	BEFORE	NULL	NULL	OLD	NEW	#		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
def	test	trg2	UPDATE	def	test	t1	1	NULL	begin
if old.i % 2 = 0 then
set new.j := -1;
end if;
end	ROW	BEFORE	NULL	NULL	OLD	NEW	#		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
def	test	trg3	UPDATE	def	test	t1	1	NULL	begin
if new.j = -1 then
set @fired:= "Yes";
end if;
end	ROW	AFTER	NULL	NULL	OLD	NEW	#		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
drop trigger trg1;
drop trigger trg2;
drop trigger trg3;
drop table t1;
create database mysqltest;
create table mysqltest.t1 (f1 int, f2 int);
create table mysqltest.t2 (f1 int);
grant select (f1) on mysqltest.t1 to user1@localhost;
grant select on mysqltest.t2 to user2@localhost;
grant select on mysqltest.* to user3@localhost;
grant select on *.* to user4@localhost;
connect  con1,localhost,user1,,mysqltest;
connect  con2,localhost,user2,,mysqltest;
connect  con3,localhost,user3,,mysqltest;
connect  con4,localhost,user4,,;
connection con1;
select * from information_schema.column_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user1'@'localhost'	def	mysqltest	t1	f1	SELECT	NO
select * from information_schema.table_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.schema_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.user_privileges order by grantee;
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'user1'@'localhost'	def	USAGE	NO
show grants;
Grants for user1@localhost
GRANT USAGE ON *.* TO `user1`@`localhost`
GRANT SELECT (f1) ON `mysqltest`.`t1` TO `user1`@`localhost`
connection con2;
select * from information_schema.column_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.table_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user2'@'localhost'	def	mysqltest	t2	SELECT	NO
select * from information_schema.schema_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.user_privileges order by grantee;
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'user2'@'localhost'	def	USAGE	NO
show grants;
Grants for user2@localhost
GRANT USAGE ON *.* TO `user2`@`localhost`
GRANT SELECT ON `mysqltest`.`t2` TO `user2`@`localhost`
connection con3;
select * from information_schema.column_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.table_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.schema_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'user3'@'localhost'	def	mysqltest	SELECT	NO
select * from information_schema.user_privileges order by grantee;
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'user3'@'localhost'	def	USAGE	NO
show grants;
Grants for user3@localhost
GRANT USAGE ON *.* TO `user3`@`localhost`
GRANT SELECT ON `mysqltest`.* TO `user3`@`localhost`
connection con4;
select * from information_schema.column_privileges where grantee like '\'user%'
order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user1'@'localhost'	def	mysqltest	t1	f1	SELECT	NO
select * from information_schema.table_privileges where grantee like '\'user%'
order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user2'@'localhost'	def	mysqltest	t2	SELECT	NO
select * from information_schema.schema_privileges where grantee like '\'user%'
order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'user3'@'localhost'	def	mysqltest	SELECT	NO
select * from information_schema.user_privileges where grantee like '\'user%'
order by grantee;
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'user1'@'localhost'	def	USAGE	NO
'user2'@'localhost'	def	USAGE	NO
'user3'@'localhost'	def	USAGE	NO
'user4'@'localhost'	def	SELECT	NO
show grants;
Grants for user4@localhost
GRANT SELECT ON *.* TO `user4`@`localhost`
connection default;
disconnect con1;
disconnect con2;
disconnect con3;
disconnect con4;
drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost;
use test;
drop database mysqltest;
drop procedure if exists p1;
drop procedure if exists p2;
create procedure p1 () modifies sql data set @a = 5;
create procedure p2 () set @a = 5;
select sql_data_access from information_schema.routines
where specific_name like 'p%' and routine_schema = 'test';
sql_data_access
MODIFIES SQL DATA
CONTAINS SQL
drop procedure p1;
drop procedure p2;
show create database information_schema;
Database	Create Database
information_schema	CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8mb3 */
create table t1(f1 LONGBLOB, f2 LONGTEXT);
select column_name,data_type,CHARACTER_OCTET_LENGTH,
CHARACTER_MAXIMUM_LENGTH
from information_schema.columns
where table_name='t1';
column_name	data_type	CHARACTER_OCTET_LENGTH	CHARACTER_MAXIMUM_LENGTH
f1	longblob	4294967295	4294967295
f2	longtext	4294967295	4294967295
drop table t1;
create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int,
f5 BIGINT, f6 BIT, f7 bit(64));
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
from information_schema.columns
where table_name='t1';
column_name	NUMERIC_PRECISION	NUMERIC_SCALE
f1	3	0
f2	5	0
f3	7	0
f4	10	0
f5	19	0
f6	1	NULL
f7	64	NULL
drop table t1;
create table t1 (f1 integer);
create trigger tr1 after insert on t1 for each row set @test_var=42;
use information_schema;
select trigger_schema, trigger_name from triggers where
trigger_name='tr1';
trigger_schema	trigger_name
test	tr1
use test;
drop table t1;
create table t1 (a int not null, b int);
use information_schema;
select column_name, column_default from columns
where table_schema='test' and table_name='t1';
column_name	column_default
a	NULL
b	NULL
use test;
show columns from t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	NO		NULL	
b	int(11)	YES		NULL	
drop table t1;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (b int);
SHOW TABLE STATUS FROM test
WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE');
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	Max_index_length	Temporary
t1	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL			#	N
t2	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL			#	N
DROP TABLE t1,t2;
create table t1(f1 int);
create view v1 (c) as select f1 from t1;
connect  con5,localhost,root,,*NO-ONE*;
select database();
database()
NULL
show fields from test.v1;
Field	Type	Null	Key	Default	Extra
c	int(11)	YES		NULL	
connection default;
disconnect con5;
drop view v1;
drop table t1;
alter database information_schema;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
drop database information_schema;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
drop table information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
alter table information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
use information_schema;
create temporary table schemata(f1 char(10));
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE PROCEDURE p1 ()
BEGIN
SELECT 'foo' FROM DUAL;
END |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema';
ROUTINE_NAME
grant all on information_schema.* to 'user1'@'localhost';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
grant select on information_schema.* to 'user1'@'localhost';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
use test;
create table t1(id int);
insert into t1(id) values (1);
select 1 from (select 1 from test.t1) a;
1
1
use information_schema;
select 1 from (select 1 from test.t1) a;
1
1
use test;
drop table t1;
create table t1 (f1 int(11));
create view v1 as select * from t1;
drop table t1;
select table_type from information_schema.tables
where table_name="v1";
table_type
VIEW
drop view v1;
create temporary table t1(f1 int, index(f1));
show columns from t1;
Field	Type	Null	Key	Default	Extra
f1	int(11)	YES	MUL	NULL	
describe t1;
Field	Type	Null	Key	Default	Extra
f1	int(11)	YES	MUL	NULL	
show indexes from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
t1	1	f1	1	f1	A	NULL	NULL	NULL	YES	BTREE			NO
drop table t1;
create table t1(f1 binary(32), f2 varbinary(64));
select character_maximum_length, character_octet_length
from information_schema.columns where table_name='t1';
character_maximum_length	character_octet_length
32	32
64	64
drop table t1;
CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT);
INSERT INTO t1 SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1;
CREATE FUNCTION func2() RETURNS BIGINT RETURN 1;
CREATE FUNCTION func1() RETURNS BIGINT
BEGIN
RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA <> 'sys');
END//
CREATE VIEW v1 AS SELECT 1 FROM t1
WHERE f3 = (SELECT func2 ());
SELECT func1();
func1()
2
DROP TABLE t1;
DROP VIEW v1;
DROP FUNCTION func1;
DROP FUNCTION func2;
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
from information_schema.columns where
table_schema='information_schema' and
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
 or column_type = 'varchar(27)')
group by column_type order by num;
column_type	group_concat(table_schema, '.', table_name)	num
varchar(7)	information_schema.ROUTINES,information_schema.VIEWS	2
varchar(20)	information_schema.ALL_PLUGINS,information_schema.ALL_PLUGINS,information_schema.ALL_PLUGINS,information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PROFILING	9
create table t1(f1 char(1) not null, f2 char(9) not null)
default character set utf8;
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
information_schema.columns where table_schema='test' and table_name = 't1';
CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH
1	3
9	27
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','','','utf8','utf8_general_ci','utf8_general_ci','n/a', 'NONE');
select routine_name from information_schema.routines where ROUTINE_SCHEMA='test';
routine_name

delete from proc where name='';
use test;
grant select on test.* to mysqltest_1@localhost;
create table t1 (id int);
create view v1 as select * from t1;
create definer = mysqltest_1@localhost
sql security definer view v2 as select 1;
connect  con16681,localhost,mysqltest_1,,test;
connection con16681;
select * from information_schema.views
where table_name='v1' or table_name='v2' order by table_name;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	ALGORITHM
def	test	v1		NONE	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v2	select 1 AS `1`	NONE	NO	mysqltest_1@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
connection default;
disconnect con16681;
drop view v1, v2;
drop table t1;
drop user mysqltest_1@localhost;
set @a:= '.';
create table t1(f1 char(5));
create table t2(f1 char(5));
select concat(@a, table_name), @a, table_name
from information_schema.tables where table_schema = 'test' order by table_name;
concat(@a, table_name)	@a	table_name
.t1	.	t1
.t2	.	t2
drop table t1,t2;
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
CREATE PROCEDURE p1() SET @a= 1;
CREATE FUNCTION f1() RETURNS INT RETURN @a + 1;
CREATE USER mysql_bug20230@localhost;
GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost;
GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost;
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
ROUTINE_NAME	ROUTINE_DEFINITION
f1	RETURN @a + 1
p1	SET @a= 1
SHOW CREATE PROCEDURE p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
SET @a= 1	latin1	latin1_swedish_ci	latin1_swedish_ci
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
RETURN @a + 1	latin1	latin1_swedish_ci	latin1_swedish_ci
connect  conn1, localhost, mysql_bug20230,,;
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
ROUTINE_NAME	ROUTINE_DEFINITION
f1	NULL
p1	NULL
SHOW CREATE PROCEDURE p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1		NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1		NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
CALL p1();
SELECT f1();
f1()
2
disconnect conn1;
connection default;
DROP FUNCTION f1;
DROP PROCEDURE p1;
DROP USER mysql_bug20230@localhost;
SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test') and table_name not like 'xtradb%';
MAX(table_name)
VIEWS
SELECT table_name from information_schema.tables
WHERE table_name=(SELECT MAX(table_name)
FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test') and table_name not like 'xtradb%');
table_name
VIEWS
DROP TABLE IF EXISTS bug23037;
DROP FUNCTION IF EXISTS get_value;
SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
COLUMN_NAME	MD5(COLUMN_DEFAULT)	LENGTH(COLUMN_DEFAULT)
fld1	85ea6a55b8f0058e640b3de141a3a9d9	65534
SELECT MD5(get_value());
MD5(get_value())
76176d2daa20c582375b8dcfc18033cd
SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
COLUMN_NAME	MD5(COLUMN_DEFAULT)	LENGTH(COLUMN_DEFAULT)	COLUMN_DEFAULT=get_value()
fld1	85ea6a55b8f0058e640b3de141a3a9d9	65534	0
DROP TABLE bug23037;
DROP FUNCTION get_value;
set @tmp_optimizer_switch=@@optimizer_switch;
set optimizer_switch='derived_merge=off,derived_with_keys=off';
create view v1 as
select table_schema as object_schema,
table_name   as object_name,
table_type   as object_type
from information_schema.tables
order by object_schema;
explain select * from v1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases; Using filesort
explain select * from (select table_name from information_schema.tables) as a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
2	DERIVED	tables	ALL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
set optimizer_switch=@tmp_optimizer_switch;
drop view v1;
create table t1 (f1 int(11));
create table t2 (f1 int(11), f2 int(11));
select table_name from information_schema.tables
where table_schema = 'test' and table_name not in
(select table_name from information_schema.columns
where table_schema = 'test' and column_name = 'f3')
order by table_name;
table_name
t1
t2
drop table t1,t2;
create table t1(f1 int);
create view v1 as select f1+1 as a from t1;
create table t2 (f1 int, f2 int);
create view v2 as select f1+1 as a, f2 as b from t2;
select table_name, is_updatable from information_schema.views order by table_name;
table_name	is_updatable
host_summary	NO
host_summary_by_file_io	NO
host_summary_by_file_io_type	YES
host_summary_by_stages	YES
host_summary_by_statement_latency	NO
host_summary_by_statement_type	YES
innodb_buffer_stats_by_schema	NO
innodb_buffer_stats_by_table	NO
innodb_lock_waits	NO
io_by_thread_by_latency	NO
io_global_by_file_by_bytes	YES
io_global_by_file_by_latency	YES
io_global_by_wait_by_bytes	YES
io_global_by_wait_by_latency	YES
latest_file_io	YES
memory_by_host_by_current_bytes	NO
memory_by_thread_by_current_bytes	NO
memory_by_user_by_current_bytes	NO
memory_global_by_current_bytes	YES
memory_global_total	NO
metrics	NO
processlist	NO
ps_check_lost_instrumentation	YES
schema_auto_increment_columns	NO
schema_index_statistics	YES
schema_object_overview	NO
schema_redundant_indexes	NO
schema_tables_with_full_table_scans	YES
schema_table_lock_waits	NO
schema_table_statistics	NO
schema_table_statistics_with_buffer	NO
schema_unused_indexes	YES
session	YES
session_ssl_status	YES
statements_with_errors_or_warnings	YES
statements_with_full_table_scans	YES
statements_with_runtimes_in_95th_percentile	YES
statements_with_sorting	YES
statements_with_temp_tables	YES
statement_analysis	YES
user	YES
user_summary	NO
user_summary_by_file_io	NO
user_summary_by_file_io_type	YES
user_summary_by_stages	YES
user_summary_by_statement_latency	NO
user_summary_by_statement_type	YES
v1	NO
v2	YES
version	NO
waits_by_host_by_latency	YES
waits_by_user_by_latency	YES
waits_global_by_latency	YES
wait_classes_global_by_avg_latency	NO
wait_classes_global_by_latency	NO
x$host_summary	NO
x$host_summary_by_file_io	NO
x$host_summary_by_file_io_type	YES
x$host_summary_by_stages	YES
x$host_summary_by_statement_latency	NO
x$host_summary_by_statement_type	YES
x$innodb_buffer_stats_by_schema	NO
x$innodb_buffer_stats_by_table	NO
x$innodb_lock_waits	NO
x$io_by_thread_by_latency	NO
x$io_global_by_file_by_bytes	YES
x$io_global_by_file_by_latency	YES
x$io_global_by_wait_by_bytes	YES
x$io_global_by_wait_by_latency	YES
x$latest_file_io	YES
x$memory_by_host_by_current_bytes	NO
x$memory_by_thread_by_current_bytes	NO
x$memory_by_user_by_current_bytes	NO
x$memory_global_by_current_bytes	YES
x$memory_global_total	NO
x$processlist	NO
x$ps_digest_95th_percentile_by_avg_us	NO
x$ps_digest_avg_latency_distribution	NO
x$ps_schema_table_statistics_io	NO
x$schema_flattened_keys	NO
x$schema_index_statistics	YES
x$schema_tables_with_full_table_scans	YES
x$schema_table_lock_waits	NO
x$schema_table_statistics	NO
x$schema_table_statistics_with_buffer	NO
x$session	YES
x$statements_with_errors_or_warnings	YES
x$statements_with_full_table_scans	YES
x$statements_with_runtimes_in_95th_percentile	YES
x$statements_with_sorting	YES
x$statements_with_temp_tables	YES
x$statement_analysis	YES
x$user_summary	NO
x$user_summary_by_file_io	NO
x$user_summary_by_file_io_type	YES
x$user_summary_by_stages	YES
x$user_summary_by_statement_latency	NO
x$user_summary_by_statement_type	YES
x$waits_by_host_by_latency	YES
x$waits_by_user_by_latency	YES
x$waits_global_by_latency	YES
x$wait_classes_global_by_avg_latency	NO
x$wait_classes_global_by_latency	NO
delete from v1;
drop view v1,v2;
drop table t1,t2;
alter database;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
alter database test;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
create database mysqltest;
create table mysqltest.t1(a int, b int, c int);
create trigger mysqltest.t1_ai after insert on mysqltest.t1
for each row set @a = new.a + new.b + new.c;
grant select(b) on mysqltest.t1 to mysqltest_1@localhost;
select trigger_name from information_schema.triggers
where event_object_table='t1';
trigger_name
t1_ai
show triggers from mysqltest;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
t1_ai	INSERT	t1	set @a = new.a + new.b + new.c	AFTER	#		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
connect  con27629,localhost,mysqltest_1,,mysqltest;
show columns from t1;
Field	Type	Null	Key	Default	Extra
b	int(11)	YES		NULL	
select column_name from information_schema.columns where table_name='t1';
column_name
b
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
select trigger_name from information_schema.triggers
where event_object_table='t1';
trigger_name
connection default;
disconnect con27629;
drop user mysqltest_1@localhost;
drop database mysqltest;
create table t1 (
f1 varchar(50),
f2 varchar(50) not null,
f3 varchar(50) default '',
f4 varchar(50) default NULL,
f5 bigint not null,
f6 bigint not null default 10,
f7 datetime not null,
f8 datetime default '2006-01-01'
);
select column_default from information_schema.columns where table_name= 't1';
column_default
NULL
NULL
''
NULL
NULL
10
NULL
'2006-01-01 00:00:00'
show columns from t1;
Field	Type	Null	Key	Default	Extra
f1	varchar(50)	YES		NULL	
f2	varchar(50)	NO		NULL	
f3	varchar(50)	YES			
f4	varchar(50)	YES		NULL	
f5	bigint(20)	NO		NULL	
f6	bigint(20)	NO		10	
f7	datetime	NO		NULL	
f8	datetime	YES		2006-01-01 00:00:00	
drop table t1;
show fields from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
show keys from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
USE information_schema;
SET max_heap_table_size = 16384;
CREATE TABLE test.t1( a INT );
SELECT *
FROM tables ta
JOIN collations co ON ( co.collation_name = ta.table_catalog )
JOIN character_sets cs ON ( cs.character_set_name = ta.table_catalog );
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	MAX_INDEX_LENGTH	TEMPORARY	COLLATION_NAME	CHARACTER_SET_NAME	ID	IS_DEFAULT	IS_COMPILED	SORTLEN	CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
DROP TABLE test.t1;
SET max_heap_table_size = DEFAULT;
USE test;
End of 5.0 tests.
select * from information_schema.engines WHERE ENGINE="MyISAM";
ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
MyISAM	DEFAULT	Non-transactional engine with good performance and small data footprint	NO	NO	NO
grant select on *.* to user3148@localhost;
connect  con3148,localhost,user3148,,test;
connection con3148;
select user,db from information_schema.processlist;
user	db
user3148	test
connection default;
disconnect con3148;
drop user user3148@localhost;
connect  pslistcon,localhost,root,,test;
SELECT 'other connection here' AS who;
who
other connection here
connection default;
SELECT IF(`time` > 0, 'OK', `time`) AS time_low,
IF(`time` < 1000, 'OK', `time`) AS time_high,
IF(time_ms >= 1000, 'OK', time_ms) AS time_ms_low,
IF(time_ms < 1000000, 'OK', time_ms) AS time_ms_high
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE ID=@tid;
time_low	time_high	time_ms_low	time_ms_high
OK	OK	OK	OK
disconnect pslistcon;
DROP TABLE IF EXISTS server_status;
DROP EVENT IF EXISTS event_status;
SET GLOBAL event_scheduler=1;
CREATE EVENT event_status
ON SCHEDULE AT NOW()
ON COMPLETION NOT PRESERVE
DO
BEGIN
CREATE TABLE server_status
SELECT variable_name
FROM information_schema.global_status
WHERE variable_name LIKE 'ABORTED_CONNECTS' OR
variable_name LIKE 'BINLOG_CACHE_DISK_USE';
END$$
SELECT variable_name FROM server_status;
variable_name
ABORTED_CONNECTS
BINLOG_CACHE_DISK_USE
DROP TABLE server_status;
SET GLOBAL event_scheduler=0;
explain select table_name from information_schema.views where
table_schema='test' and table_name='v1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	views	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 0 databases
explain select * from information_schema.tables;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Open_full_table; Scanned all databases
explain select * from information_schema.collations;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	collations	ALL	NULL	NULL	NULL	NULL	NULL	
explain select * from information_schema.tables where
table_schema='test' and table_name= 't1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Open_full_table; Scanned 0 databases
explain select table_name, table_type from information_schema.tables
where table_schema='test';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	TABLE_SCHEMA	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 1 database
explain select b.table_name
from information_schema.tables a, information_schema.columns b
where a.table_name='t1' and a.table_schema='test' and b.table_name=a.table_name;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Skip_open_table; Scanned 0 databases
1	SIMPLE	b	ALL	NULL	NULL	NULL	NULL	NULL	Using where; Open_frm_only; Scanned all databases; Using join buffer (flat, BNL join)
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'mysqltest';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = '';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'test';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
def	test	latin1	latin1_swedish_ci	NULL	
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='nonexisting';
count(*)
0
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='';
count(*)
0
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='';
count(*)
0
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='nonexisting';
count(*)
0
CREATE VIEW v1
AS SELECT *
FROM information_schema.tables;
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'v1';
VIEW_DEFINITION
select `information_schema`.`tables`.`TABLE_CATALOG` AS `TABLE_CATALOG`,`information_schema`.`tables`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`tables`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`tables`.`TABLE_TYPE` AS `TABLE_TYPE`,`information_schema`.`tables`.`ENGINE` AS `ENGINE`,`information_schema`.`tables`.`VERSION` AS `VERSION`,`information_schema`.`tables`.`ROW_FORMAT` AS `ROW_FORMAT`,`information_schema`.`tables`.`TABLE_ROWS` AS `TABLE_ROWS`,`information_schema`.`tables`.`AVG_ROW_LENGTH` AS `AVG_ROW_LENGTH`,`information_schema`.`tables`.`DATA_LENGTH` AS `DATA_LENGTH`,`information_schema`.`tables`.`MAX_DATA_LENGTH` AS `MAX_DATA_LENGTH`,`information_schema`.`tables`.`INDEX_LENGTH` AS `INDEX_LENGTH`,`information_schema`.`tables`.`DATA_FREE` AS `DATA_FREE`,`information_schema`.`tables`.`AUTO_INCREMENT` AS `AUTO_INCREMENT`,`information_schema`.`tables`.`CREATE_TIME` AS `CREATE_TIME`,`information_schema`.`tables`.`UPDATE_TIME` AS `UPDATE_TIME`,`information_schema`.`tables`.`CHECK_TIME` AS `CHECK_TIME`,`information_schema`.`tables`.`TABLE_COLLATION` AS `TABLE_COLLATION`,`information_schema`.`tables`.`CHECKSUM` AS `CHECKSUM`,`information_schema`.`tables`.`CREATE_OPTIONS` AS `CREATE_OPTIONS`,`information_schema`.`tables`.`TABLE_COMMENT` AS `TABLE_COMMENT`,`information_schema`.`tables`.`MAX_INDEX_LENGTH` AS `MAX_INDEX_LENGTH`,`information_schema`.`tables`.`TEMPORARY` AS `TEMPORARY` from `information_schema`.`tables`
DROP VIEW v1;
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME ='information_schema';
SCHEMA_NAME
information_schema
SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
TABLE_COLLATION
utf8mb3_bin
select * from information_schema.columns where table_schema = NULL;
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	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;
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	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
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
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
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
select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
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
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
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
select * from information_schema.schemata where schema_name = NULL;
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
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	IGNORED
select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;
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	IGNORED
select * from information_schema.tables where table_schema = NULL;
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	MAX_INDEX_LENGTH	TEMPORARY
select * from information_schema.tables where table_catalog = NULL;
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	MAX_INDEX_LENGTH	TEMPORARY
select * from information_schema.tables where table_name = NULL;
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	MAX_INDEX_LENGTH	TEMPORARY
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL;
TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL;
TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	ALGORITHM
select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	ALGORITHM
explain extended select 1 from information_schema.tables;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
Warnings:
Note	1003	select 1 AS `1` from `information_schema`.`tables`
use information_schema;
show events;
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
show events from information_schema;
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
show events where Db= 'information_schema';
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
use test;
#
# Bug#34166 Server crash in SHOW OPEN TABLES and prelocking
#
drop table if exists t1;
drop function if exists f1;
create table t1 (a int);
create function f1() returns int
begin
insert into t1 (a) values (1);
return 0;
end|
show open tables where f1()=0;
show open tables where f1()=0;
drop table t1;
drop function f1;
connect  conn1, localhost, root,,;
connection conn1;
select * from information_schema.tables where 1=sleep(100000);
connection default;
connection conn1;
Got one of the listed errors
connection default;
disconnect conn1;
connect  conn1, localhost, root,,;
connection conn1;
select * from information_schema.columns where 1=sleep(100000);
connection default;
connection conn1;
Got one of the listed errors
connection default;
disconnect conn1;
explain select count(*) from information_schema.tables;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
explain select count(*) from information_schema.columns;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	columns	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
explain select count(*) from information_schema.views;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	views	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
set global init_connect="drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;";
select * from information_schema.global_variables where variable_name='init_connect';
VARIABLE_NAME	VARIABLE_VALUE
INIT_CONNECT	drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
select * from information_schema.global_variables where variable_name like 'init%' order by variable_name;
VARIABLE_NAME	VARIABLE_VALUE
INIT_CONNECT	drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
INIT_FILE	
INIT_SLAVE	
set global init_connect="";
create table t0 select * from information_schema.global_status where VARIABLE_NAME='COM_SELECT';
SELECT 1;
1
1
select a.VARIABLE_VALUE - b.VARIABLE_VALUE from t0 b, information_schema.global_status a
where a.VARIABLE_NAME = b.VARIABLE_NAME;
a.VARIABLE_VALUE - b.VARIABLE_VALUE
2
drop table t0;
CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1;
SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
CREATE_OPTIONS
key_block_size=1
DROP TABLE t1;
SET TIMESTAMP=@@TIMESTAMP + 10000000;
SELECT 'NOT_OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0;
TEST_RESULT
SET TIMESTAMP=DEFAULT;
#
# Bug #50276: Security flaw in INFORMATION_SCHEMA.TABLES
#
CREATE DATABASE db1;
USE db1;
CREATE TABLE t1 (id INT);
CREATE USER nonpriv;
USE test;
connect  nonpriv_con, localhost, nonpriv,,;
connection nonpriv_con;
# connected as nonpriv
# Should return 0
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
COUNT(*)
0
USE INFORMATION_SCHEMA;
# Should return 0
SELECT COUNT(*) FROM TABLES WHERE TABLE_NAME='t1';
COUNT(*)
0
connection default;
# connected as root
disconnect nonpriv_con;
DROP USER nonpriv;
DROP TABLE db1.t1;
DROP DATABASE db1;

Bug#54422 query with = 'variables'

CREATE TABLE variables(f1 INT);
SELECT COLUMN_DEFAULT, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables';
COLUMN_DEFAULT	TABLE_NAME
NULL	variables
DROP TABLE variables;
#
# Bug #53814: NUMERIC_PRECISION for unsigned bigint field is 19, 
# should be 20
#
CREATE TABLE ubig (a BIGINT, b BIGINT UNSIGNED);
SELECT TABLE_NAME, COLUMN_NAME, NUMERIC_PRECISION 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ubig';
TABLE_NAME	COLUMN_NAME	NUMERIC_PRECISION
ubig	a	19
ubig	b	20
INSERT INTO ubig VALUES (0xFFFFFFFFFFFFFFFF,0xFFFFFFFFFFFFFFFF);
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
SELECT length(CAST(b AS CHAR)) FROM ubig;
length(CAST(b AS CHAR))
20
DROP TABLE ubig;
select 1 from information_schema.tables where table_schema=repeat('a', 2000);
1
grant usage on *.* to mysqltest_1@localhost;
connect  con1, localhost, mysqltest_1,,;
connection con1;
select 1 from information_schema.tables where table_schema=repeat('a', 2000);
1
connection default;
disconnect con1;
drop user mysqltest_1@localhost;
End of 5.1 tests.
#
# Additional test for WL#3726 "DDL locking for all metadata objects"
# To avoid possible deadlocks process of filling of I_S tables should
# use high-priority metadata lock requests when opening tables.
# Below we just test that we really use high-priority lock request
# since reproducing a deadlock will require much more complex test.
#
drop tables if exists t1, t2, t3;
create table t1 (i int);
create table t2 (j int primary key auto_increment);
connect  con3726_1,localhost,root,,test;
connection con3726_1;
lock table t2 read;
connect  con3726_2,localhost,root,,test;
connection con3726_2;
# RENAME below will be blocked by 'lock table t2 read' above but
# will add two pending requests for exclusive metadata locks.
rename table t2 to t3;
connection default;
# These statements should not be blocked by pending lock requests
select table_name, column_name, data_type from information_schema.columns
where table_schema = 'test' and table_name in ('t1', 't2') order by table_name, column_name;
table_name	column_name	data_type
t1	i	int
t2	j	int
select table_name, auto_increment from information_schema.tables
where table_schema = 'test' and table_name in ('t1', 't2') order by table_name;
table_name	auto_increment
t1	NULL
t2	1
connection con3726_1;
unlock tables;
connection con3726_2;
connection default;
disconnect con3726_1;
disconnect con3726_2;
drop tables t1, t3;
EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	KEY_COLUMN_USAGE	ALL	NULL	NULL	NULL	NULL	NULL	Open_full_table; Scanned all databases
EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	PARTITIONS	ALL	NULL	TABLE_NAME	NULL	NULL	NULL	Using where; Open_full_table; Scanned 1 database
EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA='test';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	REFERENTIAL_CONSTRAINTS	ALL	NULL	CONSTRAINT_SCHEMA	NULL	NULL	NULL	Using where; Open_full_table; Scanned 1 database
EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	TABLE_CONSTRAINTS	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Open_full_table; Scanned 0 databases
EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE EVENT_OBJECT_SCHEMA='test';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	TRIGGERS	ALL	NULL	EVENT_OBJECT_SCHEMA	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 1 database
create table information_schema.t1 (f1 INT);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
drop table information_schema.t1;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
drop temporary table if exists information_schema.t1;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
create temporary table information_schema.t1 (f1 INT);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
drop view information_schema.v1;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
create view information_schema.v1;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
create trigger mysql.trg1 after insert on information_schema.t1 for each row set @a=1;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
create table t1 select * from information_schema.t1;
ERROR 42S02: Unknown table 't1' in information_schema
CREATE TABLE t1(f1 char(100));
REPAIR TABLE t1, information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CHECKSUM TABLE t1, information_schema.tables;
Table	Checksum
test.t1	0
information_schema.tables	0
ANALYZE TABLE t1, information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CHECK TABLE t1, information_schema.tables;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
information_schema.tables	check	note	The storage engine for the table doesn't support check
OPTIMIZE TABLE t1, information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
RENAME TABLE v1 to v2, information_schema.tables to t2;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE t1, information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
LOCK TABLES t1 READ, information_schema.tables READ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE t1;
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
LEFT JOIN INFORMATION_SCHEMA.COLUMNS
USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
WHERE COLUMNS.TABLE_SCHEMA = 'test'
AND COLUMNS.TABLE_NAME = 't1';
TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME	TABLE_CATALOG	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
#
# A test case for Bug#56540 "Exception (crash) in sql_show.cc
# during rqg_info_schema test on Windows"
# Ensure that we never access memory of a closed table,
# in particular, never access table->field[] array.
# Before the fix, the below test case, produced
# valgrind errors.
#
drop table if exists t1;
drop view if exists v1;
create table t1 (a int, b int);
create view v1 as select t1.a, t1.b from t1;
alter table t1 change b c int;
lock table t1 read;
connect con1, localhost, root,,;
connection con1;
flush tables;
flush tables t1;
connection default;
select * from information_schema.views where table_schema='test';
TABLE_CATALOG	def
TABLE_SCHEMA	test
TABLE_NAME	v1
VIEW_DEFINITION	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
CHECK_OPTION	NONE
IS_UPDATABLE	
DEFINER	root@localhost
SECURITY_TYPE	DEFINER
CHARACTER_SET_CLIENT	latin1
COLLATION_CONNECTION	latin1_swedish_ci
ALGORITHM	UNDEFINED
Warnings:
Level	Warning
Code	1356
Message	View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
unlock tables;
#
# Cleanup.
#
connection con1;
# Reaping 'flush tables'
disconnect con1;
connection default;
drop table t1;
drop view v1;
#
# Test for bug #12828477 - "MDL SUBSYSTEM CREATES BIG OVERHEAD FOR
#                           CERTAIN QUERIES TO INFORMATION_SCHEMA".
#
# Check that metadata locks which are acquired during the process
# of opening tables/.FRMs/.TRG files while filling I_S table are
# not kept to the end of statement. Keeping the locks has caused
# performance problems in cases when big number of tables (.FRMs
# or .TRG files) were scanned as cost of new lock acquisition has
# increased linearly.
drop database if exists mysqltest;
create database mysqltest;
use mysqltest;
create table t0 (i int);
create table t1 (j int);
create table t2 (k int);
#
# Test that we don't keep locks in case when we to fill
# I_S table we perform full-blown table open.
#
# Acquire lock on 't2' so upcoming RENAME is
# blocked.
lock tables t2 read;
connect  con12828477_1, localhost, root,,mysqltest;
# The below RENAME should wait on 't2' while
# keeping X lock on 't1'.
rename table t1 to t3, t2 to t1, t3 to t2;
connect  con12828477_2, localhost, root,,mysqltest;
# Wait while the above RENAME is blocked.
# Issue query to I_S which will open 't0' and get
# blocked on 't1' because of RENAME.
select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name='t0' union select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name<>'t0' order by table_name;
connect  con12828477_3, localhost, root,,mysqltest;
# Wait while the above SELECT is blocked.
#
# Check that it holds no lock on 't0' so it can be renamed.
rename table t0 to t4;
connection default;
#
# Unblock the first RENAME.
unlock tables;
connection con12828477_1;
# Reap the first RENAME
connection con12828477_2;
# Reap SELECT to I_S.
table_name	auto_increment
t0	NULL
t1	NULL
t2	NULL
connection default;
#
# Now test that we don't keep locks in case when we to fill
# I_S table we read .FRM or .TRG file only (this was the case
# for which problem existed).
#
rename table t4 to t0;
# Acquire lock on 't2' so upcoming RENAME is
# blocked.
lock tables t2 read;
connection con12828477_1;
# The below RENAME should wait on 't2' while
# keeping X lock on 't1'.
rename table t1 to t3, t2 to t1, t3 to t2;
connection con12828477_2;
# Wait while the above RENAME is blocked.
# Issue query to I_S which will open 't0' and get
# blocked on 't1' because of RENAME.
select event_object_table, trigger_name from information_schema.triggers where event_object_schema='mysqltest';
connection con12828477_3;
# Wait while the above SELECT is blocked.
#
# Check that it holds no lock on 't0' so it can be renamed.
rename table t0 to t4;
connection default;
#
# Unblock the first RENAME.
unlock tables;
connection con12828477_1;
# Reap the first RENAME
connection con12828477_2;
# Reap SELECT to I_S.
event_object_table	trigger_name
connection default;
disconnect con12828477_1;
disconnect con12828477_2;
disconnect con12828477_3;
#
# MDEV-3818: Query against view over IS tables worse than equivalent query without view
#
create view v1 as select table_schema, table_name, column_name from information_schema.columns;
explain extended
select column_name from v1
where (table_schema = "osm") and (table_name = "test");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	columns	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 0 databases
Warnings:
Note	1003	select `information_schema`.`columns`.`COLUMN_NAME` AS `column_name` from `information_schema`.`columns` where `information_schema`.`columns`.`TABLE_SCHEMA` = 'osm' and `information_schema`.`columns`.`TABLE_NAME` = 'test'
explain extended
select information_schema.columns.column_name as column_name
from information_schema.columns
where (information_schema.columns.table_schema = 'osm') and (information_schema.columns.table_name = 'test');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	columns	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 0 databases
Warnings:
Note	1003	select `information_schema`.`columns`.`COLUMN_NAME` AS `column_name` from `information_schema`.`columns` where `information_schema`.`columns`.`TABLE_SCHEMA` = 'osm' and `information_schema`.`columns`.`TABLE_NAME` = 'test'
drop view v1;
#
# Clean-up.
drop database mysqltest;
#
# Test for bug #16869534 - "QUERYING SUBSET OF COLUMNS DOESN'T USE TABLE
#                           CACHE; OPENED_TABLES INCREASES"
#
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT VARIABLE_VALUE INTO @val1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE
VARIABLE_NAME LIKE 'Opened_tables';
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES;
# The below SELECT query should give same output as above SELECT query.
SELECT VARIABLE_VALUE INTO @val2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE
VARIABLE_NAME LIKE 'Opened_tables';
# The below select should return '1'
SELECT @val1 = @val2;
@val1 = @val2
1
#
# End of 5.5 tests
#
# 
# MDEV-5723: mysqldump -uroot unusable for multi-database operations, checks all databases
# 
drop database if exists db1;
connect  con1,localhost,root,,;
connection con1;
create database db1;
use db1;
create table t1 (a int);
create table t2 (a int);
create table t3 (a int);
create database mysqltest;
use mysqltest;
create table t1 (a int);
create table t2 (a int);
create table t3 (a int);
flush tables;
flush status;
SELECT 
LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA 
FROM 
INFORMATION_SCHEMA.FILES 
WHERE 
FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND 
LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME 
FROM INFORMATION_SCHEMA.FILES 
WHERE 
FILE_TYPE = 'DATAFILE' AND 
TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_SCHEMA IN ('db1')
)
) 
GROUP BY 
LOGFILE_GROUP_NAME, FILE_NAME, ENGINE 
ORDER BY 
LOGFILE_GROUP_NAME;
LOGFILE_GROUP_NAME	FILE_NAME	TOTAL_EXTENTS	INITIAL_SIZE	ENGINE	EXTRA
# This must have Opened_tables=3, not 6.
show status like 'Opened_tables';
Variable_name	Value
Opened_tables	3
drop database mysqltest;
drop database db1;
connection default;
disconnect con1;
set global sql_mode=default;
USE test;
#
# End of 10.0 tests
#
#
# Start of 10.1 tests
#
#
# MDEV-13242 Wrong results for queries with row constructors and information_schema
#
CREATE TABLE tt1(c1 INT);
CREATE TABLE tt2(c2 INT);
SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1', 'c1'));
count(*)
1
SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt2', 'c2'));
count(*)
1
SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2'));
count(*)
2
SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (SELECT 'tt1','c1' FROM dual UNION SELECT 'tt2', 'c2' FROM dual);
count(*)
2
SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name='tt1' AND column_name='c1') OR (table_name='tt2' AND column_name='c2');
count(*)
2
SELECT column_name FROM information_schema.columns WHERE (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2')) ORDER BY column_name;
column_name
c1
c2
DROP TABLE tt1, tt2;
#
# MDEV-13242 Wrong results for queries with row constructors and information_schema
#
SELECT SCHEMA_NAME from information_schema.schemata where schema_name='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
SCHEMA_NAME
SELECT SCHEMA_NAME from information_schema.schemata where schema_name=REPEAT('a',193);
SCHEMA_NAME
#
# End of 10.1 tests
#
#
# MDEV-14836: Assertion `m_status == DA_ERROR' failed in
# Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED
#
SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
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	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
Warnings:
Warning	1931	Query execution was interrupted. The query examined at least ### rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete
#
# MDEV-24179: AAssertion `m_status == DA_ERROR || m_status == DA_OK ||
# m_status == DA_OK_BULK' failed in Diagnostics_area::message()
#
call mtr.add_suppression("Sort aborted.*");
DROP DATABASE test;
CREATE DATABASE test;
USE test;
CREATE VIEW v AS SELECT table_schema  AS object_schema, table_name  AS object_name, table_type AS object_type FROM information_schema.tables ORDER BY object_schema;
SELECT * FROM v LIMIT ROWS EXAMINED 9;
ERROR HY000: Sort aborted: 
DROP VIEW v;
#
# MDEV-23408 Wrong result upon query from I_S and further Assertion `!alias_arg || strlen(alias_arg->str) == alias_arg->length' failed with certain connection charset
#
CREATE TABLE t (a INT);
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=CONCAT('t',0x00,'1');
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	MAX_INDEX_LENGTH	TEMPORARY
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=CONCAT('test',0x00,'1');
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	MAX_INDEX_LENGTH	TEMPORARY
DROP TABLE t;
CREATE TABLE `a/~.b` (a INT);
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='a/~.b';
TABLE_SCHEMA	TABLE_NAME
test	a/~.b
DROP TABLE `a/~.b`;
CREATE DATABASE `a/~.b`;
CREATE TABLE `a/~.b`.t1 (a INT);
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='a/~.b';
TABLE_SCHEMA	TABLE_NAME
a/~.b	t1
DROP DATABASE `a/~.b`;
#
# End of 10.2 Test
#
#
# MDEV-21201:No records produced in information_schema query,
# depending on projection
#
create table t (i int, constraint a check (i > 0));
select
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE
from information_schema.TABLE_CONSTRAINTS tc
join information_schema.CHECK_CONSTRAINTS cc
using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
;
TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
mysql	global_priv	Priv	json_valid(`Priv`)
test	t	a	`i` > 0
select
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE
from information_schema.CHECK_CONSTRAINTS cc
join information_schema.TABLE_CONSTRAINTS tc
using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
;
TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
mysql	global_priv	Priv	json_valid(`Priv`)
test	t	a	`i` > 0
select
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE
from information_schema.TABLE_CONSTRAINTS tc
NATURAL join information_schema.CHECK_CONSTRAINTS cc
;
TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
mysql	global_priv	Priv	json_valid(`Priv`)
test	t	a	`i` > 0
select
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE
from information_schema.CHECK_CONSTRAINTS cc
NATURAL join information_schema.TABLE_CONSTRAINTS tc
;
TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
mysql	global_priv	Priv	json_valid(`Priv`)
test	t	a	`i` > 0
select
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE,
tc.CONSTRAINT_CATALOG,
tc.CONSTRAINT_SCHEMA
from information_schema.TABLE_CONSTRAINTS tc
join information_schema.CHECK_CONSTRAINTS cc
using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
;
TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE	CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA
mysql	global_priv	Priv	json_valid(`Priv`)	def	mysql
test	t	a	`i` > 0	def	test
drop table t;
#
# MDEV-24230 subquery on information_schema fails with error message
#
create table t1 (n int);
create table t2 (n int);
insert into t1 set n = (select table_rows from information_schema.tables where table_name='t2');
drop table t1, t2;
#
# MDEV-24593 Signal 11 when group by primary key of table joined to information_schema.columns
#
create table t1 (f varchar(64) primary key);
select f from information_schema.columns i
inner join t1 on f=i.column_name
group by f;
f
drop table t1;
#
# MDEV-24929 Server crash in thr_multi_unlock or in
# get_schema_tables_result upon select from I_S with joins
#
CREATE TABLE t1 (a TIMESTAMP, KEY (a));
INSERT INTO t1 VALUES ('2012-12-12'),('2021-11-11');
SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.ROUTINES) ON (t1b.a IS NULL);
count(*)
2
SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.PROFILING) ON (t1b.a IS NULL);
count(*)
2
DROP TABLE t1;
#
# MDEV-24868 Server crashes in optimize_schema_tables_memory_usage after select from information_schema.innodb_sys_columns
#
create table t1 ( name varchar(64) character set utf8, len int);
select * from t1 where (name, len) in (select  name, len from information_schema.innodb_sys_columns having len = 8);
name	len
drop table t1;
#
# MDEV-20254 Problems with EMPTY_STRING_IS_NULL and I_S tables
#
SET SQL_MODE= 'EMPTY_STRING_IS_NULL';
CREATE OR REPLACE TABLE t1 AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0;
SHOW returned: CREATE TABLE `t1` (
  `TABLE_NAME` varchar(64) CHARACTER SET utf8mb3 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE `t1` (
  `TABLE_NAME` varchar(64) CHARACTER SET utf8mb3 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `TABLE_NAME` varchar(64) CHARACTER SET utf8mb3 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
SET SQL_MODE=DEFAULT;
SET SQL_MODE= 'EMPTY_STRING_IS_NULL';
CREATE OR REPLACE TABLE t1 AS SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0;
DROP TABLE t1;
# Executing the statement returned from SHOW CREATE TABLE
DROP TABLE t1;
SET SQL_MODE=DEFAULT;
#
# MDEV-27673 Warning after "select progress from information_schema.processlist"
#
select progress from information_schema.processlist limit 1;
progress
0.000
#
# End of 10.3 tests
#
#
# Start of 10.5 tests
#
#
# MDEV-26507 Assertion `tmp != ((long long) 0x8000000000000000LL)' failed in TIME_from_longlong_datetime_packed
#
CREATE TABLE t1 (a int);
CREATE ALGORITHM=TEMPTABLE VIEW i AS
SELECT a.created
FROM t1 w JOIN INFORMATION_SCHEMA.routines a
WHERE a.routine_name='not existing'
  ORDER BY a.last_altered;
SET SESSION sql_mode='ALLOW_INVALID_DATES';
SELECT * FROM i;
created
SET SESSION sql_mode=DEFAULT;
DROP VIEW i;
DROP TABLE t1;
#
# End of 10.5 tests
#