Recently I noticed that ON UPDATE CASCADE rule falis in MySQL on InnoDB tables if a foreign key references the same table, which is usual for tree-like data structured. At the same time ON DELETE CASCADE works fine.

This  is not corresponding to SQL standard but it’s already stated in MySQL manual:

Deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT

It was also reported as a bug but rejected years ago.
Read the rest of this entry »

I use InnoDB storage engine because of support for transactions and referral integrity rules. However, MySQL still creates new tables as MyISAM by default. It was so annoying  to always define storage engine when creating new tables and double check that I didn’t forget it until I found how to set InnoDB by default.

Read the rest of this entry »

You may find the timestamp value returned by MySQL UNIX_TIMESTAMP() function 24 seconds grater than that returned by PHP functions and methods like strtotime(), mktime(), DateTime::getTimestamp(), Zend_Date::getTimestamp().

Read the rest of this entry »