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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
