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