MySQL Bugs: #39979: main.events_time

Bug #39979	main.events_time_zone does not clean up
Submitted:	10 Oct 2008 15:14	Modified:	8 Dec 2008 13:10
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Tests: Server	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Matthias Leich	CPU Architecture:	Any

Description:
Logging: ./mysql-test-run --big --check-testcases events_time_zone
--- ... var/tmp/check-master0.result
+++ ... var/tmp/check-master0.reject
@@ -546,7 +546,7 @@
   `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
   PRIMARY KEY (`Time_zone_id`)
-) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='Time zones';
+) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='Time zones';
--- ... var/tmp/check-master0.result
+++ var/tmp/check-master0.reject
@@ -546,7 +546,7 @@
   `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
   PRIMARY KEY (`Time_zone_id`)
-) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='Time zones';
+) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='Time zones';

This bug in testcase architecture has in the moment
most probably nearly no impact on the outcome of
Pushbuild.
But significant problems will come up
- extreme likely if MTR runs with the option
  "--reorder"
  "--reorder" accelerates the execution of testsuites
- likely if someone defines its own sequence of tests
  and this sequence contains events_bugs

The bug is very easy to fix.

My environment:
- Intel Core2Duo (64 Bit)
- Linux, OpenSuSE 11.0 (64 Bit)
- MySQL compiled from source
  tree: mysql-5.1-bugteam
  last changeset; 2008-10-10 16:48:47 +0500

How to repeat:
See above

Second bad effect:
Logging: ./mysql-test-run.pl --mem --big-test events_time_zone events_time_zone
MySQL Version 5.1.30
Using dynamic switching of binlog format
Using tmpfs in /dev/shm
Using ndbcluster when necessary, mysqld supports it
Setting mysqld to support SSL connections
Binaries are debug compiled
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using NDBCLUSTER_PORT       = 9311
Using NDBCLUSTER_PORT_SLAVE = 9312
Using IM_PORT               = 9313
Using IM_MYSQLD1_PORT       = 9314
Using IM_MYSQLD2_PORT       = 9315
Killing Possible Leftover Processes
Removing Stale Files
WARNING: Removing '/work2/5.1/mysql-5.1-bugteam-work/mysql-test/var' symlink it's wrong
Creating Directories
Symlinking 'var' to '/dev/shm/var'
Installing Master Database
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.events_time_zone          [ pass ]         127554
main.events_time_zone          [ fail ]

--- /work2/5.1/mysql-5.1-bugteam-work/mysql-test/r/events_time_zone.result      2008-08-21 13:34:43.000000000 +0300
+++ /work2/5.1/mysql-5.1-bugteam-work/mysql-test/r/events_time_zone.reject      2008-11-05 22:04:27.000000000 +0300
@@ -114,6 +114,7 @@
 INSERT INTO mysql.time_zone_name VALUES ('bug16420_2', @tzid);
 SET TIME_ZONE= 'bug16420_2';
 SET GLOBAL EVENT_SCHEDULER= ON;
+Timeout in wait_condition.inc for SELECT COUNT(*) = 7 FROM t1
 SET GLOBAL EVENT_SCHEDULER= OFF;
 Below we should see the following:
 - On Jan 31 only e2 is executed, because we started later than
@@ -128,13 +129,7 @@
 appointed times, offsets are -1 and 1.
 SELECT * FROM t1 ORDER BY dt, event;
 event  dt      offset
-e2     2030-01-31      0
-e1     2030-02-28      -1
-e2     2030-02-28      1
-e1     2030-03-31      2
-e2     2030-03-31      2
-e1     2030-04-30      -1
-e2     2030-04-30      1
+e2     2030-04-30      0
 DROP EVENT e2;
 DROP EVENT e1;
 DROP TABLE t1;

mysqltest: Result content mismatch

Aborting: main.events_time_zone failed in default mode.
To continue, re-run with '--force'.
Stopping All Servers

Reason for the first problem:
1. "--check-testcases" seems to collect "SHOW CREATE
   TABLE ..." at the beginning and after the end of the
   execution of the testscript and compares this
   output afterwards.
   This is in case of mysql.time_zone something like
   CREATE TABLE `time_zone` (
   `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   ...
   ) ENGINE=MyISAM AUTO_INCREMENT=6 ...
   The value after "AUTO_INCREMENT=" is the value to be
   used for the next inserted record.
   This differs between start and end of test.
2. There are two subtests which
   - add a new time zone
   - run some statements
   - delete the new time zone
   = They cause an increment of the AUTO_INCREMENT by two.
Could this increment of the value harm other tests?
Yes, in case a following test
- prints "SHOW CREATE TABLE ..." output
- adds a new time zone and prints the content
  of mysql.time_zone.Time_zone_id

Reason for the second problem:
We run the same test two times without server
shutdown/restart between.
Some comment within the test says that names
of time zones are cached and that names of
(already deleted) time zones cannot be reused.
= The second execution of the test becomes
  the victim of this behaviour.
  Experiments revealed if the second execution
  uses different time zone names the problem
  disappears.

This means any test following and "reusing" a
time zone name used in events_time_zone will
have a good chance to suffer from this caching.

Thinkable "solutions" for
- effect 1
  a) ALTER TABLE ... AUTO_INCREMENT= ...
     at the end of the test.
     This worked well now, but I fear that there is a good
     chance that this solutions breaks if the architecture
     of system tables gets changed in future
     Example: ALTER or INSERT INTO mysql.time_zone becomes
        disallowed, add time zones with other commands etc.
  b) Run with a copy of mysql.time_zone (not tested)
     Something like
     Start of test:
     CREATE TABLE tz_copy LIKE mysql.time_zone
     INSERT INTO tz_copy SELECT * FROM mysql.time_zone
     <run subtests>
     DROP TABLE mysql.time_zone
     RENAME TABLE tz_copy to mysql.time_zone
     IMHO there is also a good chance that this breaks
     in future.
- effect 2
  c) Use time zone names which change between different
     runs. Something like the time zone name contains
     UNIX_TIMESTAMP().
     This worked fine in experiments.
  d) Future improvements of the server.
     - It is IMHO an unfortunate architecture if names
       of removed time zones have an impact on the future.
     - Maybe we get a command for removing entries from
       the cache.
- effect 1 + 2
  e) Some feature which allows to "tell" mysql-test-run.pl
     to do the following operations after running the test
     - Server shutdown (avoids effect 1)
     - recreation of initial state (avoids effect 2)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58814

2708 Matthias Leich	2008-11-14
      Fix for Bug#39979 main.events_time_zone does not clean up
      + minor improvements.

Ok to push.  Tested on Mac OS X 10.5 and Win XP 32 bit without issues.

Pushed to
6.0-bugteam
5.1-bugteam

Pushed into 5.1.31  (revid:matthias.leich@sun.com-20081114161820-mo4i7897lmhtworp) (version source revid:patrick.crews@sun.com-20081126215253-rub0b9ykdsgx9fvl) (pib:5)

Pushed into 6.0.9-alpha  (revid:matthias.leich@sun.com-20081114161820-mo4i7897lmhtworp) (version source revid:satya.bn@sun.com-20081126062555-dw4wp7jun7hoysdb) (pib:5)

Test case changes. No changelog entry needed.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)