MySQL Bugs: #14429: ERROR 1034 (HY000) when INSERTING from a mysqldump

Bug #14429	ERROR 1034 (HY000) when INSERTING from a mysqldump
Submitted:	28 Oct 2005 14:58	Modified:	31 Oct 2005 10:47
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.15	OS:	Linux (Linux AMD64)
Assigned to:		CPU Architecture:	Any

Description:
Hi all,

I did upgrade to mysql 4.1.15 (from 4.1.11) and everything seemed to work fine.  A little later, a developper wanted to add a key in a table (MyIsam), and there was an error 145 (Table marked a crashed), but a repair told me everything was ok and i never managed to create this index.

<shell>
This db is little and not crucial, so i did a mysqldump, dropped the DB, and then tried to insert my backup file :
kc71 ~ # mysqladmin drop KCPI && mysqladmin create KCPI
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'KCPI' database [y/N] y
Database "KCPI" dropped
kc71 ~ # mysql KCPI < /tmp/kcpi0510281448.sql
ERROR 1034 (HY000) at line 110: Incorrect key file for table 'MEANUM'; try to repair it
</shell>

Here come the really interesting part:

<shell>
kc71 ~ # mysql KCPI

mysql> select * from MEANUM;
+------------+------------+---------------------+-----------+
| MEANUM_IDN | PERIND_IDN | MEANUMDATTIM        | MEANUMVAL |
+------------+------------+---------------------+-----------+
|          1 |         20 | 2005-01-09 00:00:00 |       266 |
....
|        127 |         30 | 2005-02-06 00:00:00 |    142803 |
+------------+------------+---------------------+-----------+

mysql> INSERT INTO `MEANUM` VALUES (128,31,'2005-02-06 00:00:00',498);
Query OK, 1 row affected (0.00 sec)

mysql> select * from MEANUM where MEANUM_IDN=128;
ERROR 1034 (HY000): Incorrect key file for table 'MEANUM'; try to repair it

mysql> select * from MEANUM where MEANUM_IDN=127;
ERROR 1034 (HY000): Incorrect key file for table 'MEANUM'; try to repair it

mysql> Bye
kc71 ~ # mysqlrepair KCPI
KCPI.AREA                                          OK
KCPI.DPT                                           OK
KCPI.INP                                           OK
KCPI.MEANUM                                        OK
kc71 ~ # mysql KCPI

mysql> INSERT INTO `MEANUM` VALUES (128,31,'2005-02-06 00:00:00',498);
Query OK, 1 row affected (0.00 sec)

mysql> select * from MEANUM where MEANUM_IDN=128;
ERROR 1034 (HY000): Incorrect key file for table 'MEANUM'; try to repair it

mysql> select * from MEANUM;
+------------+------------+---------------------+-----------+
| MEANUM_IDN | PERIND_IDN | MEANUMDATTIM        | MEANUMVAL |
+------------+------------+---------------------+-----------+
|          1 |         20 | 2005-01-09 00:00:00 |       266 |
....
|        127 |         30 | 2005-02-06 00:00:00 |    142803 |
+------------+------------+---------------------+-----------+

</shell>

REALLY strange... The insert return an OK, but the record is not in the table, and i even can't select on the already inserted record!!!

Seems like the key file won't go past 2048 bytes on disk :

kc71 oliviert # ls -l /var/lib/mysql/KCPI/
total 84
-rw-rw----  1 mysql mysql 8752 Oct 28 16:42 AREA.frm
-rw-rw----  1 mysql mysql  624 Oct 28 16:48 AREA.MYD
-rw-rw----  1 mysql mysql 2048 Oct 28 16:48 AREA.MYI
-rw-rw----  1 mysql mysql   65 Oct 28 16:42 db.opt
-rw-rw----  1 mysql mysql 8640 Oct 28 16:42 DPT.frm
-rw-rw----  1 mysql mysql  287 Oct 28 16:48 DPT.MYD
-rw-rw----  1 mysql mysql 2048 Oct 28 16:48 DPT.MYI
-rw-rw----  1 mysql mysql 8638 Oct 28 16:42 INP.frm
-rw-rw----  1 mysql mysql 1079 Oct 28 16:48 INP.MYD
-rw-rw----  1 mysql mysql 2048 Oct 28 16:48 INP.MYI
-rw-rw----  1 mysql mysql 8700 Oct 28 16:42 MEANUM.frm
-rw-rw----  1 mysql mysql 2667 Oct 28 16:48 MEANUM.MYD
-rw-rw----  1 mysql mysql 4096 Oct 28 16:48 MEANUM.MYI

Please tell me if i can send you more insightful information...

Thanks

How to repeat:
At each time i'll try to do it it will occur... I don't know how you can repeat but here is the table definition, in case it helps :

CREATE TABLE `MEANUM` (
  `MEANUM_IDN` int(10) unsigned NOT NULL auto_increment,
  `PERIND_IDN` int(10) unsigned NOT NULL default '0',
  `MEANUMDATTIM` datetime NOT NULL default '0000-00-00 00:00:00',
  `MEANUMVAL` float NOT NULL default '0',
  PRIMARY KEY  (`MEANUM_IDN`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The inserts will always hang after 127 record ...

Thank you for a problem report. Can you, please, describe your hardware, provide the my.cnf content, the error log of your MySQL, the results of df -k command, and, if it is possible, dump of your database (or, at least, your `MEANUM` table, with all the data). You may compress the dump and upload it using the File tab, as private file, if you want to.

Ok, here we go:

kc71 oliviert # uname -a
Linux kc71 2.6.9-gentoo-r9 #2 Wed Dec 29 14:14:38 CET 2004 x86_64 AMD Athlon(tm) 64 Processor 3200+ AuthenticAMD GNU/Linux
kc71 oliviert # cat /proc/cpuinfo
processor       : 0
vendor_id       : AuthenticAMD
cpu family      : 15
model           : 12
model name      : AMD Athlon(tm) 64 Processor 3200+
stepping        : 0
cpu MHz         : 2211.147
cache size      : 512 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 1
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx mmxext lm 3dnowext 3dnow
bogomips        : 4358.14
TLB size        : 1088 4K pages
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management: ts fid vid ttp

kc71 oliviert # cat /proc/meminfo
MemTotal:       510420 kB
MemFree:         28140 kB
Buffers:           112 kB
Cached:         224408 kB
SwapCached:        944 kB
Active:         180492 kB
Inactive:       121932 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:       510420 kB
LowFree:         28140 kB
SwapTotal:      987956 kB
SwapFree:       980976 kB
Dirty:               0 kB
Writeback:           0 kB
Mapped:          91900 kB
Slab:            38856 kB
Committed_AS:  1852896 kB
PageTables:       4552 kB
VmallocTotal: 536870911 kB
VmallocUsed:      4476 kB
VmallocChunk: 536866323 kB
HugePages_Total:    64
HugePages_Free:     64
Hugepagesize:     2048 kB
kc71 oliviert # df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda3             19538272  16668316   2869956  86% /
/dev/hda5             11722616   3526448   8196168  31% /home
/dev/hda6             11722616   5858416   5864200  50% /var
/dev/hda7            196965316 133931712  53028324  72% /mnt/files
none                    255208         0    255208   0% /dev/shm

kc71 oliviert # cat /etc/mysql/my.cnf | grep -v "#"

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[safe_mysqld]
err-log         = /var/log/mysql/mysql.err

[mysqld]
skip-innodb
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
log-error       = /var/log/mysql/mysqld.err
bdb             = false
log          = /var/log/mysql/mysql.log
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-locking
set-variable    = key_buffer=64M
set-variable    = max_allowed_packet=48M
set-variable    = thread_stack=256K
set-variable    = preload_buffer_size=512K
set-variable    = query_cache_size=64M
set-variable    = read_buffer_size=1M
set-variable    = read_rnd_buffer_size=1M
set-variable    = sort_buffer_size=16M
set-variable    = key_cache_block_size=256K
port            = 3306

[mysqldump]
quick
set-variable    = max_allowed_packet=48M

[mysql]

[isamchk]
set-variable    = key_buffer=16M

----------------------------------------------------------------

The attached file...

And thanks for your quick reply...

Hi,

It seems that this error has been caused by... Me :-(

After running mysql_fix_privilege_tables, it seems that everything is working fine. I wonder why i had this problems since i upgraded from a 4.1.x version, but it seems to work now.

I'm still investigating to see if i don't encounter other issues, and i'll keep you in touch so you can close this (stupid) bug request.

Bye

Closed as requested by reporter (see last comment).