New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Missing values on modifying fields with CURRENT_TIME default and 'on update CURRENT_TIME()' attribute #13347

Closed
shtse8 opened this Issue Jun 1, 2017 · 3 comments

Comments

Projects
None yet
4 participants
@shtse8
Contributor

shtse8 commented Jun 1, 2017

Steps to reproduce

  1. alter a field to TIMESTAMP and then set CURRENT_TIMESTAMP as default and on update CURRENT_TIMESTAMP attribute
  2. go to modify it
  3. you will see the default value becomes a string current_timestamp() and the attribute has gone.
  4. once you save the modification, the attribute is gone.

Expected behaviour

The CURRENT_TIMESTAMP should be set in the default.
The attribute should be set on update CURRENT_TIMESTAMP.

Actual behaviour

The default value becomes As defined and current_timestamp.
The attribute becomes empty.

Server configuration

Operating system:
Ubuntu 16.04 LTS

Web server:
nginx/1.13.0

Database:
10.2.6-MariaDB-10.2.6+maria~xenial - mariadb.org binary distribution

PHP version:
PHP 7.1.5-1+deb.sury.org~xenial+2

phpMyAdmin version:
4.7.1

Client configuration

Browser:
Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36

Operating system:
Windows 10

@shtse8 shtse8 changed the title from Modify Fields with CURRENT_TIME default and 'on update CURRENT_TIME()' attribute to Missing values on modifying fields with CURRENT_TIME default and 'on update CURRENT_TIME()' attribute Jun 1, 2017

@lee-

This comment has been minimized.

Show comment
Hide comment
@lee-

lee- Jun 30, 2017

This is either a newly introduced bug of phpmyadmin or related to changes across database versions. The problem isn´t there with an older version of phpmyadmin and, IIRC, mysql 5.5. I´m seeing it with phpmyadmin 4.4.15.10 and mariadb 10.2.6.

Related is that when you use the insert option of phpmyadmin, the field to enter a date is being prefilled with 'current_timestamp().0000-00-00 00:00:00'. That prevents you from doing the insert without changing the prefilled value.

There´s an odditiy with datetime fields and their defaults and attributes with mariadb:

create table tmp_test (
t1 datetime null default current_timestamp(),
t2 datetime not null default current_timestamp(),
t3 datetime on update current_timestamp(),
t4 datetime not null on update current_timestamp(),
txt text
);

insert into tmp_test (txt) values ('abc');

select * from tmp_test;
+---------------------+---------------------+------+---------------------+------+
| t1 | t2 | t3 | t4 | txt |
+---------------------+---------------------+------+---------------------+------+
| 2017-06-30 03:46:49 | 2017-06-30 03:46:49 | NULL | 0000-00-00 00:00:00 | abc |
+---------------------+---------------------+------+---------------------+------+
1 row in set (0.00 sec)

I´m guessing that phpmyadmin sees both the default '0000-00-00 00:00:00' and 'current_timestamp()' and doesn´t handle this unexpected information right.

For reference, see https://mariadb.com/kb/en/mariadb/timestamp/ . On a side note:

"NULL values will now be permitted, and therefore NULL, rather than the current timestamp, will be assigned when the column is set to NULL. Note that, if NULL is permitted, assigning NULL will set the column to the current timestamp."

How do you set such fields to NULL when setting them to NULL sets them to the current timestamp?

However:

update tmp_test set t3 = NULL;

select * from tmp_test;
+---------------------+---------------------+------+---------------------+------+
| t1 | t2 | t3 | t4 | txt |
+---------------------+---------------------+------+---------------------+------+
| 2017-06-30 03:46:49 | 2017-06-30 03:46:49 | NULL | 0000-00-00 00:00:00 | abc |
+---------------------+---------------------+------+---------------------+------+
1 row in set (0.00 sec)

According to the docs, t3 would now show the current timestamp and not NULL. That leaves the question where the bug actually is.

Please note that field t4 was not updated. Fields t3 and t4 do get updated when txt is updated to 'def'. So one could assume that when a datetime field that permits NULL is not NULL and gets updated to NULL, it is updated to the current timestamp even there is no attribute specifying this. But this is not the case when I update t3 to NULL after updating txt.

Is the documentation of mariadb wrong?

lee- commented Jun 30, 2017

This is either a newly introduced bug of phpmyadmin or related to changes across database versions. The problem isn´t there with an older version of phpmyadmin and, IIRC, mysql 5.5. I´m seeing it with phpmyadmin 4.4.15.10 and mariadb 10.2.6.

Related is that when you use the insert option of phpmyadmin, the field to enter a date is being prefilled with 'current_timestamp().0000-00-00 00:00:00'. That prevents you from doing the insert without changing the prefilled value.

There´s an odditiy with datetime fields and their defaults and attributes with mariadb:

create table tmp_test (
t1 datetime null default current_timestamp(),
t2 datetime not null default current_timestamp(),
t3 datetime on update current_timestamp(),
t4 datetime not null on update current_timestamp(),
txt text
);

insert into tmp_test (txt) values ('abc');

select * from tmp_test;
+---------------------+---------------------+------+---------------------+------+
| t1 | t2 | t3 | t4 | txt |
+---------------------+---------------------+------+---------------------+------+
| 2017-06-30 03:46:49 | 2017-06-30 03:46:49 | NULL | 0000-00-00 00:00:00 | abc |
+---------------------+---------------------+------+---------------------+------+
1 row in set (0.00 sec)

I´m guessing that phpmyadmin sees both the default '0000-00-00 00:00:00' and 'current_timestamp()' and doesn´t handle this unexpected information right.

For reference, see https://mariadb.com/kb/en/mariadb/timestamp/ . On a side note:

"NULL values will now be permitted, and therefore NULL, rather than the current timestamp, will be assigned when the column is set to NULL. Note that, if NULL is permitted, assigning NULL will set the column to the current timestamp."

How do you set such fields to NULL when setting them to NULL sets them to the current timestamp?

However:

update tmp_test set t3 = NULL;

select * from tmp_test;
+---------------------+---------------------+------+---------------------+------+
| t1 | t2 | t3 | t4 | txt |
+---------------------+---------------------+------+---------------------+------+
| 2017-06-30 03:46:49 | 2017-06-30 03:46:49 | NULL | 0000-00-00 00:00:00 | abc |
+---------------------+---------------------+------+---------------------+------+
1 row in set (0.00 sec)

According to the docs, t3 would now show the current timestamp and not NULL. That leaves the question where the bug actually is.

Please note that field t4 was not updated. Fields t3 and t4 do get updated when txt is updated to 'def'. So one could assume that when a datetime field that permits NULL is not NULL and gets updated to NULL, it is updated to the current timestamp even there is no attribute specifying this. But this is not the case when I update t3 to NULL after updating txt.

Is the documentation of mariadb wrong?

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Jul 18, 2017

Member

I think editing the table works as expected:

screenshot-2017-7-18 localhost localhost bar datetime_test phpmyadmin 4 7 3-dev

Do you see something wrong here?

As for database server behavior it's out of scope for this tracker, apparently it's corner case (I think that updates to the fields marked as ON UPDATE doesn't trigger them, but that it's just my guess).

Member

nijel commented Jul 18, 2017

I think editing the table works as expected:

screenshot-2017-7-18 localhost localhost bar datetime_test phpmyadmin 4 7 3-dev

Do you see something wrong here?

As for database server behavior it's out of scope for this tracker, apparently it's corner case (I think that updates to the fields marked as ON UPDATE doesn't trigger them, but that it's just my guess).

@nijel nijel self-assigned this Jul 18, 2017

@nijel nijel added the question label Jul 18, 2017

@nijel nijel closed this Aug 29, 2017

@shwuhk

This comment has been minimized.

Show comment
Hide comment
@shwuhk

shwuhk Oct 9, 2017

@nijel I have the same issue with MariaDB 10.3.1

shwuhk commented Oct 9, 2017

@nijel I have the same issue with MariaDB 10.3.1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment