Disclaimer: This bug HAS already been reported ( https://sourceforge.net/tracker/?func=detail&aid=1334627&group_id=23067&atid=377408 ). However, it appears that SourceForge does not permit one to comment on bugs that have been Closed. If I am overlooking the ability to post comments to Closed issues, please forgive my ignorance.
The issue is that phpMyAdmin inserts the NOW() function's return value when inserting or updating a row, even when the NULL box is checked just to the right of the function drop-down menu.
Why is the NOW() function selected by default in the drop-down for a column whose default value is defined as NULL? The function menu should be set to [blank] when the page loads for columns whose default is NULL, and should be set to NOW() only when a selection is made from the function menu.
PMA has it almost correct; the NULL box is checked within the interface (just to the right of the function drop-down) when the column's default value is defined as NULL. So, again, why is NOW() selected in the function drop-down?
There is discussion of this issue at http://stackoverflow.com/questions/6576604/mysql-phpmyadmin-timestamp-always-executes-now-function . Some snide hot-shot (Bohemian) claims that, "Unlike other databases, in MySQL TIMESTAMP columns are ALWAYS updated with now() whenever the row is updated. This is a deliberate feature of the TIMESTAMP datatype." Of course, there is no link to any supporting documentation, and further, the observed behavior betrays his statement (probably why his answer was voted -2). This individual failed to understand that the OP had defined the columns in question with the NULL attribute, in order to permit NULL values.
Viktor sets the record straight with the following insightful comment:
"TIMESTAMP columns are not always updated. Try creating a table with, for instance, a VARCHAR column and a TIMESTAMP column defined as NULL DEFAULT NULL. Insert a row, specifying only the VARCHAR field. The TIMESTAMP field will stay as NULL. Update the inserted row, again specifying only the VARCHAR field. Again, the TIMESTAMP will stay as NULL. Just tested with MySQL 5.1."
So, I did the due diligence here and read the actual documentation: http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html . Jump down to the "TIMESTAMP Initialization and the NULL Attribute" section for the relevant information.
The first paragraph describes precisely the behavior that I WANT and EXPECT, and the last sentence says it all: "If a TIMESTAMP column permits NULL values, assigning NULL sets it to NULL, not to the current timestamp."
Further, "A TIMESTAMP column that permits NULL values does not take on the current timestamp at insert time except under one of the following conditions: 1) Its default value is defined as CURRENT_TIMESTAMP and no value is specified for the column; 2) CURRENT_TIMESTAMP or any of its synonyms such as NOW() is explicitly inserted into the column."
Condition 1) is not met (I have defined the default value as NULL, not CURRENT_TIMESTAMP), which seems to indicate that phpMyAdmin is, in fact, inserting CURRENT_TIMESTAMP, NOW(), etc. into the column when it should not be.
In the event that it's relevant, I roll with AJAX disabled in PMA (I find the "light-boxed" Create New Table interface unbearable).
STEPS TO REPRODUCE:
1.) Create a test table.
CREATE TABLE IF NOT EXISTS `shr_cms_content_article` (
`id` int(9) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`status` tinyint(1) unsigned NOT NULL DEFAULT '1',
`body` text COLLATE utf8_unicode_ci,
`slug` varchar(127) COLLATE utf8_unicode_ci DEFAULT NULL,
`timePublished` timestamp NULL DEFAULT NULL,
`timeDeleted` timestamp NULL DEFAULT NULL,
`deleter` int(9) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
2.) Insert data as per attached screenshot.
3.) Examine the resultant SQL:
INSERT INTO `cdmc`.`shr_cms_content_article` (
NULL , 'Test', '1', 'Test.', 'test', NOW( ) , NOW( ) , NULL