MySQL Bugs: #33045: rpl_invoked_features fails because rows are output in different order

Bug #33045	rpl_invoked_features fails because rows are output in different order
Submitted:	6 Dec 2007 19:01	Modified:	2 Mar 2008 19:35
Reporter:	Vasil Dimov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Tests: Replication	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Serge Kozlov	CPU Architecture:	Any

Description:
I get this failure with the latest 5.1 from BK:

--- cut ---
Logging: ./mysql-test-run --force --skip-test=(information_schema|information_schema_db|innodb_mysql|mysqlshow|type_bit_innodb)
MySQL Version 5.1.23
...
rpl.rpl_invoked_features       [ fail ]

--- /tmp/mysql-5.1/mysql-test/suite/rpl/r/rpl_invoked_features.result   2007-12-03 09:15:27.000000000 +0300
+++ /tmp/mysql-5.1/mysql-test/suite/rpl/r/rpl_invoked_features.reject   2007-12-06 21:11:15.000000000 +0300
@@ -134,8 +134,8 @@
 PROCEDURE      p11
 SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
 event_name     status
-e1     DISABLED
 e11    DISABLED
+e1     DISABLED
 
 SELECT COUNT(*) FROM t1;
 COUNT(*)

mysqltest: Result content mismatch
--- cut ---

configured with:
./configure --prefix=/tmp/mysql-5.1-install --with-plugins=innobase

Strangely the test passes if I run
./mysql-test-run rpl_invoked_features
or
./mysql-test-run --suite=rpl

Obviously this is caused by some non-determinism in the order of the rows in the output.

How to repeat:
Just run ./mysql-test-run

Suggested fix:
The patch is obvious:

--- mysql-test/suite/rpl/t/rpl_invoked_features.test	2007-12-03 08:15:27.000000000 +0200
+++ mysql-test/suite/rpl/t/rpl_invoked_features.test	2007-12-06 20:28:40.000000000 +0200
@@ -205,7 +205,7 @@
 SELECT table_name FROM information_schema.views WHERE table_schema='test';
 SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
 SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
-SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
+SELECT event_name, status FROM information_schema.events WHERE event_schema='test' ORDER BY event_name;
 
 # Check original data
 --echo
--- mysql-test/suite/rpl/r/rpl_invoked_features.result	2007-12-03 08:15:27.000000000 +0200
+++ mysql-test/suite/rpl/r/rpl_invoked_features.result	2007-12-06 20:28:52.000000000 +0200
@@ -132,7 +132,7 @@
 FUNCTION	f2
 PROCEDURE	p1
 PROCEDURE	p11
-SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
+SELECT event_name, status FROM information_schema.events WHERE event_schema='test' ORDER BY event_name;
 event_name	status
 e1	DISABLED
 e11	DISABLED

Thank you for the bug report. I can't repeat with today source:

rpl.rpl_insert_select          [ pass ]            254
rpl.rpl_invoked_features       [ pass ]           4326
rpl.rpl_known_bugs_detection   [ pass ]            558
rpl.rpl_load_from_master       [ pass ]            743

Miguel, how did you invoke mysql-test-run?

Obviously there is some non-determinism because the test does not fail if I run
./mysql-test-run rpl_invoked_features
or
./mysql-test-run --suite=rpl

Anyway this need not be repeated in order to be fixed. The patch is obviously correct - it only adds ORDER BY to make the order of the rows deterministic.

Hi Vasil,

According your instructions: Just run ./mysql-test-run

I am changing to verified, according suggestion patch. Thanks.

Reproduced in the build of 5.1.23-rc on many platforms,
all of them were 64 bit ones
(but not all 64 bit ones showed the deviation).

I do *not* think we should use "ORDER BY" generously on all SELECTs because it changes the flow of control in the server,
rather consider "--sorted_result".

Joerg, I agree - --sorted_result is more appropriate.

fixed

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Pushed to 5.1-telco-6.3. 

Test failure only; no end-user changes to document.