Bug #19803 "ERROR 1030: Got error 28 from table handler" on all complicated queries
Submitted: 14 May 2006 14:58 Modified: 14 May 2006 17:45
Reporter: Cyril Zlachevsky Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:3.23.58 OS:Linux (Red Hat Enterpise Linux 4)
Assigned to: CPU Architecture:Any

[14 May 2006 14:58] Cyril Zlachevsky
Description:
MySQL 3.23.58
RHEL 4 ( rebuilded RPM's from mysql-3.23.58-16.RHEL3.1.src.rpm).

InnoDB storage engine is used  few years without any problems.

But today I get error message : "ERROR 1030: Got error 28 from table handler" on all complicated queries like "SELECT DISTINCT ..." or "SELECT ... LEFT JOIN ..."
Problem only with two tables in database - complicated queries on other tables works fine.
Simply queries working fine (SELECT, INSERT,)!
I get this error on empty tables too.

Free disk space is 62G:
# df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda5             73766848   7082700  62936992  11% /
/dev/hda1               101086     11122     84745  12% /boot
/dev/hda3              1035692   1035688         0 100% /tmp
none                    513832         0    513832   0% /dev/shm
/dev/hdb1             76920416  13976076  59036932  20% /home2

I'm check access rights of created  innodb log files and data directory - all correct.
I'm check filesize of innodb_log_files - it's < 2G

I'm make database dump all your InnoDB tables, stop the server, remove all the existing tablespace files, restart the mysql server and import the dump files.

Problem still exist:
mysql> desc UNTERM;
+---------+-----------------+------+-----+---------+----------------+
| Field   | Type            | Null | Key | Default | Extra          |
+---------+-----------------+------+-----+---------+----------------+
| count   | int(8) unsigned |      | PRI | NULL    | auto_increment |
| start   | datetime        | YES  | MUL | NULL    |                |
| PartN   | tinyint(4)      | YES  | MUL | NULL    |                |
| Partn2  | tinyint(4)      | YES  | MUL | 3       |                |
| ANI     | varchar(15)     | YES  |     | NULL    |                |
| tel     | varchar(20)     | YES  |     | NULL    |                |
| dir     | varchar(30)     | YES  | MUL | NULL    |                |
| ip      | varchar(15)     | YES  |     | NULL    |                |
| IP2     | varchar(15)     | YES  | MUL | NULL    |                |
| id      | varchar(25)     | YES  |     | NULL    |                |
| d_cause | char(2)         | YES  |     | NULL    |                |
+---------+-----------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> desc termination;
+---------+------------------+------+-----+----------------+----------------+
| Field   | Type             | Null | Key | Default        | Extra          |
+---------+------------------+------+-----+----------------+----------------+
| count   | int(8) unsigned  |      | PRI | NULL           | auto_increment |
| start   | datetime         | YES  | MUL | NULL           |                |
| PartN   | tinyint(4)       | YES  | MUL | NULL           |                |
| time    | bigint(5)        | YES  | MUL | NULL           |                |
| tel     | varchar(20)      | YES  |     | NULL           |                |
| dir     | varchar(30)      | YES  | MUL | NULL           |                |
| tarif   | double(5,4)      | YES  | MUL | NULL           |                |
| up      | double(7,5)      | YES  | MUL | NULL           |                |
| ip      | varchar(15)      | YES  | MUL | NULL           |                |
| id      | varchar(25)      |      | UNI |                |                |
| v_qual  | char(3)          | YES  |     | NULL           |                |
| d_cause | char(2)          | YES  |     | NULL           |                |
| PartN2  | tinyint(4)       | YES  | MUL | 3              |                |
| ip2     | varchar(15)      | YES  | MUL | 216.40.216.208 |                |
| Answer  | enum('YES','NO') | YES  |     | YES            |                |
| tarif2  | double(5,4)      | YES  | MUL | NULL           |                |
| mypr    | double(7,5)      | YES  | MUL | NULL           |                |
+---------+------------------+------+-----+----------------+----------------+
17 rows in set (0.00 sec)

mysql> select count(*) from UNTERM;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

mysql> select distinct id from UNTERM;
ERROR 1030: Got error 28 from table handler

mysql> select distinct substring(start,1,6) from termination;
ERROR 1030: Got error 28 from table handler

my my.cfg:
[mysqld]

# files and catalog settings
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#language=/usr/share/mysql/russian
language=/usr/share/mysql/english
#log=/var/log/mysql.log
#log-update=/var/log/mysqlq.log
character-sets-dir=/usr/share/mysql/charsets/
default-character-set=koi8_ru

# InnoDB settings
innodb_data_home_dir =
innodb_data_file_path = /home2/mysql/ibdata2:2000M
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
set-variable = max_connections=600
set-variable = innodb_buffer_pool_size=256M
set-variable = innodb_additional_mem_pool_size=20M
# Set .._log_file_size to 25 % of buffer pool size
set-variable = innodb_log_file_size=64M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

How to repeat:
n/a
[14 May 2006 15:16] Valeriy Kravchuk
Thank you for a problem report. This:

dev/hda3              1035692   1035688         0 100% /tmp

can be a reason for your problem. Please, free some space in /tmp file system, repeat your queries and inform about the results.
[14 May 2006 16:32] Cyril Zlachevsky
I'm free /tmp 
Problem is gone - all work fine!
Thank you for you support!