UPDATE with WHERE+ORDER+LIMIT error(?) - test shell script

"Serge E. Yakubovich" <[email protected]>
Newsgroups gmane.comp.db.mysql.bugs
Message-ID <[email protected]> 
Hi!

Just look in attached IP.SH shell script for error description
and run it to reproduce results.

With respect,
Serge E. Yakubovich	<[email protected]>

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]
IP.SH (text/plain, 6.9 KB) 
#!/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=<TIMESTAMP> ) -
#       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	<[email protected]>
#--------------------------------------------------------------


# 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;
"
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.