#1629 (in 2.6.3) new TIMESTAMP attributes and NULL detection

2.6.1-pl3
invalid
1
2013-06-11
2005-03-15
No

MySQL server 4.1.7
phpMyAdmin 2.6.1-pl3
PHP 5.0.3

PMA is not handling the new TIMESTAMP attributes
correctly (TIMESTAMP attributes have changed
since MySQL 4.1).

Example:

CREATE TABLE `test` (
`blabla` VARCHAR(255),
`update_time` TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP
);

PMA detects the above `update_time` col as NULL.
Try to change the attributes of the `update_time` by
using the
PMA dedicated form (tbl_alter.php) and click on "Save"
without
changing any attribute but the Null attribute (set it on
NOT NULL since PMA was wrong by detecting it as NULL):
you would expect PMA NOT to alter the `test` table either
by not sending any ALTER query or by sending the following
"zero alteration" query:

ALTER TABLE `test` CHANGE `update_time`
`update_time` TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP;

Instead of that, PMA sends the following query:

ALTER TABLE `test` CHANGE `update_time`
`update_time` TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP'
NOT NULL;

which as the nasty consequence to change the DEFAULT
attribute
to '0000-00-00 00:00:00'

Keep using the PMA dedicated form and try to set the
NULL attribute
for the `update_time` col (beginning with MySQL 4.1.6,
you can
include the NULL attribute in the definition of a
TIMESTAMP column).
PMA will send the query:

ALTER TABLE `test` CHANGE `update_time`
`update_time` TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP';

which fails to do the job because the NULL attribute
is not specified (but it still sets the DEFAULT attribute
to '0000-00-00 00:00:00')

Indeed, it seems that the following queries are equivalent
for a TIMESTAMP col:

ALTER TABLE `test` CHANGE `update_time`
`update_time` TIMESTAMP;

ALTER TABLE `test` CHANGE `update_time`
`update_time` TIMESTAMP NOT NULL;

This has another nasty consequence for the PMA user.
With the table

CREATE TABLE `test2` (
`blabla` VARCHAR(255),
`update_time` TIMESTAMP NULL
);

the `update_time` col has now the NULL attribute.

Try to edit the `update_time` attributes by using the
PMA dedicated form and click on "Save" without changing
anything. PMA will try to send the following query:

ALTER TABLE `test2` CHANGE `update_time`
`update_time` TIMESTAMP DEFAULT NULL;

which will be rejected by the MySQL server (Error #1067
- Invalid
default value for 'update_time') because it is
equivalent to
the following query:

ALTER TABLE `test2` CHANGE `update_time`
`update_time` TIMESTAMP NOT NULL DEFAULT NULL;

So you'll have to give a default value not conflicting
with the NOT NULL attribute in the PMA dedicated form
if you want PMA to send a valid query. Another bad news
is that PMA will ALWAYS send a query resulting in the
NULL attribute being switched to NOT NULL (whatever
choice you make in the dedicated form).

Discussion

  • Marc Delisle

    Marc Delisle - 2005-03-15
    • status: open --> wont-fix
     
  • Marc Delisle

    Marc Delisle - 2005-03-15

    Logged In: YES
    user_id=210714

    Sorry, we already have a bug report about this. I am working
    on it.

     
  • Marc Delisle

    Marc Delisle - 2005-03-15
    • status: wont-fix --> wont-fix-duplicate
     
  • Marc Delisle

    Marc Delisle - 2005-03-16

    Logged In: YES
    user_id=210714

    Hi Herv,

    I just merged in CVS some code to take care of table
    structure editing for TIMESTAMPs. Would you like to try it?
    I think anonymous cvs currently has a problem on sf.net, but
    I can send you the code if you want.

     
  • Hervé Pagčs

    Hervé Pagčs - 2005-03-17

    Logged In: YES
    user_id=1239484

    Hi Marc,
    Right now I'm dowloading the last sources with cvs. I'll be glad
    to test the new table structure editing for TIMESTAMPs ASAP.
    Thanks

     
  • Hervé Pagčs

    Hervé Pagčs - 2005-03-18

    Logged In: YES
    user_id=1239484

    MySQL server 4.1.7
    PHP 5.0.3

    phpMyAdmin 2.6.2-dev (retrived from CVS, March 16)

    Problem 1 is NOT resolved
    -------------------------
    CREATE TABLE `test` (
    `blabla` VARCHAR(255),
    `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    PMA still detects the above `update_time` col as NULL.

    Problem 2 is resolved
    ---------------------
    Try to change the attributes of the `update_time` by using
    the table structure editing form (tbl_alter.php). Now the
    Default text field is empty and there is a nice
    CURRENT_TIMESTAMP checked checkbox just below ;-)
    Click on "Save" without changing any attribute but the Null
    attribute (set it on NOT NULL since PMA was wrong by detecting
    it as NULL). PMA sends the following query:

    ALTER TABLE `test` CHANGE `update_time`
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL

    which is OK (no more quotes around CURRENT_TIMESTAMP).

    Problem 3 is NOT resolved
    -------------------------
    Keep using the PMA dedicated form and try to set the NULL
    attribute for the `update_time` col. PMA still sends
    the query:

    ALTER TABLE `test` CHANGE `update_time`
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

    which fails to do the job because the NULL attribute
    is not specified.

    Problem 4 is NOT resolved
    -------------------------
    With the table

    CREATE TABLE `test2` (
    `blabla` VARCHAR(255),
    `update_time` TIMESTAMP NULL
    );

    the `update_time` col has now the NULL attribute.

    Try to edit the `update_time` attributes by using the
    table structure editing form and click on "Save"
    without changing anything. PMA still tries to send
    the following query:

    ALTER TABLE `test2` CHANGE `update_time`
    `update_time` TIMESTAMP DEFAULT NULL;

    resulting in a MySQL error (Error #1067 - Invalid default
    value for 'update_time'). This is because the above query
    is equivalent to the following query:

    ALTER TABLE `test2` CHANGE `update_time`
    `update_time` TIMESTAMP NOT NULL DEFAULT NULL;

    Problem 5 (new problem)
    -----------------------
    After the MySQL server rejects the above query, PMA
    displays the MySQL Error and the table structure editing
    form again. But this time the CURRENT_TIMESTAMP
    checkbox has disapeared!

    Problem 6 is NOT resolved
    -------------------------
    In the table structure editing form, enter a default value not
    conflicting with the NOT NULL (e.g. 0) and click on "Save".
    PMA sends the following query:

    ALTER TABLE `test2` CHANGE `update_time`
    `update_time` TIMESTAMP

    resulting in the unwanted side effect of switching the
    `update_time` NULL attribute to NOT NULL!

    Herv

     
  • Marc Delisle

    Marc Delisle - 2005-03-18
    • status: wont-fix-duplicate --> open-duplicate
     
  • Marc Delisle

    Marc Delisle - 2005-03-18
    • assigned_to: nobody --> lem9
     
  • Marc Delisle

    Marc Delisle - 2005-03-18

    Logged In: YES
    user_id=210714

    Thanks for testing. I will check your complete report in a
    few days.
    About the NULL, here is what I answered in the other bug report
    about this:
    ============
    From MySQL manual,
    http://dev.mysql.com/doc/mysql/en/create-table.html

    NULL values are handled differently for TIMESTAMP columns
    than for other column types. Before MySQL 4.1.6, you cannot
    store a literal NULL in a TIMESTAMP column; setting the
    column to NULL sets it to the current date and time. Because
    TIMESTAMP columns behave this way, the NULL and NOT NULL
    attributes do not apply in the normal way and are ignored if
    you specify them. On the other hand, to make it easier for
    MySQL clients to use TIMESTAMP columns, the server reports
    that such columns can be assigned NULL values (which is
    true), even though TIMESTAMP never actually contains a NULL
    value. You can see this when you use DESCRIBE tbl_name to
    get a description of your table.
    -------
    So maybe phpMyAdmin should not display anything in the Null
    column since it does not make sense (no storing of NULL
    value takes place).
    =============
    Please comment.

     
  • Marc Delisle

    Marc Delisle - 2005-03-30

    Logged In: YES
    user_id=210714

    Problem #5 fixed, please confirm.
    Also, can you please react to my previous message about NULLs ?

     
  • Marc Delisle

    Marc Delisle - 2005-04-01

    Logged In: YES
    user_id=210714

    problem 1: MySQL still detects the field as NULL
    problem 2: already resolved
    problem 3: fixed
    problem 4: fixed
    problem 5: fixed
    problem 6: fixed

     
  • Marc Delisle

    Marc Delisle - 2005-04-01
    • summary: Problems with the new TIMESTAMP attributes --> (in 2.6.2) Problems with the new TIMESTAMP attributes
    • priority: 5 --> 1
    • status: open-duplicate --> open-fixed
     
  • Hervé Pagčs

    Hervé Pagčs - 2005-04-05

    Logged In: YES
    user_id=1239484

    Hi Marc and sorry for the late answer!

    MySQL server 4.1.7
    PHP 5.0.3
    phpMyAdmin 2.6.2-rc1

    problem 1: MySQL still detects the field as NULL

    I confirm that. This still has the unfortunate
    side effect to make PMA alter a table when one
    goes to the table structure editing form and
    save without making any change...

    problem 2: already resolved

    problem 3: fixed

    YES

    problem 4: fixed

    YES

    problem 5: fixed

    YES

    problem 6: fixed

    YES

    New problem (#7):

    Try to edit the `blabla` attributes by using the
    table structure editing form. The following PHP warning
    occurs:

    Notice: Undefined variable:
    submit_default_current_timestamp in
    /var/www/html/phpMyAdmin-2.6.2-rc1/tbl_properties.inc.php
    on line 347

    Then make some change (for example change it to "not null"
    and give
    it the "aa" default value). You now get the following PHP
    warnings:

    Notice: Undefined variable: field_default_current_timestamp in
    /var/www/html/ phpMyAdmin-2.6.2-rc1/tbl_alter.php on line 69

    Notice: Undefined variable: field_comments in
    /var/www/html/phpMyAdmin-2.6.2-rc1/tbl_alter.php on line 69

    Also, it should be possible to give `blabla` the default value
    'NULL' (here the quotes are important because I want the default
    value to be the string 'NULL'). But PMA doesn't let me do
    that :-(
    Maybe you consider a user wich such desire is insane?
    But why then have you choosen to implement a checkbox in the
    case
    of a timestamp col? Why not handle the CURRENT_TIMESTAMP default
    value the same way you handle the NULL default value? (i.e if
    PMA sees the CURRENT_TIMESTAMP default value for a timestamp,
    then it simply does not quote it before to put it in the SQL
    query).
    In my opinion, this would indeed make much more sens here
    because
    a "sane' user should never have the need to set a timestamp
    default value to the string 'CURRENT_TIMESTAMP' (this string
    is not a valid date).
    In the other hand, I consider that it's perfectly legitimate
    for a user to want to set a varchar default value to the
    string 'NULL' (it should be possible for NULL varchars and
    NOT NULL varchars).

    Finally, regarding your previous message about timestamp
    and NULLs, I have to admit that I'm not fond of MySQL
    online documentation :-(
    So I did the following test (I run MySQL 4.1.7):

    First create the `test3` table with:

    CREATE TABLE `test3` (
    `blabla` varchar(255),
    `ts1` timestamp NOT NULL,
    `ts2` timestamp NULL,
    `ts3` timestamp NOT NULL,
    `ts4` timestamp NULL
    );

    DESCRIBE `test3` gives:

    +--------+--------------+------+-----+---------------------+-------+
    | Field | Type | Null | Key | Default |
    Extra |
    +--------+--------------+------+-----+---------------------+-------+
    | blabla | varchar(255) | YES | | NULL |
    |
    | ts1 | timestamp | YES | | CURRENT_TIMESTAMP |
    |
    | ts2 | timestamp | YES | | NULL |
    |
    | ts3 | timestamp | YES | | 0000-00-00 00:00:00 |
    |
    | ts4 | timestamp | YES | | NULL |
    |
    +--------+--------------+------+-----+---------------------+-------+

    But SHOW CREATE TABLE `test3` gives:

    CREATE TABLE `test3` (
    `blabla` varchar(255) default NULL,
    `ts1` timestamp NOT NULL default CURRENT_TIMESTAMP on
    update CURRENT_TIMESTAMP,
    `ts2` timestamp NULL default NULL,
    `ts3` timestamp NOT NULL default '0000-00-00 00:00:00',
    `ts4` timestamp NULL default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1

    And if you try to INSERT a line with no values at all:

    INSERT INTO `test3` () VALUES ();

    then SELECT * FROM `test3` gives:

    +--------+---------------------+------+---------------------+------+
    | blabla | ts1 | ts2 | ts3
    | ts4 |
    +--------+---------------------+------+---------------------+------+
    | NULL | 2005-04-05 06:44:13 | NULL | 0000-00-00 00:00:00
    | NULL |
    +--------+---------------------+------+---------------------+------+

    So YES there is a difference between a NULL and a NOT NULL
    timestamp. So YES a PMA should display it.
    Cheers,

    Herv

     
  • Marc Delisle

    Marc Delisle - 2005-04-07
    • priority: 1 --> 5
     
  • Marc Delisle

    Marc Delisle - 2005-04-13
    • status: open-fixed --> open
     
  • Marc Delisle

    Marc Delisle - 2005-04-13
    • summary: (in 2.6.2) Problems with the new TIMESTAMP attributes --> Problems with the new TIMESTAMP attributes
     
  • Marc Delisle

    Marc Delisle - 2005-04-13

    Logged In: YES
    user_id=210714

    Herv,
    the problem #7 (2 undefined variables) should be fixed now
    in the CVS version (HEAD or QA_2_6_2).

    Still thinking about the NULL issue.

     
  • Marc Delisle

    Marc Delisle - 2005-04-15
    • summary: Problems with the new TIMESTAMP attributes --> new TIMESTAMP attributes and NULL detection
     
  • Marc Delisle

    Marc Delisle - 2005-05-22

    Logged In: YES
    user_id=210714

    Herv,
    the last problem should be fixed now. I attach here the new
    versions that I merged in CVS, please confirm the
    correction. Merci.

     
  • Marc Delisle

    Marc Delisle - 2005-05-22
    • priority: 5 --> 3
     
  • Marc Delisle

    Marc Delisle - 2005-05-22

    last corrections

     
  • Marc Delisle

    Marc Delisle - 2005-05-25
    • summary: new TIMESTAMP attributes and NULL detection --> (in 2.6.3) new TIMESTAMP attributes and NULL detection
    • priority: 3 --> 1
    • status: open --> open-fixed
     
  • Marc Delisle

    Marc Delisle - 2005-07-03
    • status: open-fixed --> closed-fixed
     
  • Michal Čihař

    Michal Čihař - 2013-06-11
    • Status: closed-fixed --> invalid
     

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

Sign up for the SourceForge newsletter:





No, thanks