MySQL Bugs: #30290: ERROR 1030 (HY000): Got error 134 from storage engine (MyISAM)

Bug #30290	ERROR 1030 (HY000): Got error 134 from storage engine (MyISAM)
Submitted:	8 Aug 2007 3:23	Modified:	22 Feb 2009 16:34
Reporter:	Virgilio Quilario	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.0.27, 5.0.45	OS:	Linux (Linux 2.6.20-1.2320.fc5)
Assigned to:		CPU Architecture:	Any
Tags:	5.0.27-1.fc5, corruption, error 1030, error 134, myisam

Description:
hi, 

mysql returns

ERROR 1030 (HY000): Got error 134 from storage engine

when executing a query while insertions are on going on a myisam table.

here are the machine and mysql specs:
cpu: Dual Intel(R) Xeon(TM) CPU 2.80GHz
os: Linux 2.6.20-1.2320.fc5
ram: 8gb
mysql: mysql-server-5.0.27-1.fc5 
filesystem: ext3
hdd: 23Gb (2.98% used)

here is the my.cnf contents
[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-error = /var/lib/mysql/mysql.err
log-slow-queries=/var/lib/mysql/slow_queries_20070807.log

skip-locking
skip-bdb
skip-ndbcluster
skip-networking

key_buffer = 512M
max_allowed_packet = 1M
table_cache = 4096
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 1M
thread_cache_size = 200
query_cache_size = 64M
myisam_recover=FORCE,BACKUP

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

max_connect_errors = 999999
max_connections = 600
wait_timeout = 10

max_heap_table_size = 32M

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
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
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 64M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 128M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 10
innodb_thread_concurrency = 4

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

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet = 2M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 32M
sort_buffer_size = 32M
read_buffer = 1M
write_buffer = 1M

[myisamchk]
key_buffer = 32M
sort_buffer_size = 32M
read_buffer = 1M
write_buffer = 1M

[mysqlhotcopy]
interactive-timeout

How to repeat:
the show create table output

CREATE TABLE `sites_traffic_20070807` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `hour` tinyint(3) unsigned NOT NULL default '0',
  `minute` tinyint(3) unsigned NOT NULL default '0',
  `second` tinyint(3) unsigned NOT NULL default '0',
  `site_id` tinyint(3) unsigned NOT NULL default '0',
  `action` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `site_id` (`site_id`,`hour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `sites` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `url` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

the sites table contains
1, http://anysite1.com/
2, http://anysite2.com/
3, http://anysite3.com/
4, http://anysite4.com/
5, http://anysite5.com/

while executing the ff sql 1000000 times 

INSERT INTO `sites_traffic_20070807` (`id`, `hour`, `minute`, `second`, `site_id`, `action`)
	VALUES (NULL, 22, 30, 0, ?, ?);

note: 
site_id = rand(1,5)
action = rand(0,1)

execute the ff sql to check (once every 10 seconds or so)

SELECT `st`.`site_id`, `hour`, `url`, COUNT(`st`.`site_id`), COUNT(NULLIF(`st`.`action`,0)) FROM `sites_traffic_20070807` AS `st` 
	INNER JOIN `sites` AS `s` ON `s`.`id`=`st`.`site_id` GROUP BY `st`.`site_id`, `st`.`hour`;

will give the error below in approximately after 18656 insertions

ERROR 1030 (HY000): Got error 134 from storage engine

amazingly, the insertion process still continues without reporting any errors

i forgot to mention this
arch: 64-bit

Thank you for the report.

But version 5.0.27 is quite old. Please upgrade to current version 5.0.45, try with it and say us result.

hi, 

just tried the mysql-5.1.20-beta
we got same issue but we'll try the 5.0.45 let you know later

thanks

i'm having same issue on 64bit too. the problem came out ramdonly on heavily updated myisam tables. havn't got a problem after i convert it to innodb yet but maybe i should pay more time and see if really stable with innodb.

hi, 

we used the mysql 5.0.45 and still the myisam table kept on corrupting. We converted the tables into innodb. We got away with the problem but got us few deadlocks. 

It is best to use myisam tables in my situation to take advantage of its concurrent inserts and never deadlock. I've had wonderful results using mysql 4.1.20 but I need some capabilities that are only found in version 5+.

I'd like to mention that my script is doing heavy inserts and few selects.

Thanks,

Virgil

I encounter the same problem on Sun Solaris 2.10 sparc with MySQL 5.0.37-log Source distribution.

I encounter the same problem with 5.0.27 on linux. The index of the MyIsam tables get corrupted frequently. I repair the tables once every 2 days.

Hi Virgilio, I just tried for 2 hours to repeat the errno 134 on a clean 5.0.45 system, following the guidelines you gave.  No luck.

Some questions:
1) Can you repeat this on a single processor system?
2) Are there any merge tables or other users accessing sites_traffic_20070807?
3) What disk subsystem (raid controllers, etc) has this server got?

thanks!

Some questions:

1) Can you repeat this on a single processor system?
ans: we have no problems in 32-bit single/dual processor system. this is only happening on 64-bit dual xeon system.

2) Are there any merge tables or other users accessing
sites_traffic_20070807? 
ans: no merge tables and no other scripts accessing it but we got lots of simultaneous users accessing our site.

3) What disk subsystem (raid controllers, etc) has this server got?
ans: server got onboard 3ware storage scsi controller; filesystem is ext3

Virgilio, please tell more about the exact 3ware firmware versions, etc.  We saw many problems with the older firmwares and hardware.  Thanks,

here it is

3ware Storage Controller device driver for Linux v1.26.02.002
ACPI: PCI Interrupt 0000:02:01.0[A] -> GSI 24 (level, low) -> IRQ 24
scsi0 : 3ware Storage Controller
3w-xxxx: scsi0: Found a 3ware Storage Controller at 0xbc00, IRQ: 24.
scsi 0:0:0:0: Direct-Access     3ware    Logical Disk 0   1.2  PQ: 0 ANSI: 0

I had the same problem (Got error 134 from storage engine) with myIsam on Windows Server MySQL 5.1.22rc during insertions also in a mono cpu sstem.

I'm getting this too on our busy Bugzilla application, which uses MyISAM tables. As myisamchk doesn't always fix the problem, I need to mysqldump the table, drop it, then re-create it from the dump.

I'm using SuSE Linux Entreprise 10 SP1 on a quad-cpu IBM POWER5 with a 2104-DS4 storage tray.

mysql  Ver 14.12 Distrib 5.0.26, for suse-linux (powerpc) using readline 5.1

select * from bugs where bug_id = 238306;
ERROR 1030 (HY000): Got error 134 from storage engine

Please, try to repeat with a newer version, 5.0.67, and inform about the results.

try repairing the respective tables

repair table table_name;
analyze table table_name;

It worked for me

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

We are also experiencing the problem on debian-stable's version of mysql.
(Reported as http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=512651)
Additional detail: the mysql tables are on a encrypted partition (LUKS).

Question to Valeriy Kravchuk: You recommend to use newer version 5.0.67, is that
a) because there is some relevant fixes you are aware of?
b) as developer you rather debug against the most recent version of your software irrespective of whether there is changes regarding the potential source of the problem?

Ralf,

thank you for the feedback.

Regarding to version 5.0.67 at least 2 bugs: bug #28837 and bug #28574 which can be related to this problem were fixed after 5.0.45. And yes, we don't backport bug fixes, so need to prove bug exists in the latest version. So, please, upgrade.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".