UPDATE with WHERE+ORDER+LIMIT error(?) - test shell script (fwd)
am 06.11.2003 13:40:49 von serg--------------020502020608070601040903
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
>Description:
The same test script I've submitted today to bugs@lists.mysql.com,
which demonstrates improper(?) behaviour of UPDATE statement on
4.0.12 server under FreeBSD, on 4.0.15 server under Alt Linux causes
segmentation fault.
Below is messages from /var/log/mysql/info:
---------- Start of listing -----------
/usr/sbin/mysqld: ready for connections.
Version: '4.0.15' socket: '/mysql.sock' port: 3306
mysqld got signal 11;
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=67108864
read_buffer_size=131072
max_used_connections=2
max_connections=100
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 897535 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x881b578
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...
frame pointer (ebp) is NULL, did you compile with
-fomit-frame-pointer? Aborting backtrace!
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x882c048 = UPDATE ip SET
n=(@n:=@n+1),uid=@uid,ip=(@dip:=ip),tuse=0
WHERE tuse>1 ORDER BY !(uid=@uid),tuse LIMIT 1
thd->thread_id=6
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.
2003-Nov-06 13:40:48 :: execution failed
2003-Nov-06 13:40:48 :: shutdown
---------- Stop of listing -----------
>How-To-Repeat: View (edit if need ) & run attached shell script IP.SH
>Fix: N/A
>Submitter-Id: serg@UkrBiz.Com
>Originator: serg@UkrBiz.Com
>Organization: UkrBiz
>MySQL support: none
>Synopsis: improper operation (4.0.12) or server crash (4.0.15) on
UPDATE query
>Severity: serious
>Priority: <[ low | medium | high ] (one line)>
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.15 (ALT Linux MySQL RPM)
>Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.15, for
alt-linux-gnu on i586
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.15
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 30 min 17 sec
Threads: 2 Questions: 1668 Slow queries: 0 Opens: 91 Flush tables:
1 Open tables: 64 Queries per second avg: 0.918
>C compiler: i586-alt-linux-gcc (GCC) 3.2.3 (ALT Linux, build
3.2.3-alt1)
>C++ compiler: i586-alt-linux-g++ (GCC) 3.2.3 (ALT Linux, build
3.2.3-alt1)
>Environment:
System: Linux server.int.ukrbiz.com 2.4.20-alt7-up #1 Fri Mar 14
14:57:05 MSK 2003 i586 unknown unknown GNU/Linux
Architecture: i586
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i586-alt-linux/3.2.3/specs
Configured with: ../configure --prefix=/usr --libdir=/usr/lib
--with-slibdir=/lib --mandir=/usr/share/man --infodir=/usr/share/info
--enable-shared --enable-threads=posix --disable-checking
--enable-long-long --enable-__cxa_atexit
--enable-languages=c,c++,f77,objc,java --program-suffix=-3.2
--enable-objc-gc --with-system-zlib --without-included-gettext
--host=i586-alt-linux --build=i586-alt-linux --target=i586-alt-linux
Thread model: posix
gcc version 3.2.3 (ALT Linux, build 3.2.3-alt2)
Compilation info: CC='i586-alt-linux-gcc' CFLAGS='-pipe -Wall -O2
-march=i586 -mcpu=i686 -fomit-frame-pointer -D_FILE_OFFSET_BITS=64
-DHAVE_ERRNO_AS_DEFINE -DONE_THREAD' CXX='i586-alt-linux-g++'
CXXFLAGS='-pipe -Wall -O2 -march=i586 -mcpu=i686 -fomit-frame-pointer
-D_FILE_OFFSET_BITS=64 -DHAVE_ERRNO_AS_DEFINE -DONE_THREAD
-felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Sep 5 15:23 /lib/libc.so.6
-> libc-2.2.6.so
-rwxr-xr-x 2 root root 1145896 Sep 1 20:08 /lib/libc-2.2.6.so
-rw-r--r-- 1 root root 2416908 Sep 1 20:08 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 1 20:37 /usr/lib/libc.so
Configure command: ./configure '--build=i586-alt-linux'
'--host=i586-alt-linux' '--prefix=/usr' '--exec-prefix=/usr'
'--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc'
'--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib'
'--libexecdir=/usr/sbin' '--localstatedir=/var/lib'
'--sharedstatedir=/usr/com' '--mandir=/usr/share/man'
'--infodir=/usr/share/info' '--without-included-gettext'
'--localstatedir=/var/lib/mysql' '--enable-assembler' '--enable-shared'
'--enable-thread-safe-client' '--without-readline' '--without-debug'
'--with-raid' '--with-extra-charsets=all' '--with-berkeley-db'
'--with-mysqld-user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-comment=ALT
Linux MySQL RPM' 'CFLAGS=-pipe -Wall -O2 -march=i586 -mcpu=i686
-fomit-frame-pointer -D_FILE_OFFSET_BITS=64 -DHAVE_ERRNO_AS_DEFINE
-DONE_THREAD' 'CXXFLAGS=-pipe -Wall -O2 -march=i586 -mcpu=i686
-fomit-frame-pointer -D_FILE_OFFSET_BITS=64 -DHAVE_ERRNO_AS_DEFINE
-DONE_THREAD -felide-constructors -fno-exceptions -fno-rtti'
'FFLAGS=-pipe -Wall -O2 -march=i586 -mcpu=i686 -fomit-frame-pointer
-D_FILE_OFFSET_BITS=64 -DHAVE_ERRNO_AS_DEFINE -DONE_THREAD'
'build_alias=i586-alt-linux' 'host_alias=i586-alt-linux'
With respect,
Serge E. Yakubovich
--------------020502020608070601040903
Content-Type: text/plain;
name="IP.SH"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="IP.SH"
#!/bin/sh
#----------------------------------------------------------- -
# Hi!
#
# Seems to me, I'v found a bug in UPDATE statement with ORDER + LIMIT + indexes
#
# I pass below shell script that reproduce this strange ( at least to me :)
# situation.
#
# CONDITIONS:
# -----------
# Platform: i386 PC
# OS: FreeBSD 5.0-RELEASE
# Compiler: gcc (GCC) 3.2.1 [FreeBSD] 20021119 (release)
# MySQL: 4.0.12 source distribution
# "mysqladmin version" output:
# ./mysqladmin Ver 8.40 Distrib 4.0.12, for unknown-freebsd5.0 on i386
# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
# This software comes with ABSOLUTELY NO WARRANTY. This is free software,
# and you are welcome to modify and redistribute it under the GPL license
#
# Server version 4.0.12-log
# Protocol version 10
# Connection Localhost via UNIX socket
# UNIX socket /tmp/mysql.sock
# Uptime: 9 days 6 hours 34 min 6 sec
#
# Threads: 1 Questions: 1401 Slow queries: 0 Opens: 126 Flush tables: 2 Open tables: 8 Queries per second avg: 0.002
#
# PREAMBLE:
# ---------
# UPDATE query in question is intended to select dynamic IP adress "@dip"
# for dialup user with given "@uid" and update this IP info. It must select
# 1) IP not in use now by other user ( "WHERE tuse>1", tuse={0|1} means IP
# is in use now )
# 2) IP this user was already using before as most preffered
# OR first free IP ( tuse=2 ) OR first already used by other
# user IP with lowest time of usage tuse=
# for this I use "ORDER BY !(uid=@uid),tuse LIMIT 1"
#
# UPDATE is atomic; I use "strange" assigment "SET ip=(@dip:=ip)" to make table
# update and to get IP ( with firther SELECT INET_NTOA(@dip) ) at the same time,
# thus avoiding LOCK TABLES...; SELECT...; UPDATE...; UNLOCK TABLES;
#
# ERROR DESCRIPTION:
# ------------------
# In provided example table "ip", for user with @uid=2 second row with uid=2
# and IP 10.0.0.2 should be selected following above logic and my expects from query.
#
# BUT, 1st UPDATE query sample updates another, 4th row, what is wrong
# in my mind, and coused this bug report. This behaviour also differs from
# results of SELECT query with the same WHERE, ORDER and LIMIT conditions.
#
# From the other side, when I do update with IGNORE INDEX (TUSE_KEY)
# ( 2nd sample ) - all is OK; also OK is 3rd sample where I ommit "LIMIT 1" at
# all or use LIMIT with N >= number of rows in table.
#
# NOTE: I've added field "n" and construct "SET n=(@n:=@n+1)i" to track sequence
# of rows updated. Check values of field "n" in queries output; n=1 for first
# row updated in all 3 samples.
#
# ERROR REPRODUCTION:
# -------------------
# I've created below script for error condition reproduction. To reproduce,
# accomodate CMD_MYSQL line to your conditions; you may also need to change
# DB name in DROP/CREATE/USE lines, or this script can effectively delete
# your existing DB "temp"
# After all this is done, just run the script and look at output.
# On my work PC, this script gives me following output:
#
#+-----------+------+------------+------+
#| ip | uid | tuse | n |
#+-----------+------+------------+------+
#| 167772161 | 1 | 1063322601 | 0 |
#| 167772162 | 2 | 1063322602 | 0 |
#| 167772163 | 3 | 1063322603 | 0 |
#| 167772164 | 0 | 2 | 0 |
#| 167772165 | 0 | 2 | 0 |
#| 167772166 | 0 | 0 | 0 |
#| 167772167 | 0 | 0 | 0 |
#+-----------+------+------------+------+
#+-----------------+
#| INET_NTOA(@dip) |
#+-----------------+
#| 10.0.0.4 |
#+-----------------+
#+-----------+------+------------+------+
#| ip | uid | tuse | n |
#+-----------+------+------------+------+
#| 167772161 | 1 | 1063322601 | 0 |
#| 167772162 | 2 | 1063322602 | 0 |
#| 167772163 | 3 | 1063322603 | 0 |
#| 167772164 | 2 | 0 | 1 |
#| 167772165 | 0 | 2 | 0 |
#| 167772166 | 0 | 0 | 0 |
#| 167772167 | 0 | 0 | 0 |
#+-----------+------+------------+------+
#+-----------------+
#| INET_NTOA(@dip) |
#+-----------------+
#| 10.0.0.2 |
#+-----------------+
#+-----------+------+------------+------+
#| ip | uid | tuse | n |
#+-----------+------+------------+------+
#| 167772161 | 1 | 1063322601 | 0 |
#| 167772162 | 2 | 0 | 1 |
#| 167772163 | 3 | 1063322603 | 0 |
#| 167772164 | 0 | 2 | 0 |
#| 167772165 | 0 | 2 | 0 |
#| 167772166 | 0 | 0 | 0 |
#| 167772167 | 0 | 0 | 0 |
#+-----------+------+------------+------+
#+-----------------+
#| INET_NTOA(@dip) |
#+-----------------+
#| 10.0.0.3 |
#+-----------------+
#+-----------+------+------+------+
#| ip | uid | tuse | n |
#+-----------+------+------+------+
#| 167772161 | 2 | 0 | 4 |
#| 167772162 | 2 | 0 | 1 |
#| 167772163 | 2 | 0 | 5 |
#| 167772164 | 2 | 0 | 2 |
#| 167772165 | 2 | 0 | 3 |
#| 167772166 | 0 | 0 | 0 |
#| 167772167 | 0 | 0 | 0 |
#+-----------+------+------+------+
#
#
# Serge E. Yakubovich
#----------------------------------------------------------- ---
# Change this to your mysql path and flags
CMD_MYSQL='/usr/local/mysql/bin/mysql -At -uroot -hlocalhost'
# Table dump statements
CREATETABLE="
DROP TABLE IF EXISTS ip;
CREATE TABLE ip (
ip int(10) unsigned default NULL,
uid int(10) unsigned default '0',
tuse int(10) unsigned default '0',
n int(10) unsigned default '0',
KEY IP_KEY (ip),
KEY UID_KEY (uid),
KEY TUSE_KEY (tuse)
) TYPE=MyISAM;
INSERT INTO ip VALUES (INET_ATON('10.0.0.1'),1,1063322601,0);
INSERT INTO ip VALUES (INET_ATON('10.0.0.2'),2,1063322602,0);
INSERT INTO ip VALUES (INET_ATON('10.0.0.3'),3,1063322603,0);
INSERT INTO ip VALUES (INET_ATON('10.0.0.4'),0,2,0);
INSERT INTO ip VALUES (INET_ATON('10.0.0.5'),0,2,0);
INSERT INTO ip VALUES (INET_ATON('10.0.0.6'),0,0,0);
INSERT INTO ip VALUES (INET_ATON('10.0.0.7'),0,0,0);
"
# Run queries
$CMD_MYSQL -e "
DROP DATABASE IF EXISTS temp;
CREATE DATABASE temp;
USE temp;
$CREATETABLE
SELECT * FROM ip;
--- SAMPLE 1 (BAD): inproper row is updated
SET @n=0,@uid=2;
UPDATE ip SET n=(@n:=@n+1),uid=@uid,ip=(@dip:=ip),tuse=0
WHERE tuse>1 ORDER BY !(uid=@uid),tuse LIMIT 1;
SELECT INET_NTOA(@dip);
SELECT * FROM ip;
$CREATETABLE
--- SAMPLE 2 (OK): with IGNORE INDEX (TUSE_KEY),
--- proper row is updated
SET @n=0,@uid=2;
UPDATE ip IGNORE INDEX (TUSE_KEY) SET n=(@n:=@n+1),uid=@uid,ip=(@dip:=ip),tuse=0
WHERE tuse>1 ORDER BY !(uid=@uid),tuse LIMIT 1;
SELECT INET_NTOA(@dip);
SELECT * FROM ip;
$CREATETABLE
--- SAMPLE 3 (OK): no LIMIT (or LIMIT N >= COUNT(*)) in UPDATE,
--- proper first row is updated
SET @n=0,@uid=2;
--- No LIMIT here
UPDATE ip SET n=(@n:=@n+1),uid=@uid,ip=(@dip:=ip),tuse=0
WHERE tuse>1 ORDER BY !(uid=@uid),tuse;
SELECT INET_NTOA(@dip);
SELECT * FROM ip;
DROP DATABASE IF EXISTS temp;
"
--------------020502020608070601040903
Content-Type: text/plain; charset=us-ascii
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org
--------------020502020608070601040903--