Mysql Error 1030 Hy000 Got Error 124 From Storage Engine
8:57 Reporter: Roel Van de Paar (OCA) Email Updates: Status: Closed Impact on me: None Category:MySQL Server: Partitions Severity:S1 (Critical) Version:5.1.36 OS:Any Assigned to: Mattias Jonsson Triage: Triaged: D2 (Serious) View Add Comment Files Developer Edit Submission View Progress Log Contributions [10 Aug 2009 22:51] Roel Van de Paar Description: mysql> INSERT INTO met (tmin, mid, nid,cid, aid, val) SELECT 1, 399, 0,1,1,1 FROM met MD, vmm VM WHERE MD.mid = VM.mid; ERROR 1030 (HY000): Got error 124 from storage engine Repeatable testcase below. If the first index is dropped, it works fine: mysql> ALTER TABLE met DROP INDEX `mid`; Query OK, 2 rows affected (0.11 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO met (tmin, mid, nid,cid, aid, val) SELECT 1, 399, 0,1,1,1 FROM met MD, vmm VM WHERE MD.mid = VM.mid; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0 How to repeat: CREATE TABLE vmm (vmid int not null, mid int not null) ENGINE=InnoDB; CREATE TABLE met (tmin integer NOT NULL, mid int NOT NULL, nid int NOT NULL, cid int NOT NULL, aid int NOT NULL, val bigint NOT NULL ,INDEX(mid) ,INDEX(tmin,mid,nid, cid) ,INDEX(tmin, mid, cid) ) ENGINE=MYISAM, AVG_ROW_LENGTH=60, MAX_ROWS=4000000000 PARTITION BY LIST( ((tmin DIV 60) MOD 24) ) ( PARTITION hour0to3 VALUES IN (0,1,2,3), PARTITION hour4to7 VALUES IN (4,5,6,7), PARTITION hour8to11 VALUES IN (8,9,10,11), PARTITION hour12to15 VALUES IN (12,13,14,15), PARTITION hour16to19 VALUES IN (16,17,18,19), PARTITION hour20to23 VALUES IN (20,21,22,23) ); INSERT INTO vmm VALUES (399, 22); INSERT INTO met values (1, 22, 0, 1, 1,1), (1, 42, 0, 1, 1,1); INSERT INTO met (tmin, mid, nid,cid, aid, val) SELECT 1, 399, 0,1,1,1 FROM met MD, vmm VM WHERE MD.mid = VM.mid; [10 Aug 2009 22:54] Roel Van de Paar Verifying as D2 [10 Aug 2009 22:57] Roel Van de Paar Also see bug #44657 [11 Aug 2009 0:18] Roel Van de Paar Customer reported another workaround: 'If each partition has at least one row, then everything is ok.' [13 Aug 2009 16:15] Sinisa Milivojevic errno 124 is returned as "Wrong medium type", which is wrong in this case. Correct errno text is "Wrong index given to function". One thing to be checked here is whether result set from SELECT is first correctly stored in a temporary table. [17 Aug 2009 3:09] Roel Van de Paar Poss
> Which version of mysql are you using? In mysql 4, you could get away with some > differences between the definition of the merge table and the underlying tables. > > As you've discovered, the structure and index definitions must now be exactly the > same, otherwise you will get errors. > > Regards, > Gavin Towey > > -----Original Message----- > From: stutiredboy [mailto:stutiredboy@stripped] > Sent: Tuesday, August 25, 2009 12:23 AM > To: mysql@stripped > Subject: Got error 124 from storage engine > > hi, all: > > i https://bugs.mysql.com/bug.php?id=46639 have met an question as below: > > table A1,A2 > > A1 has been *packed by myisampack, and rebuild the index by myisamchk* > > A2 is a noraml table, and the struct of A1 and A2 is exactlly same > > talbe A is the merge table of A1 and A2 > > while i use: > * > http://lists.mysql.com/mysql/218498 mysql> select max(id) from A; > ** ERROR 1030 (HY000): Got error 124 from storage engine > > > +---------------+-----------------------+------+-----+-------------------+----------------+ > | Field | Type | Null | Key | Default | Extra | > > +---------------+-----------------------+------+-----+-------------------+----------------+ > | id | bigint(20) unsigned | NO | MUL | NULL | auto_increment | > > > *but when i try another table, the situation is as before, such as table > B1,B2,B > * > mysql> select max(id) from loot; > +---------+ > | max(id) | > +---------+ > | 110415 | > +---------+ > 1 row in set (0.00 sec) > > * > the only difference is (*table A the id Field is auto_increment and > table B the id is not*): > > *+-------+-----------------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +-------+-----------------------+------+-----+---------+-------+ > | id | bigint(20) unsigned | NO | MUL | NULL | | > > > *and if i do not use myisampack/myisamchk, all are work fine, > *our system is freebsd 7.2, the mysql version is 5.0.84
came across an interesting error. The particulars are below. OS: Windows Server 2008 MySQL Version: 5.1.31 64bit Usage: Supports an application Vendor Support: No After migrating the application connections to the new hardware and https://sqlfreebies.wordpress.com/2013/02/13/mysql-5-1-error-1030-storage-engine/ fresh MySQL 5.1 server, the next part of the migration was to export the https://bugs.launchpad.net/bugs/885162 data from old system into a half filled table on the new system. The servers are load balanced so data can go into the least busy server, but this does not affect anything. I should mention at this point, everything was tested and signed off on the development servers and we created a particular mysqldump command to do the mysql error job. As things goes there was a delay and the client continued with our original mysqldump command. When the DBA executed the command between 40 and 70 percent of the rows were rejected and mysql client stopped the import. This was acceptable to the client because the records were duplicates and not required. The application developer noticed that the application admin page was complaining about a database connection error. The application engine was however, mysql error 1030 using the database and was happily processing new records. The application developer started up a mysql client logged in and issued two commands; SELECT COUNT(*) FROM tablename' +-------------+ | count(*) | +-------------+ | 17070556 | +-------------+ SELECT MAX(datestamp) FROM tablename; ERROR 1030 (HY000): Got error 124 from storage engine That generate a mixture of panic, amusement and confusion. The part of application was working processing records but it was also moaning about database issue and not helpfully providing the same message as the database. The table was increasing in size but issuing a SELECT MAX died while SELECT COUNT worked. That was when I got the call. It turns out that, there was another part of the application that uses the SELECT MAX statement. This along with the "changes" to the migration process, that led me to the mysqldump script as the cause. So I copied the initial datafiles to development servers and moved the 701Mb mysqldump file across. A quick search in the mysql.com libraries and I found two links. One for MySQL 4.1 and the other for MySQL 5.1.12. Out of curiosity I did look at MySQL 4.1 artical and was surprised to find Bug #20357 made it all the way through to MySQL 5.1.12 & 5.1.31. Ah well. The theory was the script crashed out on the first dupl
person Affects Status Importance Assigned to Milestone Maria Edit Fix Released High Oleksandr "Sanja" Byelkin Edit Maria 5.3 You need to log in to change this bug's status. Affecting: Maria Filed here by: Philip Stoev When: 2011-11-02 Confirmed: 2011-11-02 Assigned: 2011-11-02 Started work: 2011-11-29 Completed: 2011-11-29 Target Distribution Baltix BOSS Juju Charms Collection Elbuntu Guadalinex Guadalinex Edu Kiwi Linux nUbuntu PLD Linux Tilix tuXlab Ubuntu Ubuntu Linaro Evaluation Build Ubuntu RTM Package (Find…) Project (Find…) Status Importance Milestone Fix Released High Maria 5.3 Assigned to Me Oleksandr "Sanja" Byelkin (sanja-byelkin) Remote Watch None, the status of the bug is updated manually. None, the status of the bug is updated manually. URL: The information about this bug in Launchpad is automatically pulled daily from the remote bug. Comment on this change (optional) Email me about changes to this bug report Also affects project (?) Also affects distribution/package Nominate for series Bug Description When executing the following query: SELECT * FROM t1 WHERE t1.f1 IN ( SELECT 'k' UNION SELECT 'e' ) ; mysqld returned: ERROR 1030 (HY000): Got error 124 from storage engine backtrace: #0 my_error (nr=1030, MyFlags=0) at my_error.c:81 #1 0x083f34ef in handler::print_error (this=0xa7550990, error=124, errflag=0) at handler.cc:2981 #2 0x0833eb0f in report_error (table=0xa754fa18, error=124) at sql_select.cc:15468 #3 0x0833f549 in join_read_key2 (thd=0xaf82a20, tab=0xa7571840, table=0xa754fa18, table_ref=0xa757199c) at sql_select.cc:15731 #4 0x0833f3da in join_read_key (tab=0xa7571840) at sql_select.cc:15692 #5 0x0833e023 in sub_select (join=0xa7576490, join_tab=0xa7571840, end_of_records=false) at sql_select.cc:15129 #6 0x0833d8d0 in do_select (join=0xa7576490, fields=0xa754c028, table=0x0, procedure=0x0) at sql_select.cc:14795 #7 0x08322156 in JOIN::exec (this=0xa7576490) at sql_select.cc:2679 #8 0x08322982 in mysql_select (thd=0xaf82a20, rref_pointer_array=0xa754c414, tables=0xa754bdcc, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=268435456, result=0xa754c628, unit=0xa754bd98, select_lex=0xa754c2d8) at sql_select.cc:2900 #9 0x0846fb8c in st_select_lex_unit::exec (this=0xa754bd98) at sql_union.cc:724 #10 0x0824d00d in subselect_union_engine::exec (this=0xa754c640) at item_subselect.cc:3009 #11 0x08246bc9 in Item_subselect::exec (this=0xa754c538) at item_subselect.cc:587 #12 0x08247092 in Item_in_subselect::exec (this=0xa754c538) at item_subselect.cc:742 #13 0x08248be6 in Item_in_subselect::val_bool (this=0xa754c538) at item_subselect.c