Please help replicate MySQL bug #37439
John Nagle
This relates to incorrect ORDER BY ordering with multiple keys
and ENUMs.
http://bugs.mysql.com/bug.php?id=37439
Over the years, bugs in this area have been reported at least
three times, and shrugged off by MySQL support as "not repeatable".
(Refs: Bugs #7516 and #967). In all three bug reports, there was an ORDER BY
clause with two fields, the first being an ENUM, and the results
were not ordered correctly. This problem in some form seems to go back
to at least 2003.
I have a test case (which can be seen in the bug report) which
has failed on three different systems, but succeeds on some others.
The pattern of failure is not obvious. For example, it failed
on Linux and Win2K but succeeded on FreeBSD. It failed
on MySQL 5.0.27 and 5.0.45 but did not fail on "the latest
version of MySQL". It succeeded on data sets of 10 and 50 records
but failed with 100 and >1000 records. On configurations where
it fails, it fails repeatably. The latest system on which a
failure was reported is Fedora Core 7 with MySQL 5.0.45,
running on a server in Switzerland.
So if anyone else can repeat this bug, especially on MySQL 5.0.51 or
later, please let us know. Update the bug report if possible.
Thanks.
John Nagle
SiteTruth
Paul Lautman
Do us a favour and give it up! You have been told that an enum field is not
the way you should be doing what you want to do. Do it the right way and you
won't be having a problem in the first place.
John Nagle
RTFM. "http://dev.mysql.com/doc/refman/5.0/en/enum.html"
The reference manual says: "ENUM values are sorted according to the order
in which the enumeration members were listed in the column specification."
So ordering by ENUM is well-specified and supported. It's just broken.
It's broken, specifically, for the cases where ORDER BY can exploit an index
and use LIMIT optimization. If a query requires a full table sort, the bug
does not appear.
Incidentally, Postgres also supports ENUM, and orders them in declaration
order. The ANSI SQL standard does not, however, include an ENUM mechanism.
An argument is sometimes made that one should use a separate table
of names and values instead of an ENUM. But that's a religious issue,
not a correctness one.
So, yes, it's a bug.
John Nagle
Axel Schwenke
>
> Over the years, bugs in this area have been reported at least
> three times, and shrugged off by MySQL support as "not repeatable".
Sorry to disappoint you, but the testcase works perfectly OK on my
test installation of 5.0.64/Linux/x86_64 (Debian Etch).
Rows are ordered by `rating_state` in the order 'running', 'queued'
and 'hold' what is exactly as specified. EXPLAIN shows the index is
being used to retrieve the result in sort order.
Can you come up with a better test case?
- exact operating system + version
- exact MySQL version installed and my.cnf if any
- table definition and data as created by mysqldump
- SQL statement exploiting wrong sort order
All bug reports are conscientiously tested, but in order to steal
time from our developers, we need a reproducible test case. There
is none so far.
XL
--
Axel Schwenke, MySQL Senior Support Engineer, Sun Microsystems
MySQL User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/
John Nagle
> John Nagle <na...@animats.com> wrote:
>> I'd like to ask the community to help replicate bug #37439.
>>
>> http://bugs.mysql.com/bug.php?id=37439
>>
>> Over the years, bugs in this area have been reported at least
>> three times, and shrugged off by MySQL support as "not repeatable".
>
> Sorry to disappoint you, but the testcase works perfectly OK on my
> test installation of 5.0.64/Linux/x86_64 (Debian Etch).
OK, we reported a subtle bug that appears on three different
32-bit configurations, and the attempt to reproduce it was on a
64-bit system with a completely different version of MySQL
on a completely different Linux distro.
>
> Rows are ordered by `rating_state` in the order 'running', 'queued'
> and 'hold' what is exactly as specified. EXPLAIN shows the index is
> being used to retrieve the result in sort order.
>
> Can you come up with a better test case?
>
> - exact operating system + version
From the original bug report:
"I've been able to reproduce the problem on MySQL "5.0.27" running on
Red Hat Linux Fedora Core 5. This is a standard server configuration
running in a server farm. The original bug was observed on
"5.0.27-community-nt" on Windows 2000 running
as a desktop system. So it's a cross-platform problem."
> - exact MySQL version installed and my.cnf if any
On Windows 2000: MySQL 5.0.27-community-nt, no my.cnf file.
On Red Hat Linux Fedora Core 5, MySQL 5.0.27:
my.cnf:
[mysqld]
# Enable local database loads. Increases risk of SQL injection attacks.
set-variable=local-infile=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
> - table definition and data as created by mysqldump
"D:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -uXXXX -pXXXX sitetruth
testratingqueue100 > \temp\sqldump1.txt"
For output, see below at end.
> - SQL statement exploiting wrong sort order
From the original bug report:
"SELECT * FROM ratingqueue ORDER BY rating_state;"
> All bug reports are conscientiously tested, but in order to steal
> time from our developers, we need a reproducible test case. There
> is none so far.
To date, there seems to have been no attempt there reproduce the problem
on a configuration similar to the one reported. It's been reported on
a mainstream configuration, the one delivered with Red Hat Fedora Core.
The attempt to reproduce it was on a version that's not even out yet.
This is a touchy bug to reproduce. Change the number of records in the
database and it may stop failing. Yet there are two similar bug reports
(see bug report for numbers) dating back to 2002, both tagged with
"unable to reproduce". That's why I'm going to so much trouble to try
to reproduce this one.
Again, I'd like to hear from other users, especially ones using
stock Red Hat 32-bit x86 systems with the stock MySQL from the distro.
John Nagle
=============================
-- MySQL dump 10.10
--
-- Host: localhost Database: sitetruth
-- ------------------------------------------------------
-- Server version 5.0.27-community-nt
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!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 `testratingqueue100`
--
DROP TABLE IF EXISTS `testratingqueue100`;
CREATE TABLE `testratingqueue100` (
`domain` varchar(255) NOT NULL,
`requestor_ip_hash` int(11) default NULL,
`rating_state` enum('running','starting','queued','hold') NOT NULL default
'queued',
`server` varchar(63) default NULL,
`priority` smallint(6) default '0',
`update_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`request_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`domain`),
UNIQUE KEY `domain` (`domain`),
KEY `requestor_ip_hash` (`requestor_ip_hash`),
KEY `rating_state` USING BTREE (`rating_state`,`request_timestamp`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;
--
-- Dumping data for table `testratingqueue100`
--
LOCK TABLES `testratingqueue100` WRITE;
/*!40000 ALTER TABLE `testratingqueue100` DISABLE KEYS */;
INSERT INTO `testratingqueue100` VALUES
('msn.com',651586,'hold',NULL,0,'2008-06-16 19:33:18','2008-06-16
19:33:18'),('google.com',651586,'hold',NULL,0,'2008-06-16 19:33:18','2008-06-16
19:33:18'),('youtube.com',651586,'hold',NULL,0,'2008-06-16 19:33:18','2008-06-16
19:33:18'),('baidu.com',651586,'hold',NULL,0,'2008-06-16 19:33:18','2008-06-16
19:33:18'),('orkut.com',651586,'hold',NULL,0,'2008-06-16 19:33:18','2008-06-16
19:33:18'),('qq.com',651586,'hold',NULL,0,'2008-06-16 19:33:18','2008-06-16
19:33:18'),('wikipedia.org',651586,'queued',NULL,0,'2008-06-16
19:33:27','2008-06-16
19:33:27'),('yahoo.co.jp',651586,'queued',NULL,0,'2008-06-16
19:33:25','2008-06-16
19:33:25'),('microsoft.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('sina.com.cn',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('megaupload.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('blogger.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('ebay.com',651586,'queued',NULL,0,'2008-06-16
19:33:25','2008-06-16
19:33:25'),('amazon.co.uk',651586,'hold',NULL,0,'2008-06-16
19:33:19','2008-06-16
19:33:19'),('rapidshare.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('facebook.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('sohu.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('fotolog.net',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('friendster.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('mail.ru',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('google.de',651586,'queued',NULL,0,'2008-06-16
19:33:26','2008-06-16
19:33:26'),('passport.net',651586,'queued',NULL,0,'2008-06-16
19:33:24','2008-06-16
19:33:24'),('google.com.br',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('google.fr',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('amazon.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('yahoo.com.cn',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('bbc.co.uk',651586,'queued',NULL,0,'2008-06-16
19:33:25','2008-06-16
19:33:25'),('www.samoandomains.ws',651586,'hold',NULL,0,'2008-06-16
19:33:22','2008-06-16
19:33:22'),('google.co.jp',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('google.es',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('wretch.cc',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('uol.com.br',651586,'queued',NULL,0,'2008-06-16
19:33:24','2008-06-16 19:33:24'),('yandex.ru',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('skyblog.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('google.pl',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('craigslist.org',651586,'queued',NULL,0,'2008-06-16
19:33:26','2008-06-16 19:33:26'),('google.cl',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('flickr.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('tom.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('google.cn',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('photobucket.com',651586,'queued',NULL,0,'2008-06-16
19:33:25','2008-06-16 19:33:25'),('onet.pl',651586,'queued',NULL,0,'2008-06-16
19:33:22','2008-06-16
19:33:22'),('imageshack.us',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('google.com.mx',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('fc2.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('aol.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('ebay.co.uk',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('mixi.jp',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('xunlei.com',651586,'queued',NULL,0,'2008-06-16
19:33:26','2008-06-16 19:33:26'),('rambler.ru',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('cnn.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('google.it',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('allegro.pl',651586,'queued',NULL,0,'2008-06-16
19:33:26','2008-06-16
19:33:26'),('google.co.in',651586,'queued',NULL,0,'2008-06-16
19:33:24','2008-06-16 19:33:24'),('ebay.de',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('google.com.ar',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('google.com.pe',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('terra.com.br',651586,'running','animats-5px372h',0,'2008-06-16
19:33:24','2008-06-16
19:33:18'),('livejournal.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('seznam.cz',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('xanga.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('geocities.com',651586,'running','animats-5px372h',0,'2008-06-16
19:33:27','2008-06-16
19:33:18'),('imagevenue.com',651586,'queued',NULL,0,'2008-06-16
19:33:19','2008-06-16
19:33:19'),('adultfriendfinder.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('dailymotion.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('uwants.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('rakuten.co.jp',651586,'queued',NULL,0,'2008-06-16
19:33:25','2008-06-16
19:33:25'),('googlesyndication.com',651586,'queued',NULL,0,'2008-06-16
19:33:23','2008-06-16 19:33:23'),('wp.pl',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('discuss.com.hk',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('adobe.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('bebo.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('livedoor.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('google.co.ve',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('digg.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('www.safenames.net',651586,'hold',NULL,0,'2008-06-16
19:33:22','2008-06-16 19:33:22'),('apple.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('soso.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('vnet.cn',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('tagged.com',651586,'queued',NULL,0,'2008-06-16
19:33:25','2008-06-16
19:33:25'),('wordpress.com',651586,'queued',NULL,0,'2008-06-16
19:33:24','2008-06-16 19:33:24'),('one.lt',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('badongo.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('deviantart.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('sendspace.com',651586,'queued',NULL,0,'2008-06-16
19:33:24','2008-06-16
19:33:24'),('google.co.il',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('walla.co.il',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('alibaba.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('google.com.au',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('vnexpress.net',651586,'queued',NULL,0,'2008-06-16
19:33:22','2008-06-16 19:33:22'),('about.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('pchome.com.tw',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('ig.com.br',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('comcast.net',651586,'queued',NULL,0,'2008-06-16
19:33:25','2008-06-16
19:33:25'),('sourceforge.net',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16
19:33:18'),('rapidshare.de',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('kooora.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('google.nl',651586,'queued',NULL,0,'2008-06-16
19:33:24','2008-06-16
19:33:24'),('statcounter.com',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18'),('goo.ne.jp',651586,'hold',NULL,0,'2008-06-16
19:33:18','2008-06-16 19:33:18');
/*!40000 ALTER TABLE `testratingqueue100` ENABLE KEYS */;
UNLOCK TABLES;
/*!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 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2008-07-19 4:56:20
Jonathan
> On Windows 2000: MySQL 5.0.27-community-nt, no my.cnf file.
On windows there should be a my.ini somewhere in MySQL's binary folder
or in the Windows tree (You should be able to locate it easily by
viewing the details of the service invocation from the Services panel).
Kind regards,
Jonathan
John Nagle
OK, found it. It's totally vanilla. See below.
John Nagle
====
# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
# Generated by the MySQL Server Instance Configuration Wizard
# (Removed big blocks of comments)
#
[client]
port=3306
[mysql]
default-character-set=utf8
#
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
#Path to installation directory. All paths are usually resolved relative to this.
basedir="D:/Program Files/MySQL/MySQL Server 5.0/"
#Path to the database root
datadir="D:/Program Files/MySQL/MySQL Server 5.0/Data/"
# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=5M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=212K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=10M
innodb_thread_concurrency=8
Axel Schwenke
> Axel Schwenke wrote:
>> John Nagle <na...@animats.com> wrote:
>>> I'd like to ask the community to help replicate bug #37439.
>>>
>>> http://bugs.mysql.com/bug.php?id=37439
>>>
>>> Over the years, bugs in this area have been reported at least
>>> three times, and shrugged off by MySQL support as "not repeatable".
>>
>> Sorry to disappoint you, but the testcase works perfectly OK on my
>> test installation of 5.0.64/Linux/x86_64 (Debian Etch).
>
> OK, we reported a subtle bug that appears on three different
> 32-bit configurations, and the attempt to reproduce it was on a
> 64-bit system with a completely different version of MySQL
> on a completely different Linux distro.
If that is indeed a bug, it is not supposed to depend on the
architecture. And sorry that I don't keep a zoo of Linux distros
on my disk. I might try later to find a FC7 machine.
About versions: you reported that problem for 5.0.45 and 5.0.27.
Both versions are old (5.0.27 was released 20 months ago). There
have literally hundreds of bugs being fixed since then. If the
problem exists in the old version, but not in the current one,
it's probably fixed (by chance, as side effect of another change)
and not considered an active bug.
That's exactly the reason why you have been asked to check if the
problem persists after upgrading. *You* have the environment.
*You* have the test case. So *you* are the one who has the least
effort to check this.
I might have mentioned this before: our developers each have a
huge pile of work on their desks. Unless we have a reproducible
test case that makes the current release fail, this bug report
will not be accepted by the Engineering department.
Jerry Stuckle
> John Nagle <na...@animats.com> wrote:
>> Axel Schwenke wrote:
>>> John Nagle <na...@animats.com> wrote:
>>>> I'd like to ask the community to help replicate bug #37439.
>>>>
>>>> http://bugs.mysql.com/bug.php?id=37439
>>>>
>>>> Over the years, bugs in this area have been reported at least
>>>> three times, and shrugged off by MySQL support as "not repeatable".
>>> Sorry to disappoint you, but the testcase works perfectly OK on my
>>> test installation of 5.0.64/Linux/x86_64 (Debian Etch).
>> OK, we reported a subtle bug that appears on three different
>> 32-bit configurations, and the attempt to reproduce it was on a
>> 64-bit system with a completely different version of MySQL
>> on a completely different Linux distro.
>
> If that is indeed a bug, it is not supposed to depend on the
> architecture. And sorry that I don't keep a zoo of Linux distros
> on my disk. I might try later to find a FC7 machine.
>
Axel,
Sorry, many times bugs do depend on the architecture. Just saying "I
can't duplicate it on my system" is not an answer.
And if you're supporting different distros, yes, you should have them on
your disk. That's the way support works.
> About versions: you reported that problem for 5.0.45 and 5.0.27.
> Both versions are old (5.0.27 was released 20 months ago). There
> have literally hundreds of bugs being fixed since then. If the
> problem exists in the old version, but not in the current one,
> it's probably fixed (by chance, as side effect of another change)
> and not considered an active bug.
>
But this is obviously not the first time the problem has occurred.
Also, 5.0.27 is (supposedly) still supported. At least I see no
announcement that it is not. So just saying "it is probably fixed" is
not an answer.
> That's exactly the reason why you have been asked to check if the
> problem persists after upgrading. *You* have the environment.
> *You* have the test case. So *you* are the one who has the least
> effort to check this.
>
Which is another way of saying "I don't know what the problem is and I'm
not going to look into it". It's an excuse.
> I might have mentioned this before: our developers each have a
> huge pile of work on their desks. Unless we have a reproducible
> test case that makes the current release fail, this bug report
> will not be accepted by the Engineering department.
>
>
> XL
> --
> Axel Schwenke, MySQL Senior Support Engineer, Sun Microsystems
>
> MySQL User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/
>
I've worked on some very large products in the past, and our developers
always had a "huge pile of work on their desks".
But the difference was we looked at problems on ANY current release.
And we didn't just tell them to "upgrade in case it was fixed". We
DETERMINED if it was fixed or not, and if it was, we gave them the exact
patch which fixed it (this was before there was a new "release" every
three weeks).
That's the difference between good support and "we're here because we
need to say we support the product, but aren't going to go out of our
way to do any real work".
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Axel Schwenke
> Axel Schwenke wrote:
>>
>> If that is indeed a bug, it is not supposed to depend on the
>> architecture. And sorry that I don't keep a zoo of Linux distros
>> on my disk. I might try later to find a FC7 machine.
>
Right. We don't know for sure that this is an architecture dependent
thing. It's just unlikely. Very unlikely. Especially when we look at
the fact that it was reported for Windows and Linux and FreeBSD.
> Just saying "I can't duplicate it on my system" is not an answer.
What I write here, I do in my spare time. At least I tried to reproduce
it on my system. Did you?
> And if you're supporting different distros, yes, you should have them on
> your disk. That's the way support works.
Once more, this is from my private desk. Also I'm not working primarily
on bug report verification. But I guess my colleague (whoever it was)
tried to reproduce on the given platforms.
>> About versions: you reported that problem for 5.0.45 and 5.0.27.
>> Both versions are old (5.0.27 was released 20 months ago). There
>> have literally hundreds of bugs being fixed since then. If the
>> problem exists in the old version, but not in the current one,
>> it's probably fixed (by chance, as side effect of another change)
>> and not considered an active bug.
>
> But this is obviously not the first time the problem has occurred.
> Also, 5.0.27 is (supposedly) still supported. At least I see no
> announcement that it is not. So just saying "it is probably fixed" is
> not an answer.
Bugs fixes are always delivered in new versions. If the bug is gone in
a newer version - fine. If it is not gone, then we have a test case.
>> That's exactly the reason why you have been asked to check if the
>> problem persists after upgrading. *You* have the environment.
>> *You* have the test case. So *you* are the one who has the least
>> effort to check this.
>
> Which is another way of saying "I don't know what the problem is and I'm
> not going to look into it". It's an excuse.
It's a question for help. For the reasons above, he is the most
suitable person for that. Especially since we weren't able to
reproduce. Maybe there is something special in his environment?
> But the difference was we looked at problems on ANY current release.
> And we didn't just tell them to "upgrade in case it was fixed". We
> DETERMINED if it was fixed or not, and if it was, we gave them the exact
> patch which fixed it (this was before there was a new "release" every
> three weeks).
Bla, bla, bla. We cannot even reproduce the abnormal behavior that
was reported. We also don't know if it is fixed or not. Aksing to try
our latest release is the best we can offer in that situation.
> That's the difference between good support and "we're here because we
> need to say we support the product, but aren't going to go out of our
> way to do any real work".
Bla, bla, bla. John Nagle does not even have a support contract
AFAIK. So everything he gets, he gets for free. Really no reason
for complaints.
Was this support you described as "much better" above delivered
for free? I don't think so!
John Nagle
> Axel Schwenke wrote:
>> John Nagle <na...@animats.com> wrote:
>>> Axel Schwenke wrote:
>>>> John Nagle <na...@animats.com> wrote:
>>>>> I'd like to ask the community to help replicate bug #37439.
>>>>>
>>>>> http://bugs.mysql.com/bug.php?id=37439
>>>>>
>>>>> Over the years, bugs in this area have been reported at least
>>>>> three times, and shrugged off by MySQL support as "not repeatable".
>>>> Sorry to disappoint you, but the testcase works perfectly OK on my
>>>> test installation of 5.0.64/Linux/x86_64 (Debian Etch).
>>> OK, we reported a subtle bug that appears on three different
>>> 32-bit configurations, and the attempt to reproduce it was on a
>>> 64-bit system with a completely different version of MySQL
>>> on a completely different Linux distro.
>>
>> If that is indeed a bug, it is not supposed to depend on the
>> architecture. And sorry that I don't keep a zoo of Linux distros
>> on my disk. I might try later to find a FC7 machine.
>>
>
> Axel,
>
> Sorry, many times bugs do depend on the architecture. Just saying "I
> can't duplicate it on my system" is not an answer.
>
> And if you're supporting different distros, yes, you should have them on
> your disk. That's the way support works.
I wasn't asking MySQL support for help in this message.
I was asking the community to try to reproduce this bug.
This bug has been duplicated on three quite different machines
(one in California, one in Arizona, and one in Switzerland).
There are two other bug reports (both closed as "not reproduceable")
referencing incorrect ordering involving ENUM values and multiple key
ORDER BY clauses. So there's probably something broken in that area.
We refer to this as a "vendor in denial" situation.
It's a very touchy bug. Making changes like changing the number of
records in the table can make the bug disappear, so it may be related
to some decision the query optimizer makes based on number of records.
That sort of decision can be related to various configuration options.
With bugs like this, you don't want to make it go away; you have to
find a reproduceable failure scenario and work from there.
It's worth noting that a production MySQL system could have this bug
and not notice that the system was generating wrong answers.
John Nagle
law...@gmail.com
> This bug has been duplicated on three quite different machines
> (one in California, one in Arizona, and one in Switzerland).
> There are two other bug reports (both closed as "not reproduceable")
> referencing incorrect ordering involving ENUM values and multiple key
> ORDER BY clauses. So there's probably something broken in that area.
>
> We refer to this as a "vendor in denial" situation.
I see both sides of the issue. I've bumped up against support people
who don't want to do their job, for whatever reason.
On the tech support side of the desk, we often get reports of 'ghost
bugs' that only show up at night, 'werewolf bugs' that only come out
during a full moon... very difficult to track down and troubleshoot
when they can't be reliably re-produced. Certain users, whom we call
'superstitious users', seem to report more of these paranormal bugs
than others.
If the vendors can't get a reproduceable bug report, how much time
should they spend chasing down a bug that may or may not exist?
> It's a very touchy bug. Making changes like changing the number of
> records in the table can make the bug disappear, so it may be related
> to some decision the query optimizer makes based on number of records.
> That sort of decision can be related to various configuration options.
> With bugs like this, you don't want to make it go away; you have to
> find a reproduceable failure scenario and work from there.
If you have an idea of the range of number of records, you could
create a test suite that determines the exact number or range of rows
that triggers the bug. For instance, if you suspect it appears from
between 100-1000 bugs, I would try to reproduce the bug for every
increment of 100-1000. And then I could say " the bug appears between
112-998 rows on WinXP, 108-1000 on FreeBSD", etc. It lends more
credibility to your case.
I'd offer to test for your bug, but I don't have the latest release,
so my bug report wouldn't mean anything to the mysql developers.
Jerry Stuckle
> Jerry Stuckle <jstu...@attglobal.net> wrote:
>> Axel Schwenke wrote:
>>> If that is indeed a bug, it is not supposed to depend on the
>>> architecture. And sorry that I don't keep a zoo of Linux distros
>>> on my disk. I might try later to find a FC7 machine.
>> Sorry, many times bugs do depend on the architecture.
>
> Right. We don't know for sure that this is an architecture dependent
> thing. It's just unlikely. Very unlikely. Especially when we look at
> the fact that it was reported for Windows and Linux and FreeBSD.
>
>> Just saying "I can't duplicate it on my system" is not an answer.
>
> What I write here, I do in my spare time. At least I tried to reproduce
> it on my system. Did you?
>
Actually, I did. But whether *I* did it or not is immaterial. MySQL is
not my product and I was not hired to support it.
But when I was working for IBM, I did work for a support center for
several years. And that answer was not an option. It is also not an
option for my current customers.
>> And if you're supporting different distros, yes, you should have them on
>> your disk. That's the way support works.
>
> Once more, this is from my private desk. Also I'm not working primarily
> on bug report verification. But I guess my colleague (whoever it was)
> tried to reproduce on the given platforms.
>
So it's from your private desk. You've repeatedly identified yourself
as part of the MySQL support structure. You can't just say "this is my
private desk" when it suits you.
>>> About versions: you reported that problem for 5.0.45 and 5.0.27.
>>> Both versions are old (5.0.27 was released 20 months ago). There
>>> have literally hundreds of bugs being fixed since then. If the
>>> problem exists in the old version, but not in the current one,
>>> it's probably fixed (by chance, as side effect of another change)
>>> and not considered an active bug.
>> But this is obviously not the first time the problem has occurred.
>> Also, 5.0.27 is (supposedly) still supported. At least I see no
>> announcement that it is not. So just saying "it is probably fixed" is
>> not an answer.
>
> Bugs fixes are always delivered in new versions. If the bug is gone in
> a newer version - fine. If it is not gone, then we have a test case.
>
Then you should be able to point out which change fixed the bug.
>>> That's exactly the reason why you have been asked to check if the
>>> problem persists after upgrading. *You* have the environment.
>>> *You* have the test case. So *you* are the one who has the least
>>> effort to check this.
>> Which is another way of saying "I don't know what the problem is and I'm
>> not going to look into it". It's an excuse.
>
> It's a question for help. For the reasons above, he is the most
> suitable person for that. Especially since we weren't able to
> reproduce. Maybe there is something special in his environment?
>
Then ask for more information about his environment. Don't just tell
him to upgrade and test. It's not always an option.
>> But the difference was we looked at problems on ANY current release.
>> And we didn't just tell them to "upgrade in case it was fixed". We
>> DETERMINED if it was fixed or not, and if it was, we gave them the exact
>> patch which fixed it (this was before there was a new "release" every
>> three weeks).
>
> Bla, bla, bla. We cannot even reproduce the abnormal behavior that
> was reported. We also don't know if it is fixed or not. Aksing to try
> our latest release is the best we can offer in that situation.
>
Bla, bla, bla. We can't find the problem immediately, and we're lazy so
we're not going to spend any time on it. You're on your own.
>> That's the difference between good support and "we're here because we
>> need to say we support the product, but aren't going to go out of our
>> way to do any real work".
>
> Bla, bla, bla. John Nagle does not even have a support contract
> AFAIK. So everything he gets, he gets for free. Really no reason
> for complaints.
>
> Was this support you described as "much better" above delivered
> for free? I don't think so!
>
>
> XL
> --
> Axel Schwenke, MySQL Senior Support Engineer, Sun Microsystems
>
> MySQL User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/
Bla, bla, bla.
You're offering the product for free. You claim to support the product.
Yet you refuse to support it unless they spend lots of dollars on a
support contract.
But I see why people have complained about MySQL support in the past. A
good company wouldn't try to make excuses like this.
Jerry Stuckle
I remember a couple of bugs when I worked for IBM. Both were very
timing critical and would only occur once a day or so, under very heavy
system load. But when they did occur, one bug brought down the system,
and another one hung a critical printer until the system was restarted.
Turns out the first one was a hole created when there was a task switch
in one area of code - it was only 4 assembler instructions. The other
one was a little more code - when a specific request came in within an
area of about 30-40 assembler instructions.
How much time did we spend on it? No guess - but I know I personally
spent a lot of hours trying to track it down. And I know others did,
also. Probably 1K+ man hours per bug total.
Now obviously it took much longer to track these down than most bugs -
but a good support group takes the time.
It's the difference between having support and just saying you have it.