Re: DB help needed to solve issue with pimitemtable

[ Comments below, in-line ]


On Tue, 11 Dec 2018 21:18:46 +0100, Erik Quaeghebeur wrote:

> Dear Pablo,

Hey Erik,

> Great to have you respond so quickly; thanks!

You are welcome.  I thought it was slow!  :D

> On dinsdag 11 december 2018 20:22:37 CET, Pablo Sanchez wrote:
>>
>> When are you getting the error?
> 
> When I tried to alter the length of the remoteId column.

I just checked, MySQL makes a copy when it is doing an ALTER TABLE. 
Knowing that sheds light in the error.  ;)

>> In MySQL, you can show the table's DDL:
>>
>>     show create table pimitemtable;
> 
> Cool. Below, I list those columns that differ (even cosmetically):

I hope you don't mind terribly, anything cosmetic I'm going to ignore.  ;)


>>   `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';

You might get an error but try it anyway.

Assuming there are zero rows, change the default to current_timestamp():

    ALTER TABLE pimitemtable ALTER atime
    SET DEFAULT current_timestamp();

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

Cheers,

--
Pablo Sanchez - Blueoak Database Engineering, Inc
Ph:    819.459.1926        iNum:  883.5100.0990.1054