Ok I just executed one of the problematic table creation statements that
Sewmini has encountered on my own MySQL 5.7.12 distribution,
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 CURRENT_TIMESTAMP,
REG_LAST_UPDATOR VARCHAR(31),
REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP,
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;
This gets created without an issue for me
Even a table like,
CREATE TABLE IF NOT EXISTS IDN_STS_STORE (
ID INTEGER AUTO_INCREMENT,
TOKEN_ID VARCHAR(255) NOT NULL,
TOKEN_CONTENT BLOB(1024) NOT NULL,
CREATE_DATE TIMESTAMP NOT NULL,
EXPIRE_DATE TIMESTAMP NOT NULL,
STATE INTEGER DEFAULT 0,
PRIMARY KEY (ID)
)ENGINE INNODB;
which simply uses NOT NULL for TIMESTAMP gets created without an issue for
me.
My mode is same as Sewmini's,
mysql> SELECT @@SESSION.sql_mode AS MODE;
+-------------------------------------------------------------------------------------------------------------------------------------------+
|
MODE
|
+-------------------------------------------------------------------------------------------------------------------------------------------+
|
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
+-------------------------------------------------------------------------------------------------------------------------------------------+
So not sure why this is only failing for Sewmini.
On 10 June 2016 at 11:23, Sewmini Jayaweera <sewm...@wso2.com> wrote:
> Hi Uvindra,
>
> I still could not get the issue resolved even after adding 'DEFAULT
> CURRENT_TIMESTAMP' in TIMESTAMP columns which had given 'DEFUALT 0'
>
> I have attached edited script (is510/dbscripts/mysql.sql) and the errors I
> got when sourcing the script. Could you please have a look at this.
>
> Below is the MySQL mode in my server.
>
>
> +-------------------------------------------------------------------------------------------------------------------------------------------+
>
> | @@sql_mode
> |
>
>
> +-------------------------------------------------------------------------------------------------------------------------------------------+
>
> |
> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
> |
>
>
> +-------------------------------------------------------------------------------------------------------------------------------------------+
>
> Regards,
>
> Sewmini
>
> Sewmini Jayaweera
> *Software Engineer - QA Team*
> Mobile: +94 (0) 773 381 250
> sewm...@wso2.com
>
> On Thu, Jun 9, 2016 at 10:56 PM, Uvindra Dias Jayasinha <uvin...@wso2.com>
> wrote:
>
>> Note that the above feature(auto initialize time stamp columns) was
>> introduced in MYSQL 5.6.5[1], so this kind of change to the scripts will
>> not be compatible with older MySQL versions.
>>
>>
>> [1]
>> http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html#idm139923373307456
>>
>> On 9 June 2016 at 22:50, Uvindra Dias Jayasinha <uvin...@wso2.com> wrote:
>>
>>> Lets just try DEFAULT CURRENT_TIMESTAMP for all TIMESTAMP fields.
>>>
>>> Avoid using ON UPDATE CURRENT_TIMESTAMP, our code already explicitly
>>> updates time stamp fields where required so we do not want MySQL to do this
>>> for us.
>>>
>>> On 9 June 2016 at 22:43, Sewmini Jayaweera <sewm...@wso2.com> wrote:
>>>
>>>> [Adding Uvindra and Maduranga]
>>>>
>>>> Sewmini Jayaweera
>>>> *Software Engineer - QA Team*
>>>> Mobile: +94 (0) 773 381 250
>>>> sewm...@wso2.com
>>>>
>>>> On Thu, Jun 9, 2016 at 12:16 PM, Sewmini Jayaweera <sewm...@wso2.com>
>>>> wrote:
>>>>
>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>>
>>> --
>>> Regards,
>>> Uvindra
>>>
>>> Mobile: 777733962
>>>
>>
>>
>>
>> --
>> Regards,
>> Uvindra
>>
>> Mobile: 777733962
>>
>
>
--
Regards,
Uvindra
Mobile: 777733962
_______________________________________________
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
