| 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: | |
| Category: | MySQL Server: Backup | Severity: | S2 (Serious) |
| Version: | 6.0.14-alpha | OS: | HP/UX |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
[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.
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