memory_triggers.result

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

insert 3.5.3.7-1d
trig 3.5.3.7-2d
drop trigger trg4d_2;

Testcase 3.5.3.8a:
------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant ALL  on *.* to test_noprivs@localhost;
revoke SELECT  on *.* from test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER, SELECT on *.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SELECT, SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
select current_user;
current_user
test_noprivs@localhost
use priv_db;
show grants;
Grants for test_noprivs@localhost
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'

Trigger create disabled - should fail - Bug 8887
------------------------------------------------
set @test_var = 'before trig 3.5.3.8-1a';
select @test_var;
@test_var
before trig 3.5.3.8-1a
insert into t1 (f1) values ('insert 3.5.3.8-1a');
select @test_var;
@test_var
before trig 3.5.3.8-1a
drop trigger trg5a_1;
use priv_db;
select current_user;
current_user
test_yesprivs@localhost
show grants;
Grants for test_yesprivs@localhost
GRANT SELECT, SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
create trigger trg5a_2 before INSERT  on t1 for each row 
set @test_var= new.f1;
set @test_var= 'before trig 3.5.3.8-2a';
select @test_var;
@test_var
before trig 3.5.3.8-2a
insert into t1 (f1) values ('insert 3.5.3.8-2a');
select @test_var;
@test_var
insert 3.5.3.8-2a
drop trigger trg5a_2;

Testcase: 3.5.3.8b
------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SUPER on *.* to test_noprivs@localhost;
grant ALL  on priv_db.* to test_noprivs@localhost;
revoke SELECT  on priv_db.* from test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `priv_db`.* TO 'test_noprivs'@'localhost'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER on *.* to test_yesprivs@localhost;
grant SELECT on priv_db.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `priv_db`.* TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8887
------------------------------------------------
set @test_var= 'before trig 3.5.3.8-1b';
insert into t1 (f1) values ('insert 3.5.3.8-1b');
select @test_var;
@test_var
before trig 3.5.3.8-1b
update t1 set  f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
select @test_var;
@test_var
before trig 3.5.3.8-1b
drop trigger trg5b_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
use priv_db;
create trigger trg5b_2 before UPDATE  on t1 for each row 
set @test_var= new.f1;
set @test_var= 'before trig 3.5.3.8-2b';
insert into t1 (f1) values ('insert 3.5.3.8-2b');
select @test_var;
@test_var
before trig 3.5.3.8-2b
update t1 set  f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
select @test_var;
@test_var
update 3.5.3.8-2b
drop trigger trg5b_2;

Testcase 3.5.3.8c:
------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SUPER on *.* to test_noprivs@localhost;
grant ALL  on priv_db.t1 to test_noprivs@localhost;
revoke SELECT  on priv_db.t1 from test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER on *.* to test_yesprivs@localhost;
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8887
------------------------------------------------
set @test_var= 'before trig 3.5.3.8-1c';
insert into t1 (f1) values ('insert 3.5.3.8-1c');
select @test_var;
@test_var
before trig 3.5.3.8-1c
drop trigger trg5c_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
use priv_db;
create trigger trg5c_2 before INSERT  on t1 for each row 
set @test_var= new.f1;
set @test_var='before trig 3.5.3.8-2c';
insert into t1 (f1) values ('insert 3.5.3.8-2c');
select @test_var;
@test_var
insert 3.5.3.8-2c
drop trigger trg5c_2;

Testcase: 3.5.3.8d:
-------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SUPER on *.* to test_noprivs@localhost;
grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER on *.* to test_yesprivs@localhost;
grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8887
------------------------------------------------
set @test_var='before trig 3.5.3.8-1d';
insert into t1 (f1) values ('insert 3.5.3.8-1d');
select @test_var;
@test_var
before trig 3.5.3.8-1d
drop trigger trg5d_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
use priv_db;
create trigger trg5d_2 before INSERT  on t1 for each row 
set @test_var= new.f1;
set @test_var='before trig 3.5.3.8-2d';
insert into t1 (f1) values ('insert 3.5.3.8-2d');
select @test_var;
@test_var
insert 3.5.3.8-2d
drop trigger trg5d_2;
drop database if exists priv_db;
drop user test_yesprivs@localhost;
drop user test_noprivs@localhost;
drop user test_noprivs;

Testcase 3.5.4:
---------------
use test;

Testcase 3.5.4.1:
-----------------
create database db_drop;
Use db_drop;
create table t1 (f1 char(30)) engine=memory;
grant INSERT, SELECT on db_drop.t1 to test_general;
Use db_drop;
Create trigger trg1 BEFORE INSERT on t1 
for each row set new.f1='Trigger 3.5.4.1';
Use db_drop;
Insert into t1 values ('Insert error 3.5.4.1');
Select * from t1;
f1
Trigger 3.5.4.1
drop trigger trg1;
select trigger_schema, trigger_name, event_object_table
from information_schema.triggers;
trigger_schema	trigger_name	event_object_table
Insert into t1 values ('Insert no trigger 3.5.4.1');
Select * from t1;
f1
Trigger 3.5.4.1
Insert no trigger 3.5.4.1
drop trigger trg1;
drop database if exists db_drop;
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';

Testcase 3.5.4.2:
-----------------
create database db_drop2;
Use db_drop2;
drop table if exists t1_432 ;
create table t1_432 (f1 char (30)) engine=memory;
Drop trigger tr_does_not_exit;
ERROR HY000: Trigger does not exist
drop table if exists t1_432 ;
drop database  if exists db_drop2;

Testcase 3.5.4.3:
-----------------
create database db_drop3;
Use db_drop3;
drop table if exists t1_433 ;
drop table if exists t1_433a ;
create table t1_433 (f1 char (30)) engine=memory;
create table t1_433a (f1a char (5)) engine=memory;
CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row 
set new.f1 = 'Trigger 3.5.4.3';
Drop trigger t1.433.trg3;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.trg3' at line 1
Drop trigger db_drop3.t1.433.trg3;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.433.trg3' at line 1
Drop trigger mysql.trg3;
ERROR HY000: Trigger does not exist
Drop trigger tbx.trg3;
ERROR HY000: Trigger does not exist
Drop trigger db_drop3.trg3;
drop table if exists t1_433;
drop table if exists t1_433a;
drop database if exists db_drop3;

Testcase 3.5.4.4:
-----------------
create database db_drop4;
Use db_drop4;
create table t1 (f1 char(30)) engine=memory;
grant INSERT, SELECT on db_drop4.t1 to test_general;
Create trigger trg4 BEFORE INSERT on t1 
for each row set new.f1='Trigger 3.5.4.4';
Use db_drop4;
Insert into t1 values ('Insert 3.5.4.4');
Select * from t1;
f1
Trigger 3.5.4.4
Drop database db_drop4;
Show databases;
Database
information_schema
mysql
test
select trigger_schema, trigger_name, event_object_table
from information_schema.triggers
where information_schema.triggers.trigger_name='trg4';
trigger_schema	trigger_name	event_object_table
create database db_drop4;
Use db_drop4;
create table t1 (f1 char(30)) engine=memory;
grant INSERT, SELECT on db_drop4.t1 to test_general;
Insert into t1 values ('2nd Insert 3.5.4.4');
Select * from t1;
f1
2nd Insert 3.5.4.4
drop trigger trg4;
ERROR HY000: Trigger does not exist
drop database if exists db_drop4;
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';

Testcase 3.5.4.5:
-----------------
create database db_drop5;
Use db_drop5;
create table t1 (f1 char(50)) engine=memory;
grant INSERT, SELECT on t1 to test_general;
Create trigger trg5 BEFORE INSERT on t1 
for each row set new.f1='Trigger 3.5.4.5';
Use db_drop5;
Insert into t1 values ('Insert 3.5.4.5');
Select * from t1;
f1
Trigger 3.5.4.5
Drop table t1;
Show tables;
Tables_in_db_drop5
select trigger_schema, trigger_name, event_object_table
from information_schema.triggers
where information_schema.triggers.trigger_name='trg5';
trigger_schema	trigger_name	event_object_table
create table t1 (f1 char(50)) engine=memory;
grant INSERT, SELECT on t1 to test_general;
Insert into t1 values ('2nd Insert 3.5.4.5');
Select * from t1;
f1
2nd Insert 3.5.4.5
drop trigger trg5;
ERROR HY000: Trigger does not exist
drop database if exists db_drop5;
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';

Testcase 3.5.5:
---------------
use test;

Testcase 3.5.5.1:
-----------------
Create trigger trg1 before INSERT on t100 for each row set new.f2=1000;
ERROR 42S02: Table 'test.t100' doesn't exist

Testcase 3.5.5.2:
-----------------
Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned);
Create trigger trg2 before INSERT 
on t1_temp for each row set new.f2=9999;