Bug #53102 perfschema.pfs_upgrade fails when executed direct after mysql_upgrade
Submitted: 23 Apr 2010 9:08 Modified: 7 Oct 2010 23:14
Reporter: Alexander Nozdrin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:M3 (Celosia) OS:Any
Assigned to: Marc Alff
Tags: experimental, pb2, sporadic, test failure
Triage: Triaged: D3 (Medium)

[23 Apr 2010 9:08] Alexander Nozdrin
Description:
perfschema.pfs_upgrade fails on 'sol10 sparc64 max' running
with MTR_MAX_PARALLEL=4.

Symptoms:
perfschema.pfs_upgrade w2 [ fail ]
        Test ended at 2010-04-22 22:49:26

CURRENT_TEST: perfschema.pfs_upgrade
--- /export/home/pb2/test/sb_0-1717512-1271967938.91/mysql-5.5.5-m3-solaris10-sparc-64bit-test/mysql-test/suite/perfschema/r/pfs_upgrade.result	2010-04-22 22:29:04.000000000 +0300
+++ /export/home/pb2/test/sb_0-1717512-1271967938.91/mysql-5.5.5-m3-solaris10-sparc-64bit-test/mysql-test/suite/perfschema/r/pfs_upgrade.reject	2010-04-22 23:49:26.008506000 +0300
@@ -8,26 +8,6 @@
 show tables like "user_table";
 Tables_in_performance_schema (user_table)
 user_table
-ERROR 1050 (42S01) at line 183: Table 'COND_INSTANCES' already exists
-ERROR 1050 (42S01) at line 213: Table 'EVENTS_WAITS_CURRENT' already exists
-ERROR 1050 (42S01) at line 227: Table 'EVENTS_WAITS_HISTORY' already exists
-ERROR 1050 (42S01) at line 241: Table 'EVENTS_WAITS_HISTORY_LONG' already exists
-ERROR 1050 (42S01) at line 261: Table 'EVENTS_WAITS_SUMMARY_BY_EVENT_NAME' already exists
-ERROR 1050 (42S01) at line 282: Table 'EVENTS_WAITS_SUMMARY_BY_INSTANCE' already exists
-ERROR 1050 (42S01) at line 303: Table 'EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME' already exists
-ERROR 1050 (42S01) at line 320: Table 'FILE_INSTANCES' already exists
-ERROR 1050 (42S01) at line 339: Table 'FILE_SUMMARY_BY_EVENT_NAME' already exists
-ERROR 1050 (42S01) at line 359: Table 'FILE_SUMMARY_BY_INSTANCE' already exists
-ERROR 1050 (42S01) at line 376: Table 'MUTEX_INSTANCES' already exists
-ERROR 1050 (42S01) at line 394: Table 'PERFORMANCE_TIMERS' already exists
-ERROR 1050 (42S01) at line 411: Table 'PROCESSLIST' already exists
-ERROR 1050 (42S01) at line 429: Table 'RWLOCK_INSTANCES' already exists
-ERROR 1050 (42S01) at line 445: Table 'SETUP_CONSUMERS' already exists
-ERROR 1050 (42S01) at line 462: Table 'SETUP_INSTRUMENTS' already exists
-ERROR 1050 (42S01) at line 482: Table 'SETUP_OBJECTS' already exists
-ERROR 1050 (42S01) at line 498: Table 'SETUP_TIMERS' already exists
-ERROR 1644 (HY000) at line 1140: Unexpected content found in the performance_schema database.
-FATAL ERROR: Upgrade failed
 show tables like "user_table";
 Tables_in_performance_schema (user_table)
 user_table
@@ -39,26 +19,6 @@
 show tables like "user_view";
 Tables_in_performance_schema (user_view)
 user_view
-ERROR 1050 (42S01) at line 183: Table 'COND_INSTANCES' already exists
-ERROR 1050 (42S01) at line 213: Table 'EVENTS_WAITS_CURRENT' already exists
-ERROR 1050 (42S01) at line 227: Table 'EVENTS_WAITS_HISTORY' already exists
-ERROR 1050 (42S01) at line 241: Table 'EVENTS_WAITS_HISTORY_LONG' already exists
-ERROR 1050 (42S01) at line 261: Table 'EVENTS_WAITS_SUMMARY_BY_EVENT_NAME' already exists
-ERROR 1050 (42S01) at line 282: Table 'EVENTS_WAITS_SUMMARY_BY_INSTANCE' already exists
-ERROR 1050 (42S01) at line 303: Table 'EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME' already exists
-ERROR 1050 (42S01) at line 320: Table 'FILE_INSTANCES' already exists
-ERROR 1050 (42S01) at line 339: Table 'FILE_SUMMARY_BY_EVENT_NAME' already exists
-ERROR 1050 (42S01) at line 359: Table 'FILE_SUMMARY_BY_INSTANCE' already exists
-ERROR 1050 (42S01) at line 376: Table 'MUTEX_INSTANCES' already exists
-ERROR 1050 (42S01) at line 394: Table 'PERFORMANCE_TIMERS' already exists
-ERROR 1050 (42S01) at line 411: Table 'PROCESSLIST' already exists
-ERROR 1050 (42S01) at line 429: Table 'RWLOCK_INSTANCES' already exists
-ERROR 1050 (42S01) at line 445: Table 'SETUP_CONSUMERS' already exists
-ERROR 1050 (42S01) at line 462: Table 'SETUP_INSTRUMENTS' already exists
-ERROR 1050 (42S01) at line 482: Table 'SETUP_OBJECTS' already exists
-ERROR 1050 (42S01) at line 498: Table 'SETUP_TIMERS' already exists
-ERROR 1644 (HY000) at line 1140: Unexpected content found in the performance_schema database.
-FATAL ERROR: Upgrade failed
 show tables like "user_view";
 Tables_in_performance_schema (user_view)
 user_view
@@ -68,26 +28,6 @@
 create procedure test.user_proc()
 select "Not supposed to be here";
 update mysql.proc set db='performance_schema' where name='user_proc';
-ERROR 1050 (42S01) at line 183: Table 'COND_INSTANCES' already exists
-ERROR 1050 (42S01) at line 213: Table 'EVENTS_WAITS_CURRENT' already exists
-ERROR 1050 (42S01) at line 227: Table 'EVENTS_WAITS_HISTORY' already exists
-ERROR 1050 (42S01) at line 241: Table 'EVENTS_WAITS_HISTORY_LONG' already exists
-ERROR 1050 (42S01) at line 261: Table 'EVENTS_WAITS_SUMMARY_BY_EVENT_NAME' already exists
-ERROR 1050 (42S01) at line 282: Table 'EVENTS_WAITS_SUMMARY_BY_INSTANCE' already exists
-ERROR 1050 (42S01) at line 303: Table 'EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME' already exists
-ERROR 1050 (42S01) at line 320: Table 'FILE_INSTANCES' already exists
-ERROR 1050 (42S01) at line 339: Table 'FILE_SUMMARY_BY_EVENT_NAME' already exists
-ERROR 1050 (42S01) at line 359: Table 'FILE_SUMMARY_BY_INSTANCE' already exists
-ERROR 1050 (42S01) at line 376: Table 'MUTEX_INSTANCES' already exists
-ERROR 1050 (42S01) at line 394: Table 'PERFORMANCE_TIMERS' already exists
-ERROR 1050 (42S01) at line 411: Table 'PROCESSLIST' already exists
-ERROR 1050 (42S01) at line 429: Table 'RWLOCK_INSTANCES' already exists
-ERROR 1050 (42S01) at line 445: Table 'SETUP_CONSUMERS' already exists
-ERROR 1050 (42S01) at line 462: Table 'SETUP_INSTRUMENTS' already exists
-ERROR 1050 (42S01) at line 482: Table 'SETUP_OBJECTS' already exists
-ERROR 1050 (42S01) at line 498: Table 'SETUP_TIMERS' already exists
-ERROR 1644 (HY000) at line 1140: Unexpected content found in the performance_schema database.
-FATAL ERROR: Upgrade failed
 select name from mysql.proc where db='performance_schema';
 name
 user_proc
....
mysqltest: Result content mismatch

How to repeat:
Check out PB.
[24 Apr 2010 8:34] Sveta Smirnova
Thank you for the report.

Verified using PB logs. Not repeatable on Support box.
[31 May 2010 13:08] Guilhem Bichot
I get it too on my local Linux box with --parallel=16 --mem. So it's not specific of pushbuil2d.
[31 May 2010 13:55] Bjørn Munch
Hmm, I see that the test redirects output of mysql_upgrade to a file, and then immediately prints that file using cat_file. The failure report seems to mean that cat_file produced no output. Perhaps the file hadn't been flushed, on a heavily loaded system? Ideally this shouldn't happen....
[9 Jun 2010 12:51] Luis Soares
Just to confirm Guilhem's comment ([31 May 15:08] Guilhem Bichot).

I observed this as well (locally) with:

 --parallel=8 --vardir=/dev/shm/var_...

  OS: x86_64 GNU/Linux
tree: mysql-next-mr-rpl-merge
[14 Jun 2010 8:23] Bjørn Munch
I've been totally unable to reproduce this problem. Tried building myself on three different platforms (Solaris Sparc and x86, Linux) and even tried running the same binaries as in the PB run that failed, on the same box, with same --parallel setting and with vardir on local disk, but still no failure. And without reproduction, I cannot test any possible fix.
[16 Jun 2010 13:17] Bjørn Munch
After umpteen reruns, I was finally able to reproduce once using a modified version of the test, one that keeps the output from all the mysql_upgrade runs.

When the test failed, all 5 runs of mysql_upgrade had just written:

This installation of MySQL is already upgraded to 5.6.99-m4, use --force if you still need to run mysql_upgrade

This does not happen if I run only this test, even if I run it over and over in several parallel threads. I got it by running the whole test suite with --parallel=4. But even then it's not consistently repeatable.
[25 Jun 2010 4:07] Anitha Gopi
Bjorn,
To reproduce the problem did you have to run the complete testsuite or just the perfschema suite. I ran the perfschema suite several times, but could not reproduce. Which machine did you use to run your tests.
[20 Jul 2010 19:12] Bugs System
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/113980

3133 Matthias Leich	2010-07-20
      Bug#53102 perfschema.pfs_upgrade fails on sol10 sparc64 max in parallel mode
      The reason for the bug above is unclear but
      - Modify pfs_upgrade so that it's result is easier to analyze in case something fails
      - Fix several minor weaknesses which could cause that a successing test (either an
        already existing or a to be developed one) fails because of imperfect cleanup,
        too slow disconnected sessions etc.
      should either fix the bug or reduce it's probability or at least
      make the analysis of failures easier.
     @ mysql-test/suite/perfschema/include/upgrade_check.inc
        New include file which contains redundant stuff taken from pfs_upgrade.test.
        Remove any file which might harm analysis of suspicious results.
     @ mysql-test/suite/perfschema/r/query_cache.result
        Updated results
     @ mysql-test/suite/perfschema/r/selects.result
        Updated results
     @ mysql-test/suite/perfschema/t/bad_option_1.test
        Add the missing remove_file at beginning and end of test.
     @ mysql-test/suite/perfschema/t/bad_option_2.test
        Add the missing remove_file at beginning and end of test.
     @ mysql-test/suite/perfschema/t/global_read_lock.test
        Add a wait routine which ensures that the disconnect is really completed when the test ends.
     @ mysql-test/suite/perfschema/t/pfs_upgrade.test
        - Move redundant actions to include/upgrade_check.inc
        - Add preemptive removal of files
     @ mysql-test/suite/perfschema/t/privilege.test
        Add a wait routine which ensures that the disconnect is really completed when the test ends.
     @ mysql-test/suite/perfschema/t/query_cache.test
        Add "flush status" so that counters are reset. (./mtr --repeat=2 perfschema.query_cache failed)
     @ mysql-test/suite/perfschema/t/read_only.test
        Add a wait routine which ensures that the disconnect is really completed when the test ends.
     @ mysql-test/suite/perfschema/t/selects-master.opt
        Needed for running with enabled event-scheduler
     @ mysql-test/suite/perfschema/t/selects.test
        - Correct the sub test for the EVENT scheduler
        - Replace "sleep" by wait_routine
        - Add premptive cleanups like "DROP ... IF EXISTS ..."
[20 Jul 2010 19:41] Matthias Leich
1. I was unable to reproduce this bug.
   IMHO either the content of the printed files (cat_file)
   - belongs to some other run + these files were not
     overwritten with actual+different results
     by $MYSQL_UPGRADE   
   or
   - belongs to the current run and shows that the
     performance_schema content is from whatever reason like
     - some preceding test does not cleanup perfect
     - missing resources on testing box
     unexpected
   pfs_upgrade.test is not 100% perfect but none of the
   minor weaknesses found could be the reason for
   the bug.
2. The patch is simply a brute force attack on the thinkable
   reasons of the bug.
[20 Jul 2010 20:27] Marc Alff
Patch approved as is.

Doc team:

This will make the performance schema test suite more robust.
Test changes only, no change in the server itself.

-- Marc
[21 Jul 2010 11:20] Bugs System
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/114031

3319 Matthias Leich	2010-07-21 [merge]
      Upmerge of fix for 
         Bug#53102 perfschema.pfs_upgrade fails on sol10 sparc64 max in parallel mode
[26 Jul 2010 16:56] Bugs System
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/114372

3152 Matthias Leich	2010-07-26 [merge]
      Merge of fix for Bug#53102 perfschema.pfs_upgrade fails on sol10 sparc64 max in parallel mode
      into actual tree. No conflicts.
[26 Jul 2010 20:18] Bugs System
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/114379

3333 Matthias Leich	2010-07-26 [merge]
      Upmerge of fix for Bug#53102 perfschema.pfs_upgrade fails on sol10 sparc64 max in parallel mode
[26 Jul 2010 20:23] Matthias Leich
Pushed to
mysql-trunk-bugfixing
mysql-next-mr-bugfixing
[4 Aug 2010 7:53] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:11] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:27] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 9:05] Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)
[4 Aug 2010 20:10] Paul Dubois
No changelog entry needed.
[12 Aug 2010 12:26] Guilhem Bichot
I get it with the latest trunk-bugfixing
revision-id:marc.alff@oracle.com-20100812104430-0kua6r2vwzx2ly02
on linux. It's repeatable with
./mtr --mem   mysql_upgrade pfs_upgrade_lc0
(verify that it executes tests in this order).
A possible theory: mysql_upgrade.test upgraded the database, and pfs_upgrade_lc0 operates on this database (cleanup problem): so when pfs_upgrade_lc0 calls mysql_upgrade, mysql_upgrade finds that the db is already upgraded, and does nothing. In mysql_upgrade.test such problem is avoided by using --force.
[30 Aug 2010 13:41] Matthias Leich
Hi Guilhem,

thank you for catching this. I tested with so many variants
but never with mixup of main and perf_schema suite. :-(

Regards

Matthias
[2 Sep 2010 14:26] Bernt Marius Johnsen
Fails also on trunk and next-mr for pfs_upgrade_lc0 (possibly also lc1 and lc2). The bug is not sporadic but can be repeated consisently like this:

./mtr  mysql_upgrade pfs_upgrade --suite=main,perfschema
or (on 5.6):
./mtr  mysql_upgrade pfs_upgrade_lc0 --suite=main,perfschema
[13 Sep 2010 23:19] Bugs System
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/118121

3207 Marc Alff	2010-09-13
      Bug#53102 perfschema.pfs_upgrade fails when executed direct after mysql_upgrade
      
      Implemented post review comments.
      
      Added --force to the mysql_upgrade command in the test scripts,
      so that the test output does not depends on whether other tests involving an
      upgrade have been executed or not in the same test suite execution.
[13 Sep 2010 23:47] Marc Alff
Post review fix pushed into:
- mysql-5.5-bugfixing
- mysql-trunk-bugfixing
- mysql-next-mr-bugfixing.

Doc team:
minor fix in a test script to make it more robust, no functional change to document.
[2 Oct 2010 18:13] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alexander.nozdrin@oracle.com-20101002180948-852x1cuv7c6i85ea) (version source revid:alexander.nozdrin@oracle.com-20101002180857-an32jpuwzemsp4f2) (merge vers: 5.6.1-m4) (pib:21)
[2 Oct 2010 18:16] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[2 Oct 2010 18:16] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:alexander.nozdrin@oracle.com-20101002180831-590ka2tuit9qoxbb) (version source revid:alexander.nozdrin@oracle.com-20101002180831-590ka2tuit9qoxbb) (merge vers: 5.5.7-rc) (pib:21)
[7 Oct 2010 23:14] Paul Dubois
No changelog entry needed.