Bug #50788 main.merge fails on HPUX
Submitted: 1 Feb 2010 11:48 Modified: 12 Aug 2010 18:57
Reporter: Ingo Strüwing Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S2 (Serious)
Version:6.0.14-alpha OS:HP/UX
Assigned to: Ingo Strüwing CPU Architecture:Any

[1 Feb 2010 11:48] Ingo Strüwing
Description:
This happens on mysql-6.0-backup-merge only (for now). So category: backup.

main.merge [ fail ]
        Test ended at 2010-01-28 11:19:26

CURRENT_TEST: main.merge
mysqltest: At line 2049: query 'INSERT INTO m2 SELECT * FROM m1 LIMIT 40' failed: 1194: Table 'm1' is marked as crashed and should be repaired

The result from queries just before the failure was:
< snip >
#
CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
INSERT_METHOD=LAST SELECT * FROM m1;
ERROR HY000: 'test.m2' is not BASE TABLE
#
CREATE TABLE m2 LIKE m1;
SHOW CREATE TABLE m2;
Table	Create Table
m2	CREATE TABLE `m2` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
SELECT * FROM m2;
c1	c2
111	121
211	221
212	222
213	223
# Nowadays CREATE ... LIKE copies the UNION. # Note: Limit rows! Otherwise inserted rows are selected: infinite loop.

More results from queries before failure can be found in /export/tmp/pb2/test/sb_1-1245184-1264671802.21/mysql-6.0.14-alpha-hpux11.31-ia64-test/mysql-test/var-n_mix/log/merge.log

This happens even after I inserted REPAIR TABLE for m1 and all of its children. now it looks like problems within the INSERT-SELECT statement. This bug report solicits investogation on this.

How to repeat:
http://pb2.norway.sun.com/web.py?branch=mysql-6.0-backup-merge&template=show_pushes

http://pb2.norway.sun.com/web.py?template=mysql_show_test_failure&search=yes&push_id=88064...

http://pb2.norway.sun.com/web.py?action=archive_download&archive_id=1256016&pretty=please
[2 Feb 2010 22:20] Ingo Strüwing
The problem is limited to HPUX because it does not seem to enable the use of memory mapped files (HAVE_MMAP).

By default, the use of memory mapped files is turned off on all systems (--myisam-use-mmap defaults to OFF). However, MERGE does not care about this option. If the optimizer requests HA_EXTRA_MMAP on a TEMPORARY MERGE table, mmapping is turned on for the MyISAM tables regardless of the option. On HPUX this does not work as mmapping doesn't seem to be compiled in.

If MyISAM mmapping is turned on for an INSERT-SELECT statement, where source and destination tables are identical, the inserted rows are selected back. The loop ends when the table is full.

If mmapping is turned off, the loop breaks after the original rows have been copied. But this doesn't seem to happen by design, but by a bug in mf_iocache.c or by its caller. The loop breaks with the false error message "table is marked as crashed".

By copying the suppression of HA_EXTRA_MMAP in case of opt_myisam_use_mmap==0 from MyISAM to MERGE, the behavior of HPUX can be repeated on Linux too.

Extracted test case:

CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE=MyISAM;
CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
  INSERT_METHOD=LAST;
CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) INSERT_METHOD=LAST;
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2);
INSERT INTO m2 SELECT * FROM m1;
[5 Feb 2010 16:15] 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/99481

3826 Ingo Struewing	2010-02-05
      Bug#50788 - main.merge fails on HPUX
      
      The test case main.merge failed on HPUX. The problem wass limited to
      HPUX because it does not seem to enable the use of memory mapped files
      (HAVE_MMAP).
      
      The analyze revealed three problems:
      
      1. If memory mapping is not used, the test failed with a bogus error
      of "table marked as crashed". This was caused by a misinterpretation
      of the return codes from the I/O cache in MyISAM.
      
      2. If memory mapping is used, it is possible in an INSERT-SELECT
      statement to read back records that have been written in the same
      statement, thus producing infinite records.
      
      3. The MERGE engine did not honor the --myisam-use-mmap option.
      It always turned on MyISAM memory mapping, when the optimizer
      suggested to do so.
      
      The first problem is fixed by adding a check for end of file if
      the I/O cache returns error.
      
      The second problem is fixed by adding an outer loop for MERGE
      tables to the search for a duplicate table. That way, duplicates of
      children can be detected.
      
      The third problem is fixed by ignoring a request for memory mapping,
      if the --myisam-use-mmap option is not active. This is, what MyISAM
      does too.
     @ mysql-test/r/merge.result
        Bug#50788 - main.merge fails on HPUX
        Updated test result.
     @ mysql-test/r/merge_mmap.result
        Bug#50788 - main.merge fails on HPUX
        New test result file.
     @ mysql-test/t/merge.test
        Bug#50788 - main.merge fails on HPUX
        Fixed test case.
        INSERT_SELECT does no longer require a LIMIT clause.
        Added more SELECTs and SHOWs.
     @ mysql-test/t/merge_mmap-master.opt
        Bug#50788 - main.merge fails on HPUX
        New options file.
     @ mysql-test/t/merge_mmap.test
        Bug#50788 - main.merge fails on HPUX
        New test file.
     @ mysys/mf_iocache.c
        Bug#50788 - main.merge fails on HPUX
        Added comments.
     @ sql/sql_base.cc
        Bug#50788 - main.merge fails on HPUX
        Added an outer loop for MERGE tables to the search
        for a duplicate table.
     @ storage/myisam/mi_statrec.c
        Bug#50788 - main.merge fails on HPUX
        Fixed misinterpretation of the return value of my_b_read().
        Added comments.
     @ storage/myisammrg/ha_myisammrg.cc
        Bug#50788 - main.merge fails on HPUX
        Skip HA_EXTRA_MMAP, if MyISAM memory mapping is disabled.
[16 Feb 2010 8:40] 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/100465

3826 Ingo Struewing	2010-02-16
      Bug#50788 - main.merge fails on HPUX
      
      The test case main.merge failed on HPUX. The problem wass limited to
      HPUX because it does not seem to enable the use of memory mapped files
      (HAVE_MMAP).
      
      The analyze revealed three problems:
      
      1. If memory mapping is not used, the test failed with a bogus error
      of "table marked as crashed". This was caused by a misinterpretation
      of the return codes from the I/O cache in MyISAM.
      
      2. If memory mapping is used, it is possible in an INSERT-SELECT
      statement to read back records that have been written in the same
      statement, thus producing infinite records.
      
      3. The MERGE engine did not honor the --myisam-use-mmap option.
      It always turned on MyISAM memory mapping, when the optimizer
      suggested to do so.
      
      The first problem is fixed by adding a check for end of file if
      the I/O cache returns error.
      
      The second problem is fixed by adding an outer loop for MERGE
      tables to the search for a duplicate table. That way, duplicates of
      children can be detected.
      
      The third problem is fixed by ignoring a request for memory mapping,
      if the --myisam-use-mmap option is not active. This is, what MyISAM
      does too.
     @ mysql-test/r/merge.result
        Bug#50788 - main.merge fails on HPUX
        Updated test result.
     @ mysql-test/r/merge_mmap.result
        Bug#50788 - main.merge fails on HPUX
        New test result file.
     @ mysql-test/t/merge.test
        Bug#50788 - main.merge fails on HPUX
        Fixed test case.
        INSERT_SELECT does no longer require a LIMIT clause.
        Added more SELECTs and SHOWs.
     @ mysql-test/t/merge_mmap-master.opt
        Bug#50788 - main.merge fails on HPUX
        New options file.
     @ mysql-test/t/merge_mmap.test
        Bug#50788 - main.merge fails on HPUX
        New test file.
     @ mysys/mf_iocache.c
        Bug#50788 - main.merge fails on HPUX
        Added comments.
     @ sql/sql_base.cc
        Bug#50788 - main.merge fails on HPUX
        Added an outer loop for MERGE tables to the search
        for a duplicate table.
     @ storage/myisam/mi_statrec.c
        Bug#50788 - main.merge fails on HPUX
        Fixed misinterpretation of the return value of my_b_read().
        Added comments.
     @ storage/myisammrg/ha_myisammrg.cc
        Bug#50788 - main.merge fails on HPUX
        Skip HA_EXTRA_MMAP, if MyISAM memory mapping is disabled.
[17 Feb 2010 9:57] 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/100581

3102 Ingo Struewing	2010-02-17
      Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
        Bug#50788 - main.merge fails on HPUX
        
        The test case main.merge failed on HPUX. The problem wass limited to
        HPUX because it does not seem to enable the use of memory mapped files
        (HAVE_MMAP).
        
        The analyze revealed three problems:
        
        1. If memory mapping is not used, the test failed with a bogus error
        of "table marked as crashed". This was caused by a misinterpretation
        of the return codes from the I/O cache in MyISAM.
        
        2. If memory mapping is used, it is possible in an INSERT-SELECT
        statement to read back records that have been written in the same
        statement, thus producing infinite records.
        
        3. The MERGE engine did not honor the --myisam-use-mmap option.
        It always turned on MyISAM memory mapping, when the optimizer
        suggested to do so.
        
        The first problem is fixed by adding a check for end of file if
        the I/O cache returns error.
        
        The second problem is fixed by adding an outer loop for MERGE
        tables to the search for a duplicate table. That way, duplicates of
        children can be detected.
        
        The third problem is fixed by ignoring a request for memory mapping,
        if the --myisam-use-mmap option is not active. This is, what MyISAM
        does too.
      
      This leaves merge.test with failures. They are to be fixed by later backports.
     @ mysql-test/r/merge.result
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Updated test result.
     @ mysql-test/r/merge_mmap.result
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            New test result file.
     @ mysql-test/t/merge.test
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Fixed test case.
            INSERT_SELECT does no longer require a LIMIT clause.
            Added more SELECTs and SHOWs.
     @ mysql-test/t/merge_mmap-master.opt
            Bug#50788 - main.merge fails on HPUX
            New options file.
     @ mysql-test/t/merge_mmap.test
            Bug#50788 - main.merge fails on HPUX
            New test file.
     @ mysys/mf_iocache.c
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Added comments.
     @ sql/sql_base.cc
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Added an outer loop for MERGE tables to the search
            for a duplicate table.
     @ storage/myisam/mi_statrec.c
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Fixed misinterpretation of the return value of my_b_read().
            Added comments.
     @ storage/myisammrg/ha_myisammrg.cc
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Skip HA_EXTRA_MMAP, if MyISAM memory mapping is disabled.
[18 Feb 2010 13:55] Ingo Strüwing
Queued to mysql-6.0-backup-merge.
[20 Feb 2010 9:19] Bugs System
Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:ingo.struewing@sun.com-20100216105855-frec1blvpn2n5oo9) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 1:22] Paul DuBois
Does not appear in any released version. No changelog entry needed.
[9 Mar 2010 20:03] 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/102777

3125 Ingo Struewing	2010-03-09
      Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
        Bug#50788 - main.merge fails on HPUX
        
        The test case main.merge failed on HPUX. The problem wass limited to
        HPUX because it does not seem to enable the use of memory mapped files
        (HAVE_MMAP).
        
        The analyze revealed three problems:
        
        1. If memory mapping is not used, the test failed with a bogus error
        of "table marked as crashed". This was caused by a misinterpretation
        of the return codes from the I/O cache in MyISAM.
        
        2. If memory mapping is used, it is possible in an INSERT-SELECT
        statement to read back records that have been written in the same
        statement, thus producing infinite records.
        
        3. The MERGE engine did not honor the --myisam-use-mmap option.
        It always turned on MyISAM memory mapping, when the optimizer
        suggested to do so.
        
        The first problem is fixed by adding a check for end of file if
        the I/O cache returns error.
        
        The second problem is fixed by adding an outer loop for MERGE
        tables to the search for a duplicate table. That way, duplicates of
        children can be detected.
        
        The third problem is fixed by ignoring a request for memory mapping,
        if the --myisam-use-mmap option is not active. This is, what MyISAM
        does too.
     @ mysql-test/r/merge.result
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Updated test result.
     @ mysql-test/r/merge_mmap.result
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            New test result file.
     @ mysql-test/t/merge.test
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Fixed test case.
            INSERT_SELECT does no longer require a LIMIT clause.
            Added more SELECTs and SHOWs.
     @ mysql-test/t/merge_mmap-master.opt
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            New options file.
     @ mysql-test/t/merge_mmap.test
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            New test file.
     @ mysys/mf_iocache.c
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Added comments.
     @ sql/sql_base.cc
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Added an outer loop for MERGE tables to the search
            for a duplicate table.
     @ storage/myisam/mi_statrec.c
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Fixed misinterpretation of the return value of my_b_read().
            Added comments.
     @ storage/myisammrg/ha_myisammrg.cc
        Backport of revid:ingo.struewing@sun.com-20100216083934-xl120fzz89xmmbe6
            Bug#50788 - main.merge fails on HPUX
            Skip HA_EXTRA_MMAP, if MyISAM memory mapping is disabled.
[26 May 2010 9:54] Konstantin Osipov
The fix is not in any main tree.
[29 Jun 2010 14:31] 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/112488

3066 Konstantin Osipov	2010-06-29
      Salvage comments added by Ingo while working on 
      Bug#52114 and Bug#50788.
      The bugs themselves are regressions that are introduced
      by an incomplete fix for Bug#36171 and will not be pushed.
[2 Jul 2010 15: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/112771

3074 Konstantin Osipov	2010-07-02
      A test case for Bug#50788 "main.merge fails on HPUX",
      and a backport of relevant changes from the 6.0
      version of the fix done by Ingo Struewing.
      The bug itself was fixed by the patch for Bug#54811.
      
      
      MyISAMMRG engine would try to use MMAP on its children
      even on platforms that don't support it and even if
      myisam_use_mmap option was off.
      This lead to an infinite hang in INSERT ... SELECT into 
      a MyISAMMRG table when the destination MyISAM table
      was also selected from.
      
      A bug in duplicate detection fixed by 54811 was essential to 
      the hang - when a duplicate is detected, the optimizer 
      disables the use of memory mapped files, and it wasn't the case.
      
      The patch below is also to not turn on MMAP on children tables
      if myisam_use_mmap is off.
      A test case is added to cover MyISAMMRG and myisam_use_mmap
      option.
     @ mysql-test/r/merge_mmap.result
        Result file - Bug#50788.
     @ mysql-test/t/merge_mmap-master.opt
        An option file for the test for Bug#50788 -- use mmap.
     @ mysql-test/t/merge_mmap.test
        Try INSERT ... SELECT into a merge table when myisam_use_mmap is on (Bug#50788).
     @ storage/myisam/mi_statrec.c
        Fixed misinterpretation of the return value of my_b_read().
     @ storage/myisammrg/ha_myisammrg.cc
        Skip HA_EXTRA_MMAP if MyISAM memory mapping is disabled.
[2 Jul 2010 15:21] 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/112773

3074 Konstantin Osipov	2010-07-02
      A test case for Bug#50788 "main.merge fails on HPUX",
      and a backport of relevant changes from the 6.0
      version of the fix done by Ingo Struewing.
      The bug itself was fixed by the patch for Bug#54811.
      
      
      MyISAMMRG engine would try to use MMAP on its children
      even on platforms that don't support it and even if
      myisam_use_mmap option was off.
      This lead to an infinite hang in INSERT ... SELECT into 
      a MyISAMMRG table when the destination MyISAM table
      was also selected from.
      
      A bug in duplicate detection fixed by 54811 was essential to 
      the hang - when a duplicate is detected, the optimizer 
      disables the use of memory mapped files, and it wasn't the case.
      
      The patch below is also to not turn on MMAP on children tables
      if myisam_use_mmap is off.
      A test case is added to cover MyISAMMRG and myisam_use_mmap
      option.
     @ mysql-test/r/merge_mmap.result
        Result file - Bug#50788.
     @ mysql-test/t/merge_mmap-master.opt
        An option file for the test for Bug#50788 -- use mmap.
     @ mysql-test/t/merge_mmap.test
        Try INSERT ... SELECT into a merge table when myisam_use_mmap is on (Bug#50788).
     @ storage/myisam/mi_statrec.c
        Fixed misinterpretation of the return value of my_b_read().
     @ storage/myisammrg/ha_myisammrg.cc
        Skip HA_EXTRA_MMAP if MyISAM memory mapping is disabled.
[2 Jul 2010 15:28] Konstantin Osipov
Queued in 5.5.6.
[4 Aug 2010 7:54] 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 9:00] 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:20)
[4 Aug 2010 9:06] Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)
[12 Aug 2010 18:57] Paul DuBois
Noted in 5.5.6 changelog.

The MERGE storage engine tried to use memory mapping on the
underlying MyISAM tables even on platforms that do not support it and
even when myisam_use_mmap was disabled. This led to a hang for
INSERT INTO ... SELECT FROM statements that selected from a MyISAM
table into a a MERGE table that contained the same MyISAM table.