innodb_mysql.test

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

  ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
disconnect con1;
connect (con2,localhost,root,,);
connection con2;
select * from test;
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
  ON DUPLICATE KEY UPDATE `test2` = '1234';
select * from test;
flush tables;
select * from test;
disconnect con2;
connection default;
drop table test;
set global query_cache_type=@save_qcache_type;
set global query_cache_size=@save_qcache_size;

-- source include/have_innodb.inc

#
# Bug #27650: INSERT fails after multi-row INSERT of the form:
# INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id)
#

create table t1(
id int auto_increment,
c char(1) not null,
counter int not null default 1,
primary key (id),
unique key (c)
) engine=innodb;

insert into t1 (id, c) values
(NULL, 'a'),
(NULL, 'a')
on duplicate key update id = values(id), counter = counter + 1;

select * from t1;

insert into t1 (id, c) values
(NULL, 'b')
on duplicate key update id = values(id), counter = counter + 1;

select * from t1;

truncate table t1;

insert into t1 (id, c) values (NULL, 'a');

select * from t1;

insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
on duplicate key update id = values(id), c = values(c), counter = counter + 1;

select * from t1;

insert into t1 (id, c) values (NULL, 'a')
on duplicate key update id = values(id), c = values(c), counter = counter + 1;

select * from t1;

drop table t1;

#
# Bug #28189: optimizer erroniously prefers ref access to range access 
#             for an InnoDB table
#

CREATE TABLE t1(
  id int AUTO_INCREMENT PRIMARY KEY,
  stat_id int NOT NULL,
  acct_id int DEFAULT NULL,
  INDEX idx1 (stat_id, acct_id),
  INDEX idx2 (acct_id)
) ENGINE=MyISAM;

CREATE TABLE t2(
  id int AUTO_INCREMENT PRIMARY KEY,
  stat_id int NOT NULL,
  acct_id int DEFAULT NULL,
  INDEX idx1 (stat_id, acct_id),
  INDEX idx2 (acct_id)
) ENGINE=InnoDB;

INSERT INTO t1(stat_id,acct_id) VALUES
  (1,759), (2,831), (3,785), (4,854), (1,921),
  (1,553), (2,589), (3,743), (2,827), (2,545),
  (4,779), (4,783), (1,597), (1,785), (4,832),
  (1,741), (1,833), (3,788), (2,973), (1,907);

INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
UPDATE t1 SET acct_id=785 
  WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
OPTIMIZE TABLE t1;

SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t1 WHERE acct_id=785;

EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; 

INSERT INTO t2 SELECT * FROM t1;
OPTIMIZE TABLE t2;

EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;

DROP TABLE t1,t2; 

#
# Bug #28652: assert when alter innodb table operation
#
create table t1(a int) engine=innodb;
alter table t1 comment '123';
show create table t1;
drop table t1;

#
# Bug #25866: Getting "#HY000 Can't find record in..." on and INSERT
#
CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8;
INSERT INTO t1 VALUES ('uk'),('bg');
SELECT * FROM t1 WHERE a = 'uk';
DELETE FROM t1 WHERE a = 'uk';
SELECT * FROM t1 WHERE a = 'uk';
UPDATE t1 SET a = 'us' WHERE a = 'uk';
SELECT * FROM t1 WHERE a = 'uk';

CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
INSERT INTO t2 VALUES ('uk'),('bg');
SELECT * FROM t2 WHERE a = 'uk';
DELETE FROM t2 WHERE a = 'uk';
SELECT * FROM t2 WHERE a = 'uk';
INSERT INTO t2 VALUES ('uk');
UPDATE t2 SET a = 'us' WHERE a = 'uk';
SELECT * FROM t2 WHERE a = 'uk';

CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
INSERT INTO t3 VALUES ('uk'),('bg');
SELECT * FROM t3 WHERE a = 'uk';
DELETE FROM t3 WHERE a = 'uk';
SELECT * FROM t3 WHERE a = 'uk';
INSERT INTO t3 VALUES ('uk');
UPDATE t3 SET a = 'us' WHERE a = 'uk';
SELECT * FROM t3 WHERE a = 'uk';

DROP TABLE t1,t2,t3;

#
# Test bug when trying to drop data file which no InnoDB directory entry
#

create table t1 (a int) engine=innodb;
copy_file $MYSQLTEST_VARDIR/master-data/test/t1.frm $MYSQLTEST_VARDIR/master-data/test/t2.frm;
--error 1146
select * from t2;
drop table t1;
--error 1051
drop table t2;
create table t2 (a int);
drop table t2;


#
# Bug #29154: LOCK TABLES is not atomic when >1 InnoDB tables are locked
#

CREATE TABLE t1 (a INT) ENGINE=InnoDB; 
CREATE TABLE t2 (a INT) ENGINE=InnoDB; 

CONNECT (c1,localhost,root,,);
CONNECT (c2,localhost,root,,);

--echo switch to connection c1
CONNECTION c1;
SET AUTOCOMMIT=0;
INSERT INTO t2 VALUES (1);

--echo switch to connection c2
CONNECTION c2;
SET AUTOCOMMIT=0;
--error ER_LOCK_WAIT_TIMEOUT
LOCK TABLES t1 READ, t2 READ;

--echo switch to connection c1
CONNECTION c1;
COMMIT;
INSERT INTO t1 VALUES (1);

--echo switch to connection default
CONNECTION default;
SET AUTOCOMMIT=default;
DISCONNECT c1;
DISCONNECT c2;
DROP TABLE t1,t2;

#
# Bug #25798: a query with forced index merge returns wrong result 
#

CREATE TABLE t1 (
  id int NOT NULL auto_increment PRIMARY KEY,
  b int NOT NULL,
  c datetime NOT NULL,
  INDEX idx_b(b),
  INDEX idx_c(c)
) ENGINE=InnoDB;

CREATE TABLE t2 (
  b int NOT NULL auto_increment PRIMARY KEY,
  c datetime NOT NULL
) ENGINE= MyISAM;

INSERT INTO t2(c) VALUES ('2007-01-01');
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;

INSERT INTO t1(b,c) SELECT b,c FROM t2;
UPDATE t2 SET c='2007-01-02';
INSERT INTO t1(b,c) SELECT b,c FROM t2;
UPDATE t2 SET c='2007-01-03';
INSERT INTO t1(b,c) SELECT b,c FROM t2;

set @@sort_buffer_size=8192;

SELECT COUNT(*) FROM t1;

--replace_column 9 #
EXPLAIN 
SELECT COUNT(*) FROM t1 
  WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
SELECT COUNT(*) FROM t1 
  WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;

--replace_column 9 #
EXPLAIN 
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) 
  WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
  WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;

set @@sort_buffer_size=default;

DROP TABLE t1,t2;


# Bug#27296 Assertion in ALTER TABLE SET DEFAULT in Linux Debug build
# (possible deadlock).
#
# The bug is applicable only to a transactoinal table.
# Cover with tests behavior that no longer causes an
# assertion.
#
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (a int) engine=innodb;
alter table t1 alter a set default 1;
drop table t1;


--echo
--echo Bug#24918 drop table and lock / inconsistent between 
--echo perm and temp tables
--echo
--echo Check transactional tables under LOCK TABLES
--echo
--disable_warnings
drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp, 
t24918_access;
--enable_warnings
create table t24918_access (id int);
create table t24918 (id int) engine=myisam;
create temporary table t24918_tmp (id int) engine=myisam;
create table t24918_trans (id int) engine=innodb;
create temporary table t24918_trans_tmp (id int) engine=innodb;

lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write;
drop table t24918;
--error ER_TABLE_NOT_LOCKED
select * from t24918_access;
drop table t24918_trans;
--error ER_TABLE_NOT_LOCKED
select * from t24918_access;
drop table t24918_trans_tmp;
--error ER_TABLE_NOT_LOCKED
select * from t24918_access;
drop table t24918_tmp;
--error ER_TABLE_NOT_LOCKED
select * from t24918_access;
unlock tables;

drop table t24918_access;
#
# Bug #28591: MySQL need not sort the records in case of ORDER BY
# primary_key on InnoDB table
#

CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
INSERT INTO t1 SELECT a + 8, 2 FROM t1;
INSERT INTO t1 SELECT a + 16, 1 FROM t1;
query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
SELECT * FROM t1 WHERE b=2 ORDER BY a;
query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;

CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
  ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;

query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
SELECT * FROM t2 WHERE b=1 ORDER BY a;
query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;

DROP TABLE t1,t2;


#
# Bug #29644: alter table hangs if records locked in share mode by long
# running transaction
#

CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
INSERT INTO t1 SELECT a + 8  FROM t1;
INSERT INTO t1 SELECT a + 16 FROM t1;

DELIMITER |;
CREATE PROCEDURE p1 ()
BEGIN
  DECLARE i INT DEFAULT 50;
  DECLARE cnt INT;
  START TRANSACTION;
    ALTER TABLE t1 ENGINE=InnoDB;
  COMMIT;
  START TRANSACTION;
  WHILE (i > 0) DO
    SET i = i - 1;
    SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE;
  END WHILE;
  COMMIT;
END;|

DELIMITER ;|

CONNECT (con1,localhost,root,,);
CONNECT (con2,localhost,root,,);

CONNECTION con1;
SEND CALL p1();
CONNECTION con2;
SEND CALL p1();
CONNECTION default;
CALL p1();

CONNECTION con1;
REAP;
CONNECTION con2;
REAP;
CONNECTION default;
DISCONNECT con1;
DISCONNECT con2;

DROP PROCEDURE p1;
DROP TABLE t1;

#
# Bug #28125: ERROR 2013 when adding index.
#
create table t1(a text) engine=innodb default charset=utf8; 
insert into t1 values('aaa');
alter table t1 add index(a(1024));
show create table t1;
drop table t1;
# Bug #28570: handler::index_read() is called with different find_flag when 
# ORDER BY is used
#

CREATE TABLE t1 (
  a INT,
  b INT,
  KEY (b)
) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);

START TRANSACTION;
SELECT * FROM t1 WHERE b=20 FOR UPDATE;

--connect (conn2, localhost, root,,test)

# This statement gives a "failed: 1205: Lock wait timeout exceeded; try 
# restarting transaction" message when the bug is present.
START TRANSACTION;
SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
ROLLBACK;

--disconnect conn2
--connection default

ROLLBACK;
DROP TABLE t1;

#
# Bug#30596: GROUP BY optimization gives wrong result order
#  
CREATE TABLE t1(
  a INT, 
  b INT NOT NULL, 
  c INT NOT NULL, 
  d INT, 
  UNIQUE KEY (c,b)
) engine=innodb;

INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);

EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
SELECT c,b,d FROM t1 GROUP BY c,b,d;
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
SELECT c,b,d FROM t1 ORDER BY c,b,d;

EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
SELECT c,b,d FROM t1 GROUP BY c,b;
EXPLAIN SELECT c,b   FROM t1 GROUP BY c,b;
SELECT c,b   FROM t1 GROUP BY c,b;

DROP TABLE t1;

#
# Bug #31001: ORDER BY DESC in InnoDB not working
#
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);

#The two queries below should produce different results, but they don't.
query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;

query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
SELECT * FROM t1 ORDER BY b ASC, a ASC;
query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
SELECT * FROM t1 ORDER BY b DESC, a DESC;
query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
SELECT * FROM t1 ORDER BY b ASC, a DESC;
query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
SELECT * FROM t1 ORDER BY b DESC, a ASC;

DROP TABLE t1;

--echo End of 5.0 tests