Launch the following commands to create a test case: CREATE TABLE test (id INTEGER, myint INTEGER); INSERT INTO test VALUES (1,10),(1,20),(2,30),(2,40),(3,50),(3,60),(4,10),(4,20),(5,30),(5,40),(6,50),(6,60); CREATE VIEW test_view AS SELECT id, myint / 5 as comp FROM test WHERE id BETWEEN 2 AND 5; The following queries work: SELECT * FROM test_view where comp = 12; SELECT * FROM test_view where comp = 12 ORDER BY comp; The next one crash the server: SELECT * FROM test_view where comp = 12 ORDER BY id; Result on client side: ERROR 2013 (HY000): Lost connection to MySQL server during query In /var/log/mysql/mysqld.err: =========================== mysqld: item.cc:5650: virtual bool Item_direct_view_ref::eq(const Item*, bool) const: Assertion `(*ref)->real_item()->type() == item_ref_ref->real_item()->type()' failed. 071225 15:53:59 - mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=258048 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92780 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8cfc588 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x4a7b67c0, backtrace may not be correct. Bogus stack limit or frame pointer, fp=0x4a7b67c0, stack_bottom=0xb2e60000, thread_stack=196608, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8d37068 = SELECT * FROM test_view where comp = 12 ORDER BY id thd->thread_id=1 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. emerge --info Portage 2.1.3.19 (default-linux/x86/2007.0/desktop, gcc-4.1.2, glibc-2.6.1-r0, 2.6.23-gentoo-r3 i686) ================================================================= System uname: 2.6.23-gentoo-r3 i686 Genuine Intel(R) CPU T2400 @ 1.83GHz Timestamp of tree: Tue, 25 Dec 2007 12:16:01 +0000 ccache version 2.4 [enabled] app-shells/bash: 3.2_p17 dev-java/java-config: 1.3.7, 2.0.33-r1 dev-lang/python: 2.4.4-r6 dev-python/pycrypto: 2.0.1-r6 dev-util/ccache: 2.4-r7 sys-apps/baselayout: 1.12.10-r5 sys-apps/sandbox: 1.2.18.1-r2 sys-devel/autoconf: 2.13, 2.61-r1 sys-devel/automake: 1.5, 1.7.9-r1, 1.8.5-r3, 1.9.6-r2, 1.10 sys-devel/binutils: 2.18-r1 sys-devel/gcc-config: 1.3.16 sys-devel/libtool: 1.5.24 virtual/os-headers: 2.6.23-r2 ACCEPT_KEYWORDS="x86" CBUILD="i686-pc-linux-gnu" CFLAGS="-march=prescott -O2 -pipe -fomit-frame-pointer" CHOST="i686-pc-linux-gnu" CONFIG_PROTECT="/etc /usr/kde/3.5/env /usr/kde/3.5/share/config /usr/kde/3.5/shutdown /usr/share/config" CONFIG_PROTECT_MASK="/etc/env.d /etc/env.d/java/ /etc/gconf /etc/php/apache2-php5/ext-active/ /etc/php/cgi-php5/ext-active/ /etc/php/cli-php5/ext-active/ /etc/revdep-rebuild /etc/splash /etc/terminfo /etc/udev/rules.d" CXXFLAGS="-march=prescott -O2 -pipe -fomit-frame-pointer" DISTDIR="/var/distfiles" FEATURES="ccache distlocks metadata-transfer parallel-fetch sandbox sfperms strict unmerge-orphans userfetch" GENTOO_MIRRORS="http://mirror.ovh.net/gentoo-distfiles/" LC_ALL="fr_BE.UTF-8" LINGUAS="fr nl" MAKEOPTS="-j3" PKGDIR="/usr/portage/packages" PORTAGE_RSYNC_EXTRA_OPTS="--exclude-from=/etc/portage/rsync_excludes" 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 --filter=H_**/files/digest-*" PORTAGE_TMPDIR="/var/tmp" PORTDIR="/usr/portage" PORTDIR_OVERLAY="/usr/portage/local/layman/xeffects" SYNC="rsync://rsync.europe.gentoo.org/gentoo-portage" USE="X acl acpi alsa apache2 arts avi bash-completion berkdb bitmap-fonts cairo cdr cli cracklib crypt cups dbus directfb dri dvd dvdr dvdread emboss encode evo fam firefox flac foomaticdb gdbm gif glibc-omitfp gpm gstreamer hal iconv isdnlog javascript jpeg kde kdeenablefinal kdehiddenvisibility kerberos ldap mad midi mikmod mmx mng mp3 mpeg mplayer mudflap musicbrainz ncurses newspr nls nptl nptlonly nsplugin ogg opengl openmp oss pam pcre pdf php pic png pppd python qt qt3 qt3support qt4 quicktime readline reflection samba sasl sdl session spell spl ssl svg tcpd tiff truetype truetype-fonts type1-fonts unicode utf8 vhosts vorbis win32codecs x86 xinerama xml xorg xv xvid zeroconf zlib" ALSA_CARDS="ali5451 als4000 atiixp atiixp-modem bt87x ca0106 cmipci emu10k1 emu10k1x ens1370 ens1371 es1938 es1968 fm801 hda-intel intel8x0 intel8x0m maestro3 trident usb-audio via82xx via82xx-modem ymfpci" ALSA_PCM_PLUGINS="adpcm alaw asym copy dmix dshare dsnoop empty extplug file hooks iec958 ioplug ladspa lfloat linear meter mulaw multi null plug rate route share shm softvol" APACHE2_MODULES="actions alias auth_basic authn_alias authn_anon authn_dbm authn_default authn_file authz_dbm authz_default authz_groupfile authz_host authz_owner authz_user autoindex cache dav dav_fs dav_lock deflate dir disk_cache env expires ext_filter file_cache filter headers include info log_config logio mem_cache mime mime_magic negotiation rewrite setenvif speling status unique_id userdir usertrack vhost_alias" ELIBC="glibc" INPUT_DEVICES="keyboard mouse" KERNEL="linux" LCD_DEVICES="bayrad cfontz cfontz633 glk hd44780 lb216 lcdm001 mtxorb ncurses text" LINGUAS="fr nl" USERLAND="GNU" VIDEO_CARDS="fglrx" Unset: CPPFLAGS, CTARGET, EMERGE_DEFAULT_OPTS, INSTALL_MASK, LANG, LDFLAGS, PORTAGE_COMPRESS, PORTAGE_COMPRESS_FLAGS
I forgot to mention that this bug is 100% reproducible on my x86 box but that I am unable to reproduce it on my x86_64 one (same mysql version)
This looks a lot like this one, resolved in 5.0.46: http://bugs.mysql.com/bug.php?id=29104 "Noted in 5.0.46, 5.1.21 changelogs. An assertion failure occurred if a query contained a conjunctive predicate of the form view_column = constant in the WHERE clause and the GROUP BY clause contained a reference to a different view column. The fix also enables application of an optimization that was being skipped if a query contained a conjunctive predicate of the form view_column = constant in the WHERE clause and the GROUP BY clause contained a reference to the same view column."
I have been able to reproduce this bug, but it only seems to occur with the debug use flag switched on. I assume DBUG_ASSERT is trapping these, and is only active when compiled with the debug use flag. bool Item_direct_view_ref::eq(const Item *item, bool binary_cmp) const { if (item->type() == REF_ITEM) { Item_ref *item_ref= (Item_ref*) item; if (item_ref->ref_type() == VIEW_REF) { Item *item_ref_ref= *(item_ref->ref); DBUG_ASSERT((*ref)->real_item()->type() == item_ref_ref->real_item()->type()); return ((*ref)->real_item() == item_ref_ref->real_item()); } } return FALSE; }
(In reply to comment #3) > I have been able to reproduce this bug, but it only seems to occur with the > debug use flag switched on. > > I assume DBUG_ASSERT is trapping these, and is only active when compiled with > the debug use flag. debug flag is not activated at my side. mysql has been emerged (and re-emerged) with: dev-db/mysql-5.0.44-r2 USE="berkdb debug ssl -big-tables -cluster -embedded -extraengine -latin1 -max-idx-128 -minimal -perl (-selinux) -static"
(In reply to comment #4) > (In reply to comment #3) > > I have been able to reproduce this bug, but it only seems to occur with the > > debug use flag switched on. > > > > I assume DBUG_ASSERT is trapping these, and is only active when compiled with > > the debug use flag. > > debug flag is not activated at my side. mysql has been emerged (and re-emerged) > with: > > dev-db/mysql-5.0.44-r2 USE="berkdb debug ssl -big-tables -cluster -embedded > -extraengine -latin1 -max-idx-128 -minimal -perl (-selinux) -static" I really need to buy glasses... Don't know how and why a line like "dev-db/mysql debug" is present in my package.use. Anyway, this bug shouldn't occurs with debug activated but removing it solves the problem. Thanks Gareth for pointing this out :)
This has been resolved in upstream sources, I've just tested with 5.0.51 with debug enabled - it runs without issue. This follows upstream bug: http://bugs.mysql.com/bug.php?id=29104 This patch submitted to MySQL bitkeeper resolved the issue. http://mysql.bkbits.net:8080/mysql-5.0-community/sql/item.cc?PAGE=diffs&REV=4679834eR-SU9AwtX53lhdZ-6HXkDQ For Gentoo users there are two resolutions; release the newer version into portage, or backport the patch above using the mysql-extras tarball.
5.0.54 is in the tree now pmasked for more testing, resolving this.
