2.3.1->2.5.1 fails with "Invalid default value for \'DateExpires\'"

cmroanirgo

Hi,
Using a test server, I overwrote the 2.3.1 files with the 2.5.1 files, cleared the cache & removed the settingscontroller (as per the 2.5 update README).
When I ran mydomain.com/utility/update, I got a 'something went wrong error', so I enabled DEBUG mode and re-ran it.

Any ideas on what I can do? (I'm assuming that I'd get the same error running this on the main site). The 2.3.1 install was a new install btw.

This is the DEBUG output, which seems to indicate a default value problem in the DB:

Invalid default value for 'DateExpires'
The error occurred on or near: /home/mydomain.com/public_html/library/database/class.database.php

403: // If we get here then the pdo statement prepared properly.

404: break;

405:

406: } catch (Gdn_UserException $uex) {

407: trigger_error($uex->getMessage(), E_USER_ERROR);

408: } catch (Exception $ex) {

409: list($state, $code, $message) = $pDO->errorInfo();

410:

411: // If the error code is consistent with a disconnect, attempt to retry

Backtrace:

/home/mydomain.com/public_html/library/database/class.database.phpPHP::gdn_ErrorHandler();

[/home/mydomain.com/public_html/library/database/class.database.php:407] PHP::trigger_error();

[/home/mydomain.com/public_html/library/database/class.databasestructure.php:442] Gdn_Database->query();

[/home/mydomain.com/public_html/library/database/class.mysqlstructure.php:313] Gdn_DatabaseStructure->executeQuery();

[/home/mydomain.com/public_html/library/database/class.databasestructure.php:509] Gdn_MySQLStructure->_create();

[/home/mydomain.com/public_html/applications/dashboard/settings/structure.php:292] Gdn_DatabaseStructure->set();

[/home/mydomain.com/public_html/applications/dashboard/models/class.updatemodel.php:666] PHP::include();

[/home/mydomain.com/public_html/applications/dashboard/controllers/class.utilitycontroller.php:290] UpdateModel->runStructure();

[/home/mydomain.com/public_html/library/core/class.dispatcher.php:833] UtilityController->update();

[/home/mydomain.com/public_html/library/core/class.dispatcher.php:262] Gdn_Dispatcher->dispatchController();

[/home/mydomain.com/public_html/index.php:29] Gdn_Dispatcher->dispatch();

Variables in local scope:

[sql] 'create table GDN_AccessToken (
AccessTokenID int not null auto_increment,
Token varchar(100) not null,
UserID int not null,
Type varchar(20) not null,
Scope text null,
DateInserted timestamp not null,
InsertUserID int null,
InsertIPAddress varbinary(16) not null,
DateExpires timestamp not null,
Attributes text null,
primary key (AccessTokenID),
unique index UX_AccessToken (Token),
index IX_AccessToken_UserID (UserID),
index IX_AccessToken_Type (Type)
) engine=innodb default character set utf8mb4 collate utf8mb4_unicode_ci;'

[inputParameters] NULL

[options] array (
)

[returnType] NULL

[tries] 2

[try] 0

[pDO] array (
)

[pDOStatement] false

[state] '42000'

[code] 1067

[message] 'Invalid default value for \'DateExpires\''

[uex] array (
)

Additional information for support personnel:

Application: Vanilla
Application Version: 2.5.1
PHP Version: 7.0.28-0ubuntu0.16.04.1
Operating System: Linux
Server Software: Apache
User Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:59.0) Gecko/20100101 Firefox/59.0
Request Uri: /utility/update
Controller: PHP
Method: gdn_ErrorHandler

R_J

Try disabling MySQLs strict mode

cmroanirgo

Disabling strict mode is not really an option for the whole mysql process, as there's live data running on it ;-) (hence the test setup)

Having a look into the DB (using phpmyadmin), I notice there's no GDN_AccessToken in it at all.

Any other suggestions?

R_J

From https://github.com/vanilla/vanilla#self-hosting-requirements

Our minimum requirements are now:
...

• MySQL strict mode disabled.

I hate not being able to help, but disabling strict mode is a requirement, not an option.

You can go forward and try to hack your way through it. Creating the table GDN_AccessTaken has failed. If you insist on keeping the strict mode you can try the following:

1. Replace ->column('DateExpires', 'timestamp', false) from /applications/dashboard/settings/structure.php with ->column('DateExpires', 'timestamp', true) to allow null values. I do not know if that might have negative consequences. Directly after the installation/update you can run a sql which disallows null values and makes CURRENT_TIMESTAMP the default value.
2. Look at https://github.com/vanilla/vanilla/commit/09c0ffd2bfe35ca76ecbd47ba4bf2ce01ab4dc64 and https://github.com/vanilla/vanilla/commit/6502a5f2dedbdb854c7d486fbec1579d504abc71 to see how this problem has been taken care of in the current master

Whatever you do is a bit risky: you'll never be able to tell if something is working the way it is intended if you are changing the db structure.

---

I'm no database specialist: what downsides are there when changing strict mode in a productive environment?