Re: DB help needed to solve issue with pimitemtable

On dinsdag 11 december 2018 21:34:26 CET, Pablo Sanchez wrote:
>>>   `atime` timestamp NOT NULL DEFAULT current_timestamp(),
>> 
>>   `atime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
>> 
>> I guess we see here what the problem is. How should I change 
>> this. How did this come to be?
>
> Yes, that's the problem.  I appears that the default is being 
> used as a NULL value.  ;)
>
> You could try the following:
>
>    SELECT COUNT(*) FROM pimitemtable
>    WHERE atime = '0000-00-00 00:00:00';

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set, 2 warnings (0,02 sec)

> Assuming there are zero rows, change the default to current_timestamp():
>
>    ALTER TABLE pimitemtable ALTER atime
>    SET DEFAULT current_timestamp();

mysql> ALTER TABLE pimitemtable ALTER atime SET DEFAULT 
current_timestamp();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'current_timestamp()' at line 1

So based on the table schema, I tried

mysql> ALTER TABLE pimitemtable ALTER atime SET DEFAULT CURRENT_TIMESTAMP;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'CURRENT_TIMESTAMP' at line 1

So based on Server version: 5.7.24 Gentoo Linux mysql-5.7.24
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
I tried

mysql> ALTER TABLE pimitemtable ALTER atime SET DEFAULT NOW();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'NOW()' at line 1

?

> After you do the above, try the original update.  If all works 
> well, have a celebratory beverage.

I'm having that anyway.


Cheers,

Erik