2
# This test was created to ensure appropriate locks are obtained on the myisam
3
# tables during a myisam native driver restore. See BUG#36749.
5
# The test uses two connections and debug synchronization to ensure the restore
6
# in the middle of processing when a trigger attempts to insert data.
9
--source include/not_embedded.inc
10
--source include/have_innodb.inc
11
--source include/have_debug_sync.inc
13
SET DEBUG_SYNC= 'RESET';
15
connect(con1, localhost, root,,);
16
connect(con2, localhost, root,,);
17
connect(breakpoints, localhost, root,,);
23
DROP DATABASE IF EXISTS db1;
24
DROP DATABASE IF EXISTS db2;
25
DROP DATABASE IF EXISTS db3;
29
--remove_file $MYSQLTEST_VARDIR/master-data/db1.bak
32
--remove_file $MYSQLTEST_VARDIR/master-data/db3.bak
34
--echo Create database 1 and a table then populate it
36
CREATE TABLE db1.t1 (a INT) ENGINE=MYISAM;
38
INSERT INTO db1.t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
39
INSERT INTO db1.t1 SELECT * FROM db1.t1;
40
INSERT INTO db1.t1 SELECT * FROM db1.t1;
41
INSERT INTO db1.t1 SELECT * FROM db1.t1;
42
INSERT INTO db1.t1 SELECT * FROM db1.t1;
43
INSERT INTO db1.t1 SELECT * FROM db1.t1;
44
INSERT INTO db1.t1 SELECT * FROM db1.t1;
45
INSERT INTO db1.t1 SELECT * FROM db1.t1;
46
INSERT INTO db1.t1 SELECT * FROM db1.t1;
47
INSERT INTO db1.t1 SELECT * FROM db1.t1;
49
INSERT INTO db1.t1 SELECT * FROM db1.t1;
50
INSERT INTO db1.t1 SELECT * FROM db1.t1;
51
INSERT INTO db1.t1 SELECT * FROM db1.t1;
52
INSERT INTO db1.t1 SELECT * FROM db1.t1;
53
INSERT INTO db1.t1 SELECT * FROM db1.t1;
54
INSERT INTO db1.t1 SELECT * FROM db1.t1;
56
CREATE TABLE db1.t2 (a int) ENGINE=MEMORY;
58
INSERT INTO db1.t2 VALUES (1),(2),(3),(4),(5);
60
CREATE TABLE db1.t3 (a int) ENGINE=INNODB;
62
INSERT INTO db1.t3 VALUES (11),(12),(13);
64
--echo Show initial count of table
65
SELECT COUNT(*) FROM db1.t1;
67
SELECT COUNT(*) FROM db1.t2;
69
SELECT COUNT(*) FROM db1.t3;
74
--echo Create database 2 and a table then populate it and add a trigger
75
--echo that updates the table in database 1
78
CREATE TABLE db2.t2 (A INT);
82
CREATE TRIGGER db2.trg AFTER INSERT ON db2.t2 FOR EACH ROW
84
INSERT INTO db1.t1 VALUES ('99');
92
--echo Now do the backup
94
BACKUP DATABASE db1 TO 'db1.bak';
98
--echo now start the restore and while the restore is running, fire the trigger
99
--echo activate synchronization points for restore.
100
SET DEBUG_SYNC= 'restore_in_progress SIGNAL wait_for_restore WAIT_FOR finish';
101
--send RESTORE FROM 'db1.bak'
103
--echo From breakpoints:
104
--connection breakpoints
105
--echo Wait for restore to reach its synchronization point.
106
SET DEBUG_SYNC= 'now WAIT_FOR wait_for_restore';
108
--echo breakpoints: Show process list.
110
query_vertical SELECT id, command, state, info FROM INFORMATION_SCHEMA.PROCESSLIST
111
WHERE info LIKE "RESTORE%";
115
--echo Now do the insert while restore is running.
116
send INSERT INTO db2.t2 VALUES (0);
118
--echo From breakpoints:
119
--connection breakpoints
120
--echo breakpoints: Sending finish signal to wake restore.
121
SET DEBUG_SYNC= 'now SIGNAL finish';
123
--echo Reattach to connection 2 and finish.
127
--echo Reattach to connection 1 and finish.
132
--echo Show the count for t1. It should be 1 more than before restore.
133
SELECT COUNT(*) FROM db1.t1;
135
SELECT * FROM db1.t2;
137
SELECT * FROM db1.t3;
139
SET DEBUG_SYNC= 'RESET';
142
# BUG#36778 - Data loss during select at time of restore.
147
--echo now start the restore and while the restore is running, fire the trigger
148
--echo activate synchronization points for restore.
149
SET DEBUG_SYNC= 'restore_in_progress SIGNAL wait_for_restore WAIT_FOR finish';
150
--send RESTORE FROM 'db1.bak'
152
--echo From breakpoints:
153
--connection breakpoints
154
--echo Wait for restore to reach its synchronization point.
155
SET DEBUG_SYNC= 'now WAIT_FOR wait_for_restore';
157
--echo breakpoints: Show process list.
159
query_vertical SELECT id, command, state, info FROM INFORMATION_SCHEMA.PROCESSLIST
160
WHERE info LIKE "RESTORE%";
164
--echo Now do the select while restore is running.
165
send SELECT * FROM db1.t1 limit 10;
167
--echo From breakpoints:
168
--connection breakpoints
169
--echo breakpoints: Sending finish signal to wake restore.
170
SET DEBUG_SYNC= 'now SIGNAL finish';
172
--echo Reattach to connection 2 and finish.
176
--echo Reattach to connection 1 and finish.
181
--echo Show the count for t1. It should be the same as before restore.
182
SELECT COUNT(*) FROM db1.t1;
184
SELECT * FROM db1.t2;
186
SELECT * FROM db1.t3;
188
SET DEBUG_SYNC= 'RESET';
191
# BUG#36782 - Data loss with restore of view.
194
--echo Create a database with a table and a view using the MyISAM engine.
196
CREATE TABLE db3.t1(name CHAR(10)) ENGINE=MYISAM;
197
INSERT INTO db3.t1 VALUES('A'),('B'),('C'),('D');
198
CREATE VIEW db3.v1 AS SELECT * FROM db3.t1;
200
--echo Show the data before backup
201
SELECT * FROM db3.t1;
202
SELECT * FROM db3.v1;
204
--echo Backup the database.
206
BACKUP DATABASE db3 TO 'db3.bak';
208
--echo Now drop then restore the database.
211
RESTORE FROM 'db3.bak';
213
--echo Show the table and view.
214
SHOW FULL TABLES FROM db3;
216
--echo Show the data after restore.
217
SELECT * FROM db3.v1;
218
SELECT * FROM db3.t1;
226
--remove_file $MYSQLTEST_VARDIR/master-data/db1.bak
229
--remove_file $MYSQLTEST_VARDIR/master-data/db3.bak