| Bug #38215 | Query fails to return correct info from table | ||
|---|---|---|---|
| Submitted: | 17 Jul 2008 20:55 | Modified: | 20 Aug 2009 6:14 |
| Reporter: | Mattias Eriksson | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0.67 | OS: | FreeBSD |
| Assigned to: | CPU Architecture: | Any | |
[18 Jul 2008 3:41]
Valeriy Kravchuk
Thank you for a problem report. As 5.0.51 binaries for FreeBSD on that platfrom are not created by MySQL (see http://dev.mysql.com/downloads/mysql/5.0.html#freebsd), please, send the configure command line and gcc version used to build them.
[18 Jul 2008 8:40]
Mattias Eriksson
The gcc version used is the one shipped with FreeBSD 6.3 sparc64 [root@njord ~]# gcc --version gcc (GCC) 3.4.6 [FreeBSD] 20060305 Copyright (C) 2006 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. The mysql version was originaly 5.0.45 from ports built by FreeBSD ports maintainer. But when I discovered the problem I removed 5.0.45 and build 5.0.51 frm ports hoping that the problem would disappear, but it didn't. So here is the ./configure taken from config.log: ./configure --localstatedir=/var/db/mysql --without-debug --without-readline --without-libedit --without-bench --without-extra-tools --with-libwrap --with-mysqlfs --with-low-memory --with-comment=FreeBSD port: mysql-server-5.0.51a --enable-thread-safe-client --with-named-thread-libs=-pthread --prefix=/usr/local --mandir=/usr/local/man --infodir=/usr/local/info/ --build=sparc64-portbld-freebsd6.3 I will attach config.log as well.
[6 Aug 2008 9:34]
Susanne Ebrecht
Many thanks for reporting a bug.
I can't repeat this by using FreeBSD 7.0 and AMD64 architecture.
mysql> SELECT /* Parser::replaceLinkHolders WikiSysop */ * FROM `page` WHERE (page_namespace=0 AND page_title IN('TCP', 'UDP', 'PIM', 'RIPng', 'OSPFv6', 'RIP', 'TP4', 'SCTP', 'HP', 'Huvudsidan', 'Extreme_Networks', 'Cisco', 'Allied_Telesyn', 'OSPF', 'IS-IS', 'SDH', 'Token_Ring', 'FDDI', 'Ethernet', 'Optisk_fiber', 'Spanning-tree', 'CLNS', 'BGP', 'EIGRP', 'IPX', 'IPv6', 'IPv4', 'Twisted_pair'))\G
*************************** 1. row ***************************
page_id: 7
page_namespace: 0
page_title: Cisco
page_restrictions:
page_counter: 4
page_is_redirect: 0
page_is_new: 0
page_random: 0.626507716055
page_touched: 20080717113152
page_latest: 54
page_len: 222
*************************** 2. row ***************************
page_id: 6
page_namespace: 0
page_title: Huvudsidan
page_restrictions:
page_counter: 2
page_is_redirect: 1
page_is_new: 0
page_random: 0.14495238096
page_touched: 20080717152458
page_latest: 43
page_len: 23
*************************** 3. row ***************************
page_id: 5
page_namespace: 0
page_title: IS-IS
page_restrictions:
page_counter: 36
page_is_redirect: 0
page_is_new: 0
page_random: 0.862870064524
page_touched: 20080717135928
page_latest: 104
page_len: 4182
*************************** 4. row ***************************
page_id: 16
page_namespace: 0
page_title: OSPF
page_restrictions:
page_counter: 4
page_is_redirect: 0
page_is_new: 0
page_random: 0.235461878317
page_touched: 20080717140003
page_latest: 106
page_len: 142
4 rows in set (0.01 sec)
Unfortunately, we don't have a sparc test machine with FreeBSD.
Please paste output from:
EXPLAIN SELECT /* Parser::replaceLinkHolders WikiSysop */ * FROM `page` WHERE (page_namespace=0 AND page_title IN('TCP', 'UDP', 'PIM', 'RIPng', 'OSPFv6', 'RIP', 'TP4', 'SCTP', 'HP', 'Huvudsidan', 'Extreme_Networks', 'Cisco', 'Allied_Telesyn', 'OSPF', 'IS-IS', 'SDH', 'Token_Ring', 'FDDI', 'Ethernet', 'Optisk_fiber', 'Spanning-tree', 'CLNS', 'BGP', 'EIGRP', 'IPX', 'IPv6', 'IPv4', 'Twisted_pair'))
Also I need to know, which FreeBSD version you use.
[6 Aug 2008 9:35]
Susanne Ebrecht
Sorry, I forgot ... it also would be nice when you try the newer MySQL version: MySQL 5.0.51b.
[6 Aug 2008 11:13]
Mattias Eriksson
I am using FreeBSD 6.3 on sparc64.
Here is the output from the query:
mysql> EXPLAIN SELECT /* Parser::replaceLinkHolders WikiSysop */ * FROM `page` WHERE (page_namespace=0 AND page_title IN('TCP', 'UDP', 'PIM', 'RIPng', 'OSPFv6', 'RIP', 'TP4', 'SCTP', 'HP', 'Huvudsidan', 'Extreme_Networks', 'Cisco', 'Allied_Telesyn', 'OSPF', 'IS-IS', 'SDH', 'Token_Ring', 'FDDI', 'Ethernet', 'Optisk_fiber', 'Spanning-tree', 'CLNS', 'BGP', 'EIGRP', 'IPX', 'IPv6', 'IPv4', 'Twisted_pair'));
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | page | ref | name_title | name_title | 4 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.01 sec)
mysql>
[6 Aug 2008 11:31]
Mattias Eriksson
Thanks for your efforts. There are more differences than similarities between sparc64 and amd64. Both are 64bit but sparc64 is bigendian and has different memory alignments. I think it would be necessary to set up a system with FreeBSD 6.3 on sparc64 to resolve this. My FreeBSD install is from the distribution CD. I will try to recompile mysql, with the new version as soon as possible, but I need to do changes to the ports build script. Best Regards. Mattias
[6 Aug 2008 12:00]
Susanne Ebrecht
Hmmm, that looks the same as here. Can you try to shrink the SELECT Statement down so that we could get more exact informations which part isn't working correct.
I mean something like:
SELECT /* Parser::replaceLinkHolders WikiSysop */ * FROM `page` WHERE
(page_namespace=0 AND page_title IN('OSPF'));
Or just
SELECT /* Parser::replaceLinkHolders WikiSysop */ * FROM `page` WHERE
page_title IN(..., 'OSPF', ...));
[6 Aug 2008 13:01]
Mattias Eriksson
I hope I understand you correct. This is the output when running my debug script. Note that I am running this against my production database, and I have added more pages to wikipedia (OSPFv6). The script will be attached in a minute. [materi@njord ~]$ ./debug_mysql.sh page_id page_namespace page_title page_restrictions page_counter page_is_redirect page_is_new page_random page_touched page_latest page_len 18 0 OSPFv6 3 0 0 0.334469699443 20080722085332 135 1787 page_id page_namespace page_title page_restrictions page_counter page_is_redirect page_is_new page_random page_touched page_latest page_len 6 0 Huvudsidan 2 1 0 0.14495238096 20080723185017 43 23 page_id page_namespace page_title page_restrictions page_counter page_is_redirect page_is_new page_random page_touched page_latest page_len 7 0 Cisco 43 0 0 0.626507716055 20080727104908 178 1176 page_id page_namespace page_title page_restrictions page_counter page_is_redirect page_is_new page_random page_touched page_latest page_len 16 0 OSPF 11 0 0 0.235461878317 20080722090127 172 183 page_id page_namespace page_title page_restrictions page_counter page_is_redirect page_is_new page_random page_touched page_latest page_len 5 0 IS-IS 58 0 0 0.862870064524 20080722090102 171 5640
[6 Aug 2008 13:04]
Mattias Eriksson
Would it be easier if you had access to the database? If that would be better, then how do we communicate passwords,ip addresses and such, securely. Best Regards
[6 Aug 2008 13:26]
Mattias Eriksson
More results
Attachment: mysql_more_results.txt.gz (application/x-gzip, text), 764 bytes.
[6 Aug 2008 13:52]
Susanne Ebrecht
The results are just mad and I have seen such a behaviour before ... I have to search when and where it was.
[6 Aug 2008 13:53]
Mattias Eriksson
The previous file clearly shows the variation of the result.
[6 Aug 2008 17:36]
Mattias Eriksson
I've rebuilt mysql (version 5.0.51b) without any freebsd patches (from ports) but the database still behaves like previous versions. The bug persists. I ran my debug script and the output was exactly the same.
[7 Aug 2008 11:37]
Susanne Ebrecht
I am pretty sure that I saw a similar behaviour a few weeks ago ... I meant that a comparing list of strings won't compare right on some OS. But I can't find the bug report again.
[9 Aug 2008 7:28]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.67, and inform about the results.
[9 Aug 2008 11:02]
Mattias Eriksson
I've rebuilt mysql using version 5.0.67 and there is no difference, the bug is still there. Does any one have any pointers to where the comparison occurs in the source? Or any other ways to do more debugging? Regards
[16 Oct 2008 19:59]
Sveta Smirnova
Thank you for the feedback. Please try to compile without options --without-readline --without-libedit to see if it affects. Also please provide output of SHOW VARIABLES LIKE 'char%', SHOW VARIABLES LIKE 'coll%' and SHOW CREATE TABLE page on both FreeBSD and Linux. I want to check client character set/collation settings you use and table create options.
[17 Oct 2008 18:33]
Mattias Eriksson
Output from above queries on FreeBSD (sparc64)
Attachment: codepages_FreeBSD.txt (text/plain), 5.29 KiB.
[17 Oct 2008 18:34]
Mattias Eriksson
Output from above queries on Linux (i386)
Attachment: codepages_Linux.txt (text/plain), 5.31 KiB.
[17 Oct 2008 18:36]
Mattias Eriksson
I ran my debug script and the queries on FreeBSD, still returns incorrect results. Best Regards Mattias
[17 Oct 2008 18:40]
Mattias Eriksson
...forgot to explicitly say that I did recompile mysql-5.0.67 as you suggested. Cheers.
[4 Feb 2009 10:49]
Susanne Ebrecht
Unfortunately, we don't have FreeBSD on a bigendian ... but if it is an endian problem it should be reproducable with other OS on bigendian. As soon as I have installed actual 5.0 on such a machine I will give it a try and test it again.
[4 Feb 2009 12:13]
Susanne Ebrecht
Not repeatable with PPC.
[23 Mar 2009 15:55]
Susanne Ebrecht
Mattias, we still aren't able to repeat your behaviour. The only thing what we can do at the moment is to plead you to try all with MySQL 5.1.
[23 Apr 2009 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[17 Aug 2009 8:42]
Susanne Ebrecht
We are still waiting for the report what happens with 5.1 here.
[17 Aug 2009 21:34]
Mattias Eriksson
I have replaced my server with x86/amd64 hardware and Linux, since I know that it works. I needed to have MySQL working, it's a production server.
[20 Aug 2009 6:14]
Susanne Ebrecht
Many thanks for your feedback. Of course changing sparc against x86 is not a workaround at all. Anyway, we still don't have a sparc + FreeBSD test machine. I will set this to can't repeat now.
Description: When I execute this query on FreeBSD sparc64 i get an incorrect result. mysql> SELECT /* Parser::replaceLinkHolders WikiSysop */ * FROM `page` WHERE (page_namespace=0 AND page_title IN('TCP', 'UDP', 'PIM', 'RIPng', 'OSPFv6', 'RIP', 'TP4', 'SCTP', 'HP', 'Huvudsidan', 'Extreme_Networks', 'Cisco', 'Allied_Telesyn', 'OSPF', 'IS-IS', 'SDH', 'Token_Ring', 'FDDI', 'Ethernet', 'Optisk_fiber', 'Spanning-tree', 'CLNS', 'BGP', 'EIGRP', 'IPX', 'IPv6', 'IPv4', 'Twisted_pair')); +---------+----------------+------------+-------------------+--------------+------------------+-------------+----------------+----------------+-------------+----------+ | page_id | page_namespace | page_title | page_restrictions | page_counter | page_is_redirect | page_is_new | page_random | page_touched | page_latest | page_len | +---------+----------------+------------+-------------------+--------------+------------------+-------------+----------------+----------------+-------------+----------+ | 7 | 0 | Cisco | | 4 | 0 | 0 | 0.626507716055 | 20080717113152 | 54 | 222 | | 6 | 0 | Huvudsidan | | 2 | 1 | 0 | 0.14495238096 | 20080717152458 | 43 | 23 | | 5 | 0 | IS-IS | | 36 | 0 | 0 | 0.862870064524 | 20080717135928 | 104 | 4182 | +---------+----------------+------------+-------------------+--------------+------------------+-------------+----------------+----------------+-------------+----------+ 3 rows in set (0.01 sec) If I execute the same query on Linux Slackware i386 it generates this result, which is the correct one. mysql> SELECT /* Parser::replaceLinkHolders WikiSysop */ * FROM `page` WHERE (page_namespace=0 AND page_title IN('TCP', 'UDP', 'PIM', 'RIPng', 'OSPFv6', 'RIP', 'TP4', 'SCTP', 'HP', 'Huvudsidan', 'Extreme_Networks', 'Cisco', 'Allied_Telesyn', 'OSPF', 'IS-IS', 'SDH', 'Token_Ring', 'FDDI', 'Ethernet', 'Optisk_fiber', 'Spanning-tree', 'CLNS', 'BGP', 'EIGRP', 'IPX', 'IPv6', 'IPv4', 'Twisted_pair')); +---------+----------------+------------+-------------------+--------------+------------------+-------------+----------------+----------------+-------------+----------+ | page_id | page_namespace | page_title | page_restrictions | page_counter | page_is_redirect | page_is_new | page_random | page_touched | page_latest | page_len | +---------+----------------+------------+-------------------+--------------+------------------+-------------+----------------+----------------+-------------+----------+ | 7 | 0 | Cisco | | 4 | 0 | 0 | 0.626507716055 | 20080717113152 | 54 | 222 | | 6 | 0 | Huvudsidan | | 2 | 1 | 0 | 0.14495238096 | 20080717152458 | 43 | 23 | | 5 | 0 | IS-IS | | 36 | 0 | 0 | 0.862870064524 | 20080717135928 | 104 | 4182 | | 16 | 0 | OSPF | | 4 | 0 | 0 | 0.235461878317 | 20080717140003 | 106 | 142 | +---------+----------------+------------+-------------------+--------------+------------------+-------------+----------------+----------------+-------------+----------+ 4 rows in set (0,00 sec) There is clearly a bug in FreeBSD sparc64 version of mysql. I have tested this with version 5.0.45 on FreeBSD sparc64 but I get the same incorrect result. How to repeat: Create this table and execute the above query: -- -- Host: localhost Database: wikidb -- ------------------------------------------------------ -- Server version 5.0.51a /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `page` -- SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `page` ( `page_id` int(10) unsigned NOT NULL, `page_namespace` int(11) NOT NULL, `page_title` varchar(255) character set latin1 collate latin1_bin NOT NULL, `page_restrictions` tinyblob NOT NULL, `page_counter` bigint(20) unsigned NOT NULL default '0', `page_is_redirect` tinyint(3) unsigned NOT NULL default '0', `page_is_new` tinyint(3) unsigned NOT NULL default '0', `page_random` double unsigned NOT NULL, `page_touched` binary(14) NOT NULL default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `page_latest` int(10) unsigned NOT NULL, `page_len` int(10) unsigned NOT NULL, PRIMARY KEY (`page_id`), UNIQUE KEY `name_title` (`page_namespace`,`page_title`), KEY `page_random` (`page_random`), KEY `page_len` (`page_len`) ); SET character_set_client = @saved_cs_client; -- -- Dumping data for table `page` -- INSERT INTO `page` VALUES (1,0,'Huvudsida','',98,0,0,0.1461295294,'20080717152457',107,1065); INSERT INTO `page` VALUES (2,0,'Networking','',10,1,0,0.679272818636,'20080717134231',16,22); INSERT INTO `page` VALUES (3,0,'MediaWiki','',7,0,0,0.704115813588,'20080716221509',8,401); INSERT INTO `page` VALUES (4,0,'Nätverk','',45,0,0,0.028620918818,'20080717135928',93,740); INSERT INTO `page` VALUES (5,0,'IS-IS','',36,0,0,0.862870064524,'20080717135928',104,4182); INSERT INTO `page` VALUES (6,0,'Huvudsidan','',2,1,0,0.14495238096,'20080717152458',43,23); INSERT INTO `page` VALUES (7,0,'Cisco','',4,0,0,0.626507716055,'20080717113152',54,222); INSERT INTO `page` VALUES (8,0,'Applikationer','',5,0,0,0.007082257644,'20080717134354',95,82); INSERT INTO `page` VALUES (9,0,'Editorer','',3,0,0,0.822226797476,'20080717140842',96,82); INSERT INTO `page` VALUES (10,0,'Kryptering','',5,0,0,0.922970808944,'20080717134433',97,82); INSERT INTO `page` VALUES (11,0,'Operativ_system','',2,0,0,0.830072960524,'20080717134455',98,82); INSERT INTO `page` VALUES (12,0,'Programmering','',4,0,0,0.254573825652,'20080717152540',108,82); INSERT INTO `page` VALUES (13,0,'Biodrivmedel','',3,0,1,0.468905716495,'20080717152606',101,82); INSERT INTO `page` VALUES (14,0,'Etanol','',1,0,1,0.215060230067,'20080717135211',102,82); INSERT INTO `page` VALUES (15,0,'Metanol','',2,0,1,0.127097040572,'20080717140855',103,82); INSERT INTO `page` VALUES (16,0,'OSPF','',4,0,0,0.235461878317,'20080717140003',106,142); /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2008-07-17 20:53:13