MySQL

Cascade update fails on MySQL for foreign keys referring to the same table

by

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 more

How to set InnoDB as a default storage engine for MySQL tables

by

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 more

Why MySQL timestamp is 24 seconds different from PHP

by

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 more