>Description:
Copy existing file to text using mysqldump
Read mysqldump onto new machine using 'source'
Resulting database has incomplete indexes (cardinality NULL) (why?)
Try to fix resulting indexes using myisamchk -r
Everything appears to work, takes about 1 hour for 22M records
Attempt to check resulting table with myisamchk
Error results. See actual shell log below.
>How-To-Repeat:
-- MySQL dump 8.19
--
-- Host: localhost Database: elenco2g
---------------------------------------------------------
-- Server version 4.0.1-alpha
--
-- Table structure for table 'White'
--
CREATE TABLE White (
rec_no int(10) unsigned NOT NULL auto_increment,
phone_no varchar(16) NOT NULL default '',
prefix smallint(5) unsigned zerofill NOT NULL default '00000',
title varchar(20) NOT NULL default '',
first_name varchar(50) NOT NULL default '',
last_name varchar(140) NOT NULL default '',
street_no varchar(20) NOT NULL default '',
street_type enum('','Autostrada','A.','Casella
Postale','C.P.','Corso','C.','Galleria','G.','Largo','LG.','Piazza','P.','Piazzale','PL.','Privato','PR.','Rua','R.','Strada','S.','Via','V.','Viale','VL.','Vicolo','VC.')
NOT NULL default '',
street_name varchar(70) NOT NULL default '',
street_short varchar(70) NOT NULL default '',
city varchar(70) NOT NULL default '',
province char(2) NOT NULL default '',
postal_code varchar(5) NOT NULL default '',
listed tinyint(1) NOT NULL default '0',
hide tinyint(1) NOT NULL default '0',
PRIMARY KEY (rec_no),
KEY phone_no (phone_no(8)),
KEY loc (city(4),street_name(2)),
KEY postal_code (postal_code),
KEY clf (city(6),last_name(6),first_name(1)),
KEY plf (province,last_name(7),first_name(2)),
KEY pref (prefix,city(1))
) TYPE=MyISAM;
/*!40000 ALTER TABLE White DISABLE KEYS */;
// ==========================
root@db1 elenco2g]# ls -l
total 3690476
-rw-rw---- 1 mysql mysql 1964830232 Feb 28 21:27 White.MYD
-rw-rw---- 1 mysql mysql 1810504704 Feb 28 21:47 White.MYI
-rw-rw---- 1 mysql mysql 9159 Feb 28 18:15 White.frm
[root@db1 elenco2g]# myisamchk -r White;
- recovering (with sort) MyISAM-table 'White'
Data records: 16777169
- Fixing index 1
- Fixing index 2
myisamchk: Error writing file '/tmp/STzMlliO' (Errcode: 28)
myisamchk: error: 28 when fixing table
MyISAM-table 'White' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option
[root@db1 elenco2g]# myisamchk -r --tmpdir=/var/lib/mysql/tmp White;
- recovering (with sort) MyISAM-table 'White'
Data records: 22079089
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6
- Fixing index 7
[root@db1 elenco2g]# myisamchk White;
Checking MyISAM file: White
Data records: 22079089 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check data record references index: 5
- check data record references index: 6
- check data record references index: 7
- check record links
myisamchk: warning: Found 22079089 parts Should be: 0 parts
MyISAM-table 'White' is usable but should be fixed
[root@db1 elenco2g]# ls -l
total 3433832
-rw-rw---- 1 mysql mysql 1964830232 Feb 28 22:40 White.MYD
-rw-rw---- 1 mysql mysql 1547953152 Feb 28 23:22 White.MYI
-rw-rw---- 1 mysql mysql 9159 Feb 28 18:15 White.frm
[root@db1 elenco2g]# myisamchk -r --tmpdir=/var/lib/mysql/tmp White;
- recovering (with sort) MyISAM-table 'White'
Data records: 22079089
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6
- Fixing index 7
[root@db1 elenco2g]# myisamchk White;
Checking MyISAM file: White
Data records: 22079089 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check data record references index: 5
- check data record references index: 6
- check data record references index: 7
- check record links
myisamchk: warning: Found 22079089 parts Should be: 0 parts
MyISAM-table 'White' is usable but should be fixed
[root@db1 elenco2g]# ls -l
total 3433832
-rw-rw---- 1 mysql mysql 1964830232 Feb 28 22:40 White.MYD
-rw-rw---- 1 mysql mysql 1547953152 Feb 28 23:22 White.MYI
-rw-rw---- 1 mysql mysql 9159 Feb 28 18:15 White.frm
[root@db1 elenco2g]#
>Fix:
No clue, this happens every time.
>Submitter-Id: <submitter ID>
>Originator: Steve Rapaport
>Organization: A-Tono
>MySQL support: none
>Synopsis: Indexing fails, myisamchk cannot repair, no error msg.
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.1-alpha (Official MySQL binary)
>Environment:
System: Linux db1 2.2.18pre11-va2.1smp #1 SMP Thu Mar 29 10:12:18 PST 2001
i686 unknown
Architecture: i686
Some paths: /usr/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='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3
-mpentium -felide-constructors' LDFLAGS='-static'
LIBC:
lrwxrwxrwx 1 root root 13 Apr 26 2001 /lib/libc.so.6 ->
libc-2.1.3.so
-rwxr-xr-x 1 root root 4109653 Feb 6 2001 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20344984 Feb 6 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Feb 6 2001 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql
'--with-comment=Official MySQL binary' --with-extra-charsets=complex
--with-server-suffix= --enable-thread-safe-client --enable-assembler
--with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static
--disable-shared
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
