Strikes & Gutters, having trouble with mysql integer columns wrapping?

1 year ago

having trouble with mysql integer columns wrapping?
we ran into an interesting mysql issue today. since i was having trouble finding a solution among my database friends, i figure i’d post it up here to help others.

the basic problem is this: if you’re storing counters, currencies or other numbers which should be zero or greater you probably have unsigned int, mediumint or bigint columns in your database tables. you’re also probably doing simple things like ‘set column_a=column_a+Y’ or ‘set column_a=column_a-Y’.

this works well until you’re trying to subtract a value greater than the current value. at which point, mysql will either wrap value (unsigned column) or turn it into a negative (signed column). for most cases, neither behavior is desirable and depending on the data type, wrapping can be *very* bad. in our case, we had a virtual currency that was going from near 0 to MAX_BIGINT-{a few} …. lol?

here’s a quick test case:
```
mysql> create table test_table (column_a bigint unsigned not null default 0);
Query OK, 0 rows affected (0.19 sec)
mysql> desc test_table;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| column_a | bigint(20) unsigned |      |     | 0       |       |
+----------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert test_table values (10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_table;
+----------+
| column_a |
+----------+
|       10 |
+----------+
1 row in set (0.01 sec)
mysql> update test_table set column_a=column_a-11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from test_table;
+----------------------+
| column_a             |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.02 sec)
```
obviously we don’t want that number in the database, even if it’s mathematically correct! the solution? wrap the columns in the update sql with the GREATEST() function and CAST() the result of the decrement to signed so that it’ll end up being less than 0 instead of wrapping:
```
mysql> select * from test_table;
+----------+
| column_a |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)
mysql> update test_table set column_a=GREATEST(0,CAST(column_a-11 as signed));
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from test_table;
+----------+
| column_a |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
```
viola! no more wrapped integer columns and no need to do followup select queries for bounds checking (which also leads to race conditions and wasted database cycles).

-mike
Notes

having trouble with mysql integer columns wrapping?

Notes

Following

Tumblr