mysqldump.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

drop view v2;
drop database mysqldump_test_db;
use test;

#
# Bug #9756
#

CREATE TABLE t1 (a char(10));
INSERT INTO t1 VALUES ('\'');
--exec $MYSQL_DUMP --skip-comments test t1
DROP TABLE t1;

#
# Bug #10927 mysqldump: Can't reload dump with view that consist of other view
#

create table t1(a int, b int, c varchar(30));

insert into t1 values(1, 2, "one"), (2, 4, "two"), (3, 6, "three");

create view v3 as
select * from t1;

create  view v1 as
select * from v3 where b in (1, 2, 3, 4, 5, 6, 7);

create  view v2 as
select v3.a from v3, v1 where v1.a=v3.a and v3.b=3 limit 1;

--exec $MYSQL_DUMP --skip-comments test

drop view v1, v2, v3;
drop table t1;
#
# Test for dumping triggers
#

CREATE TABLE t1 (a int, b bigint default NULL);
CREATE TABLE t2 (a int);
delimiter |;
create trigger trg1 before insert on t1 for each row
begin
  if new.a > 10 then
    set new.a := 10;
    set new.a := 11;
  end if;
end|
create trigger trg2 before update on t1 for each row begin
  if old.a % 2 = 0 then set new.b := 12; end if;
end|
set sql_mode="traditional"|
create trigger trg3 after update on t1 for each row
begin
  if new.a = -1 then
    set @fired:= "Yes";
  end if;
end|
create trigger trg4 before insert on t2 for each row
begin
  if new.a > 10 then
    set @fired:= "No";
  end if;
end|
set sql_mode=default|
delimiter ;|
--replace_column 6 '0000-00-00 00:00:00'
show triggers like "t1";
INSERT INTO t1 (a) VALUES (1),(2),(3),(22);
update t1 set a = 4 where a=3;
# Triggers should be dumped by default
--exec $MYSQL_DUMP --skip-comments --databases test
# Skip dumping triggers
--exec $MYSQL_DUMP --skip-comments --databases --skip-triggers test
# Dump and reload...
--exec $MYSQL_DUMP --skip-comments --databases test > $MYSQLTEST_VARDIR/tmp/mysqldump.sql
drop table t1;
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/mysqldump.sql
# Check that tables have been reloaded
show tables;
--replace_column 6 #
show triggers;
DROP TABLE t1, t2;

#
# Bugs #9136, #12917: problems with --defaults-extra-file option
#

--system echo '[mysqltest1]' > $MYSQLTEST_VARDIR/tmp/tmp.cnf
--system echo 'port=1234' >> $MYSQLTEST_VARDIR/tmp/tmp.cnf
--exec $MYSQL_MY_PRINT_DEFAULTS -c $MYSQLTEST_VARDIR/tmp/tmp.cnf mysqltest1
--exec $MYSQL_MY_PRINT_DEFAULTS -e $MYSQLTEST_VARDIR/tmp/tmp.cnf mysqltest1 mysqltest1
--system rm $MYSQLTEST_VARDIR/tmp/tmp.cnf

#
# Test of fix to BUG 12597
#

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `a1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
  `a2` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DELIMITER //;
CREATE TRIGGER `testref` BEFORE INSERT ON `test1` FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1; END  //
DELIMITER ;//

INSERT INTO `test1` VALUES (1);
SELECT * FROM `test2`;

# dump
--exec $MYSQL_DUMP --skip-comments --databases test > $MYSQLTEST_VARDIR/tmp/mysqldump.sql

#DROP TRIGGER testref;
#DROP TABLE test1;
#DROP TABLE test2;
# restore
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/mysqldump.sql
SHOW TRIGGERS;
SELECT * FROM `test1`;
SELECT * FROM `test2`;

DROP TRIGGER testref;
DROP TABLE test1;
DROP TABLE test2;

#
# BUG#9056 - mysqldump does not dump routines
#

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP FUNCTION IF EXISTS bug9056_func1;
DROP FUNCTION IF EXISTS bug9056_func2;
DROP PROCEDURE IF EXISTS bug9056_proc1;
DROP PROCEDURE IF EXISTS bug9056_proc2;
DROP PROCEDURE IF EXISTS `a'b`;
--enable_warnings

CREATE TABLE t1 (id int);
INSERT INTO t1 VALUES(1), (2), (3), (4), (5);

DELIMITER //;
CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11) RETURN a+b //
CREATE PROCEDURE `bug9056_proc1`(IN a INT, IN b INT, OUT c INT)
BEGIN SELECT a+b INTO c; end  //

create function bug9056_func2(f1 char binary) returns char binary
begin
  set f1= concat( 'hello', f1 );
  return f1;
end //

CREATE PROCEDURE bug9056_proc2(OUT a INT) 
BEGIN 
  select sum(id) from t1 into a; 
END //

DELIMITER ;//

set sql_mode='ansi';
create procedure `a'b` () select 1; # to fix syntax highlighting :')
set sql_mode='';

# Dump the DB and ROUTINES 
--exec $MYSQL_DUMP --skip-comments --routines --databases test

# ok, now blow it all away
DROP FUNCTION bug9056_func1;
DROP FUNCTION bug9056_func2;
DROP PROCEDURE bug9056_proc1;
DROP PROCEDURE bug9056_proc2;
DROP PROCEDURE `a'b`;
drop table t1;

#
# BUG# 13052 - mysqldump timestamp reloads broken
#
--disable_warnings
drop table if exists t1;
--enable_warnings

create table t1 (`d` timestamp, unique (`d`));
set time_zone='+00:00';
insert into t1 values ('2003-10-25 22:00:00'),('2003-10-25 23:00:00');
# results should show two different time values
select * from t1;
set time_zone='Europe/Moscow';
# results should show two same time values, despite unique
select * from t1;
set global time_zone='Europe/Moscow';
--exec $MYSQL_DUMP --skip-comments --databases test
--exec $MYSQL_DUMP --skip-tz-utc --skip-comments --databases test
drop table t1;
set global time_zone=default;
set time_zone=default;

#
# Test of fix to BUG 13146 - ansi quotes break loading of triggers 
#
--disable_warnings
DROP TABLE IF EXISTS `t1 test`;
DROP TABLE IF EXISTS `t2 test`;
--enable_warnings

CREATE TABLE `t1 test` (
  `a1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `t2 test` (
  `a2` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DELIMITER //;
CREATE TRIGGER `test trig` BEFORE INSERT ON `t1 test` FOR EACH ROW BEGIN
INSERT INTO `t2 test` SET a2 = NEW.a1; END  //
DELIMITER ;//

INSERT INTO `t1 test` VALUES (1);
INSERT INTO `t1 test` VALUES (2);
INSERT INTO `t1 test` VALUES (3);
SELECT * FROM `t2 test`;
# dump with compatible=ansi. Everything except triggers should be double 
# quoted
--exec $MYSQL_DUMP --skip-comments --compatible=ansi --triggers test

DROP TRIGGER `test trig`;
DROP TABLE `t1 test`;
DROP TABLE `t2 test`;

#
# BUG# 12838 mysqldump -x with views exits with error 
#

--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (a int, b varchar(32), c varchar(32));
insert into t1 values (1, 'first value', 'xxxx');
insert into t1 values (2, 'second value', 'tttt');
insert into t1 values (3, 'third value', 'vvv vvv');

create view v1 as select * from t1;
create view v0 as select * from v1;
create view v2 as select * from v0;

select * from v2;
--exec $MYSQL_DUMP -x --skip-comments --databases test

drop view v2;
drop view v0;
drop view v1;
drop table t1;

#
# BUG#14554 - mysqldump does not separate words "ROW" and "BEGIN"
# for tables with trigger created in the IGNORE_SPACE sql mode.
#

SET @old_sql_mode = @@SQL_MODE;
SET SQL_MODE = IGNORE_SPACE;

CREATE TABLE t1 (a INT);
DELIMITER |;
CREATE TRIGGER tr1 BEFORE INSERT ON t1
       FOR EACH ROW
       BEGIN
         SET new.a = 0;
       END|
DELIMITER ;|

SET SQL_MODE = @old_sql_mode;

--exec $MYSQL_DUMP --skip-comments --databases test

DROP TRIGGER tr1;
DROP TABLE t1;

#
# Bug #13318: Bad result with empty field and --hex-blob
# 
create table t1 (a binary(1), b blob);
insert into t1 values ('','');
--exec $MYSQL_DUMP --skip-comments --skip-extended-insert --hex-blob test t1
--exec $MYSQL_DUMP --skip-comments --hex-blob test t1
drop table t1;

# End of 4.1 tests

#
# Bug 14871 Invalid view dump output
#

create table t1 (a int);
insert into t1 values (289), (298), (234), (456), (789);
create definer = CURRENT_USER view v1 as select * from t1;
create SQL SECURITY INVOKER view v2 as select * from t1;
create view v3 as select * from t1 with local check option;
create algorithm=merge view v4 as select * from t1 with cascaded check option;
create algorithm =temptable view v5 as select * from t1;

# dump tables and views
--exec $MYSQL_DUMP test > $MYSQLTEST_VARDIR/tmp/bug14871.sql

# drop the db, tables and views
drop table t1;
drop view v1, v2, v3, v4, v5;

# Reload dump
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug14871.sql

# check that all tables and views could be created
show tables;
select * from v3 order by a;

drop table t1;
drop view v1, v2, v3, v4, v5;

#
# Bug #16878 dump of trigger
#

create table t1 (a int, created datetime);
create table t2 (b int, created datetime);
create trigger tr1 before insert on t1 for each row set
new.created=now();
delimiter |;
create trigger tr2 after insert on t1
for each row
begin
  insert into t2 set b=new.a and created=new.created;
end|
delimiter ;|

# dump table and trigger
--exec $MYSQL_DUMP test > $MYSQLTEST_VARDIR/tmp/bug16878.sql
drop trigger tr1;
drop trigger tr2;
drop table t1, t2;

# reload dump
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug16878.sql
--replace_column 6 #
show triggers;
drop trigger tr1;
drop trigger tr2;
drop table t1, t2;


#
# Bug#14857 Reading dump files with single statement stored routines fails.
# fixed by patch for bug#16878
#
#
DELIMITER |;
/*!50003 CREATE FUNCTION `f`() RETURNS bigint(20)
return 42 */|
/*!50003 CREATE PROCEDURE `p`()
select 42 */|
DELIMITER ;|
show create function f;
show create procedure p;
drop function f;
drop procedure p;