While using Mantis, I noticed that ordering by columns was not working properly. I assumed this to be a bug with Mantis, and downgraded only to find the same issue. I convinced Mantis to tell me the queries it was running, and put them in the mysql client by hand. They seemed sane and had explicit the expected ORDER BY clause. However, the results were never what the ORDER BY clause specified -- they were always sorted by id -- the first column in the table (and the query). Removing or rearranging what appeared in the ORDER BY clause had no effect. Downgrading back down to dev-db/mysql-4.1.20 everything works again just fine. $ sudo emerge --info Portage 2.1-r1 (default-linux/x86/2005.1, gcc-3.4.6, glibc-2.3.6-r4, 2.6.13-gentoo-r3 i686) ================================================================= System uname: 2.6.13-gentoo-r3 i686 Intel(R) Pentium(R) 4 CPU 2.53GHz Gentoo Base System version 1.6.15 app-admin/eselect-compiler: [Not Present] dev-lang/python: 2.3.5, 2.4.3-r1 dev-python/pycrypto: 2.0.1-r5 dev-util/ccache: [Not Present] dev-util/confcache: [Not Present] sys-apps/sandbox: 1.2.17 sys-devel/autoconf: 2.13, 2.59-r7 sys-devel/automake: 1.4_p6, 1.5, 1.6.3, 1.7.9-r1, 1.8.5-r3, 1.9.6-r2 sys-devel/binutils: 2.16.1-r3 sys-devel/gcc-config: 1.3.13-r3 sys-devel/libtool: 1.5.22 virtual/os-headers: 2.6.11-r2 ACCEPT_KEYWORDS="x86" AUTOCLEAN="yes" CBUILD="i686-pc-linux-gnu" CFLAGS="-O2 -march=pentium4 -pipe -fomit-frame-pointer" CHOST="i686-pc-linux-gnu" CONFIG_PROTECT="/etc" CONFIG_PROTECT_MASK="/etc/env.d /etc/gconf" CXXFLAGS="-O2 -march=pentium4 -pipe -fomit-frame-pointer" DISTDIR="/usr/portage/distfiles" FEATURES="autoconfig distlocks fixpackages metadata-transfer sandbox sfperms strict" GENTOO_MIRRORS="ftp://gentoo.chem.wisc.edu/gentoo/ ftp://mirror.datapipe.net/gentoo http://mirror.datapipe.net/gentoo http://prometheus.cs.wmich.edu/gentoo http://modzer0.cs.uaf.edu/public/gentoo/ ftp://mirror.averse.net/pub/gentoo http://mirror.averse.net/pub/gentoo/" PKGDIR="/usr/portage/packages" PORTAGE_RSYNC_OPTS="--recursive --links --safe-links --perms --times --compress --force --whole-file --delete --delete-after --stats --timeout=180 --exclude='/distfiles' --exclude='/local' --exclude='/packages'" PORTAGE_TMPDIR="/var/tmp" PORTDIR="/usr/portage" SYNC="rsync://rsync.gentoo.org/gentoo-portage" USE="x86 alsa apache2 arts avi bash-completion berkdb bitmap-fonts bzip2 cgi cli crypt dlloader dri eds emboss encode foomaticdb gdbm gif gpm gstreamer gtk2 imlib ipv6 isdnlog jpeg libg++ libwww mad md5sum mikmod mime mmx motif mp3 mpeg ncurses nfs nls ogg oggvorbis opengl oss pam pcre pdflib perl png pppd python qt3 qt4 quicktime readline reflection sasl sdl session spell spl sse sse2 ssl tcpd truetype truetype-fonts type1-fonts vhosts vorbis xml2 xmms xorg xv zlib elibc_glibc input_devices_keyboard input_devices_mouse input_devices_evdev kernel_linux userland_GNU" Unset: CTARGET, EMERGE_DEFAULT_OPTS, INSTALL_MASK, LANG, LC_ALL, LDFLAGS, LINGUAS, MAKEOPTS, PORTAGE_RSYNC_EXTRA_OPTS, PORTDIR_OVERLAY
USE="berkdb perl ssl -big-tables -cluster -debug -embedded -extraengine -latin1 -minimal -raid -srvdir -static"
NOTE: A MySQL bug relating to the speed of ORDER BY operations <http://bugs.mysql.com/bug.php?id=4981> was fixed in 4.1.21.
is practically impossible to solve this bug without more informations, at least: the query used, the EXPLAIN SELECT ... stuff. Just a hint, if you need the query a MySQL server is running restart it with the log = /tmp/mysqld.sql option in the my.cnf file (the option is different but still existant in later versions of mysql)
Sorry, I figured I'd give description and see what information might be wanted. The machine running mysql is in a production enviroment, so I can't be putting it back in a broken state very often. The query that was displaying the problem was something very similar to this: ----------------------------------------------------------------------------- SELECT DISTINCT mantis_bug_table.* FROM mantis_bug_table WHERE mantis_bug_table.id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 192, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 194, 193, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204) ORDER BY sticky DESC, last_updated ASC, date_submitted DESC ----------------------------------------------------------------------------- What else do you want to know?
It's assigned to us, no need to CC us too... Best regards, CHTEKK.
Created attachment 98727 [details] Tee log illustrating the issue. Here's a tee log illustrating the bug, you'll notice no matter how I play with the ORDER BY clause, I always get the results ordered by the id. I also wanted to make sure it wasnt't the IN set part of the command so at one point I made 2 come before 1 in the set. What further information can I offer?
Could you please run this set of queries, and attach the output? SHOW CREATE TABLE mantis_bug_table; SELECT id,last_updated,date_submitted FROM mantis_bug_table WHERE id < 30 ORDER BY last_updated ASC, date_submitted DESC; SELECT id,last_updated,date_submitted FROM mantis_bug_table WHERE id < 30 ORDER BY date_submitted DESC, last_updated ASC;
Created attachment 98766 [details] Queries Requested by Comment #7 Here's a log with the queries requested in comment #7 by Robin Johnson. The queries do *not* show the same symptoms, so I ran them again with DISTINCT and the symptom reappear. It looks like it's not just ORDER BY, but ORDER BY combined with DISTINCT.
Ok, I found the upstream bug now. http://bugs.mysql.com/bug.php?id=21456 Here's upstreams patch for 4.1.22 http://lists.mysql.com/commits/10763 Looking at the full changelog of 4.1.22 to date, I don't think it's wise to just apply this single patch. Use the upstream recommended workaround for the moment: SELECT (SELECT DISTINCT ...) ORDER BY ...
I'd use the work around, but like I said I found this using another application (which I don't quite feel like re-writing ;) ). Is there any chance of bringing back the dev-db/mysql-4.1.20 ebuild so I could just jump back down to that? That would keep me from having to do the more painful 4.1 to 4.0 downgrade. Continuing the thought train: should 4.1.21 be masked for this? It's an S2 (Serious) bug in the MySQL bug tracker.
*** Bug 152429 has been marked as a duplicate of this bug. ***
