[+Uvindra] Hi,
IMO we need to go for a solution which we do not have to push users to change their database server settings as all the users may not have direct access or their company policies may not allow such. I suppose that we can overcome this issue by removing NOT NULL attribute as we always fill these time stamp columns on every database update operation. As per the article [1], I came across another approach which we can try out just in case we will not retrieve any values for these columns. We can check the behaviour of REG_RESOURCE table when created time set to DEFAULT CURRENT_TIMESTAMP and last updated time set to ON UPDATE CURRENT_TIMESTAMP. By that AFAIU the default value of created time will be set to current time and won't get updated while last_updated_time will get updated on each record modification. [1] http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html Thanks. 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 >> >> > -- 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
