Lost primary key from table
Hi, I've really interresting topic this time: our application stopped getting data. It turned out these queries were unexpectedly slow while operating on MyISAM tables: ++---+---+--+-+--+++ | Id | User | Host | db | Command | Time | State | | Info | | ++---+---+--+-+--+++ | 16 | wwwpedant | localhost | Saccharomyces_cerevisiae | Query | 0| Sorting |result | select descr,hitcode,pval,score from blast where prot_data_id=180 and |pval0.5 order by 4 desc | In case of other databases same applications and same mysqld operated normally. I think I see the problem why it's so slow: Instead of having: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | id | int(11) | | PRI | 0 | | | prot_data_id | int(11) | | MUL | 0 | | | contig_data_id | int(11) | | MUL | 0 | | | contig_data_code | varchar(100) | | | | | | code | varchar(100) | | MUL | | | | hitcode | varchar(255) | | MUL | | | | score| int(11) | | | 0 | | | pval | varchar(255) | | | | | | ident| int(11) | | | 0 | | | posit| int(11) | | | 0 | | | begin| int(11) | | | 0 | | | end | int(11) | | | 0 | | | length | int(11) | | | 0 | | | hitlength| int(11) | | | 0 | | | hitstart | int(11) | | | 0 | | | hitstop | int(11) | | | 0 | | | descr| varchar(255) | YES | MUL | NULL| | | aln | longtext | YES | | NULL| | | conf | varchar(6) | YES | | NULL| | | manual | char(3) | YES | | NULL| | +--+--+--+-+-+---+ we have in one case: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | id | int(11) | | | 0 | | | prot_data_id | int(11) | | | 0 | | | contig_data_id | int(11) | | | 0 | | | contig_data_code | varchar(100) | | | | | | code | varchar(100) | | | | | | hitcode | varchar(255) | | | | | | score| int(11) | | | 0 | | | pval | varchar(255) | | | | | | ident| int(11) | | | 0 | | | posit| int(11) | | | 0 | | | begin| int(11) | | | 0 | | | end | int(11) | | | 0 | | | length | int(11) | | | 0 | | | hitlength| int(11) | | | 0 | | | hitstart | int(11) | | | 0 | | | hitstop | int(11) | | | 0 | | | descr| varchar(255) | YES | | NULL| | | aln | longtext | YES | | NULL| | | conf | varchar(6) | YES | | NULL| | | manual | char(3) | YES | | NULL| | +--+--+--+-+-+---+ Actually, here's the diff: 4,6c4,6 | id | int(11) | | | 0 | | | prot_data_id | int(11) | | | 0 | | | contig_data_id | int(11) | | | 0 | | --- | id | int(11) | | PRI | 0 | | | prot_data_id | int(11) | | MUL | 0 | | | contig_data_id | int(11) | | MUL | 0 | | 8,9c8,9 | code | varchar(100) | | | | | | hitcode | varchar(255) | | | | | --- | code | varchar(100) | | MUL | | | |
Re: Lost primary key from table
From: Martin MOKREJ [EMAIL PROTECTED] OK, my question is: could someone imagine a way how such a thing could happen? The information about KEY's is set when the table is created. I suspect more end-user problem then mysqld problem, but could it happen that the whole information in Key column got dropped? Can one get it out from the .frm file? Did you, at any time backup and restore the table in the following manner? CREATE TABLE blast_backup SELECT * FROM blast; DROP TABLE blast; CREATE TABLE blast SELECT * FROM blast_backup; I've done this in the past, and this method of creating a table doesn't copy key info. Took me a couple tries, but I finally figured it out. I don't know if this is your problem, persay, but it's the only time I've seen that happen unexplainedly. -- denonymous www.coldcircuit.net - 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
