Installation fails with "Invalid default value for 'accounts_modified_date'"

[totten]

I was trying to reproduce an unrelated issue where the JIRA report incidentally involved this extension, so I installed it. The installation crashes on this error. It might be a MySQL 5.7 issue.

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => CREATE TABLE `civicrm_account_contact` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Unique Entity Setting ID',
  `contact_id` INT(10) UNSIGNED NULL DEFAULT NULL,
  `accounts_contact_id` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
  `last_sync_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the contact last synced.',
  `accounts_modified_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'When was the invoice last Altered in the accounts system.',
  `accounts_display_name` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Name from Accounts Package' COLLATE 'utf8_unicode_ci',
  `accounts_data` TEXT NULL COMMENT 'json array of data as returned from accounts system' COLLATE 'utf8_unicode_ci',
  `error_data` TEXT NULL COMMENT 'json array of error data' COLLATE 'utf8_unicode_ci',
  `plugin` VARCHAR(32) NOT NULL COMMENT 'Plugin creating the account' COLLATE 'utf8_unicode_ci',
  `connector_id` INT(11) NULL DEFAULT NULL COMMENT 'ID of connector. Relevant to connect to more than one account of the same type',
  `accounts_needs_update` TINYINT(4) NULL DEFAULT '1' COMMENT 'Include in next push to accounts',
  `do_not_sync` TINYINT(4) NULL DEFAULT '0' COMMENT 'Do Not Sync',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `account_system_id` (`accounts_contact_id`, `connector_id`, `plugin`),
  UNIQUE INDEX `contact_id_plugin` (`contact_id`, `connector_id`, `plugin`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB [nativecode=1067 ** Invalid default value for 'accounts_modified_date']

[eileenmcnaughton]

Hmm yes, that command works for me locally so I think it must be a mysql change - can you confirm your mysql version (I'm not going to dig on this too much right now but that is good info to have)

[eileenmcnaughton]

Just adding this link https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html (which seems to say you are wrong & it does work :-) but I have only really skimmed it

[totten]

Running the mysqld binary from Ubuntu 16.10 in a ramdisk configuration.

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper
$ amp sql -a
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1583
Server version: 5.7.18-0ubuntu0.16.10.1 (Ubuntu)

https://gist.github.com/totten/c51f424b848aa67dfc025e2b0940d7ff#file-system-get-json-L384

It seems there's a configuration option explicit_defaults_for_timestamp which appears to be OFF by default.

https://dev.mysql.com/doc/refman/5.6/en/server-systemvariables.html#sysvar_explicit_defaults_for_timestamp

That page says:

Depending on whether strict SQL mode or the NO_ZERO_DATE SQL mode is enabled, a default value of '0000-00-00 00:00:00' may be invalid. Be aware that the TRADITIONAL SQL mode includes strict mode and NO_ZERO_DATE. See Section 5.1.8, “Server SQL Modes”.

[kenwest]

@eileenmcnaughton I get a related issue when upgrading from 554586f to 5bae5ac

ALTER TABLE `civicrm_account_invoice` ALTER COLUMN `accounts_status_id` SET DEFAULT 0
[nativecode=1067 ** Invalid default value for 'accounts_modified_date']

It seems to be related to my SQL version and the sql_mode.

My MySQL version is 5.7.23. Here's how I worked around this issue ...

During the upgrade, when I get notification of the error, I continue the upgrade by skipping this step, as the other steps don't fail without it. Then I do the following in a MySQL client ...

SELECT @@SESSION.sql_mode session;
#
# This returns a long string of mode parameters - we just want to turn off STRICT_TRANS_TABLES
#
set session sql_mode = 'all modes except STRICT_TRANS_TABLES';
ALTER TABLE `civicrm_account_invoice` ALTER COLUMN `accounts_status_id` SET DEFAULT 0;

(@totten suggested NO_ZERO_DATE but that is now deprecated in favour of STRICT_TRANS_TABLES)

[kenwest]

@eileenmcnaughton the use of zero dates seems to becoming a bit flaky.

What do you think of replacing the test of

accounts_modified_date == '0000-00-00 00:00:00'

with

account_status_id == 0

throughout the code? The accounts_modified_date could then just default to NULL.

For invoices this seems straightforward as we get both the status and the modified date from Xero.

For contacts we'd need to add some logic to set account_status_id = 1 (say) when we get data from Xero.

[mcollopy]

I could only get this to work by editing the auto_install.sql file to set the date fields to NULL
e.g. accounts_modified_date TIMESTAMP NULL
This was after pasting the commands directly into phpMyAdmin and replicating the same error.
Default 0 was not accepted, so for now decided on NULL to be modified later.

MySQL Server version: 5.7.25-0ubuntu0.16.04.2 - (Ubuntu)

[eileenmcnaughton]

@mcollopy oK - seems like there is a mysql setting compatibility. I'm definitely open to merging a change here -probably along the lines of @kenwest's suggestion - it would need some testing though!

[mcollopy]

@eileenmcnaughton Could be to do with MySQL settings - I was also getting errors from contributions, which stopped occurring after removing NO_ZERO_IN_DATE, NO_ZERO_DATE from my.cnf, after reading this https://issues.civicrm.org/jira/browse/VOL-299

I suspect if I was to do a fresh installation it may also resolve this issue.

[mcollopy]

Confirmed - changing the sql-mode configuration as per below enabled an error free installation:
sql-mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Not sure if there's any unintended risks in doing this, but seems like a solution.

[mattwire]

I think this can be closed?

[eileenmcnaughton]

@mattwire should I maybe remove that default? It feels like it is not really correct & perhaps defaulting to NULL would be more correct?