List:General Discussion« Previous MessageNext Message »
From:BAUMEISTER Alexandre Date:December 11 2001 1:00pm
Subject:Re[2]: Stange Bug or Table corruption ... but strange.
View as plain text  
Bonjour Richard,

RE> Have  you checked your table with myisamchk? Sounds like a corrupt
RE> index to me.

  I did "check" and "repair" table from mysql console.

  And from the documentation :

 "CHECK  TABLE  only works on MyISAM tables. On MyISAM tables it's the
 same thing as running myisamchk -m table_name on the table."

 "REPAIR  TABLE only works on MyISAM tables and is the same as running
 myisamchk -r table_name on the table."

 ...

 But as I copied it in my original email :

>> mysql> check table historique_AMS;
>> +--------------------------+-------+----------+----------+
>> | Table                    | Op    | Msg_type | Msg_text |
>> +--------------------------+-------+----------+----------+
>> | cotations.historique_AMS | check | status   | OK       |
>> +--------------------------+-------+----------+----------+
>> 1 row in set (15.27 sec)
>> 
>> mysql> repair table historique_AMS;
>> +--------------------------+--------+----------+----------+
>> | Table                    | Op     | Msg_type | Msg_text |
>> +--------------------------+--------+----------+----------+
>> | cotations.historique_AMS | repair | status   | OK       |
>> +--------------------------+--------+----------+----------+
>> 1 row in set (27.02 sec)


  Regards,
  Alex.

       ==================================================

RE> BAUMEISTER Alexandre wrote:

>> Bonjour,
>> 
>>   I have a table :
>> 
>> [11:09am]> mysqldump -d cotations historique_AMS
>> # MySQL dump 8.16
>> #
>> # Host: localhost    Database: cotations
>> #--------------------------------------------------------
>> # Server version        3.23.45
>> 
>> #
>> # Table structure for table 'historique_AMS'
>> #
>> 
>> CREATE TABLE historique_AMS (
>>   symbole char(20) NOT NULL default '',
>>   date date NOT NULL default '0000-00-00',
>>   ouverture double(17,5) NOT NULL default '0.00000',
>>   haut double(17,5) NOT NULL default '0.00000',
>>   bas double(17,5) NOT NULL default '0.00000',
>>   cloture double(17,5) NOT NULL default '0.00000',
>>   volume int(10) unsigned NOT NULL default '0',
>>   UNIQUE KEY symbole (symbole,date)
>> ) TYPE=MyISAM PACK_KEYS=1;
>> 
>> 
>>   There are about 700.000 lines in it :
>> 
>> mysql> select count(*) from historique_AMS;
>> +----------+
>> | count(*) |
>> +----------+
>> |   685536 |
>> +----------+
>> 1 row in set (0.02 sec)
>> 
>>   The  problem is that when I ask for all lines for a given symbol, it
>>   does not return all the lines :
>> 
>> mysql> select * from historique_AMS where symbole='1rAUNIA';
>> +---------+------------+-----------+----------+----------+----------+--------+
>> | symbole | date       | ouverture | haut     | bas      | cloture  | volume |
>> +---------+------------+-----------+----------+----------+----------+--------+
>> | 1rAUNIA | 2001-10-29 |  58.50000 | 58.80000 | 56.15000 | 56.55000 |   2710 |
>> | 1rAUNIA | 2001-10-30 |  56.55000 | 57.25000 | 55.50000 | 56.90000 |   4566 |
>> | 1rAUNIA | 2001-10-31 |  55.00000 | 58.95000 | 55.00000 | 58.95000 |   5166 |
>> ...
>> ...
>> | 1rAUNIA | 2001-12-07 |  62.60000 | 63.10000 | 61.55000 | 63.10000 |   7076 |
>> | 1rAUNIA | 2001-12-10 |  62.00000 | 63.15000 | 60.00000 | 62.95000 |   2724 |
>> +---------+------------+-----------+----------+----------+----------+--------+
>> 31 rows in set (0.12 sec)
>> 
>>   And if I do the same query but with "ORDER BY date DESC" :
>> 
>> mysql> select * from historique_AMS_bak where symbole='1rAUNIA' order by date
> desc;
>> +----------+------------+-----------+----------+----------+----------+--------+
>> | symbole  | date       | ouverture | haut     | bas      | cloture  | volume |
>> +----------+------------+-----------+----------+----------+----------+--------+
>> | 1rAUNIA  | 2001-12-10 |  62.00000 | 63.15000 | 60.00000 | 62.95000 |   2724 |
>> | 1rAUNIA  | 2001-12-07 |  62.60000 | 63.10000 | 61.55000 | 63.10000 |   7076 |
>> | 1rAUNIA  | 2001-12-06 |  62.80000 | 63.10000 | 62.80000 | 63.10000 |    661 |
>> | 1rAUNIA  | 2001-12-05 |  60.90000 | 62.95000 | 60.65000 | 62.95000 |   1266 |
>> | 1rAUNIA  | 2001-12-04 |  60.55000 | 63.20000 | 60.55000 | 63.15000 |   2054 |
>> ...
>> ...
>> | 1rAUNIA  | 2001-10-31 |  55.00000 | 58.95000 | 55.00000 | 58.95000 |   5166 |
>> | 1rAUNIA  | 2001-10-30 |  56.55000 | 57.25000 | 55.50000 | 56.90000 |   4566 |
>> | 1rAUNIA  | 2001-10-29 |  58.50000 | 58.80000 | 56.15000 | 56.55000 |   2710 |
>>  | 2001-10-26 |  55.50000 | 58.05000 | 55.50000 | 57.95000 |   3230 |
>>  | 2001-10-25 |  56.40000 | 58.30000 | 53.80000 | 55.90000 |   3297 |
>>  | 2001-10-24 |  57.30000 | 57.65000 | 56.40000 | 56.40000 |   2253 |
>>  | 2001-10-23 |  59.55000 | 59.55000 | 57.70000 | 57.70000 |   2533 |
>>  | 2001-10-22 |  58.60000 | 59.50000 | 56.00000 | 59.40000 |  15464 |
>>  | 2001-10-19 |  57.00000 | 57.50000 | 56.50000 | 56.80000 |   1378 |
>>  | 2001-10-18 |  58.90000 | 58.90000 | 57.05000 | 57.05000 |    843 |
>>  | 2001-10-17 |  59.20000 | 59.20000 | 57.10000 | 58.50000 |   1485 |
>> ...
>> ...
>>  | 2000-10-09 |  56.85000 | 57.45000 | 55.55000 | 57.05000 |  12041 |
>>  | 2000-10-06 |  55.00000 | 57.40000 | 55.00000 | 56.85000 |  22097 |
>>  | 2000-10-05 |  53.60000 | 55.15000 | 53.60000 | 54.20000 |   8455 |
>>  | 2000-10-04 |  54.95000 | 55.10000 | 54.50000 | 55.00000 |   4315 |
>>  | 2000-10-03 |  55.00000 | 55.00000 | 53.60000 | 55.00000 |   9578 |
>>  | 2000-10-02 |  54.00000 | 55.75000 | 53.15000 | 54.60000 |  12922 |
>>  | 2000-09-29 |  53.85000 | 54.05000 | 52.10000 | 53.60000 |   5133 |
>>  | 2000-09-28 |  53.00000 | 53.80000 | 52.05000 | 52.05000 |   1445 |
>>  | 2000-09-27 |  52.95000 | 53.90000 | 52.05000 | 53.85000 |  20901 |
>> +----------+------------+-----------+----------+----------+----------+--------+
>> 306 rows in set (0.05 sec)
>> 
>>   As  you can see, in this case it returns 306 lines while it returned
>>   only  31 from the previous query. But the most strange thing is that
>>   after the first 31 lines, the column "symbol" is empty ...
>> 
>>   While  in the same time the data are good (these are historical data
>>   for  stock  exchange symbol '1rAUNIA' and the chart is ok with these
>>   data).
>> 
>>   I did a lot of things to try to correct that :
>> 
>> mysql> check table historique_AMS;
>> +--------------------------+-------+----------+----------+
>> | Table                    | Op    | Msg_type | Msg_text |
>> +--------------------------+-------+----------+----------+
>> | cotations.historique_AMS | check | status   | OK       |
>> +--------------------------+-------+----------+----------+
>> 1 row in set (15.27 sec)
>> 
>> mysql> repair table historique_AMS;
>> +--------------------------+--------+----------+----------+
>> | Table                    | Op     | Msg_type | Msg_text |
>> +--------------------------+--------+----------+----------+
>> | cotations.historique_AMS | repair | status   | OK       |
>> +--------------------------+--------+----------+----------+
>> 1 row in set (27.02 sec)
>> 
>> mysql> optimize table historique_AMS;
>> +--------------------------+----------+----------+----------+
>> | Table                    | Op       | Msg_type | Msg_text |
>> +--------------------------+----------+----------+----------+
>> | cotations.historique_AMS | optimize | status   | OK       |
>> +--------------------------+----------+----------+----------+
>> 1 row in set (13.84 sec)
>> 
>>   But still the same thing :(
>> 
>>   If I do :
>> 
>> mysql> select * from historique_AMS into outfile '/tmp/test';
>> Query OK, 685536 rows affected (37.80 sec)
>> 
>>   Then  the  file  '/tmp/test'  contains  some  lines with no 'symbol'
>>   column :
>> 
>> 1aACBELS        2000-10-02      0.24000 0.27000 0.24000 0.27000 0
>>         2000-10-02      0.69000 0.69000 0.69000 0.69000 2000
>>         2000-10-02      0.95000 0.95000 0.95000 0.95000 0
>>         2000-10-02      0.43000 0.43000 0.43000 0.43000 0
>>         2000-10-02      1.10000 1.10000 1.00000 1.01000 0
>>         2000-10-02      0.52000 0.52000 0.48000 0.48000 0
>> 1aAINTJA        2001-11-01      14.90000        14.90000        14.90000       
> 14.90000        100
>>         2000-10-02      0.30000 0.30000 0.28000 0.28000 0
>>         2000-10-02      0.45000 0.45000 0.45000 0.45000 0
>>         2000-10-02      0.19000 0.20000 0.10000 0.20000 100000
>>         2000-10-02      0.33000 0.33000 0.33000 0.33000 0
>>         2000-10-02      0.11000 0.11000 0.11000 0.11000 10000
>>         2000-10-02      0.20000 0.20000 0.20000 0.20000 0
>>         2000-10-02      0.74000 0.74000 0.45000 0.45000 0
>> 1aACBMOT        2000-10-02      0.21000 0.21000 0.21000 0.21000 0
>> 1aACBNAS        2000-10-02      0.92000 0.92000 0.92000 0.92000 300
>> 1dFED   2001-11-02      90.47000        90.76000        90.19000        90.38000 
>       0  
>> 
>>   Any idea ?
>> 
>>   Regards,
>>   Alex.
>> 
>> 
>> ---------------------------------------------------------------------
>> 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 <mysql-thread93680@stripped>
>> To unsubscribe, e-mail
> <mysql-unsubscribe-mysql=freebsd.csie.nctu.edu.tw@stripped>
>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>> 
>> 





-----------------------------------------------------------------------
Alexandre BAUMEISTER                        Standard : +333-83-36-58-67
Email : alex@stripped                Fax:       +333-83-36-69-44

FINANCE NET - 1,rue St Dizier 54000 NANCY - FRANCE (www.finance-net.fr)
-----------------------------------------------------------------------

Thread
Can't import dump file OS Xollie10 Dec
Re: Can't import dump file OS Xollie11 Dec
  • Stange Bug or Table corruption ... but strange.BAUMEISTER Alexandre11 Dec
Re: Stange Bug or Table corruption ... but strange.Sinisa Milivojevic11 Dec
  • Re[2]: Stange Bug or Table corruption ... but strange.BAUMEISTER Alexandre11 Dec
Re[2]: Stange Bug or Table corruption ... but strange.BAUMEISTER Alexandre11 Dec
Re[3]: Stange Bug or Table corruption ... but strange.BAUMEISTER Alexandre11 Dec