1drop table if exists t1, t2, t3, t4, t5, t6; 2create table t1 (id int(8), name varchar(32)); 3create table t2 (id int(8), name varchar(32)) ENGINE="MyISAM"; 4create table t3 (id int(8), name varchar(32)) ENGINE="MEMORY"; 5create table t4 (id int(8), name varchar(32)) ENGINE="HEAP"; 6create table t5 (id int(8), name varchar(32)) ENGINE="ARCHIVE"; 7create table t6 (id int(8), name varchar(32)) ENGINE="InnoDB"; 8insert into t1 values (1, 'first value'); 9insert into t1 values (2, 'first value'); 10insert into t1 values (3, 'first value'); 11insert into t1 values (4, 'first value'); 12insert into t1 values (5, 'first value'); 13insert into t2 values (1, 'first value'); 14insert into t2 values (2, 'first value'); 15insert into t2 values (3, 'first value'); 16insert into t2 values (4, 'first value'); 17insert into t2 values (5, 'first value'); 18insert into t3 values (1, 'first value'); 19insert into t3 values (2, 'first value'); 20insert into t3 values (3, 'first value'); 21insert into t3 values (4, 'first value'); 22insert into t3 values (5, 'first value'); 23insert into t4 values (1, 'first value'); 24insert into t4 values (2, 'first value'); 25insert into t4 values (3, 'first value'); 26insert into t4 values (4, 'first value'); 27insert into t4 values (5, 'first value'); 28insert into t5 values (1, 'first value'); 29insert into t5 values (2, 'first value'); 30insert into t5 values (3, 'first value'); 31insert into t5 values (4, 'first value'); 32insert into t5 values (5, 'first value'); 33insert into t6 values (1, 'first value'); 34insert into t6 values (2, 'first value'); 35insert into t6 values (3, 'first value'); 36insert into t6 values (4, 'first value'); 37insert into t6 values (5, 'first value'); 38select * from t1; 39id name 401 first value 412 first value 423 first value 434 first value 445 first value 45select * from t2; 46id name 471 first value 482 first value 493 first value 504 first value 515 first value 52select * from t3; 53id name 541 first value 552 first value 563 first value 574 first value 585 first value 59select * from t4; 60id name 611 first value 622 first value 633 first value 644 first value 655 first value 66select * from t5; 67id name 681 first value 692 first value 703 first value 714 first value 725 first value 73select * from t6; 74id name 751 first value 762 first value 773 first value 784 first value 795 first value 80 81/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 82/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 83/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 84/*!40101 SET NAMES utf8mb4 */; 85/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; 86/*!40103 SET TIME_ZONE='+00:00' */; 87/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 88/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 89/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 90/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 91 92CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */; 93 94USE `test`; 95DROP TABLE IF EXISTS `t1`; 96/*!40101 SET @saved_cs_client = @@character_set_client */; 97/*!40101 SET character_set_client = utf8 */; 98CREATE TABLE `t1` ( 99 `id` int(8) DEFAULT NULL, 100 `name` varchar(32) DEFAULT NULL 101) ENGINE=MyISAM DEFAULT CHARSET=latin1; 102/*!40101 SET character_set_client = @saved_cs_client */; 103 104/*!40000 ALTER TABLE `t1` DISABLE KEYS */; 105INSERT DELAYED IGNORE INTO `t1` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 106/*!40000 ALTER TABLE `t1` ENABLE KEYS */; 107DROP TABLE IF EXISTS `t2`; 108/*!40101 SET @saved_cs_client = @@character_set_client */; 109/*!40101 SET character_set_client = utf8 */; 110CREATE TABLE `t2` ( 111 `id` int(8) DEFAULT NULL, 112 `name` varchar(32) DEFAULT NULL 113) ENGINE=MyISAM DEFAULT CHARSET=latin1; 114/*!40101 SET character_set_client = @saved_cs_client */; 115 116/*!40000 ALTER TABLE `t2` DISABLE KEYS */; 117INSERT DELAYED IGNORE INTO `t2` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 118/*!40000 ALTER TABLE `t2` ENABLE KEYS */; 119DROP TABLE IF EXISTS `t3`; 120/*!40101 SET @saved_cs_client = @@character_set_client */; 121/*!40101 SET character_set_client = utf8 */; 122CREATE TABLE `t3` ( 123 `id` int(8) DEFAULT NULL, 124 `name` varchar(32) DEFAULT NULL 125) ENGINE=MEMORY DEFAULT CHARSET=latin1; 126/*!40101 SET character_set_client = @saved_cs_client */; 127 128/*!40000 ALTER TABLE `t3` DISABLE KEYS */; 129INSERT DELAYED IGNORE INTO `t3` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 130/*!40000 ALTER TABLE `t3` ENABLE KEYS */; 131DROP TABLE IF EXISTS `t4`; 132/*!40101 SET @saved_cs_client = @@character_set_client */; 133/*!40101 SET character_set_client = utf8 */; 134CREATE TABLE `t4` ( 135 `id` int(8) DEFAULT NULL, 136 `name` varchar(32) DEFAULT NULL 137) ENGINE=MEMORY DEFAULT CHARSET=latin1; 138/*!40101 SET character_set_client = @saved_cs_client */; 139 140/*!40000 ALTER TABLE `t4` DISABLE KEYS */; 141INSERT DELAYED IGNORE INTO `t4` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 142/*!40000 ALTER TABLE `t4` ENABLE KEYS */; 143DROP TABLE IF EXISTS `t5`; 144/*!40101 SET @saved_cs_client = @@character_set_client */; 145/*!40101 SET character_set_client = utf8 */; 146CREATE TABLE `t5` ( 147 `id` int(8) DEFAULT NULL, 148 `name` varchar(32) DEFAULT NULL 149) ENGINE=ARCHIVE DEFAULT CHARSET=latin1; 150/*!40101 SET character_set_client = @saved_cs_client */; 151 152/*!40000 ALTER TABLE `t5` DISABLE KEYS */; 153INSERT DELAYED IGNORE INTO `t5` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 154/*!40000 ALTER TABLE `t5` ENABLE KEYS */; 155DROP TABLE IF EXISTS `t6`; 156/*!40101 SET @saved_cs_client = @@character_set_client */; 157/*!40101 SET character_set_client = utf8 */; 158CREATE TABLE `t6` ( 159 `id` int(8) DEFAULT NULL, 160 `name` varchar(32) DEFAULT NULL 161) ENGINE=InnoDB DEFAULT CHARSET=latin1; 162/*!40101 SET character_set_client = @saved_cs_client */; 163 164/*!40000 ALTER TABLE `t6` DISABLE KEYS */; 165INSERT IGNORE INTO `t6` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 166/*!40000 ALTER TABLE `t6` ENABLE KEYS */; 167/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; 168 169/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 170/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 171/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 172/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 173/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; 174/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; 175/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 176 177 178/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 179/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 180/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 181/*!40101 SET NAMES utf8mb4 */; 182/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; 183/*!40103 SET TIME_ZONE='+00:00' */; 184/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 185/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 186/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 187/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 188 189CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */; 190 191USE `test`; 192DROP TABLE IF EXISTS `t1`; 193/*!40101 SET @saved_cs_client = @@character_set_client */; 194/*!40101 SET character_set_client = utf8 */; 195CREATE TABLE `t1` ( 196 `id` int(8) DEFAULT NULL, 197 `name` varchar(32) DEFAULT NULL 198) ENGINE=MyISAM DEFAULT CHARSET=latin1; 199/*!40101 SET character_set_client = @saved_cs_client */; 200 201/*!40000 ALTER TABLE `t1` DISABLE KEYS */; 202INSERT DELAYED INTO `t1` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 203/*!40000 ALTER TABLE `t1` ENABLE KEYS */; 204DROP TABLE IF EXISTS `t2`; 205/*!40101 SET @saved_cs_client = @@character_set_client */; 206/*!40101 SET character_set_client = utf8 */; 207CREATE TABLE `t2` ( 208 `id` int(8) DEFAULT NULL, 209 `name` varchar(32) DEFAULT NULL 210) ENGINE=MyISAM DEFAULT CHARSET=latin1; 211/*!40101 SET character_set_client = @saved_cs_client */; 212 213/*!40000 ALTER TABLE `t2` DISABLE KEYS */; 214INSERT DELAYED INTO `t2` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 215/*!40000 ALTER TABLE `t2` ENABLE KEYS */; 216DROP TABLE IF EXISTS `t3`; 217/*!40101 SET @saved_cs_client = @@character_set_client */; 218/*!40101 SET character_set_client = utf8 */; 219CREATE TABLE `t3` ( 220 `id` int(8) DEFAULT NULL, 221 `name` varchar(32) DEFAULT NULL 222) ENGINE=MEMORY DEFAULT CHARSET=latin1; 223/*!40101 SET character_set_client = @saved_cs_client */; 224 225/*!40000 ALTER TABLE `t3` DISABLE KEYS */; 226INSERT DELAYED INTO `t3` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 227/*!40000 ALTER TABLE `t3` ENABLE KEYS */; 228DROP TABLE IF EXISTS `t4`; 229/*!40101 SET @saved_cs_client = @@character_set_client */; 230/*!40101 SET character_set_client = utf8 */; 231CREATE TABLE `t4` ( 232 `id` int(8) DEFAULT NULL, 233 `name` varchar(32) DEFAULT NULL 234) ENGINE=MEMORY DEFAULT CHARSET=latin1; 235/*!40101 SET character_set_client = @saved_cs_client */; 236 237/*!40000 ALTER TABLE `t4` DISABLE KEYS */; 238INSERT DELAYED INTO `t4` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 239/*!40000 ALTER TABLE `t4` ENABLE KEYS */; 240DROP TABLE IF EXISTS `t5`; 241/*!40101 SET @saved_cs_client = @@character_set_client */; 242/*!40101 SET character_set_client = utf8 */; 243CREATE TABLE `t5` ( 244 `id` int(8) DEFAULT NULL, 245 `name` varchar(32) DEFAULT NULL 246) ENGINE=ARCHIVE DEFAULT CHARSET=latin1; 247/*!40101 SET character_set_client = @saved_cs_client */; 248 249/*!40000 ALTER TABLE `t5` DISABLE KEYS */; 250INSERT DELAYED INTO `t5` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 251/*!40000 ALTER TABLE `t5` ENABLE KEYS */; 252DROP TABLE IF EXISTS `t6`; 253/*!40101 SET @saved_cs_client = @@character_set_client */; 254/*!40101 SET character_set_client = utf8 */; 255CREATE TABLE `t6` ( 256 `id` int(8) DEFAULT NULL, 257 `name` varchar(32) DEFAULT NULL 258) ENGINE=InnoDB DEFAULT CHARSET=latin1; 259/*!40101 SET character_set_client = @saved_cs_client */; 260 261/*!40000 ALTER TABLE `t6` DISABLE KEYS */; 262INSERT INTO `t6` VALUES (1,'first value'),(2,'first value'),(3,'first value'),(4,'first value'),(5,'first value'); 263/*!40000 ALTER TABLE `t6` ENABLE KEYS */; 264/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; 265 266/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 267/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 268/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 269/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 270/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; 271/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; 272/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 273 274drop table t1; 275drop table t2; 276drop table t3; 277drop table t4; 278drop table t5; 279drop table t6; 280SELECT @@global.default_storage_engine INTO @old_engine; 281SET GLOBAL default_storage_engine=InnoDB; 282CREATE VIEW v1 AS SELECT * FROM t1; 283INSERT INTO t1 VALUES(); 284SELECT COUNT(*) FROM v1; 285COUNT(*) 2861 287SELECT COUNT(*) FROM v1; 288COUNT(*) 2891 290DROP VIEW v1; 291DROP TABLE t1; 292SET GLOBAL default_storage_engine=@old_engine; 293connect c1,127.0.0.1,root,,test,$MASTER_MYPORT,; 294connect c2,127.0.0.1,root,,test,$MASTER_MYPORT,; 295connect c3,127.0.0.1,root,,test,$MASTER_MYPORT,; 296connection default; 297SET binlog_format= mixed; 298RESET MASTER; 299CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 300INSERT INTO t1 VALUES (1),(2); 301CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; 302INSERT INTO t2 VALUES (1,0), (2,0); 303SELECT GET_LOCK("block_queries_1", 120); 304GET_LOCK("block_queries_1", 120) 3051 306connection c3; 307SELECT GET_LOCK("block_queries_2", 120); 308GET_LOCK("block_queries_2", 120) 3091 310connection c1; 311SET @c= 0; 312SELECT IF(@c<1, @c:=@c+1, GET_LOCK("block_queries_1", 120)) FROM t1 ORDER BY a; 313connection c2; 314SET binlog_format="row"; 315SET @d= 10; 316UPDATE t2 SET b=IF(@d<=10, @d:=@d+1, GET_LOCK("block_queries_2", 120)) ORDER BY a; 317connection default; 318# Make sure other queries are running (and waiting). 319SELECT RELEASE_LOCK("block_queries_1"); 320RELEASE_LOCK("block_queries_1") 3211 322connection c3; 323SELECT RELEASE_LOCK("block_queries_2"); 324RELEASE_LOCK("block_queries_2") 3251 326connection c1; 327IF(@c<1, @c:=@c+1, GET_LOCK("block_queries_1", 120)) 3281 3291 330connection c2; 331connection default; 332SELECT * FROM t2 ORDER BY a; 333a b 3341 11 3352 1 336DROP TABLE t1; 337DROP TABLE t2; 338include/show_binlog_events.inc 339Log_name Pos Event_type Server_id End_log_pos Info 340master-bin.000001 # Gtid # # BEGIN GTID #-#-# 341master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (1,0), (2,0) 342master-bin.000001 # Xid # # COMMIT /* XID */ 343-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=<pos>; 344SELECT * FROM t1 ORDER BY a; 345a 3461 3472 348SELECT * FROM t2 ORDER BY a; 349a b 3501 0 3512 0 352DROP TABLE t1,t2; 353