Incorrect integer value: '' for column 'password_invalid' while migrating from pg to mysql

[avestnik]

Steps to reproduce

1. create an empty mysql 8.0.15 database
2. run occ db:convert-type

Expected behaviour

converted mysql database

Actual behaviour

conversion fails on oc_authtoken
An exception occurred while executing 'INSERT INTO oc_authtoken (id, uid, login_name, password, name, token, type, remember, last_activity, last_check, scope, expires, private_key, public_key, version, password_invalid) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [33, "...", "...", "...", 2, false]:

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'password_invalid' at row 1

I guess it is trying to insert false value into mysql tinyint column, false should be translated to 0b0, true to 0b1 for the statement to be correct

Server configuration

Operating system: centos7

Web server: apache

Database: postgres

PHP version: 7.2.10

Nextcloud version: (see Nextcloud admin page) 15.0.5

Updated from an older Nextcloud/ownCloud or fresh install: updated from 13

Where did you install Nextcloud from: nextcloud web site

Signing status: No errors have been found.

List of activated apps:

Enabled:
  - accessibility: 1.1.0
  - activity: 2.8.2
  - calendar: 1.6.4
  - cloud_federation_api: 0.1.0
  - comments: 1.5.0
  - contacts: 3.0.5
  - dav: 1.8.1
  - federatedfilesharing: 1.5.0
  - federation: 1.5.0
  - files: 1.10.0
  - files_pdfviewer: 1.4.0
  - files_sharing: 1.7.0
  - files_texteditor: 2.7.0
  - files_trashbin: 1.5.0
  - files_versions: 1.8.0
  - files_videoplayer: 1.4.0
  - firstrunwizard: 2.4.0
  - gallery: 18.2.0
  - groupfolders: 2.0.4
  - logreader: 2.0.0
  - lookup_server_connector: 1.3.0
  - nextcloud_announcements: 1.4.0
  - notifications: 2.3.0
  - oauth2: 1.3.0
  - password_policy: 1.5.0
  - provisioning_api: 1.5.0
  - serverinfo: 1.5.0
  - sharebymail: 1.5.0
  - support: 1.0.0
  - survey_client: 1.3.0
  - systemtags: 1.5.0
  - theming: 1.6.0
  - twofactor_backupcodes: 1.4.1
  - updatenotification: 1.5.0
  - user_ldap: 1.5.0
  - workflowengine: 1.5.0
Disabled:
  - admin_audit
  - encryption
  - files_external

Are you using external storage, if yes which one: no

Are you using encryption: no

Are you using an external user-backend, if yes which one: openldap

[stale]

This issue has been automatically marked as stale because it has not had recent activity and it seems to be missing some essential informations. It will be closed if no further activity occurs. Thank you for your contributions.

[JHirte]

I have the same problem with migrating from postgres to mariadb. It seems the migration tool fails on boolean fields. I've seen this with oc_authtoken, oc_calendarobjects_props and oc_share. For oc_authtoken and oc_calendarobjects_props I simply dropped the entries. But for oc_share this isn't an option.

Happened with nc-15.0.7 and nc-16.0.1.

[kesselb]

@JHirte mind to share the broken queries? The password_invalid column looks fine for me on mysql.

[JHirte]

@JHirte mind to share the broken queries? The password_invalid column looks fine for me on mysql.

As I dropped the problematic content from oc_authtkoken, I don't have anything in password_invalid. At moment I have two entries in oc_authtkoken, and password_invalid is empty for both of them. I'm just running the migration script again. Should fail in oc_share. I can provide a cleaned up query of this then.

[JHirte]

broken query from oc_authtkoken (some data stripped out):

An exception occurred while executing 'INSERT INTO oc_authtoken (id, uid, login_name, password, name, token, type, remember, last_activity, last_check, scope, expires, private_key, public_key, version, password_invalid) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [726, "johannes.hirte", "johannes.hirte", "STRIPPED", "Mozilla/5.0 (Android) ownCloud-android/3.6.2", "STRIPPED", 0, 0, 1561227173, 1561227173, null, null, "", "-----BEGIN PUBLIC KEY----- STRIPPED -----END PUBLIC KEY-----\n", 2, false]:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column khaoscloud.oc_authtoken.password_invalid at row 1

So the last value is a boolean, and this seems to be handled wrong in the php code.

[digrouz]

I have the same issue.

Any idea how to fix ? Or a workaround ?

[kesselb]

Do you have a stack trace?

cc @nickvergessen @rullzer I can guess the issue from the migration code. password_invalid is a boolean column on pqsql and a tinyint on mysql.

server/core/Command/Db/ConvertType.php

Lines 352 to 354 in bb352fb

	foreach ($row as $key => $value) {
	$insertQuery->setValue($key, $insertQuery->createParameter($key));
	}

Data from pqsql is copied to mysql without conversion because the column type is not set. There is a method getColumnType but no way to do a special conversion from pqsql to mysql for int columns.

[digrouz]

here is the stripped output:

$ php occ  db:convert-type --all-apps mysql nextcloud-user mysql-host nextcloud-db --clear-schema
What is the database password?
Clearing schema in new database
Creating schema in new database
The following tables will not be converted:
oc_users_external
oc_privatedata
Continue with the conversion (y/n)? [n] y
 oc_accounts
 2/2 [============================] 100%oc_activity
chunked query, 18 chunks
 17662/17662 [============================] 100%oc_activity_mq
    0 [>---------------------------]oc_addressbookchanges
 13/13 [============================] 100%oc_addressbooks
 2/2 [============================] 100%oc_appconfig  
 182/182 [============================] 100%oc_audioplayer_albums
    0 [>---------------------------]oc_audioplayer_artists
    0 [>---------------------------]oc_audioplayer_genre
    0 [>---------------------------]oc_audioplayer_playlist_tracks
    0 [>---------------------------]oc_audioplayer_playlists
    0 [>---------------------------]oc_audioplayer_stats
    0 [>---------------------------]oc_audioplayer_streams
    0 [>---------------------------]oc_audioplayer_tracks
    0 [>---------------------------]oc_authtoken
 1/7 [====>-----------------------]  14%
In AbstractMySQLDriver.php line 125:

  An exception occurred while executing 'INSERT INTO `oc_authtoken` (`id`, `uid`, `login_name`, `password`, `name`, `token`, `type`, `remember`, `last_activity`, `last_check`, `scope`, `expires`, `private_key`, `public_key`, `version`, `password_invalid`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [3072, "jeanjacques", "jeanjacques", "<STRIPPED>", "Oneplus GM1913", "<STRIPPED>", 1, 0, 1567847336, 1567847336, null, null, "<STRIPPED>", "-----BEGIN PUBLIC KEY-----\n<STRIPPED>\n-----END PUBLIC KEY-----\n", 2, false]:

  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'password_invalid' at row 1


In PDOStatement.php line 143:

  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'password_invalid' at row 1


In PDOStatement.php line 141:

  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'password_invalid' at row 1


db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

[bstegmaier75]

Just to confirm, I have exactly the same issue converting postgres DB to MariaDB.

[milkomeda]

Same problem here.