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
_______________________________________________ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
