CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT,
  my_column VARCHAR(30),
  name LONGTEXT,
  PRIMARY KEY (id));

INSERT INTO t1(my_column,name) VALUES('2','two');
INSERT INTO t1(my_column,name) VALUES('1','one');
INSERT INTO t1(my_column,name) VALUES('4','four');
INSERT INTO t1(my_column,name) VALUES('2','two');
INSERT INTO t1(my_column,name) VALUES('3','three');

This test will

Some questions with recommended action depending on the answer:

Let's assume we have to check that every new created table causes a row in information_schema.tables.

 --replace_column 15 <CREATE_TIME> 16 <UPDATE_TIME> 17 <CHECK_TIME>
SELECT * FROM information_schema.tables; 

The SELECT above makes the test extreme "greedy" for changes in behaviour which is maybe good for general bug hunting but not for smart and frequent automatic tests. It is to be expected that such a test requires frequent maintenance like adjustment of expected results.

The problems with the select above: We will (sometimes only maybe) get a result set difference whenever

 --replace_column 15 <CREATE_TIME> 16 <UPDATE_TIME> 17 <CHECK_TIME>
SELECT * FROM information_schema.tables
  WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; 

Final statement:

 Variant 1:
SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.tables
  WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; 

 Variant 2:
SELECT COUNT(*) FROM information_schema.tables
  WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; 

It's on you which variant you choose. I decide depending on the situation.

Disconnects are performed asynchronous. This is most probably good for the throughput of usual application but has some annoying consequences for the development of tests.

If a

occurs within a script for mysqltest than the next statements will be executed even if the disconnect or kill is not complete finished.

I guess this also applies to the end (-> end of statements to be executed by mysqltest) of a test. It triggers some disconnect for any open session but this is also asynchronous.

Not complete finished means that the session is visible within the processlist and the entry about the 'quit' of the session is not already appended to the general log.

So it could happen that a succeeding test suffers by unexpected events like

According to my experiences the likelihood that some test could harm a successing test by unfinished disconnects increases with

In my opinion the most robust solution is to add a

 --source include/wait_until_disconnected.inc 

just after the disconnect.

In case you don't believe me, run the sniplet following later

./mtr --skip-ndb --no-check-testcases --repeat=100 <sniplet> 

and you will most probably observe something like

TEST                                 RESULT   TIME (ms)
------------------------------------------------------------
...
<sniplet>                           [ pass ]      4
<sniplet>                           [ fail ]
...
CURRENT_TEST: <sniplet>
--- <result>
+++ <reject>
@@ -1,4 +1,4 @@
SELECT COUNT(*) FROM information_schema.processlist WHERE id < CONNECTION_ID();
COUNT(*)
-0
+1
SELECT SLEEP(10);
...
mysqltest: Result content mismatch

Sniplet for demonstration purposes:

SELECT COUNT(*) FROM information_schema.processlist WHERE id < CONNECTION_ID();  # (1)
 connect (con1,localhost,root,,);
 send SELECT SLEEP(10); # (2)
 connection default;
 # Wait till the SQL statement of con1 is "in work"
 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
 WHERE State = 'User sleep' AND Info = 'SELECT SLEEP(10)';
 --source include/wait_condition.inc  # (3)
 SELECT COUNT(*) = 1 FROM information_schema.processlist
 WHERE State = 'User sleep' AND Info = 'SELECT SLEEP(10)'; # (4)
 disconnect con1; (5)
 <end of script>

The sniplet is intended for demonstration purposes and contains code which is obvious "crap" but needed to enforce the intended effect. Some notes:

(5) should be replaced by
connection con1;
disconnect con1;
--source include/wait_until_disconnected.inc
connection default;