Stange Bug or Table corruption ... but strange.
Bonjour, I have a table : [11:09am] mysqldump -d cotations historique_AMS # MySQL dump 8.16 # # Host: localhostDatabase: cotations # # Server version3.23.45 # # Table structure for table 'historique_AMS' # CREATE TABLE historique_AMS ( symbole char(20) NOT NULL default '', date date NOT NULL default '-00-00', ouverture double(17,5) NOT NULL default '0.0', haut double(17,5) NOT NULL default '0.0', bas double(17,5) NOT NULL default '0.0', cloture double(17,5) NOT NULL default '0.0', 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.5 | 58.8 | 56.15000 | 56.55000 | 2710 | | 1rAUNIA | 2001-10-30 | 56.55000 | 57.25000 | 55.5 | 56.9 | 4566 | | 1rAUNIA | 2001-10-31 | 55.0 | 58.95000 | 55.0 | 58.95000 | 5166 | ... ... | 1rAUNIA | 2001-12-07 | 62.6 | 63.1 | 61.55000 | 63.1 | 7076 | | 1rAUNIA | 2001-12-10 | 62.0 | 63.15000 | 60.0 | 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.0 | 63.15000 | 60.0 | 62.95000 | 2724 | | 1rAUNIA | 2001-12-07 | 62.6 | 63.1 | 61.55000 | 63.1 | 7076 | | 1rAUNIA | 2001-12-06 | 62.8 | 63.1 | 62.8 | 63.1 |661 | | 1rAUNIA | 2001-12-05 | 60.9 | 62.95000 | 60.65000 | 62.95000 | 1266 | | 1rAUNIA | 2001-12-04 | 60.55000 | 63.2 | 60.55000 | 63.15000 | 2054 | ... ... | 1rAUNIA | 2001-10-31 | 55.0 | 58.95000 | 55.0 | 58.95000 | 5166 | | 1rAUNIA | 2001-10-30 | 56.55000 | 57.25000 | 55.5 | 56.9 | 4566 | | 1rAUNIA | 2001-10-29 | 58.5 | 58.8 | 56.15000 | 56.55000 | 2710 | | 2001-10-26 | 55.5 | 58.05000 | 55.5 | 57.95000 | 3230 | | 2001-10-25 | 56.4 | 58.3 | 53.8 | 55.9 | 3297 | | 2001-10-24 | 57.3 | 57.65000 | 56.4 | 56.4 | 2253 | | 2001-10-23 | 59.55000 | 59.55000 | 57.7 | 57.7 | 2533 | | 2001-10-22 | 58.6 | 59.5 | 56.0 | 59.4 | 15464 | | 2001-10-19 | 57.0 | 57.5 | 56.5 | 56.8 | 1378 | | 2001-10-18 | 58.9 | 58.9 | 57.05000 | 57.05000 |843 | | 2001-10-17 | 59.2 | 59.2 | 57.1 | 58.5 | 1485 | ... ... | 2000-10-09 | 56.85000 | 57.45000 | 55.55000 | 57.05000 | 12041 | | 2000-10-06 | 55.0 | 57.4 | 55.0 | 56.85000 | 22097 | | 2000-10-05 | 53.6 | 55.15000 | 53.6 | 54.2 | 8455 | | 2000-10-04 | 54.95000 | 55.1 | 54.5 | 55.0 | 4315 | | 2000-10-03 | 55.0 | 55.0 | 53.6 | 55.0 | 9578 | | 2000-10-02 | 54.0 | 55.75000 | 53.15000 | 54.6 | 12922 | | 2000-09-29 | 53.85000 | 54.05000 | 52.1 | 53.6 | 5133 | | 2000-09-28 | 53.0 | 53.8 | 52.05000 | 52.05000 | 1445 | | 2000-09-27 | 52.95000 | 53.9 | 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
Re: Stange Bug or Table corruption ... but strange.
BAUMEISTER Alexandre writes: Bonjour, I have a table : [skip] Any idea ? Regards, Alex. There were some bug fixes with packed keys. 3.23.47 will soon be out and it would be fine if you could test your case with it again. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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
Re[2]: Stange Bug or Table corruption ... but strange.
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: localhostDatabase: cotations # # Server version3.23.45 # # Table structure for table 'historique_AMS' # CREATE TABLE historique_AMS ( symbole char(20) NOT NULL default '', date date NOT NULL default '-00-00', ouverture double(17,5) NOT NULL default '0.0', haut double(17,5) NOT NULL default '0.0', bas double(17,5) NOT NULL default '0.0', cloture double(17,5) NOT NULL default '0.0', 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.5 | 58.8 | 56.15000 | 56.55000 | 2710 | | 1rAUNIA | 2001-10-30 | 56.55000 | 57.25000 | 55.5 | 56.9 | 4566 | | 1rAUNIA | 2001-10-31 | 55.0 | 58.95000 | 55.0 | 58.95000 | 5166 | ... ... | 1rAUNIA | 2001-12-07 | 62.6 | 63.1 | 61.55000 | 63.1 | 7076 | | 1rAUNIA | 2001-12-10 | 62.0 | 63.15000 | 60.0 | 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.0 | 63.15000 | 60.0 | 62.95000 | 2724 | | 1rAUNIA | 2001-12-07 | 62.6 | 63.1 | 61.55000 | 63.1 | 7076 | | 1rAUNIA | 2001-12-06 | 62.8 | 63.1 | 62.8 | 63.1 |661 | | 1rAUNIA | 2001-12-05 | 60.9 | 62.95000 | 60.65000 | 62.95000 | 1266 | | 1rAUNIA | 2001-12-04 | 60.55000 | 63.2 | 60.55000 | 63.15000 | 2054 | ... ... | 1rAUNIA | 2001-10-31 | 55.0 | 58.95000 | 55.0 | 58.95000 | 5166 | | 1rAUNIA | 2001-10-30 | 56.55000 | 57.25000 | 55.5 | 56.9 | 4566 | | 1rAUNIA | 2001-10-29 | 58.5 | 58.8 | 56.15000 | 56.55000 | 2710 | | 2001-10-26 | 55.5 | 58.05000 | 55.5 | 57.95000 | 3230 | | 2001-10-25 | 56.4 | 58.3 | 53.8 | 55.9 | 3297 | | 2001-10-24 | 57.3 | 57.65000 | 56.4 | 56.4 | 2253 | | 2001-10-23 | 59.55000 | 59.55000 | 57.7 | 57.7 | 2533 | | 2001-10-22 | 58.6 | 59.5 | 56.0 | 59.4 | 15464 | | 2001-10-19 | 57.0 | 57.5 | 56.5 | 56.8 | 1378 | | 2001-10-18 | 58.9 | 58.9 | 57.05000 | 57.05000 |843 | | 2001-10-17 | 59.2 | 59.2 | 57.1 | 58.5 | 1485 | ... ... | 2000-10-09 | 56.85000 | 57.45000 | 55.55000 | 57.05000 | 12041 | | 2000-10-06 | 55.0 | 57.4 | 55.0 | 56.85000 | 22097 | | 2000-10-05 | 53.6 | 55.15000 | 53.6 | 54.2 |
Re[2]: Stange Bug or Table corruption ... but strange.
Bonjour Sinisa,
SM There were some bug fixes with packed keys.
SM 3.23.47 will soon be out and it would be fine if you could test your
SM case with it again.
I tried to do
alter table historique_AMS PACK_KEYS=0;
But it did not correct the problem.
Then I wanted check if the PACK_KEYS was equal to 0 when I do a
mysqldump -d of the table.
But I forgot the -d ... and here is what I found : There are some
\r at the end of some symbols ! (see below)
I don't know yet if this is a bug from my programs, but I don't
think so.
Any idea of how these \r appeared ? Could these have been added by
Mysql ?
Does it explain to you why the following query returns 31 rows
mysql select * from historique_AMS where symbole='1rAUNIA';
while the following returns 306 rows ?
mysql select * from historique_AMS_bak where symbole='1rAUNIA' order by date desc;
Regards,
Alex.
INSERT INTO historique_AMS VALUES
('1aA13456\r','2000-09-28',104.4,104.4,104.4,104.4,0);
INSERT INTO historique_AMS VALUES
('1aA850737','2000-09-28',8.0,8.0,8.0,8.0,0);
INSERT INTO historique_AMS VALUES
('1aA716338','2000-09-28',101.8,101.8,101.8,101.8,0);
INSERT INTO historique_AMS VALUES
('1aAAABTE','2001-11-06',138.5,141.0,137.2,140.0,7280);
INSERT INTO historique_AMS VALUES
('1aAFINTO','2000-09-28',36.0,36.0,36.0,36.0,0);
INSERT INTO historique_AMS VALUES
('1aAFTUSF','2000-09-28',23.45000,23.45000,23.45000,23.45000,0);
INSERT INTO historique_AMS VALUES
('1aA12283','2000-09-28',104.0,104.0,104.0,104.0,25);
INSERT INTO historique_AMS VALUES
('1aA12299','2000-09-28',99.75000,99.75000,98.75000,98.75000,19000);
INSERT INTO historique_AMS VALUES
('1aA148140\r','2000-09-28',101.5,101.5,101.5,101.5,0);
INSERT INTO historique_AMS VALUES
('1rAOPG','2001-11-08',37.7,37.9,37.15000,37.45000,10317);
INSERT INTO historique_AMS VALUES
('1rAFOBRA\r','2000-09-28',27.5,27.5,27.5,27.5,0);
INSERT INTO historique_AMS VALUES
('1rAFOBR\r','2000-09-28',24.0,24.0,24.0,24.0,0);
INSERT INTO historique_AMS VALUES
('1aAFEF\r','2000-09-28',36.95000,36.95000,36.05000,36.95000,5006);
INSERT INTO historique_AMS VALUES
('1aAFEFSC\r','2000-09-28',68.25000,68.25000,68.25000,68.25000,0);
INSERT INTO historique_AMS VALUES
('1aAFORJA\r','2000-09-28',34.95000,35.0,34.65000,35.0,2064);
INSERT INTO historique_AMS VALUES
('1aAFRODF\r','2000-09-28',49.3,49.3,49.3,49.3,8528);
INSERT INTO historique_AMS VALUES
('1aAFRNX\r','2000-09-28',25.82000,25.82000,25.82000,25.82000,38920);
INSERT INTO historique_AMS VALUES
('1aAFRNI\r','2000-09-28',24.25000,24.3,24.25000,24.25000,61900);
INSERT INTO historique_AMS VALUES
('1aAFRNV\r','2000-09-28',25.12000,25.12000,25.12000,25.12000,41454);
INSERT INTO historique_AMS VALUES
('1rAFOCCA\r','2000-09-28',914.0,914.0,914.0,914.0,0);
INSERT INTO historique_AMS VALUES
('1aAFBAMF\r','2000-09-28',33.25000,33.75000,33.25000,33.75000,1438);
INSERT INTO historique_AMS VALUES
('1rAFOCCB\r','2000-09-28',25.15000,25.15000,25.15000,25.15000,14250);
INSERT INTO historique_AMS VALUES
('1rAFORDA','2000-09-28',27.0,27.0,27.0,27.0,0);
INSERT INTO historique_AMS VALUES
('1aAFRNAF\r','2000-09-28',66.2,66.2,66.2,66.2,78);
INSERT INTO historique_AMS VALUES
('1aAFBNAS','2000-09-28',51.9,51.9,51.0,51.0,9447);
INSERT INTO historique_AMS VALUES
('1aAFRNUI\r','2000-09-28',52.98000,52.98000,52.98000,52.98000,760);
INSERT INTO historique_AMS VALUES
('1aAFOWMF\r','2000-09-28',53.5,53.5,53.0,53.0,18889);
-
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
Re[3]: Stange Bug or Table corruption ... but strange.
Bonjour, BA But I forgot the -d ... and here is what I found : There are some BA \r at the end of some symbols ! I corrected everything with one query : mysql UPDATE historique_AMS set symbole=TRIM(TRAILING '\r' FROM symbole); These '\r' may come from a program a colleague did. Sorry for the disturb. 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
