Hi,Thanks,
What are the sizes of your index files? (i.e. name_index2.MYI in the datadir).
It's a complete guess but with a large table like that, it's possible that you're reaching some kind of limit with your os/mysql setup due to file size. (greater than 4 GB would be my guess)
Cheers,
Andrew
Files aren't huge - row size is 86 bytes.
name_index2.MYD 1190595954 name_index2.MYI would go over 1 Gbyte as well.
It feels like some limit within MySQL - perhaps thrashing in the key cache (not that I know what I'm talking about) - but that should just make it much slower, shouldn't it? For better, or more probably worse, I'm operating with some swap (1 Gbyte RAM with 256 Mbyte swap).
Using the huge configuration (for 1 to 2 Gbytes RAM) makes no difference, errors after an hour.
Dropping the first two indexes, and then adding the troublesome third index on its own works. .MYI Index size is 212,271,104 bytes takes 5min 2.83 secs.
Cheers, Chad
-----Original Message----- From: Chad Hanna [mailto:[EMAIL PROTECTED] Sent: Tuesday 01 April 2003 17:03 To: [EMAIL PROTECTED] Subject: Adding index fails with error 1034: 126
Hi, I've been bashing my brains out for a day or two on this, so I'd appreciate some pointers.
When altering a table to add indexes I get a message like :
error 1034: 126 at record pos 957847618
mysql -V tells me: mysql Ver 11.18 Distrib 3.23.51, for unknown-freebsdelf4.6.2 (i386)
myisamchk -dv name_index2 tells me:
myisamchk -dv name_index2
MyISAM file: name_index2 Record format: Fixed length Character set: latin1 (8) File-version: 1 Creation time: 2003-04-01 13:32:18 Recover time: 2003-04-01 14:26:25 Status: checked Data records: 13844139 Deleted blocks: 0 Datafile parts: 13844139 Deleted data: 0 Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3 Datafile length: 1190595954 Keyfile length: 522063872 Max datafile length: 369367187454 Max keyfile length: 17179868159 Recordlength: 86
table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 4 multip. unsigned long 0 412131328 1024 6 4 unsigned long 0 2 11 20 multip. char packed stripped 0 97328128 1024 55 20 char stripped 0 80 3 char 0
show create table name_index2 tells me the create statement is:
CREATE TABLE `name_index2` ( `dataset_vers_id` int(10) unsigned NOT NULL default '0', `row_id` int(10) unsigned NOT NULL default '0', `quality_level` tinyint(4) NOT NULL default '100', `surname` char(20) NOT NULL default '', `emanrus` char(20) NOT NULL default '', `surname_id` int(10) unsigned NOT NULL default '0', `forename` char(20) NOT NULL default '', `forename_id` int(10) unsigned NOT NULL default '0', `forename_pos` tinyint(4) NOT NULL default '0', `place_code` char(3) NOT NULL default '', `start_year` smallint(6) NOT NULL default '0', `end_year` smallint(6) NOT NULL default '2100', KEY `name_index_idx1` (`dataset_vers_id`,`row_id`), KEY `name_index_idx2` (`surname`,`forename`,`place_code`) ) TYPE=MyISAM
I've been adding the indexes one at time to try and isolate the problem, the failure occurs when I add:
alter table name_index2 add index name_index_idx3 (surname_id, forename_id, place_code);
Possibly important: 744845 data records (~ 5%) have a surname_id = 0 I'm using the 'large' my.cnf from the distribution with a couple of tweaks.
A couple of weeks ago, a 10% smaller table took 45 minutes to add the indexes. This one an hour to add two indexes and several hours to fail to add all four.
I'm getting to the stage when I'm considering MERGE tables.
Any ideas welcome!
-- Chad Hanna Chairman Berkshire Family History Society www.berksfhs.org.uk
-- Chad Hanna Chairman Berkshire Family History Society www.berksfhs.org.uk Quality Family History Data www.familyhistoryonline.net
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
