load data infile / myisamchk / 3.23.32 bug
I posted this problem before and heard nothing, but this time I compiled mysqld to debug and have posted some of the debugging output. The following worked fine under 3.23.8. It fails miserably under 3.23.32 - also 3.23.33 but I used 3.23.32 for the output below. It seems that mysqlimport reads and writes the entire data file. At least the .MYD file is roughly the same size as the file being loaded in. At that point, mysqladmin processlist shows a status line indicating the load data infile but then also says 'repair with key cache'. I then have to kill off mysqld or it never finishes. I know the table is good since there is nothing in it and I created it with a mysqladmin create command. mysqladmin flush-tables myisamchk -rq --keys-used=0 /data1/book/book mysqladmin flush-tables mysqlimport book /data2/bip/book # Never gets to the following lines mysqladmin flush-tables myisamchk -rq /data1/book/book mysqladmin flush-tables Here is 235 lines of the debug output. The first thing that looks suspicious to me is on line 48. T@7171 : | | | | | str_to_TIME T@7171 : | | | | | | enter: str: 1984-06-00 T@7171 : | | | | | str_to_TIME T@7171 : | | | | | update_auto_increment T@7171 : | | | | | update_auto_increment T@7171 : | | | | | mi_write T@7171 : | | | | | | enter: isam: 9 data: 10 T@7171 : | | | | | | _mi_readinfo T@7171 : | | | | | | _mi_readinfo T@7171 : | | | | | | _mi_rec_pack T@7171 : | | | | | | | exit: packed length: 207 T@7171 : | | | | | | _mi_rec_pack T@7171 : | | | | | | write_dynamic_record T@7171 : | | | | | | | _mi_find_writepos T@7171 : | | | | | | | _mi_find_writepos T@7171 : | | | | | | | _mi_write_part_record T@7171 : | | | | | | | | header: Memory: bf5ff360 Bytes: (4) 03 00 CF 01 T@7171 : | | | | | | | _mi_write_part_record T@7171 : | | | | | | write_dynamic_record T@7171 : | | | | | | _mi_writeinfo T@7171 : | | | | | | _mi_writeinfo T@7171 : | | | | | mi_write T@7171 : | | | | | str_to_TIME T@7171 : | | | | | | enter: str: 1984-06-00 T@7171 : | | | | | str_to_TIME T@7171 : | | | | | update_auto_increment T@7171 : | | | | | update_auto_increment T@7171 : | | | | | mi_write T@7171 : | | | | | | enter: isam: 9 data: 10 T@7171 : | | | | | | _mi_readinfo T@7171 : | | | | | | _mi_readinfo T@7171 : | | | | | | _mi_rec_pack T@7171 : | | | | | | | exit: packed length: 207 T@7171 : | | | | | | _mi_rec_pack T@7171 : | | | | | | write_dynamic_record T@7171 : | | | | | | | _mi_find_writepos T@7171 : | | | | | | | _mi_find_writepos T@7171 : | | | | | | | _mi_write_part_record T@7171 : | | | | | | | | header: Memory: bf5ff360 Bytes: (4) 03 00 CF 01 T@7171 : | | | | | | | _mi_write_part_record T@7171 : | | | | | | write_dynamic_record T@7171 : | | | | | | _mi_writeinfo T@7171 : | | | | | | _mi_writeinfo T@7171 : | | | | | mi_write T@7171 : | | | | | my_read T@7171 : | | | | | | my: Fd: 11 Buffer: 40a19008 Count: 129408 MyFlags: 16 T@7171 : | | | | | | warning: Read only 0 bytes off 129408 from 11, errno: -1 T@7171 : | | | | | my_read T@7171 : | | | | | update_auto_increment T@7171 : | | | | | update_auto_increment T@7171 : | | | | | mi_write T@7171 : | | | | | | enter: isam: 9 data: 10 T@7171 : | | | | | | _mi_readinfo T@7171 : | | | | | | _mi_readinfo T@7171 : | | | | | | _mi_rec_pack T@7171 : | | | | | | | exit: packed length: 41 T@7171 : | | | | | | _mi_rec_pack T@7171 : | | | | | | write_dynamic_record T@7171 : | | | | | | | _mi_find_writepos T@7171 : | | | | | | | _mi_find_writepos T@7171 : | | | | | | | _mi_write_part_record T@7171 : | | | | | | | | header: Memory: bf5ff360 Bytes: (3) 01 00 29 T@7171 : | | | | | | | _mi_write_part_record T@7171 : | | | | | | write_dynamic_record T@7171 : | | | | | | _mi_writeinfo T@7171 : | | | | | | _mi_writeinfo T@7171 : | | | | | mi_write T@7171 : | | | | read_sep_field T@7171 : | | | | mi_extra T@7171 : | | | | | end_io_cache T@7171 : | | | | | | flush_io_cache T@7171 : | | | | | | | my_write T@7171 : | | | | | | | | my: Fd: 10 Buffer: 40a3a008 Count: 508 MyFlags: 36 T@7171 : | | | | | | | my_write T@7171 : | | | | | | flush_io_cache T@7171 : | | | | | | my_free T@7171 : | | | | | | | my: ptr: 40a3a008 T@7171 : | | | | | | my_free T@7171 : | | | | | end_io_cache T@7171 : | | | | mi_extra T@7171 : | | | | activate_all_index T@7171 : | | | | | ha_myisam::repair T@7171 : | | | | | | fn_format T@7171 : | | | | | | | enter: name: ./book/book dsk: form: .MYI flag: 20 T@7171 : | | | | | | | dirname_part T@7171 : | | | | | | | | enter: './book/book' T@7171 : | | | | | | | dirname_part T@7171 : | | | | | | | unpack_dirname T@7171 : | | | | | | | | dirname_part T@7171 : | | | | | | | | | enter: './book/' T@7171 : | | | | | | | | dirname_part T@7171 : | | | | | | | | cleanup_dirname T@7171 : | | | | | | | | | enter: from: './book/' T@7171 : | | | | | | | | | exit: to: './book/' T@7171 : | | | | | | | | cleanup_dirname T@7171 : | | | | | | | unpack_dirname T@7171 : | | | | | | | strlength T@7171 : | | | | | | | strlength T@7171 : | | | | | |
Repair with key cache error?
Description: I have this little script (1) that builds a table each night. For quite a while the output shows a normal load data infile (2). Then, it switches to a state of 'Repair with keycache' (3). This just started happening with mysql 3.23.32. Until I upgraded a few days ago it was not a problem. I was using 3.23.8. The idea is to load the data minus the keys and then add the keys, since this is much faster. The data file for load data infile is 1.3 gigs. At about the time the message changes, the mysql file sizes are - sq1_invhist_rec.MYD 616840056, sq1_invhist_rec.MYI 15904768. I also made sure the files did not start out corrupt since I did a mysqldump -d on them, rm'ed and recreated them. I never reach the 'pause' line in the script, at least not until I forcibly (-9) brought down the server. It didn't seem to want to shutdown any other way. This all seems to be sporadic. It will work fine for a couple of days taking a couple of hours to build the tables. Then for the next couple of days it will bomb out and still be building when I come in ten hours after it started. So I shutdown mysql to abort the build. It sounds somewhat similar to a post I saw from David J. Potter where he writes This happens while inserting a large number rows at high speed being read from a text file. It crashes usually around the 1,000,000th inserted row into a table. Though other details he mentions are different. Any ideas? 1. The script. #!/bin/sh set -x cd /data2/sq1tmp PATH=$PATH:/usr/local/bin echo delete from sq1_invhist_rec | mysql book mysqladmin flush-tables myisamchk --keys-used=0 -rq /data/book/sq1_invhist_rec mysqladmin flush-tables echo "load data infile '/data2/sq1tmp/$file' into table $file" | mysql book echo pausing now read 2. Line from mysqladmin processlist before the repair message | 43 | root | localhost | book | Query | 1074 | | load |data infile '/data2/sq1tmp/sq1_invhist_rec' into table sq1_invhist_rec | 0| 3. Line from mysqladmin processlist once the repair message appears | 43 | root | localhost | book | Query | 1226 | Repair with keycache | load data |infile '/data2/sq1tmp/sq1_invhist_rec' into table sq1_invhist_rec | How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator: Organization: organization of PR author (multiple lines) MySQL support: [email support] (I believe so. For Powell's Books) Synopsis: Problem with nightly table build Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.32 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.14 Distrib 3.23.32, for pc-linux-gnu on i586 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.32 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 57 min 49 sec Threads: 3 Questions: 2765 Slow queries: 0 Opens: 63 Flush tables: 1 Open tables: 57 Queries per second avg: 0.797 Environment: machine, os, target, libraries (multiple lines) System: Linux snowball.burnside.powells.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999 i586 unknown Architecture: i586 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 17 1999 /lib/libc.so.6 - libc-2.1.1.so -rwxr-xr-x 1 root root 4016683 Apr 16 1999 /lib/libc-2.1.1.so -rw-r--r-- 1 root root 19533408 Apr 16 1999 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 16 1999 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --localstatedir=/data --with-raid Perl: This is perl, version 5.005_03 built for i386-linux - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
