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