UPDATE with WHERE+ORDER+LIMIT error(?) - test shell script
am 06.11.2003 11:32:48 von serg--------------010009030307050105000004
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Hi!
Just look in attached IP.SH shell script for error description
and run it to reproduce results.
With respect,
Serge E. Yakubovich
--------------010009030307050105000004
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;
"
--------------010009030307050105000004
Content-Type: text/plain; charset=us-ascii
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org
--------------010009030307050105000004--