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
|
#
# Specific tests for case sensitive file systems
# i.e. lower_case_filesystem=OFF
#
-- source include/have_case_sensitive_file_system.inc
-- source include/not_embedded.inc
-- source include/have_perfschema.inc
set GLOBAL sql_mode="";
set LOCAL sql_mode="";
connect (master,localhost,root,,);
connection master;
create database d1;
grant all on d1.* to 'sample'@'localhost' identified by 'password';
flush privileges;
connect (sample,localhost,sample,password,d1);
connection sample;
select database();
--error ER_DBACCESS_DENIED_ERROR
create database d2;
--error ER_DBACCESS_DENIED_ERROR
create database D1;
disconnect sample;
--source include/wait_until_disconnected.inc
connection master;
drop user 'sample'@'localhost';
drop database if exists d1;
disconnect master;
--source include/wait_until_disconnected.inc
connection default;
# End of 4.1 tests
#
# Bug#41049 does syntax "grant" case insensitive?
#
CREATE DATABASE d1;
USE d1;
CREATE TABLE T1(f1 INT);
CREATE TABLE t1(f1 INT);
GRANT SELECT ON T1 to user_1@localhost;
connect (con1,localhost,user_1,,d1);
--error ER_TABLEACCESS_DENIED_ERROR
select * from t1;
select * from T1;
connection default;
GRANT SELECT ON t1 to user_1@localhost;
connection con1;
--sorted_result
select * from information_schema.table_privileges;
connection default;
disconnect con1;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
DROP USER user_1@localhost;
DROP DATABASE d1;
USE test;
CREATE DATABASE db1;
USE db1;
CREATE PROCEDURE p1() BEGIN END;
CREATE FUNCTION f1(i INT) RETURNS INT RETURN i+1;
GRANT USAGE ON db1.* to user_1@localhost;
GRANT EXECUTE ON PROCEDURE db1.P1 to user_1@localhost;
GRANT EXECUTE ON FUNCTION db1.f1 to user_1@localhost;
GRANT UPDATE ON db1.* to USER_1@localhost;
connect (con1,localhost,user_1,,db1);
call p1();
call P1();
select f1(1);
connect (con2,localhost,USER_1,,db1);
--error ER_PROCACCESS_DENIED_ERROR
call p1();
--error ER_PROCACCESS_DENIED_ERROR
call P1();
--error ER_PROCACCESS_DENIED_ERROR
select f1(1);
connection default;
disconnect con1;
disconnect con2;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_1@localhost;
DROP FUNCTION f1;
DROP PROCEDURE p1;
DROP USER user_1@localhost;
DROP USER USER_1@localhost;
DROP DATABASE db1;
use test;
# End of 5.0 tests
--echo #
--echo # Extra test coverage for Bug#56595 RENAME TABLE causes assert on OS X
--echo #
CREATE TABLE t1(a INT);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1;
RENAME TABLE t1 TO T1;
ALTER TABLE T1 RENAME t1;
DROP TABLE t1;
#
# MDEV-13912 mysql_upgrade: case (in)sensitivity for stored procedures
#
create database TEST;
create procedure TEST.pr() begin end;
create procedure test.pr() begin end;
--exec $MYSQL_UPGRADE --force 2>&1
drop procedure test.pr;
drop database TEST;
# End of 5.5 tests
#
# MDEV-9014 SHOW TRIGGERS not case sensitive
#
create table t1 (a int);
create trigger t1_bi before insert on t1 for each row set new.a= 1;
show triggers like '%T1%';
drop table t1;
let $datadir= `select @@datadir`;
remove_file $datadir/mariadb_upgrade_info;
set GLOBAL sql_mode=default;
--echo #
--echo # MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode
--echo #
--echo # Compatibility schema names respect the filesystem case sensitivity
--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a MARIADB_SCHEMA.date);
--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a Mariadb_schema.date);
CREATE TABLE t1 (a mariadb_schema.date);
DROP TABLE t1;
--echo # End of 10.3 tests
--echo #
--echo # MDEV-36979 Same alias name with different case on same table is not working in functions
--echo #
create table t1 (a int);
insert t1 values (1);
create table T1 (b int);
insert T1 values (10);
delimiter //;
create function t1test(val int) returns int
begin
return (select a from t1 where exists (select b from T1 where b > val));
end//
delimiter ;//
select t1test(1);
drop function t1test;
drop table t1, T1;
--echo # End of 10.11 tests
--echo #
--echo # MDEV-32973 SHOW TABLES LIKE shows temporary tables with non-matching names
--echo #
create temporary table t1 (a int);
create temporary table t2 (a int);
create temporary table T1 (a int);
show tables like 't1';
select table_name from information_schema.tables where table_schema='test'
and table_name='t1';
show tables like '_1';
show tables like 't%';
--echo # End of 11.2 tests
|
