1
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2
DROP DATABASE IF EXISTS test1;
3
DROP DATABASE IF EXISTS test2;
6
CREATE TABLE test1.t1 (f1 VARCHAR(20)) ENGINE = <engine_to_be_used>;
7
CREATE TABLE test1.t2 (f1 VARCHAR(20)) ENGINE = <engine_to_be_used>;
8
CREATE TABLE test2.t1 (f1 VARCHAR(20)) ENGINE = <engine_to_be_used>;
11
IF(INSTR(table_comment,'InnoDB free') = 0
12
AND INSTR(table_comment,'number_of_replicas') = 0,
13
LENGTH(table_comment),
14
INSTR(table_comment,'InnoDB free')
15
+ INSTR(table_comment,'number_of_replicas') - 1))
17
'-----------------------------------------------------' AS "Separator"
18
FROM information_schema.tables
19
WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'
20
ORDER BY table_schema,table_name;
38
TABLE_COLLATION latin1_swedish_ci
43
Separator -----------------------------------------------------
61
TABLE_COLLATION latin1_swedish_ci
66
Separator -----------------------------------------------------
84
TABLE_COLLATION latin1_swedish_ci
89
Separator -----------------------------------------------------
90
SHOW TABLES FROM test1;
94
SHOW TABLES FROM test2;
97
DROP USER testuser1@localhost;
98
CREATE USER testuser1@localhost;
99
GRANT SELECT ON test1.* TO testuser1@localhost;
100
# Establish connection testuser1 (user=testuser1)
103
IF(INSTR(table_comment,'InnoDB free') = 0
104
AND INSTR(table_comment,'number_of_replicas') = 0,
105
LENGTH(table_comment),
106
INSTR(table_comment,'InnoDB free')
107
+ INSTR(table_comment,'number_of_replicas') - 1))
109
'-----------------------------------------------------' AS "Separator"
110
FROM information_schema.tables
111
WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'
112
ORDER BY table_schema,table_name;
116
TABLE_TYPE BASE TABLE
123
MAX_DATA_LENGTH #MDL#
130
TABLE_COLLATION latin1_swedish_ci
135
Separator -----------------------------------------------------
139
TABLE_TYPE BASE TABLE
146
MAX_DATA_LENGTH #MDL#
153
TABLE_COLLATION latin1_swedish_ci
158
Separator -----------------------------------------------------
159
SHOW TABLES FROM test1;
163
SHOW TABLES FROM test2;
164
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'test2'
165
# Switch to connection default and close connection testuser1
166
DROP USER testuser1@localhost;