← Back to branch summary

~jlukas79/+junk/mysql-server

« back to all changes in this revision

Viewing changes to mysql-test/r/information_schema.result

manual merge 6.0-main --> 6.0-bka-review

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/r/information_schema.result
33
33
create table mysqltest.t4(a int);
34
34
create table t5 (id int auto_increment primary key);
35
35
insert into t5 values (10);
36
 
create view v1 (c) as select table_name from information_schema.TABLES where table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status';
 
36
create view v1 (c) as select table_name
 
37
from information_schema.TABLES
 
38
where table_name not like 'ndb_%' and
 
39
table_name not like 'falcon%';
37
40
select * from v1;
38
41
c
39
42
CHARACTER_SETS
840
843
drop table t1;
841
844
grant select on test.* to mysqltest_4@localhost;
842
845
SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 
843
 
where COLUMN_NAME='TABLE_NAME';
 
846
WHERE COLUMN_NAME='TABLE_NAME' AND TABLE_NAME NOT LIKE 'FALCON%';
844
847
TABLE_NAME      COLUMN_NAME     PRIVILEGES
845
848
COLUMNS TABLE_NAME      select
846
849
COLUMN_PRIVILEGES       TABLE_NAME      select
856
859
delete from mysql.user where user='mysqltest_4';
857
860
delete from mysql.db where user='mysqltest_4';
858
861
flush privileges;
859
 
SELECT table_schema, count(*) FROM information_schema.TABLES where table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA;
 
862
SELECT table_schema, count(*) FROM information_schema.TABLES
 
863
WHERE table_name NOT LIKE 'ndb_%' AND table_name NOT LIKE 'falcon%' GROUP BY TABLE_SCHEMA;
860
864
table_schema    count(*)
861
865
information_schema      29
862
866
mysql   24
1230
1234
DROP FUNCTION f1;
1231
1235
DROP PROCEDURE p1;
1232
1236
DROP USER mysql_bug20230@localhost;
1233
 
SELECT t.table_name, c1.column_name
1234
 
FROM information_schema.tables t
1235
 
INNER JOIN
1236
 
information_schema.columns c1
1237
 
ON t.table_schema = c1.table_schema AND
1238
 
t.table_name = c1.table_name
1239
 
WHERE t.table_schema = 'information_schema' AND
1240
 
c1.ordinal_position =
1241
 
( SELECT COALESCE(MIN(c2.ordinal_position),1)
1242
 
FROM information_schema.columns c2
1243
 
WHERE c2.table_schema = t.table_schema AND
1244
 
c2.table_name = t.table_name AND
1245
 
c2.column_name LIKE '%SCHEMA%'
1246
 
        );
1247
 
table_name      column_name
1248
 
CHARACTER_SETS  CHARACTER_SET_NAME
1249
 
COLLATIONS      COLLATION_NAME
1250
 
COLLATION_CHARACTER_SET_APPLICABILITY   COLLATION_NAME
1251
 
COLUMNS TABLE_SCHEMA
1252
 
COLUMN_PRIVILEGES       TABLE_SCHEMA
1253
 
ENGINES ENGINE
1254
 
EVENTS  EVENT_SCHEMA
1255
 
FILES   TABLE_SCHEMA
1256
 
GLOBAL_STATUS   VARIABLE_NAME
1257
 
GLOBAL_VARIABLES        VARIABLE_NAME
1258
 
KEY_COLUMN_USAGE        CONSTRAINT_SCHEMA
1259
 
PARAMETERS      SPECIFIC_SCHEMA
1260
 
PARTITIONS      TABLE_SCHEMA
1261
 
PLUGINS PLUGIN_NAME
1262
 
PROCESSLIST     ID
1263
 
PROFILING       QUERY_ID
1264
 
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
1265
 
ROUTINES        ROUTINE_SCHEMA
1266
 
SCHEMATA        SCHEMA_NAME
1267
 
SCHEMA_PRIVILEGES       TABLE_SCHEMA
1268
 
SESSION_STATUS  VARIABLE_NAME
1269
 
SESSION_VARIABLES       VARIABLE_NAME
1270
 
STATISTICS      TABLE_SCHEMA
1271
 
TABLES  TABLE_SCHEMA
1272
 
TABLE_CONSTRAINTS       CONSTRAINT_SCHEMA
1273
 
TABLE_PRIVILEGES        TABLE_SCHEMA
1274
 
TRIGGERS        TRIGGER_SCHEMA
1275
 
USER_PRIVILEGES GRANTEE
1276
 
VIEWS   TABLE_SCHEMA
1277
 
SELECT t.table_name, c1.column_name
1278
 
FROM information_schema.tables t
1279
 
INNER JOIN
1280
 
information_schema.columns c1
1281
 
ON t.table_schema = c1.table_schema AND
1282
 
t.table_name = c1.table_name
1283
 
WHERE t.table_schema = 'information_schema' AND
1284
 
c1.ordinal_position =
1285
 
( SELECT COALESCE(MIN(c2.ordinal_position),1)
1286
 
FROM information_schema.columns c2
1287
 
WHERE c2.table_schema = 'information_schema' AND
1288
 
c2.table_name = t.table_name AND
1289
 
c2.column_name LIKE '%SCHEMA%'
1290
 
        );
1291
 
table_name      column_name
1292
 
CHARACTER_SETS  CHARACTER_SET_NAME
1293
 
COLLATIONS      COLLATION_NAME
1294
 
COLLATION_CHARACTER_SET_APPLICABILITY   COLLATION_NAME
1295
 
COLUMNS TABLE_SCHEMA
1296
 
COLUMN_PRIVILEGES       TABLE_SCHEMA
1297
 
ENGINES ENGINE
1298
 
EVENTS  EVENT_SCHEMA
1299
 
FILES   TABLE_SCHEMA
1300
 
GLOBAL_STATUS   VARIABLE_NAME
1301
 
GLOBAL_VARIABLES        VARIABLE_NAME
1302
 
KEY_COLUMN_USAGE        CONSTRAINT_SCHEMA
1303
 
PARAMETERS      SPECIFIC_SCHEMA
1304
 
PARTITIONS      TABLE_SCHEMA
1305
 
PLUGINS PLUGIN_NAME
1306
 
PROCESSLIST     ID
1307
 
PROFILING       QUERY_ID
1308
 
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
1309
 
ROUTINES        ROUTINE_SCHEMA
1310
 
SCHEMATA        SCHEMA_NAME
1311
 
SCHEMA_PRIVILEGES       TABLE_SCHEMA
1312
 
SESSION_STATUS  VARIABLE_NAME
1313
 
SESSION_VARIABLES       VARIABLE_NAME
1314
 
STATISTICS      TABLE_SCHEMA
1315
 
TABLES  TABLE_SCHEMA
1316
 
TABLE_CONSTRAINTS       CONSTRAINT_SCHEMA
1317
 
TABLE_PRIVILEGES        TABLE_SCHEMA
1318
 
TRIGGERS        TRIGGER_SCHEMA
1319
 
USER_PRIVILEGES GRANTEE
1320
 
VIEWS   TABLE_SCHEMA
1321
1237
SELECT MAX(table_name) FROM information_schema.tables;
1322
1238
MAX(table_name)
1323
1239
VIEWS
1373
1289
from information_schema.tables t
1374
1290
inner join information_schema.columns c1
1375
1291
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
1376
 
where t.table_schema = 'information_schema' and
 
1292
where t.table_schema = 'information_schema' AND
 
1293
t.table_name not like 'falcon%' AND
1377
1294
c1.ordinal_position =
1378
1295
(select isnull(c2.column_type) -
1379
1296
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
1688
1605
drop procedure p1;
1689
1606
drop procedure p2;
1690
1607
drop function f1;
 
1608
#
 
1609
# Bug#35955 Regression: I_S table cause reduced privilege requirements in SELECT.
 
1610
#
 
1611
DROP DATABASE IF EXISTS testdb1;
 
1612
CREATE DATABASE testdb1;
 
1613
USE testdb1;
 
1614
CREATE TABLE t1 (c1 int);
 
1615
CREATE TABLE t2 (c2 int);
 
1616
GRANT SELECT ON testdb1.t1 TO mysqluser_35955@localhost;
 
1617
SELECT * FROM INFORMATION_SCHEMA.tables, t1;
 
1618
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   c1
 
1619
SELECT * FROM t2;
 
1620
ERROR 42000: SELECT command denied to user 'mysqluser_35955'@'localhost' for table 't2'
 
1621
SELECT * FROM t2, INFORMATION_SCHEMA.tables;
 
1622
ERROR 42000: SELECT command denied to user 'mysqluser_35955'@'localhost' for table 't2'
 
1623
SELECT * FROM INFORMATION_SCHEMA.tables, t2;
 
1624
ERROR 42000: SELECT command denied to user 'mysqluser_35955'@'localhost' for table 't2'
 
1625
DROP TABLE t1,t2;
 
1626
DROP DATABASE testdb1;
 
1627
DROP USER mysqluser_35955@localhost;
 
1628
USE test;
 
1629
#
 
1630
# Additional test for WL#3726 "DDL locking for all metadata objects"
 
1631
# To avoid possible deadlocks process of filling of I_S tables should
 
1632
# use high-priority metadata lock requests when opening tables.
 
1633
# Below we just test that we really use high-priority lock request
 
1634
# since reproducing a deadlock will require much more complex test.
 
1635
#
 
1636
drop tables if exists t1, t2, t3;
 
1637
create table t1 (i int);
 
1638
create table t2 (j int primary key auto_increment);
 
1639
# Switching to connection 'con3726_1'
 
1640
lock table t2 read;
 
1641
# Switching to connection 'con3726_2'
 
1642
# RENAME below will be blocked by 'lock table t2 read' above but
 
1643
# will add two pending requests for exclusive metadata locks.
 
1644
rename table t2 to t3;
 
1645
# Switching to connection 'default'
 
1646
# These statements should not be blocked by pending lock requests
 
1647
select table_name, column_name, data_type from information_schema.columns
 
1648
where table_schema = 'test' and table_name in ('t1', 't2');
 
1649
table_name      column_name     data_type
 
1650
t1      i       int
 
1651
t2      j       int
 
1652
select table_name, auto_increment from information_schema.tables
 
1653
where table_schema = 'test' and table_name in ('t1', 't2');
 
1654
table_name      auto_increment
 
1655
t1      NULL
 
1656
t2      1
 
1657
# Switching to connection 'con3726_1'
 
1658
unlock tables;
 
1659
# Switching to connection 'con3726_2'
 
1660
# Switching to connection 'default'
 
1661
drop tables t1, t3;