Skip to content

Mysql Update: Null + 1 is Null!

You can't add to Null


Here's something about mysql create table definitions that can easily catch you if you aren't careful. Consider this table definition:




mysql> CREATE TABLE screenshots (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, votes INT);      
Query OK, 0 rows affected (0.09 sec)
 


What the user wanted was a simple table to keep track of user submitted screen shots. Of course the real table had a submission date, and name column but I've omitted those in order to focus on what can happen when you allow a numeric column to have NULL values.

In the application in question, when a user votes for the screen shot they like, the system should "count" the vote, by increasing the values in the "votes" column by one. Initially the developer working on this application was trying to read the value from the database, and in a PHP web script, they would increment this value and take the result and set "votes" to be equal to it in an UPDATE statement. I explained that this could cause lost votes, because if two or more users were voting at nearly the same time, each would overwrite the value of the "vote" column. In fact there are scenarios far worse than that --- a user with a cached page could vote and set the vote count back days or weeks. I didn't bother to mention the possibility that someone might recognize what was going on in the web form, and start tampering with it, since it was plainly evident that the form was passing the current number of votes.

One of the many benefits of using a relational database is built in concurrency. In an UPDATE statement, you can add to the value of the column without having to know what its original value is, just as computer languages allow assignment to a variable that references the variable's current value (ie. $a = $a + 1, $a++).

  1. UPDATE screenshots SET votes = votes + 1 WHERE id =  


All that's needed is to have the serverside language provide a value for a particular "id" and the votes will be tallied and updated correctly. Even more importantly, mysql will serialize the updates, insuring that no votes are lost.

However, given the original Mysql CREATE TABLE statement , what will happen if our code embeds the UPDATE statement provided?

The Dangers of Null number columns


First off, let's describe the table we just created and see what we actually got.


mysql> describe screenshots;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| votes | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
 


Notice the definition for votes which allows for NULL and has no Default value. This occurs because with mysql, the default for columns in a CREATE TABLE statement is for columns to allow NULL unless explicitly specified to be NOT NULL.

When a screenshot row is created, unless we're very careful we can easily insert a row like this:


mysql> insert into screenshots (votes) values ('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into screenshots (votes) values (null);  
Query OK, 1 row affected (0.00 sec)

mysql> select * from screenshots;
+----+-------+
| id | votes |
+----+-------+
|  1 |     0 |
|  2 |  NULL |
+----+-------+
2 rows in set (0.00 sec)

mysql> insert into screenshots (id) values (3);        
Query OK, 1 row affected (0.00 sec)

mysql> select * from screenshots;              
+----+-------+
| id | votes |
+----+-------+
|  1 |     0 |
|  2 |  NULL |
|  3 |  NULL |
+----+-------+
3 rows in set (0.01 sec)
 


So we get our first glimpse of some odd mysql behavior. When we assign null directly, mysql dutifully sets the column to be null. Also if the column is omitted, it also gets set to null. Oddly enough, when attempting to assign what many languages would consider a null compatible "Null string" ('' or "") mysql decides to set the value to zero. While this is what we want to have happen, we shouldn't need to rely on this odd side effect to get zero. You might also ask, why do we care whether the value is zero initially?

Null + 1?


What happens when you try and UPDATE votes + 1 when the original value of Votes IS NULL?


mysql> UPDATE screenshots SET votes = votes + 1 WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
 


The command line mysql client provides us some very important information here -- Rows matched: 1 Changed: 0 Warnings: 0

So while Mysql found the row to update, it didn't actually change the row. This is one of the many reasons you need to be very careful with NULL values in any relational database engine. NULL is often considered from the database point of view to be the "absence of a value". In other words, the value is unknown, so mysql takes the position that "UNKNOWN" + 1 is still "UNKNOWN". Logical enough, but this may come as a big surprise to those people who would rather in this case that NULL be considered to be the same as Zero. Selecting the table confirms that the row was not changed.


mysql> select * from screenshots;
+----+-------+
| id | votes |
+----+-------+
|  1 |     0 |
|  2 |  NULL |
|  3 |  NULL |
+----+-------+
3 rows in set (0.00 sec)
 


If however, we update the row with id 1, using the same statement, mysql works as expected:


mysql> UPDATE screenshots SET votes = votes + 1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from screenshots;
+----+-------+
| id | votes |
+----+-------+
|  1 |     1 |
|  2 |  NULL |
|  3 |  NULL |
+----+-------+
3 rows in set (0.00 sec)
 



Default and NOT NULL to the rescue


The solution to this problem is to insure that the row is initialized so that the votes column starts out with 0. Let's drop the table and recreate it using NOT NULL and Default "0" for the votes column definition":




Now when we describe screenshots, we get this:


mysql> describe screenshots;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| votes | int(11) | NO   |     | 0       |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
 


Even if we were to try and pass a null value for votes, or omit it, mysql will prevent us from having a null vote count.


mysql> insert into screenshots (votes) values (null);
ERROR 1048 (23000): Column 'votes' cannot be null
 


So in our first attempt, explicitly telling it to make votes NULL is disallowed by mysql. While you might consider an explicit declaration of null to be far fetched, many people use object relational mapping libraries or database classes that utilize a database mapping system and initialize values for you automagically. Unless you've read the code of the library yourself and verified the behavior you don't actually know what it might be doing behind the scenes, and it's not illogical for such a library to start with null values for a column that allows null, when no other value has been specified.

Our second attempt intentionally omits the vote column in the insert statement.


mysql> insert into screenshots (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from screenshots;
+----+-------+
| id | votes |
+----+-------+
|  1 |     0 |
+----+-------+
1 row in set (0.00 sec)
 


This could be simply a mistake in the code, or an intentional strategy. Regardless, we now, thanks to the Default option, get the initialization we wanted, even though the client did not specify a value for "votes".

Is NOT NULL necessary if we already have set a Default?


The answer to this question is YES. The reason for this is that if we explicitly try to assign NULL to votes, mysql will allow it, and not produce an error. Because the value is explicitly set, the Default will not cause an error, and we will inject a row with a NULL value into the database, which is what we don't want to happen.


mysql> alter table screenshots modify column votes int Default "0";
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> describe screenshots;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| votes | int(11) | YES  |     | 0       |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> delete from screenshots;
Query OK, 1 row affected (0.00 sec)

mysql> insert into screenshots (votes) values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from screenshots;
+----+-------+
| id | votes |
+----+-------+
|  2 |  NULL |
+----+-------+
1 row in set (0.00 sec)
 
Defined tags for this entry: , , , , ,

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

Add Comment

Pavatar, Gravatar, Favatar, MyBlogLog, Pavatar author images supported.
BBCode format allowed
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
Form options