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
fail if the feature AUTO_INCREMENT is temporary broken
will (depending on existence of prerequisite checks) fail or get skipped if the default storage engine does not support AUTO_INCREMENT or LONGTEXT
==> no coverage for other features or properties checked within this test
Some questions with recommended action depending on the answer:
Is the table t1 just an auxiliary table and not the test object?
Yes: Please ensure that the test does not break or gets skipped if the default storage engine does not support AUTO_INCREMENT or LONGTEXT and you are done.
No: no action
Do we check AUTO_INCREMENT and the corresponding column is t1.id?
Yes: no action
No: Remove the use of AUTO_INCREMENT
Do we check LONGTEXT and the corresponding column is t1.name?
Yes: no action
No: Remove the use of LONGTEXT
Do we check AUTO_INCREMENT and LONGTEXT in combination?
Yes: no action
No: Split the test at least if it should be a test of basic functionality
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
we get a new system table
Completeness of result sets without WHERE are outside of the focus.
--replace_column 15 <CREATE_TIME> 16 <UPDATE_TIME> 17 <CHECK_TIME> SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
the number of columns within information_schema.tables changes
the data type of a column within ... changes
the content within one of the columns within ... changes
Lets assume something intentional like VERSION is now 11 instead of 10.
This means we should try to avoid "SELECT *".
Variant 1: SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
Are we really consequent? No, but we could
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.
disconnect <connection> or
KILL <connection_id> or
--exec <client program which connects to the server>
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
observing the session belonging to the previous test within the processlist
being surprised by an entry (the 'quit' of the disconnected session) within the general log which was not caused by its own activity etc.
According to my experiences the likelihood that some test could harm a successing test by unfinished disconnects increases with
additional parallel load on the testing box
"disconnect <connection>" is missing within the test
"shorter" distance between "disconnect <connection>", "KILL <connection_id>" or "--exec ..." and end of test
the session runs a "heavy" SQL statement just before its disconnect
In my opinion the most robust solution is to add a
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:
(1) Such a statement at the begin of a test makes this test very sensitive to not finished disconnects.
(2) The wait routine (3) prevents that our look on the process list (4) happens too early = before the server has started the processing of (2). Otherwise we get random result set differences for (4) under high parallel load.
How to to prevent that this test harms the succeeding test?
(5) should be replaced by connection con1; disconnect con1; --source include/wait_until_disconnected.inc connection default;
Where is the "crap"?
There should be a 'reap' for every preceeding 'send <statement>'. This is not just for academic completeness it also ensures here that (2) is really finished.
Do we really need 10 seconds within the SLEEP? According to my experiences sleep times <= 2 seconds are critical under high load. What about SLEEP(5)?