List:General Discussion« Previous MessageNext Message »
From:Patrik Rak Date:April 23 1999 6:43pm
Subject:update a set b=b+19 where b>0 ; does not work properly
View as plain text  
>Description:
	

	Hi!

	It seems that the following query caused to process the priority
	field again and again until it went out of range...

	The priority was in range -94..64 before this query.

	mysql> update articles set priority = priority + 19 where priority > 0 ;
	Query OK, 116100 rows affected (41 min 1.73 sec)
	Rows matched: 137616  Changed: 116100  Warnings: 42287

	mysql> select count(*) from articles ;
	+----------+
	| count(*) |
	+----------+
	|    53352 |
	+----------+
	1 row in set (0.05 sec)

	mysql> show fields from articles ;
	+-------------+-----------------------------------+------+-----+------------+----------------+
	| Field       | Type                              | Null | Key | Default    | Extra      
   |
	+-------------+-----------------------------------+------+-----+------------+----------------+
	| id          | int(10) unsigned                  |      | PRI | 0          |
auto_increment |
	| title       | varchar(255)                      |      | MUL |            |            
   |
	| subtitle    | varchar(255)                      |      |     |            |            
   |
	| author      | varchar(64)                       |      |     |            |            
   |
	| date        | date                              |      | MUL | 0000-00-00 |            
   |
	| source      | varchar(64)                       |      |     |            |            
   |
	| location    | varchar(64)                       |      | MUL |            |            
   |
	| text        | text                              |      |     | NULL       |            
   |
	| link        | text                              |      |     | NULL       |            
   |
	| report      | varchar(64)                       |      | MUL |            |            
   |
	| box         | varchar(64)                       |      | MUL |            |            
   |
	| section     | varchar(64)                       |      | MUL |            |            
   |
	| priority    | tinyint(4)                        |      | MUL | 0          |            
   |
	| editor      | varchar(64)                       |      | MUL |            |            
   |
	| flags       | set('approved','lead','bulletin') |      |     |            |            
   |
	| update_time | timestamp(14)                     | YES  | MUL | NULL       |            
   |
	| create_time | timestamp(14)                     | YES  | MUL | NULL       |            
   |
	+-------------+-----------------------------------+------+-----+------------+----------------+
	17 rows in set (0.00 sec)

	mysql> show keys from articles ;
	+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+
	| Table    | Non_unique | Key_name    | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part |
	+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+
	| articles |          0 | PRIMARY     |            1 | id          | A         |      
53352 |     NULL |
	| articles |          1 | date        |            1 | date        | A         |       
NULL |     NULL |
	| articles |          1 | update_time |            1 | update_time | A         |       
NULL |     NULL |
	| articles |          1 | create_time |            1 | create_time | A         |       
NULL |     NULL |
	| articles |          1 | title       |            1 | title       | A         |       
NULL |       32 |
	| articles |          1 | section     |            1 | section     | A         |       
NULL |       16 |
	| articles |          1 | section     |            2 | date        | A         |       
NULL |     NULL |
	| articles |          1 | box         |            1 | box         | A         |       
NULL |        8 |
	| articles |          1 | box         |            2 | date        | A         |       
NULL |     NULL |
	| articles |          1 | priority    |            1 | priority    | A         |       
NULL |     NULL |
	| articles |          1 | report      |            1 | report      | A         |       
NULL |       32 |
	| articles |          1 | report      |            2 | date        | A         |       
NULL |     NULL |
	| articles |          1 | location    |            1 | location    | A         |       
NULL |       32 |
	| articles |          1 | editor      |            1 | editor      | A         |       
NULL |       32 |
	+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+
	14 rows in set (0.00 sec)

	mysql> select priority,count(*) from articles group by priority order by priority desc
limit 20 ;
	+----------+----------+
	| priority | count(*) |
	+----------+----------+
	|      127 |    21516 |
	|        0 |     2463 |
	|       -1 |     2390 |
	|       -2 |     2309 |
	|       -3 |     2227 |
	|       -4 |     2140 |
	|       -5 |     2037 |
	|       -6 |     1924 |
	|       -7 |     1820 |
	|       -8 |     1690 |
	|       -9 |     1537 |
	|      -10 |     1396 |
	|      -11 |     1265 |
	|      -12 |     1141 |
	|      -13 |     1016 |
	|      -14 |      896 |
	|      -15 |      772 |
	|      -16 |      665 |
	|      -17 |      568 |
	|      -18 |      482 |
	+----------+----------+
	20 rows in set (0.52 sec)



>How-To-Repeat:
	Hmm, I don't know if this is reproducible but should be easy
	to find in the source for the people in the know.
>Fix:
	Unknown.

>Submitter-Id:	<submitter ID>
>Originator:	Patrik Rak
>Organization:
 European Internet Network, Inc.
>MySQL support: none
>Synopsis:	update x where x ; sets x many times
>Severity:	serious
>Priority:	high
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-3.22.21 (Source distribution)
>Server: /usr/local/bin/mysqladmin  Ver 7.11 Distrib 3.22.21, for pc-linux-gnu on i586
TCX Datakonsult AB, by Monty

Server version		3.22.21
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/tmp/mysql.sock
Uptime:			2 days 11 hours 11 min 18 sec

Threads: 10  Questions: 2254  Slow queries: 3  Opens: 32  Flush tables: 1  Open tables: 16
>Environment:
	
System: Linux slink.int.ein.cz 2.0.37 #3 Thu Mar 18 11:16:20 CET 1999 i586 unknown
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/i386-redhat-linux/2.7.2.3/specs
gcc version 2.7.2.3
Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
Configure command: ./configure  --enable-assembler
Perl: This is perl, version 5.004_05 built for i386-linux
Thread
update a set b=b+19 where b>0 ; does not work properlyPatrik Rak23 Apr
  • Re: update a set b=b+19 where b>0 ; does not work properlyBenjamin Pflugmann24 Apr