Bug #31451 ERROR 1105 (HY000): Unknown error on a simple SELECT statement
Submitted: 8 Oct 2007 14:52 Modified: 30 Nov 2007 23:38
Reporter: Dimitry Butko Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.26/5.0BK OS:Linux (Gentoo)
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: Got error 124 from storage engine, unknown error

[8 Oct 2007 14:52] Dimitry Butko
Description:
Got 

 ERROR 1105 (HY000): Unknown error
 or
 ERROR 1030 (HY000): Got error 124 from storage engine

after some data manipulations.

I did not found any similar bug here....
Also I have noted that problem is dissapearing after REPAIR TABLE statement.
So, I can assume it is something with "KEY cat (category_id, vendor_id)"?

How to repeat:
DROP TEMPORARY TABLE IF EXISTS search_results;
Query OK, 0 rows affected (0.01 sec)

CREATE TEMPORARY TABLE search_results(

  product_id  int(11) PRIMARY KEY,
  icecat_id   int(11),
  vendor_id   int(11),
  category_id int(11),

  prod_id     varchar(64),
  vendor      varchar(64),
  prodlevid   varchar(64),
  supplier    varchar(16),
  name        varchar(255),
  short_desc  varchar(255),
  long_desc   text,
  cat_name    varchar(255),
  catid       varchar(8),

  small_pic   varchar(255),
  thumb_pic   varchar(255),
  low_pic     varchar(255),
  high_pic    varchar(255),

  pprice      decimal(12,2),
  sprice      decimal(12,2),
  euprice     decimal(12,2),
  stock       int(11),
  relevance   int(11),

  popularity   int(11)      NOT NULL DEFAULT 0,
  top_seller   int(11)      NOT NULL DEFAULT 0,

  private_stock int(11) NOT NULL DEFAULT 0,

  UNIQUE (vendor_id, prod_id),
  UNIQUE (vendor, prod_id),
  KEY cat (category_id, vendor_id)
) DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.01 sec)

INSERT IGNORE INTO search_results(product_id, icecat_id, vendor_id, category_id, prod_id, vendor, name, short_desc, cat_name, small_pic, thumb_pic, low_pic, high_pic, relevance)
SELECT HIGH_PRIORITY SQL_BUFFER_RESULT p.product_id, p.icecat_id, p.vendor_id, p.category_id, p.prod_id, v.name, pd.name, pd.short_desc, cd.name, p.small_pic, p.thumb_pic, p.low_pic, p.high_pic, 1
FROM product p JOIN product_description pd ON p.product_id = pd.product_id AND pd.language_id = '2'
JOIN vendor v ON p.vendor_id = v.vendor_id
JOIN category c ON c.uncatid = '43172402' AND p.category_id = c.category_id
JOIN category_description cd ON c.category_id = cd.category_id AND cd.language_id = '2'

Query OK, 604 rows affected (0.12 sec)
Records: 604  Duplicates: 0  Warnings: 0

SELECT distinct category_id FROM search_results;
ERROR 1105 (HY000): Unknown error

SELECT category_id FROM search_results;
ERROR 1030 (HY000): Got error 124 from storage engine
[8 Oct 2007 15:42] MySQL Verification Team
Thank you for the bug report. Your version is pretty older could you please
test with the last released version and comment the results here. Thanks in
advance.
[9 Oct 2007 7:49] Dimitry Butko
Dear Miguel,

We are planning to update the server software (i.e. migrating to a new server).
But that will be done closer to the end of the month.
So you can close the bug report for now if you think it should be closed.
In any case, I will let you know the output a bit later.

Thanks, Dimitry
[9 Oct 2007 8:04] Valeriy Kravchuk
Please, send test data that shows the problem in your case. Then we will be able to check on latest version without waiting for you to upgrade.
[9 Oct 2007 16:48] MySQL Verification Team
Thank you for the feedback. I was able to repeat and after the repair
table the issue goes away.

[miguel@skybr 5.0]$ bin/mysql -uroot db5
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.52-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TEMPORARY TABLE search_results(
    -> 
    ->   product_id  int(11) PRIMARY KEY,
    ->   icecat_id   int(11),
    ->   vendor_id   int(11),
    ->   category_id int(11),
    -> 
    ->   prod_id     varchar(64),
    ->   vendor      varchar(64),
    ->   prodlevid   varchar(64),
    ->   supplier    varchar(16),
    ->   name        varchar(255),
    ->   short_desc  varchar(255),
    ->   long_desc   text,
    ->   cat_name    varchar(255),
    ->   catid       varchar(8),
    -> 
    ->   small_pic   varchar(255),
    ->   thumb_pic   varchar(255),
    ->   low_pic     varchar(255),
    ->   high_pic    varchar(255),
    -> 
    ->   pprice      decimal(12,2),
    ->   sprice      decimal(12,2),
    ->   euprice     decimal(12,2),
    ->   stock       int(11),
    ->   relevance   int(11),
    -> 
    ->   popularity   int(11)      NOT NULL DEFAULT 0,
    ->   top_seller   int(11)      NOT NULL DEFAULT 0,
    -> 
    ->   private_stock int(11) NOT NULL DEFAULT 0,
    -> 
    ->   UNIQUE (vendor_id, prod_id),
    ->   UNIQUE (vendor, prod_id),
    ->   KEY cat (category_id, vendor_id)
    -> ) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT IGNORE INTO search_results(product_id, icecat_id, vendor_id, category_id, prod_id,
    -> vendor, name, short_desc, cat_name, small_pic, thumb_pic, low_pic, high_pic, relevance)
    -> SELECT HIGH_PRIORITY SQL_BUFFER_RESULT p.product_id, p.icecat_id, p.vendor_id,
    -> p.category_id, p.prod_id, v.name, pd.name, pd.short_desc, cd.name, p.small_pic,
    -> p.thumb_pic, p.low_pic, p.high_pic, 1
    -> FROM product p JOIN product_description pd ON p.product_id = pd.product_id AND
    -> pd.language_id = '2'
    -> JOIN vendor v ON p.vendor_id = v.vendor_id
    -> JOIN category c ON c.uncatid = '43172402' AND p.category_id = c.category_id
    -> JOIN category_description cd ON c.category_id = cd.category_id AND cd.language_id = '2';

Query OK, 604 rows affected (0.56 sec)
Records: 604  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT distinct category_id FROM search_results;
ERROR 1030 (HY000): Got error 124 from storage engine
mysql> SELECT category_id FROM search_results;
ERROR 1030 (HY000): Got error 124 from storage engine
[30 Nov 2007 23:38] Gleb Shchepa
Already fixed by the patch for bug #30384.