MySQL Bugs: #40178: Test main.completion_type_func does not clean up / needs to be rewritten

Bug #40178	Test main.completion_type_func does not clean up / needs to be rewritten
Submitted:	20 Oct 2008 15:16	Modified:	18 Mar 2009 14:37
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Tests: Server	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Patrick Crews	CPU Architecture:	Any
Tags:	pushbuild, random failure, test failure, Tests

Description:
Test main.identity_func failing randomly on Pushbuild:

For some reason, the failures noted all fail at this step:

mysqltest: At line 34: query 'CREATE TABLE t1
35274: (
35275: id INT NOT NULL auto_increment,
35276: PRIMARY KEY (id),
35277: name VARCHAR(30)
35278: ) ENGINE = INNODB' failed: 1005: Can't create table 'test.t1' (errno: 121)
35279: 
35280: The result from queries just before the failure was:
35281: drop table if exists t1;
35282: drop table if exists t2;
35283: ## Creating new table t1 ##
35284: CREATE TABLE t1
35285: (
35286: id INT NOT NULL auto_increment,
35287: PRIMARY KEY (id),
35288: name VARCHAR(30)
35289: ) ENGINE = INNODB;

How to repeat:
Run the test from the mysql-test directory:

./mtr identity_func

Suggested fix:
Examine the test-case to determine if it is at fault.

The fact that the test is randomly failing on a CREATE statement is curious.  It is possible that this test or one of it's neighbors isn't cleaning up properly.

Noticed that MTR is using --skip-innodb option for every failure.

As this test calls for innodb, one would think it would be skipped in such a scenario.  However, initial tests don't indicate that this is the cause of the failures.

Found that creating a new database, creating a table - t1, and copying the t1.FRM file to the test database will allow DROP TABLE t1 to properly clear out InnoDB's data dictionary and allow us to create a t1 table for this test.

Still trying to locate the test that is causing this mess as the above method is a bit of a hack (doesn't correct the original problem and leaves the potential for this problem to occur with some other test).

This test will not fail when MTR is run without the --skip-innodb option.  Currently examining the innodb tests that precede this test to try to figure out where things are going wrong.

Found that completion_type_func.test is causing the identity_func failure as it does not cleanup at the end of the test.

This only occurs when run with --skip-innodb. However, fixing this clears up identity_func failures.

Patch is on it's way.

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/59009

2712 Patrick Crews	2008-11-17
      Bug#40178 Test main.identity_func failing randomly on Pushbuild
      
      Found that main.completion_type_func was not properly cleaning up after itself.
      This was leaving an orphan t1 table in the Innodb data dictionary.
      
      Added SQL to clean up after the test, cleaned up SQL syntax in completion_type_func,
      and removed redundant master.opt files for both tests.  They use include/have_innodb.inc, so 
      a master.opt file is not necessary.

Hi Patrick,
it's great that you found that
- main.completion_type_func.test is guilty
- the opt files are not needed.

I am fine with the code changes but please would you do
the following so that nothing of the information we
have is lost. Sorry for inconvenience.
1. Modify the bug title so that it reflects our current knowledge
   Something like
   "main.completion_type_func does not clean up, main.identity_func fails"
   IMHO this helps to search within the BugsDB.
2. I had of course a look into main.completion_type_func and
   I am not happy about this test at all.
   - A subtest of @@session.completion_type = 1 is missing.
   - It is for me not obvious that it is tested what needs
     to be checked.
   - It should test "completion_type" and not break if PRIMARY
     KEY or AUTO_INCREMENT are wrong working or not available.
   So please add to the header of the test
   FIXME(mleich):
   - Reimplement this test
   - Remove usage of PRIMARY KEY and AUTO_INCREMENT
3. Merge + push

Thank you

Matthias

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/67388

2812 Patrick Crews	2009-02-24
      Bug#40178: Test main.completion_type_func does not clean up / needs to be rewritten
      
      Revised the test to include a test of completion_type = 1 as well as making the test more readable / worthwhile
      Removed the master.opt file as it was redundant / unnecessary.
     @ mysql-test/suite/sys_vars/t/completion_type_func-master.opt
        Removed as redundant.  Test uses include/have_innodb.inc.

Reveiwed. Everything ok.

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:patrick.crews@sun.com-20090224142000-mg367021plijo0l9) (merge vers: 5.1.33) (pib:6)

Test case changes. No changelog entry needed.

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:patrick.crews@sun.com-20090225083650-5nengju975odpgdf) (merge vers: 6.0.10-alpha) (pib:6)

Test case changes. No changelog entry needed.

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)