List:General Discussion« Previous MessageNext Message »
From:BAUMEISTER Alexandre Date:December 11 2001 10:20am
Subject:Stange Bug or Table corruption ... but strange.
View as plain text  
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.

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