installed the dev version findings

[durangod]

Hi,

 

Ok im not quite ready to call these issues but they are findings. I know how to install WHMCS and cant think of a reason that so many tables would be listed on the install report as failing or not existing.

 

So what i will do is just list them here as i find them. If there is no issue on your side then fine, if so then at least i did good by telling you. I just feel bettery saying something than not..

 

All of these i will be adding to the DB manually since they failed in the install. I will just list the results fail and why or pass.

 

 

 

1. tblactivitylog fail

 

CREATE TABLE IF NOT EXISTS `tblactivitylog` (

`id` int(1) unsigned zerofill NOT NULL auto_increment,

`date` datetime NOT NULL default '0000-00-00 00:00:00',

`description` text COLLATE utf8_general_ci NOT NULL,

`user` text COLLATE utf8_general_ci NOT NULL,

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'date'

 

 

I believe the default should be default NULL on that instead

 

- - - Updated - - -

 

2. tbladdons failed at install but passed on manual install

 

CREATE TABLE IF NOT EXISTS `tbladdons` (

`id` int(1) unsigned zerofill NOT NULL auto_increment,

`packages` text COLLATE utf8_general_ci NOT NULL,

`name` text COLLATE utf8_general_ci NOT NULL,

`description` text COLLATE utf8_general_ci NOT NULL,

`recurring` decimal(10,2) NOT NULL default '0.00',

`setupfee` decimal(10,2) NOT NULL default '0.00',

`billingcycle` text COLLATE utf8_general_ci NOT NULL,

`showorder` text COLLATE utf8_general_ci NOT NULL,

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

 

- - - Updated - - -

 

3. tbladminlog failed

 

 

CREATE TABLE IF NOT EXISTS `tbladminlog` (

`id` int(10) unsigned zerofill NOT NULL auto_increment,

`adminusername` text COLLATE utf8_general_ci NOT NULL,

`logintime` datetime NOT NULL default '0000-00-00 00:00:00',

`logouttime` datetime NOT NULL default '0000-00-00 00:00:00',

`ipaddress` text COLLATE utf8_general_ci NOT NULL,

`sessionid` text COLLATE utf8_general_ci NOT NULL,

`lastvisit` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'logintime'

 

 

 

again i think it should be default NULL for that as well as logouttime and lastvisit also

 

- - - Updated - - -

 

3. tblaffiliateaccounts fail

 

CREATE TABLE IF NOT EXISTS `tblaffiliatesaccounts` (

`id` int(1) unsigned zerofill NOT NULL auto_increment,

`affiliateid` text COLLATE utf8_general_ci NOT NULL,

`domain` text COLLATE utf8_general_ci NOT NULL,

`package` text COLLATE utf8_general_ci NOT NULL,

`billingcycle` text COLLATE utf8_general_ci NOT NULL,

`regdate` date default NULL,

`amount` decimal(10,2) NOT NULL default '0.00',

`commission` decimal(10,2) NOT NULL,

`lastpaid` date NOT NULL default '0000-00-00',

`relid` int(10) unsigned zerofill NOT NULL default '0000000000',

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'lastpaid'

 

 

 

they seem to be date related, again probably default NULL i dont think mysql will let you use default that way or from a function like NOW() or DATE() either.. default NULL is prob preferrable.

 

- - - Updated - - -

 

4. tblcancelrequests fail

 

CREATE TABLE IF NOT EXISTS `tblcancelrequests` (

`id` int(10) unsigned zerofill NOT NULL auto_increment,

`date` datetime NOT NULL default '0000-00-00 00:00:00',

`relid` int(10) unsigned zerofill NOT NULL default '0000000000',

`reason` text COLLATE utf8_general_ci NOT NULL,

`type` text COLLATE utf8_general_ci NOT NULL,

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'date'

 

 

 

ill just say dito lol

 

- - - Updated - - -

 

5. tbldomains fail

 

CREATE TABLE IF NOT EXISTS `tbldomains` (

`id` int(10) unsigned zerofill NOT NULL auto_increment,

`userid` int(10) unsigned zerofill NOT NULL default '0000000000',

`orderid` int(1) NOT NULL,

`registrationdate` date NOT NULL,

`domain` text COLLATE utf8_general_ci NOT NULL,

`firstpaymentamount` decimal(10,2) NOT NULL default '0.00',

`recurringamount` decimal(10,2) NOT NULL,

`registrar` text COLLATE utf8_general_ci NOT NULL,

`registrationperiod` int(1) NOT NULL default '1',

`expirydate` date default NULL,

`subscriptionid` text COLLATE utf8_general_ci NOT NULL,

`status` text COLLATE utf8_general_ci NOT NULL,

`nextduedate` date NOT NULL default '0000-00-00',

`nextinvoicedate` date NOT NULL,

`additionalnotes` text COLLATE utf8_general_ci NOT NULL,

`paymentmethod` text COLLATE utf8_general_ci NOT NULL,

`urlforwarding` text COLLATE utf8_general_ci NOT NULL,

`emailforwarding` text COLLATE utf8_general_ci NOT NULL,

PRIMARY KEY (`id`)

) D[...]

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'nextduedate'

 

 

 

dito...

 

- - - Updated - - -

 

6. tblgatewaylog fail

 

 

CREATE TABLE IF NOT EXISTS `tblgatewaylog` (

`id` int(10) unsigned zerofill NOT NULL auto_increment,

`date` datetime NOT NULL default '0000-00-00 00:00:00',

`gateway` text COLLATE utf8_general_ci NOT NULL,

`data` text COLLATE utf8_general_ci NOT NULL,

`result` text COLLATE utf8_general_ci NOT NULL,

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'date'

 

 

 

dito

 

- - - Updated - - -

 

7. tblhosting fail

 

CREATE TABLE IF NOT EXISTS `tblhosting` (

`id` int(10) unsigned zerofill NOT NULL auto_increment,

`userid` int(10) unsigned zerofill NOT NULL default '0000000000',

`orderid` int(1) NOT NULL,

`regdate` date NOT NULL,

`domain` text COLLATE utf8_general_ci NOT NULL,

`server` text COLLATE utf8_general_ci NOT NULL,

`paymentmethod` text COLLATE utf8_general_ci NOT NULL,

`firstpaymentamount` decimal(10,2) NOT NULL default '0.00',

`amount` decimal(10,2) NOT NULL default '0.00',

`billingcycle` text COLLATE utf8_general_ci NOT NULL,

`nextduedate` date default NULL,

`nextinvoicedate` date NOT NULL,

`domainstatus` text COLLATE utf8_general_ci NOT NULL,

`username` text COLLATE utf8_general_ci NOT NULL,

`password` text COLLATE utf8_general_ci NOT NULL,

`notes` text COLLATE utf8_general_ci NOT NULL,

`subscriptionid` text COLLATE utf8_general_ci NOT NULL,

`packageid` int(10) unsigned zerofill NOT NULL default '0000000000',

`overideautosuspend` t[...]

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'lastupdate'

 

 

 

ditto...

 

- - - Updated - - -

 

8. tblhostingaddons fail

 

 

 

CREATE TABLE IF NOT EXISTS `tblhostingaddons` (

`id` int(10) unsigned zerofill NOT NULL auto_increment,

`orderid` int(1) NOT NULL,

`hostingid` int(10) unsigned zerofill NOT NULL default '0000000000',

`name` text COLLATE utf8_general_ci NOT NULL,

`setupfee` decimal(10,2) NOT NULL default '0.00',

`recurring` decimal(10,2) NOT NULL default '0.00',

`billingcycle` text COLLATE utf8_general_ci NOT NULL,

`status` text COLLATE utf8_general_ci NOT NULL,

`regdate` date NOT NULL default '0000-00-00',

`nextduedate` date default NULL,

`nextinvoicedate` date NOT NULL,

`paymentmethod` text COLLATE utf8_general_ci NOT NULL,

`subscriptionid` text COLLATE utf8_general_ci NOT NULL,

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'regdate'

 

 

dito..

[durangod]

9. tblinvoices fail

 

CREATE TABLE IF NOT EXISTS `tblinvoices` (

`id` int(1) unsigned zerofill NOT NULL auto_increment,

`date` date default NULL,

`duedate` date default NULL,

`datepaid` datetime NOT NULL default '0000-00-00 00:00:00',

`userid` int(10) unsigned zerofill NOT NULL default '0000000000',

`subtotal` decimal(10,2) NOT NULL,

`credit` decimal(10,2) NOT NULL,

`tax` decimal(10,2) NOT NULL,

`total` decimal(10,2) NOT NULL default '0.00',

`taxrate` decimal(10,2) NOT NULL,

`status` text COLLATE utf8_general_ci NOT NULL,

`randomstring` text COLLATE utf8_general_ci NOT NULL,

`paymentmethod` text COLLATE utf8_general_ci NOT NULL,

`notes` text COLLATE utf8_general_ci NOT NULL,

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'datepaid'

 

 

dito...

 

- - - Updated - - -

 

10. tblreselleraccountsetup pass on manual load..

 

passed

 

- - - Updated - - -

 

11. tableticketnotes fail

 

CREATE TABLE IF NOT EXISTS `tblticketnotes` (

`id` int(10) unsigned zerofill NOT NULL auto_increment,

`admin` text COLLATE utf8_general_ci NOT NULL,

`date` datetime NOT NULL default '0000-00-00 00:00:00',

`message` text COLLATE utf8_general_ci NOT NULL,

`ticketid` int(10) unsigned zerofill NOT NULL default '0000000000',

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'date'

 

 

 

dito..

 

- - - Updated - - -

 

12. tbltickets fail

 

 

CREATE TABLE IF NOT EXISTS `tbltickets` (

`id` int(10) unsigned zerofill NOT NULL auto_increment,

`tid` int(6) NOT NULL default '0',

`did` int(3) unsigned zerofill NOT NULL default '000',

`userid` int(10) unsigned zerofill NOT NULL default '0000000000',

`name` text COLLATE utf8_general_ci NOT NULL,

`email` text COLLATE utf8_general_ci NOT NULL,

`c` text COLLATE utf8_general_ci NOT NULL,

`date` datetime NOT NULL default '0000-00-00 00:00:00',

`title` text COLLATE utf8_general_ci NOT NULL,

`message` text COLLATE utf8_general_ci NOT NULL,

`status` text COLLATE utf8_general_ci NOT NULL,

`urgency` text COLLATE utf8_general_ci NOT NULL,

`admin` text COLLATE utf8_general_ci NOT NULL,

`attachment` text COLLATE utf8_general_ci NOT NULL,

`lastreply` datetime NOT NULL,

`flag` int(1) NOT NULL,

`clientunread` int(1) NOT NULL,

`adminunread` int(1) NOT NULL,

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

 

 

 

MySQL said: Documentation

#1067 - Invalid default value for 'date'

 

 

dito..

 

- - - Updated - - -

 

ok that seems to be it for now... on this part.... now i just need to compare what i have to what i should have..

 

- - - Updated - - -

 

i am assuming i just need to run the 539sql update and not have to go thru all those sql files before it? Is that correct?

 

also i dont suppose any of the tables that failed had predata loaded during the install?

 

- - - Updated - - -

 

well thats not right... i have 91 tables in dev, and 111 in production... i knew it would come to this ...

 

- - - Updated - - -

 

ok maybe not so bad, i have 15 module tables on the production so with that im only missing 5 tables in dev

 

update: ok now we are down to 3 - opensrs sync and opensrs ssl are addons, so now to figure out which 3 lol

 

update 3 coupon tables so we are even now... all is well..

Edited August 29, 2014 by durangod

[durangod]

Im guessing that this must be just me and not a real issue since no reply in 6 days

[mbit]

Well... probably

[durangod]

well i had to reinstall dev and same issue. i know there are some very particular issues with the lastest mysql when it comes to default values for dates and using timestamps.

 

this time when i installed i got this

 

 

Errors Occurred

 

Please open a ticket with the debug information below for support

 

File: install.sql

Line 2 - Invalid default value for 'date'

Line 4 - Invalid default value for 'logintime'

Line 7 - Invalid default value for 'lastpaid'

Line 14 - Invalid default value for 'date'

Line 21 - Invalid default value for 'nextduedate'

Line 28 - Invalid default value for 'date'

Line 29 - Invalid default value for 'lastupdate'

Line 30 - Invalid default value for 'regdate'

Line 33 - Invalid default value for 'datepaid'

Line 51 - Invalid default value for 'date'

Line 55 - Invalid default value for 'date'

 

 

since they will prob tell me nothing is wrong its just me, especially since they never even recognized this post in the first place. Then ill forego the ticket and just fix it as i did before.

 

They must think i install this unlike any other install i have ever done.

[mbit]

Maybe you have NO_ZERO_DATE set on your database?

[durangod]

Could be but it does not affect my other installs server wide, so it does not seem to be a global issue and this was a fresh install both times. The other thing is that mysql says you can only have one default set to current_timestamp in a table so with tables with multiple date fields, how do they expect anyone to do this. '0' does not work because its not a valid date, the only option would be to make one current_timestamp and the other one default null. Not even 0000-00-00 works in most cases now which i believe is the issue here.

 

- - - Updated - - -

 

Here they suggest using a trigger to do this, but this is not for me to do, this is for whmcs to do.

 

http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column

[mbit]

My guess is you have both no zero date mode and strict mode enabled.

 

No zero mode enabled should only give you warning about zero datetime. If you get full errors then you probably have both strict mode on and no zero date mode on.

 

Set it up

[durangod]

thanks i will check that out as well..

[durangod]

thanks mbit,

 

Both are in there no zero in date and no zero date logically i guess no zero date means no default 0 and no zero in date means no 02 04 no leading zeros in day or month. Ill eat crow on this one as i did tell the tech awhile back when they ask me if i wanted to harden this a bit and i sayd yes, they sent me the notes of what they did but i didnt notice this setting. oops

 

i suppose its better to leave the no zero in date but remove the no zero date

[TekStorm Inc - James]

NO_ZERO_DATE

 

The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

 

NO_ZERO_IN_DATE

 

The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'. To control whether the server permits '0000-00-00', use the NO_ZERO_DATE mode.) The effect of NO_ZERO_IN_DATE also depends on whether strict SQL mode is enabled.

[durangod]

Thanks james