Hi All,
I get "ERROR 1067 (42000): Invalid default value for
'REG_LAST_UPDATED_TIME' error when sourcing
'<wso2is-5.1.0>/dbscripts/mysql.sql, even after removing 'DEFAULT 0'
Please find further information below.
*1. Issue description*
CREATE TABLE IF NOT EXISTS REG_RESOURCE(
REG_PATH_ID INTEGER NOT NULL,
REG_NAME VARCHAR(256),
REG_VERSION INTEGER NOT NULL AUTO_INCREMENT,
REG_MEDIA_TYPE VARCHAR(500),
REG_CREATOR VARCHAR(31) NOT NULL,
REG_CREATED_TIME TIMESTAMP NOT NULL,
REG_LAST_UPDATOR VARCHAR(31),
REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL,
REG_DESCRIPTION VARCHAR(1000),
REG_CONTENT_ID INTEGER,
REG_TENANT_ID INTEGER DEFAULT 0,
REG_UUID VARCHAR(100) NOT NULL,
CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION,
REG_TENANT_ID)
)ENGINE INNODB;
When creating above table "ERROR 1067 (42000): Invalid default value for
'REG_LAST_UPDATED_TIME' error occurred and table didn't get created. Please
note that this error shows *only when there are two timestamp columns*
(REG_CREATED_TIME
and REG_LAST_UPDATED_TIME) in the table.
*2. What happens when there are two time stamp columns?*
According to [1] when we have two timestamp columns not declared an
explicit 'DEFAULT' or 'ON UPDATE' clause, first column is automatically
assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP
attributes, while the second one is assigned the '0000-00-00 00:00:00' (the
“zero” timestamp).
*3. Why are we getting the Error explained In issue description?*
When 'strict SQL mode' and ' NO_ZERO_DATE' mode is enabled in MySQL server
the “zero” timestamp is not allowed and it gives an error [2]. In MySQL 5.7
by default 'NO_ZERO_DATE' mode is enabled.
*4.Question*
As a fix I found below
1. Setting MySQL mode to 'ALLOW_INVALID_DATES' [3].
EX: SET SQL_MODE='ALLOW_INVALID_DATES';
*Is it okay to use this or is there a much more appropriate way of doing
that? *
[1].
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
[2].
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
[3].
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_allow_invalid_dates
Thank You,
Best Regards,
Sewmini.
Sewmini Jayaweera
*Software Engineer - QA Team*
Mobile: +94 (0) 773 381 250
sewm...@wso2.com
On Wed, May 25, 2016 at 12:13 PM, Kishanthan Thangarajah <
kishant...@wso2.com> wrote:
> We can verify and fix this for 4.4.6.
>
> On Tue, May 24, 2016 at 3:17 PM, Nuwan Dias <nuw...@wso2.com> wrote:
>
>> This issue is still open.
>>
>> If we don't fix this before kernel 4.4.6, none of our new products are
>> going to be compatible with MySQL 5.7. Which would be a major limitation in
>> the platform.
>>
>> Thanks,
>> NuwanD.
>>
>> On Tue, May 10, 2016 at 4:15 PM, Chandana Napagoda <chand...@wso2.com>
>> wrote:
>>
>>> Hi Janaka,
>>>
>>> Please note that, this fix will be available in the next kernel release.
>>>
>>> Regards,
>>> Chandana
>>> On May 10, 2016 3:14 PM, "Thilini Cooray" <thili...@wso2.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> A public JIRA is raised in [1].
>>>>
>>>> [1] https://wso2.org/jira/browse/REGISTRY-3610
>>>>
>>>> Thanks.
>>>>
>>>> On Tue, May 10, 2016 at 12:11 PM, Janaka Ranabahu <jan...@wso2.com>
>>>> wrote:
>>>>
>>>>> Hi Thushara
>>>>>
>>>>> On Tue, May 10, 2016 at 12:06 PM, Thushara Ranawaka <
>>>>> thusha...@wso2.com> wrote:
>>>>>
>>>>>> Hi Thilini,
>>>>>>
>>>>>> AFAIU you workaround need to be updated with the current
>>>>>> timestamp[1][2]. Setting default value to zero is not nice. You might
>>>>>> have
>>>>>> to figure out the correct syntax for other DB types and test against
>>>>>> them.
>>>>>>
>>>>> What Thilini is pointing is an issue in the Registry database
>>>>> creation script. Hence we will create a public JIRA and assign it to you
>>>>> guys. Please fix them.
>>>>>
>>>>> We can not modify the Registry database creation script since API
>>>>> Manager would not capture the complete scenarios of G-Reg.
>>>>>
>>>>> Thanks,
>>>>> Janaka
>>>>>
>>>>>
>>>>>>
>>>>>>
>>>>>> [1] - DEFAULT CURRENT_TIMESTAMP
>>>>>> [2] -
>>>>>> http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column
>>>>>>
>>>>>> Thanks,
>>>>>> Thushara.
>>>>>>
>>>>>> On Tue, May 10, 2016 at 11:52 AM, Thilini Cooray <thili...@wso2.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Hi GReg team,
>>>>>>>
>>>>>>> We encounter following issue [1] while testing API Manager with
>>>>>>> MySQL 5.7
>>>>>>> MySQL 5.7 has enabled NO_ZERO_IN_DATE, NO_ZERO_DATE policies by
>>>>>>> default. Therefore it does not allow 0 as a valid default value for
>>>>>>> date/time.
>>>>>>>
>>>>>>> As a workaround we can set MySQL server to accept 0 changing the
>>>>>>> default server settings if the database owner agrees on it.
>>>>>>> [2] contains the related table creation script.
>>>>>>>
>>>>>>> Appreciate your feedback on a fix for this.
>>>>>>>
>>>>>>> [1] https://wso2.org/jira/browse/APIMANAGER-4645
>>>>>>>
>>>>>>> [2] CREATE TABLE IF NOT EXISTS REG_RESOURCE (
>>>>>>> REG_PATH_ID INTEGER NOT NULL,
>>>>>>> REG_NAME VARCHAR(256),
>>>>>>> REG_VERSION INTEGER NOT NULL AUTO_INCREMENT,
>>>>>>> REG_MEDIA_TYPE VARCHAR(500),
>>>>>>> REG_CREATOR VARCHAR(31) NOT NULL,
>>>>>>> REG_CREATED_TIME TIMESTAMP NOT NULL *DEFAULT 0*,
>>>>>>> REG_LAST_UPDATOR VARCHAR(31),
>>>>>>> REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL *DEFAULT 0*,
>>>>>>> REG_DESCRIPTION VARCHAR(1000),
>>>>>>> REG_CONTENT_ID INTEGER,
>>>>>>> REG_TENANT_ID INTEGER DEFAULT 0,
>>>>>>> REG_UUID VARCHAR(100) NOT NULL,
>>>>>>> CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION,
>>>>>>> REG_TENANT_ID)
>>>>>>> )ENGINE INNODB;
>>>>>>>
>>>>>>> Thanks.
>>>>>>>
>>>>>>> --
>>>>>>> Best Regards,
>>>>>>>
>>>>>>> *Thilini Cooray*
>>>>>>> Software Engineer
>>>>>>> Mobile : +94 (0) 774 570 112 <%2B94%20%280%29%20773%20451194>
>>>>>>> E-mail : thili...@wso2.com
>>>>>>>
>>>>>>> WSO2 Inc. www.wso2.com
>>>>>>> lean.enterprise.middleware
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> Dev mailing list
>>>>>>> Dev@wso2.org
>>>>>>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Thanks,
>>>>>> Thushara Kasun Ranawaka
>>>>>> Software Engineer
>>>>>> WSO2 Inc.; <http://www.wso2.com>
>>>>>> lean.enterprise.middleware
>>>>>> Mobile : *+94 (0) 773438949 <%2B94%20%280%29%20773438949>*
>>>>>> *thusha...@wso2.com <thusha...@wso2.com>*
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> *Janaka Ranabahu*
>>>>> Associate Technical Lead, WSO2 Inc.
>>>>> http://wso2.com
>>>>>
>>>>>
>>>>> *E-mail: jan...@wso2.com <http://wso2.com>**M: **+94 718370861
>>>>> <%2B94%20718370861>*
>>>>>
>>>>> Lean . Enterprise . Middleware
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Best Regards,
>>>>
>>>> *Thilini Cooray*
>>>> Software Engineer
>>>> Mobile : +94 (0) 774 570 112 <%2B94%20%280%29%20773%20451194>
>>>> E-mail : thili...@wso2.com
>>>>
>>>> WSO2 Inc. www.wso2.com
>>>> lean.enterprise.middleware
>>>>
>>>
>>
>>
>> --
>> Nuwan Dias
>>
>> Technical Lead - WSO2, Inc. http://wso2.com
>> email : nuw...@wso2.com
>> Phone : +94 777 775 729
>>
>
>
>
> --
> *Kishanthan Thangarajah*
> Associate Technical Lead,
> Platform Technologies Team,
> WSO2, Inc.
> lean.enterprise.middleware
>
> Mobile - +94773426635
> Blog - *http://kishanthan.wordpress.com <http://kishanthan.wordpress.com>*
> Twitter - *http://twitter.com/kishanthan <http://twitter.com/kishanthan>*
>
> _______________________________________________
> Dev mailing list
> Dev@wso2.org
> http://wso2.org/cgi-bin/mailman/listinfo/dev
>
>
_______________________________________________
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
