MariaDB 10.2.7: Install broken on master due to database column options

[tomneedham]

Summary

Symptom:

An exception occurred while executing 
'ALTER TABLE oc_calendarsubscriptions CHANGE `lastmodified` lastmodified INT UNSIGNED DEFAULT NULL NOT NULL'

Reason: MariaDB v10.2.7+ is not supported by doctrine/dbal prior to v2.7.0
OC 10.0.4 - Fix on top of older doctrine/dbal: #29240
OC 10.4.0 - doctrine/dbal updated to v2.8.0: #36290
Quick older doctrine/dbal patch: #28695 (comment)

Initial report:

Master as of today 15/8/17

PHP 7.1.8-2+ubuntu16.04.1+deb.sury.org+4
10.2.7-MariaDB-10.2.7+maria~xenial

1. I setup a new laptop, with apache, git, node, mariadb, php7.1.
2. Git cloned core, ran make
3. Setup db, user
4. ran install via occ

tomneedham@tomneedham-X453MA:/var/www/html$ sudo -u www-data ./occ maintenance:install -vv --database=mysql --database-name=owncloud --database-host=localhost --database-user=owncloud --database-pass=owncloud --admin-user=Tom --admin-pass=Tom
ownCloud is not installed - only a limited number of commands are available
Error while trying to create admin user: An exception occurred while executing 'ALTER TABLE oc_calendarsubscriptions CHANGE `lastmodified` lastmodified INT UNSIGNED DEFAULT NULL NOT NULL':

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'lastmodified'

Obviously we cant have default=null and NOT NULL....!

Traced this to line https://github.com/owncloud/core/blob/master/core/Migrations/Version20170101215145.php#L213

Dav install migration is here: https://github.com/owncloud/core/blob/master/apps/dav/appinfo/Migrations/Version20170116150538.php#L387 and only specifies unsigned => true.

I think this should have been in the dav app migrations.... de295ca

Weirdly I can't reproduce on another machine.

Could this be related to now having the 8/9 => 10 migrations in core, which include the setup for the dav app. So, during install, we are 'installing' dav before dav has had a change to do its setup?? Maybe....? Brain is still thinking this through.

@PVince81 @DeepDiver1975 ?

[tomneedham]

I added in logging to show the migrations being performed. You can see it is running the 8/9 => 10 migrations, despite this being a fresh 10 install. This means the 8/9 migratuions are 'installing' the dav app despite it not being installed yet, which is tricking future core migrations into thinking we are just doing an update here and hence modifying the dav tables, which are not actually up to date yet.

[tomneedham]

Confirmed that removing https://github.com/owncloud/core/blob/master/core/Migrations/Version20170101010100.php fixes the issue for a clean install

[tomneedham]

Should https://github.com/owncloud/core/blob/master/core/Migrations/Version20170101010100.php not check if we are updating, not a fresh install?

[PVince81]

we shouldn't detect install vs update in a migration, but we can detect whether a table/index already exists or not or a column is of a specific type.

[hanwentao]

I also encountered this issue when upgrading from 9.1 to 10.0. The generated SQL statement has DEFAULT NULL NOT NULL, which is contradictory. Look at the end of file Version20170101215145.php. Should we specify a default value for migration of lastmodified column?

[PVince81]

moving to "planned".

@tomneedham did you have any news regarding this ?

[TheFiZi]

Just upgraded from owncloud-files-9.1.6-1.1.noarch to owncloud-files-10.0.3-1.1.noarch on a CentOS 6 64-bit box.

PHP 7.0.22
MariaDB 10.2.8
Apache 2.2

Upgrading via the WebUI returns this:

Preparing update
Set log level to debug
Turned on maintenance mode
Updating database schema
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE oc_calendarsubscriptions CHANGE `lastmodified` lastmodified INT UNSIGNED DEFAULT NULL NOT NULL': SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'lastmodified'

Upgrading via the CLI

-bash-4.1$ ./occ upgrade
ownCloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
2017-09-19T17:02:02+00:00 Set log level to debug
2017-09-19T17:02:02+00:00 Turned on maintenance mode
2017-09-19T17:02:02+00:00 Repair step: Repair MySQL database engine
2017-09-19T17:02:02+00:00 Repair step: Repair MySQL collation
2017-09-19T17:02:02+00:00 Repair info: Change collation for oc_migrations ...
2017-09-19T17:02:02+00:00 Repair step: Repair SQLite autoincrement
2017-09-19T17:02:02+00:00 Repair step: Repair duplicate entries in oc_lucene_status
2017-09-19T17:02:02+00:00 Repair info: lucene_status table does not exist -> nothing to do
2017-09-19T17:02:02+00:00 Repair step: Upgrade app code from the marketplace
2017-09-19T17:02:02+00:00 Repair info: Enabling market app to assist with update
2017-09-19T17:02:02+00:00 Repair info: Using market to update existing apps
2017-09-19T17:02:02+00:00 Repair info: Attempting to update the following existing compatible apps from market:
2017-09-19T17:02:02+00:00 Repair info: Fetching app from market: activity
2017-09-19T17:02:03+00:00 Repair info:
2017-09-19T17:02:03+00:00 Repair info: Fetching app from market: comments
2017-09-19T17:02:05+00:00 Repair info: App (comments) is not known at the marketplace.
2017-09-19T17:02:05+00:00 Repair info: Fetching app from market: configreport
2017-09-19T17:02:06+00:00 Repair info: App (configreport) is not known at the marketplace.
2017-09-19T17:02:06+00:00 Repair info: Fetching app from market: dav
2017-09-19T17:02:07+00:00 Repair info: App (dav) is not known at the marketplace.
2017-09-19T17:02:07+00:00 Repair info: Fetching app from market: federatedfilesharing
2017-09-19T17:02:09+00:00 Repair info: App (federatedfilesharing) is not known at the marketplace.
2017-09-19T17:02:09+00:00 Repair info: Fetching app from market: federation
2017-09-19T17:02:10+00:00 Repair info: App (federation) is not known at the marketplace.
2017-09-19T17:02:10+00:00 Repair info: Fetching app from market: files
2017-09-19T17:02:11+00:00 Repair info: App (files) is not known at the marketplace.
2017-09-19T17:02:11+00:00 Repair info: Fetching app from market: files_external
2017-09-19T17:02:11+00:00 Repair info: App (files_external) is not installed
2017-09-19T17:02:11+00:00 Repair info: Fetching app from market: files_pdfviewer
2017-09-19T17:02:13+00:00 Repair info:
2017-09-19T17:02:13+00:00 Repair info: Fetching app from market: files_sharing
2017-09-19T17:02:14+00:00 Repair info: App (files_sharing) is not known at the marketplace.
2017-09-19T17:02:14+00:00 Repair info: Fetching app from market: files_texteditor
2017-09-19T17:02:15+00:00 Repair info:
2017-09-19T17:02:15+00:00 Repair info: Fetching app from market: files_trashbin
2017-09-19T17:02:16+00:00 Repair info: App (files_trashbin) is not known at the marketplace.
2017-09-19T17:02:16+00:00 Repair info: Fetching app from market: files_versions
2017-09-19T17:02:18+00:00 Repair info: App (files_versions) is not known at the marketplace.
2017-09-19T17:02:18+00:00 Repair info: Fetching app from market: files_videoplayer
2017-09-19T17:02:19+00:00 Repair info: App (files_videoplayer) is not known at the marketplace.
2017-09-19T17:02:19+00:00 Repair info: Fetching app from market: firstrunwizard
2017-09-19T17:02:20+00:00 Repair info: App (firstrunwizard) is not known at the marketplace.
2017-09-19T17:02:20+00:00 Repair info: Fetching app from market: market
2017-09-19T17:02:22+00:00 Repair info:
2017-09-19T17:02:22+00:00 Repair info: Fetching app from market: notifications
2017-09-19T17:02:23+00:00 Repair info: App (notifications) is not known at the marketplace.
2017-09-19T17:02:23+00:00 Repair info: Fetching app from market: provisioning_api
2017-09-19T17:02:24+00:00 Repair info: App (provisioning_api) is not known at the marketplace.
2017-09-19T17:02:24+00:00 Repair info: Fetching app from market: systemtags
2017-09-19T17:02:25+00:00 Repair info: App (systemtags) is not known at the marketplace.
2017-09-19T17:02:25+00:00 Repair info: Fetching app from market: templateeditor
2017-09-19T17:02:27+00:00 Repair info: App (templateeditor) is not known at the marketplace.
2017-09-19T17:02:27+00:00 Repair info: Fetching app from market: updatenotification
2017-09-19T17:02:28+00:00 Repair info: App (updatenotification) is not known at the marketplace.
2017-09-19T17:02:28+00:00 Repair info: App was not updated: activity
2017-09-19T17:02:28+00:00 Repair info: App was not updated: comments
2017-09-19T17:02:28+00:00 Repair info: App was not updated: configreport
2017-09-19T17:02:28+00:00 Repair info: App was not updated: dav
2017-09-19T17:02:28+00:00 Repair info: App was not updated: federatedfilesharing
2017-09-19T17:02:28+00:00 Repair info: App was not updated: federation
2017-09-19T17:02:28+00:00 Repair info: App was not updated: files
2017-09-19T17:02:28+00:00 Repair info: App was not updated: files_external
2017-09-19T17:02:28+00:00 Repair info: App was not updated: files_pdfviewer
2017-09-19T17:02:28+00:00 Repair info: App was not updated: files_sharing
2017-09-19T17:02:28+00:00 Repair info: App was not updated: files_texteditor
2017-09-19T17:02:28+00:00 Repair info: App was not updated: files_trashbin
2017-09-19T17:02:28+00:00 Repair info: App was not updated: files_versions
2017-09-19T17:02:28+00:00 Repair info: App was not updated: files_videoplayer
2017-09-19T17:02:28+00:00 Repair info: App was not updated: firstrunwizard
2017-09-19T17:02:28+00:00 Repair info: App was not updated: market
2017-09-19T17:02:28+00:00 Repair info: App was not updated: notifications
2017-09-19T17:02:28+00:00 Repair info: App was not updated: provisioning_api
2017-09-19T17:02:28+00:00 Repair info: App was not updated: systemtags
2017-09-19T17:02:28+00:00 Repair info: App was not updated: templateeditor
2017-09-19T17:02:28+00:00 Repair info: App was not updated: updatenotification
2017-09-19T17:02:28+00:00 Updating database schema
2017-09-19T17:02:28+00:00 Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE oc_calendarsubscriptions CHANGE `lastmodified` lastmodified INT UNSIGNED DEFAULT NULL NOT NULL':

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'lastmodified'
2017-09-19T17:02:28+00:00 Update failed
2017-09-19T17:02:28+00:00 Maintenance mode is kept active
2017-09-19T17:02:28+00:00 Reset log level