>Description Having followed the instructions in section 5.2.8 to create a table using LOAD DATA INFILE *without* its indexes, the indexes are *still* created at the end of the LOAD DATA INFILE statement >How-To-Repeat: The following shell script demonstrates the problem. There are two directory listings, before and after the load. In the second one BestGuess.MYI should be unchanged at 1024 bytes but it isn't because the indexes have been created even though myisamchk -k 0 has been executed
Script assumes: /tmp is available databases are in /usr/local/var (line 1) <<<start script>>> MYSQLDIR=/usr/local/var cat >/tmp/bgdata <<EOF 1|1|'Fordham'|'Emma'|344|'12'|'94'|'7ka3hn5qhtDRbPkJ1uWX/A'|''|1|3|'Lexdon & Winstree'|'N' 2|1|'Fordham'|'Enoch'|625|'22'|'318'|'Daf1Dt+XH3aq47D8gigWDw'|''|1|3|'Wakefi eld'|'N' 3|1|'Fordham'|'George'|500|'12'|'164'|'i1L5pD8Q8gI+CFfqkkOWzQ'|''|1|3|'Saffr on Walden'|'N' 4|1|'Fordham'|'Henry'|548|'14'|'144'|'Md3MtuwgcJtjGs5rKcU8pg'|''|1|3|'Saint Ives'|'N' 5|1|'Fordham'|'Henry'|209|'3'|'43'|'EpyvpR6wK4tXDCg0dUWThA'|''|1|3|'Edmonton '|'N' 6|1|'Fordham'|'John'|116|'14'|'7'|'SE1U6ewpSZkU0wA0WRe/pg'|''|1|3|'Cambridge '|'N' 7|1|'Fordham'|'Lewis'|348|'14'|'68'|'JeHwC7Jnhx6VNFabWRCwQA'|''|1|3|'Linton' |'N' 8|1|'Fordham'|'Stephen'|268|'6'|'359'|'LS/iLpTXxrfqwlfAVoJdyQ'|''|1|3|'Hatfi eld& Welwyn'|'N' 9|1|'Fordham'|'female'|416|'14'|'89'|'s91dCImO+HeyIy6rXxldGA'|''|1|3|'Newmar ket'|'N' 10|2|'FOTHERGILL'|'ROBERT'|206|'25'|'289'|'UIOL6Q5/8cMoeA/k/DmlZg'|''|1|3|'E astWard'|'N' EOF mysql -D test <<EOF drop table BestGuess; EOF mysql -D test <<EOF create table BestGuess ( RecordNumber INT UNSIGNED NOT NULL AUTO_INCREMENT, ChunkNumber INT UNSIGNED NOT NULL, Confirmed VARCHAR( 1 ), Surname VARCHAR( 50 ) NOT NULL, GivenName VARCHAR( 50 ) NOT NULL, AgeAtDeath VARCHAR(50), DistrictNumber SMALLINT UNSIGNED NOT NULL, District VARCHAR( 50 ) NOT NULL, DistrictFlag TINYINT UNSIGNED NOT NULL, Volume VARCHAR( 50 ) NOT NULL, Page VARCHAR( 50 ) NOT NULL, Hash CHAR(22) NOT NULL, RecordTypeID TINYINT NOT NULL, QuarterNumber SMALLINT NOT NULL, PRIMARY KEY (RecordNumber), INDEX (ChunkNumber), INDEX (Surname(10),GivenName(10),QuarterNumber), INDEX (Surname(10),GivenName(10),DistrictNumber), INDEX (Surname(10),QuarterNumber), INDEX (Surname(10),DistrictNumber), INDEX (GivenName(10),QuarterNumber), INDEX (Volume,Page,QuarterNumber), INDEX (DistrictNumber,QuarterNumber) ); EOF ls -l $MYSQLDIR/test/BestGuess.M* mysqladmin flush-tables myisamchk -k 0 -rq $MYSQLDIR/test/BestGuess mysql -D test <<EOF LOAD DATA INFILE '/tmp/bgdata' IGNORE INTO TABLE BestGuess FIELDS TERMINATED BY '|' ENCLOSED BY "'" (RecordNumber, ChunkNumber,Surname,GivenName, DistrictNumber,Volume,Page,Hash, AgeAtDeath,RecordTypeID,QuarterNumber, District,Confirmed) EOF ls -l $MYSQLDIR/test/BestGuess.M* <<<end script>>> >Fix: Unknown >Submitter-Id: barrie >Originator: Barrie Archer >Organization: FreeBMD (http://freebmd.rootsweb.com) >MySQL support: none >Synopsis: drop keys does not inhibit creation of indexes on load >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-3.23.51 (Source distribution) >Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.51, for unknown-freebsdelf4.2 on i386 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.51 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 5 days 6 hours 7 min 19 sec Threads: 2 Questions: 2215677 Slow queries: 22 Opens: 300 Flush tables: 16 Open tables: 1 Queries per second avg: 4.880 >Environment: System: FreeBSD sump.inv.thebunker.net 4.2-RELEASE FreeBSD 4.2-RELEASE #0: Wed Feb 7 18:04:50 GMT 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/SUMP i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1169076 Nov 20 2000 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Feb 6 2001 /usr/lib/libc.so -> libc.so.4 -r--r--r-- 1 root wheel 559196 Nov 20 2000 /usr/lib/libc.so.4 Configure command: ./configure --with-innodb --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
