The MySQL documentation does an excellent job of describing the changes so I'll simply link to that information here.
What the documentation doesn't tell you is that in versions prior to 5.6 the default engine was the ISAM engine. Since it is inferior in many ways and rarely changed by developers after initial setup of a database, the default in version 5.6 was changed to use the InnoDB engine. Some features in the ISAM engine behave differently or to be more correct, exhibited quirky behavior that some of us have used either knowingly or unwittingly. Now that the default engine is InnoDB you may be faced with some interesting bugs in your software. You must now not only mitigate issues related to fixes to both engines over time, you will also need to understand the differences between the engines or set your default engine back to ISAM.
create table foo (
id int auto_increment primary key,
foo_stuff varchar(16),
foo_enabled boolean,
event_time timestamp not null default current_timestamp
)
You'll notice that I explicitly tell MySQL to disallow NULL values, but provide a default. Now suppose we add a row but I explicitly set the TIMESTAMP column to NULL...hoping that because I provided a default value, MySQL would honor that.
insert into foo (id,foo_stuff,foo_enabled,event_time)
values (0,'foo bar', 1, NULL);
Let's take a look:
mysql> select * from foo;
+----+-----------+-------------+---------------------+
| id | foo_stuff | foo_enabled | event_time |
+----+-----------+-------------+---------------------+
| 1 | foo bar | 1 | 2015-04-10 00:05:45 |
+----+-----------+-------------+---------------------+
1 row in set (0.00 sec)
Hey that worked! Of course, it probably should not have worked. Setting a column to NULL is in fact providing a value (or at least a NULL value), so why should MySQL use the default? Does DEFAULT mean, "use this value if the column is NULL"? I guess it does in the ISAM engine. As you can see, MySQL has indeed updated the TIMESTAMP column with the current time. Now let's see what happens with version 5.6 of MySQL when we do the insert.
mysql> insert into foo (id,foo_stuff, foo_enabled, event_time) values (0,'foo bar',1,NULL);
ERROR 1048 (23000): Column 'event_time' cannot be null
Hmmm...this doesn't work. Looks like someone rationally decided that the paradox of declaring a column to disallow NULLs and then allowing someone to set the column to NULL did not make sense...or...there is another explanation. And in fact there is. MySQL behaves differently based on whether it is in strict mode or not. The default, I'm guessing for the InnoDB engine is strict mode, while the default for the ISAM engine is not. It's a little more complex than that since the modes are actually based on whether the tables are transactional or not, so a careful reading of MySQL's modes is probably a good idea when switching engines and versions.
The difference in behavior has to do with the way TIMESTAMP is implemented in the ISAM engine versus the InnoDB engine. In the InnoDB engine, you must omit the TIMESTAMP column in the insert or the update if you want the value to be updated by MySQL.
Also, do not expect the InnoDB engine to automatically update the TIMESTAMP column on updates unless you told it to do so either impicitly (by not providing a default when you defined the table, or explicitly with an ON UPDATE constraint on the table). Let's see what happens with the InnoDB engine.
mysql> insert into foo (id,foo_stuff) values (0,'foo bar');
Query OK, 1 row affected (0.01 sec)
mysql> select * from foo;
+----+-----------+-------------+---------------------+
| id | foo_stuff | foo_enabled | event_time |
+----+-----------+-------------+---------------------+
| 1 | foo bar | NULL | 2015-04-10 00:09:16 |
+----+-----------+-------------+---------------------+
1 row in set (0.00 sec)
mysql> update foo set foo_enabled = true where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from foo;
+----+-----------+-------------+---------------------+
| id | foo_stuff | foo_enabled | event_time |
+----+-----------+-------------+---------------------+
| 1 | foo bar; | 1 | 2015-04-10 00:09:16 |
+----+-----------+-------------+---------------------+
1 row in set (0.01 sec)
In our InnoDB example, by omitting the TIMESTAMP column MySQL updated the TIMESTAMP column on insert. But as you can see, not on update. Let's change the definition of the column.
mysql> alter table foo modify event_time timestamp not null default current_timestamp on update current_timestamp;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update foo set foo_enabled = false where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from foo;
+----+-----------+-------------+---------------------+
| id | foo_stuff | foo_enabled | event_time |
+----+-----------+-------------+---------------------+
| 1 | foo bar; | 0 | 2015-04-10 00:13:44 |
+----+-----------+-------------+---------------------+
1 row in set (0.00 sec)
Conclusion
MySQL's new TIMESTAMP behavior is probably more correct (after all I guess they know what they are doing?) but it might break your software if you've relied on the ISAM's engine quirks.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.