mirror of
https://github.com/MariaDB/server.git
synced 2025-08-01 03:47:19 +03:00
e3d9369774
let's always disconnect a user connection before dropping the said user. MariaDB is traditionally very tolerant to active connections of the dropped user, which isn't the case for most other databases. Let's avoid unintentionally spreading incompatible behavior and disconnect before drop. Except in cases when the test specifically tests such a behavior.
194 lines
6.9 KiB
Plaintext
194 lines
6.9 KiB
Plaintext
#
|
|
# MDEV-14474: Create INFORMATION_SCHEMA.CHECK_CONSTRAINTS
|
|
#
|
|
CREATE user boo1;
|
|
GRANT select,create,alter,drop on foo.* to boo1;
|
|
SHOW GRANTS for boo1;
|
|
Grants for boo1@%
|
|
GRANT USAGE ON *.* TO `boo1`@`%`
|
|
GRANT SELECT, CREATE, DROP, ALTER ON `foo`.* TO `boo1`@`%`
|
|
CREATE user boo2;
|
|
create database foo;
|
|
connect con1,localhost, boo1,, foo;
|
|
SET check_constraint_checks=1;
|
|
CREATE TABLE t0
|
|
(
|
|
t int, check (t>32) # table constraint
|
|
) ENGINE=myisam;
|
|
SELECT * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def foo t0 CONSTRAINT_1 Table `t` > 32
|
|
ALTER TABLE t0
|
|
ADD CONSTRAINT CHK_t0_t CHECK(t<100);
|
|
SELECT * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def foo t0 CHK_t0_t Table `t` < 100
|
|
def foo t0 CONSTRAINT_1 Table `t` > 32
|
|
ALTER TABLE t0
|
|
DROP CONSTRAINT CHK_t0_t;
|
|
SELECT * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def foo t0 CONSTRAINT_1 Table `t` > 32
|
|
ALTER TABLE t0
|
|
ADD CONSTRAINT CHECK(t<50);
|
|
SELECT * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def foo t0 CONSTRAINT_1 Table `t` > 32
|
|
def foo t0 CONSTRAINT_2 Table `t` < 50
|
|
CREATE TABLE t1
|
|
( t int CHECK(t>2), # field constraint
|
|
tt int,
|
|
CONSTRAINT CHECK (tt > 32), CONSTRAINT CHECK (tt <50),# autogenerated names table constraints
|
|
CONSTRAINT CHK_tt CHECK(tt<100) # named table constraint
|
|
) ENGINE=InnoDB;
|
|
SELECT * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def foo t0 CONSTRAINT_1 Table `t` > 32
|
|
def foo t0 CONSTRAINT_2 Table `t` < 50
|
|
def foo t1 CHK_tt Table `tt` < 100
|
|
def foo t1 CONSTRAINT_1 Table `tt` > 32
|
|
def foo t1 CONSTRAINT_2 Table `tt` < 50
|
|
def foo t1 t Column `t` > 2
|
|
ALTER TABLE t1
|
|
DROP CONSTRAINT CHK_tt;
|
|
SELECT * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def foo t0 CONSTRAINT_1 Table `t` > 32
|
|
def foo t0 CONSTRAINT_2 Table `t` < 50
|
|
def foo t1 CONSTRAINT_1 Table `tt` > 32
|
|
def foo t1 CONSTRAINT_2 Table `tt` < 50
|
|
def foo t1 t Column `t` > 2
|
|
CREATE TABLE t2
|
|
(
|
|
name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint
|
|
start_date DATE,
|
|
end_date DATE,
|
|
CONSTRAINT CHK_dates CHECK(start_date IS NULL) #table constraint
|
|
)ENGINE=Innodb;
|
|
SELECT * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def foo t0 CONSTRAINT_1 Table `t` > 32
|
|
def foo t0 CONSTRAINT_2 Table `t` < 50
|
|
def foo t1 CONSTRAINT_1 Table `tt` > 32
|
|
def foo t1 CONSTRAINT_2 Table `tt` < 50
|
|
def foo t1 t Column `t` > 2
|
|
def foo t2 CHK_dates Table `start_date` is null
|
|
def foo t2 name Column char_length(`name`) > 2
|
|
ALTER TABLE t1
|
|
ADD CONSTRAINT CHK_new_ CHECK(t>tt);
|
|
SELECT * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def foo t0 CONSTRAINT_1 Table `t` > 32
|
|
def foo t0 CONSTRAINT_2 Table `t` < 50
|
|
def foo t1 CHK_new_ Table `t` > `tt`
|
|
def foo t1 CONSTRAINT_1 Table `tt` > 32
|
|
def foo t1 CONSTRAINT_2 Table `tt` < 50
|
|
def foo t1 t Column `t` > 2
|
|
def foo t2 CHK_dates Table `start_date` is null
|
|
def foo t2 name Column char_length(`name`) > 2
|
|
CREATE TABLE t3
|
|
(
|
|
a int,
|
|
b int check (b>0), # field constraint named 'b'
|
|
CONSTRAINT b check (b>10), # table constraint
|
|
# `CHECK_CLAUSE` should allow more then `var(64)` constraints
|
|
CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789)
|
|
) ENGINE=InnoDB;
|
|
SELECT * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def foo t0 CONSTRAINT_1 Table `t` > 32
|
|
def foo t0 CONSTRAINT_2 Table `t` < 50
|
|
def foo t1 CHK_new_ Table `t` > `tt`
|
|
def foo t1 CONSTRAINT_1 Table `tt` > 32
|
|
def foo t1 CONSTRAINT_2 Table `tt` < 50
|
|
def foo t1 t Column `t` > 2
|
|
def foo t2 CHK_dates Table `start_date` is null
|
|
def foo t2 name Column char_length(`name`) > 2
|
|
def foo t3 b Column `b` > 0
|
|
def foo t3 b Table `b` > 10
|
|
def foo t3 b1 Table `b` < 123456789012345678901234567890123456789012345678901234567890123456789
|
|
disconnect con1;
|
|
connect con2, localhost, boo2,,"*NO-ONE*";
|
|
SELECT * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
disconnect con2;
|
|
connect con1, localhost, boo1,,foo;
|
|
DROP TABLE t0;
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DROP TABLE t3;
|
|
DROP DATABASE foo;
|
|
disconnect con1;
|
|
connection default;
|
|
DROP USER boo1;
|
|
DROP USER boo2;
|
|
#
|
|
# MDEV-18440: Information_schema.check_constraints possible data leak
|
|
#
|
|
CREATE USER foo;
|
|
CREATE DATABASE db;
|
|
USE db;
|
|
CREATE TABLE t1 (a int, b int, CONSTRAINT CHECK (b > 0));
|
|
INSERT INTO t1 VALUES (1, 2), (2, 3);
|
|
GRANT SELECT (a) ON t1 TO foo;
|
|
SHOW GRANTS FOR foo;
|
|
Grants for foo@%
|
|
GRANT USAGE ON *.* TO `foo`@`%`
|
|
GRANT SELECT (`a`) ON `db`.`t1` TO `foo`@`%`
|
|
SELECT * FROM information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def db t1 CONSTRAINT_1 Table `b` > 0
|
|
def mysql global_priv Priv Column json_valid(`Priv`)
|
|
def mysql servers Options Column json_valid(`Options`)
|
|
connect con1,localhost, foo,, db;
|
|
SELECT a FROM t1;
|
|
a
|
|
1
|
|
2
|
|
SELECT * FROM information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
disconnect con1;
|
|
connection default;
|
|
DROP USER foo;
|
|
DROP DATABASE db;
|
|
#
|
|
# MDEV-24601: INFORMATION_SCHEMA doesn't differentiate between
|
|
# column and table-level CHECK constraints
|
|
#
|
|
use test;
|
|
create table t(check (t0>0),
|
|
t0 int,
|
|
t1 int check (t1<0),
|
|
t2 int check (t2<-1),
|
|
CONSTRAINT tc_1 check(t1 > 1),
|
|
CONSTRAINT t2 check(t2 > 1));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`t0` int(11) DEFAULT NULL,
|
|
`t1` int(11) DEFAULT NULL CHECK (`t1` < 0),
|
|
`t2` int(11) DEFAULT NULL CHECK (`t2` < -1),
|
|
CONSTRAINT `CONSTRAINT_1` CHECK (`t0` > 0),
|
|
CONSTRAINT `tc_1` CHECK (`t1` > 1),
|
|
CONSTRAINT `t2` CHECK (`t2` > 1)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
select * from information_schema.table_constraints where CONSTRAINT_TYPE='CHECK';
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
|
|
def mysql Options mysql servers CHECK
|
|
def mysql Priv mysql global_priv CHECK
|
|
def test CONSTRAINT_1 test t CHECK
|
|
def test t1 test t CHECK
|
|
def test t2 test t CHECK
|
|
def test t2 test t CHECK
|
|
def test tc_1 test t CHECK
|
|
select * from information_schema.check_constraints;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
|
|
def mysql global_priv Priv Column json_valid(`Priv`)
|
|
def mysql servers Options Column json_valid(`Options`)
|
|
def test t CONSTRAINT_1 Table `t0` > 0
|
|
def test t t1 Column `t1` < 0
|
|
def test t t2 Column `t2` < -1
|
|
def test t t2 Table `t2` > 1
|
|
def test t tc_1 Table `t1` > 1
|
|
drop table t;
|