#3579 (ok 4.1.6) NOW() function incorrectly selected

4.1.5
fixed
1
2014-02-09
2012-04-24
Ben Johnson
No

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` (
`id` ,
`name` ,
`status` ,
`body` ,
`slug` ,
`timePublished` ,
`timeDeleted` ,
`deleter`
)
VALUES (
NULL , 'Test', '1', 'Test.', 'test', NOW( ) , NOW( ) , NULL
);

Discussion

1 2 > >> (Page 1 of 2)
  • Ben Johnson

    Ben Johnson - 2012-04-24

    System details:
    MySQL 5.1.57-community
    PHP 5.3.6
    Apache/2.2.19 (Win32) mod_ssl/2.2.19 OpenSSL/0.9.8r PHP/5.3.6

     
  • Marc Delisle

    Marc Delisle - 2012-05-01
    • assigned_to: nobody --> lem9
     
  • Marc Delisle

    Marc Delisle - 2012-05-01
    • priority: 5 --> 1
    • summary: NOW() value inserted when NULL box is checked (see 1334627) --> (ok 3.5.2) NOW() function incorrectly selected
    • status: open --> open-fixed
     
  • Ben Johnson

    Ben Johnson - 2012-05-01

    It looks as though that change will have the intended effect. Thanks, lem9!

     
  • Marc Delisle

    Marc Delisle - 2012-07-20
    • status: open-fixed --> closed-fixed
     
  • David Maass

    David Maass - 2013-05-27

    Seems that the bug was reintroduced to phpmyadmin 4 branch.
    Timestamp-Columns are always overwritten with value NOW() - regardless of already inserted value or a NULL definition.
    Works OK in 3.5 branch

     
  • Marc Delisle

    Marc Delisle - 2013-05-28
    • summary: (ok 3.5.2) NOW() function incorrectly selected --> NOW() function incorrectly selected
    • status: closed-fixed --> open
    • assigned_to: Marc Delisle --> nobody
    • Group: 3.5.0 --> 4.0.2
     
  • Marc Delisle

    Marc Delisle - 2013-05-28

    Reopened.

     
  • Marc Delisle

    Marc Delisle - 2013-05-29

    David,
    with version 4.0.2 and the bug scenario, this (IMO) correct query is generated:
    INSERT INTO test2.shr_cms_content_article (id, name, status, body, slug, timePublished, timeDeleted, deleter) VALUES (NULL, 'test', '1', 'test.', 'test', NOW(), NULL, NULL);

    I believe that the only problem is that the NOW() functions is always selected, correct?

     
  • Marc Delisle

    Marc Delisle - 2013-05-29
    • assigned_to: Marc Delisle
    • Priority: 1 --> 5
     
  • David Maass

    David Maass - 2013-05-31

    Hi Marc,

    yes, phpmyadmin always selects "now" as value. The generated query is OK, if the null-checkbox is activated.

    Your fix partially fixes the problem. If the column contains a value, the value is not overwritten with NOW (good).

    But there are still problems:
    - the NULL value of a timestamp column is not recognized. Editing a row with a NULL-timestamp column does not check the "null" checkbox in the GUI but pre-selects NOW as new value
    - i'm not sure if entering a value in the field should automaticaly deselect the NOW function. Otherwise, if i forget to set the NOW field to empty, the NOW is inserted in favor to the manual inserted value.

     
  • David Maass

    David Maass - 2013-05-31

    I just tried your online demo (which should use the current git-version) and it looks ok!
    No more selection of NOW but checked NULL Checkbox.

    Thanks! Nice support

     
  • Marc Delisle

    Marc Delisle - 2013-05-31

    I'm happy to hear that. By the way, version 4.0.3-rc1 contains these patches.

     
  • Marc Delisle

    Marc Delisle - 2013-05-31
    • summary: NOW() function incorrectly selected --> (ok 4.0.3) NOW() function incorrectly selected
    • status: open --> open-fixed
    • Priority: 5 --> 1
     
  • Michal Čihař

    Michal Čihař - 2013-06-11
    • Status: open-fixed --> resolved
     
  • Michal Čihař

    Michal Čihař - 2013-06-11
    • Status: resolved --> fixed
     
  • Josef V

    Josef V - 2014-01-21

    Please reopen this bug.
    Version: 4.1.5.

    SQL:

    CREATE TABLE post(
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    note TEXT NOT NULL,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_update TIMESTAMP NULL
    PRIMARY KEY (id)
    ) ENGINE=InnoDB;

    Column last_update has by PhpMyAdmin default selected NOW(), which is bug.

     
  • Marc Delisle

    Marc Delisle - 2014-01-21
    • summary: (ok 4.0.3) NOW() function incorrectly selected --> NOW() function incorrectly selected
    • status: fixed --> open
    • assigned_to: Marc Delisle --> nobody
    • Group: 4.0.2 --> 4.1.5
    • Priority: 1 --> 5
     
  • Marc Delisle

    Marc Delisle - 2014-01-22
    • assigned_to: Marc Delisle
     
  • Marc Delisle

    Marc Delisle - 2014-01-22
    • summary: NOW() function incorrectly selected --> (ok 4.1.6) NOW() function incorrectly selected
    • status: open --> resolved
    • Priority: 5 --> 1
     
1 2 > >> (Page 1 of 2)

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks