Server: Long data are not truncated correctly during database conversion

Created on 16 Jan 2018 · 13Comments · Source: nextcloud/server

Steps to reproduce

Create a Nextcloud instance with SQLite3 as database backend
Create an event in the calendar and enter a long description. For example:

104 學年度第 1 學期起，經教務會議通過休學期滿學生，不須再辦理復學手續，學籍由本組於每學期開始自動回復為在學狀態。惟如係提前復學，復學系統尚未開放者，得於初選一階開始日起書寫報告書申請提前復學。

(254 characters, 260 bytes long if encoded with UTF-8)

Try to convert the database from SQLite3 to PostgreSQL

Expected behaviour

Conversion fine

Actual behaviour

Command line logs of db:convert-type

# sudo -u http php ./occ -vvv db:convert-type --clear-schema --all-apps pgsql nextcloud db.chyen.cc nextcloud
sudo: setrlimit(RLIMIT_CORE): Operation not permitted
What is the database password?
Creating schema in new database
The following tables will not be converted:
oc_notes_meta
oc_notifications
oc_notifications_pushtokens
oc_ocsms_config
oc_ocsms_conversation_read_states
oc_ocsms_sendmessage_queue
oc_ocsms_smsdatas
oc_ocsms_user_datas
oc_privatedata
Continue with the conversion (y/n)? [n] y
oc_migrations
Skipping migrations table because it was already filled by running the migrations
oc_calendar_reminders
 1/1 [============================] 100% < 1 sec/< 1 sec 24.0 MiBoc_appconfig
 182/182 [============================] 100% 14 secs/14 secs 24.0 MiBoc_storages
 4/4 [============================] 100%  1 sec/1 sec  24.0 MiBoc_federated_reshares
    0 [>---------------------------] < 1 sec 24.0 MiBoc_trusted_servers
    0 [>---------------------------] < 1 sec 24.0 MiBoc_mimetypes
 70/70 [============================] 100% 6 secs/6 secs 24.0 MiBoc_user_transfer_owner
    0 [>---------------------------] < 1 sec 24.0 MiBoc_external_mounts
    0 [>---------------------------] < 1 sec 24.0 MiBoc_filecache
 251/251 [============================] 100% 20 secs/20 secs 24.0 MiBoc_group_user
 1/1 [============================] 100% < 1 sec/< 1 sec 24.0 MiBoc_group_admin
    0 [>---------------------------] < 1 sec 24.0 MiBoc_groups
 1/1 [============================] 100% < 1 sec/< 1 sec 24.0 MiBoc_preferences
 198/198 [============================] 100% 17 secs/17 secs 24.0 MiBoc_external_applicable
    0 [>---------------------------] < 1 sec 24.0 MiBoc_properties
 9/9 [============================] 100%  1 sec/1 sec  24.0 MiBoc_external_config
    0 [>---------------------------] < 1 sec 24.0 MiBoc_external_options
    0 [>---------------------------] < 1 sec 24.0 MiBoc_users
 1/1 [============================] 100% < 1 sec/< 1 sec 24.0 MiBoc_share_external
    0 [>---------------------------] < 1 sec 24.0 MiBoc_files_trash
    0 [>---------------------------] < 1 sec 24.0 MiBoc_oauth2_clients
    0 [>---------------------------] < 1 sec 24.0 MiBoc_oauth2_access_tokens
    0 [>---------------------------] < 1 sec 24.0 MiBoc_privacy_admins
    0 [>---------------------------] < 1 sec 24.0 MiBoc_text_documents
    0 [>---------------------------] < 1 sec 24.0 MiBoc_text_sessions
    0 [>---------------------------] < 1 sec 24.0 MiBoc_text_steps
    0 [>---------------------------] < 1 sec 24.0 MiBoc_comments_read_markers
    0 [>---------------------------] < 1 sec 24.0 MiBoc_credentials
 1/1 [============================] 100% < 1 sec/< 1 sec 24.0 MiBoc_accounts
 1/1 [============================] 100%  1 sec/1 sec  24.0 MiBoc_twofactor_backupcodes
 10/10 [============================] 100%  1 sec/1 sec  24.0 MiBoc_mounts
 3/3 [============================] 100% < 1 sec/< 1 sec 24.0 MiBoc_flow_checks
    0 [>---------------------------] < 1 sec 24.0 MiBoc_flow_operations
    0 [>---------------------------] < 1 sec 24.0 MiBoc_flow_operations_scope
    0 [>---------------------------] < 1 sec 24.0 MiBoc_twofactor_totp_secrets
 1/1 [============================] 100% < 1 sec/< 1 sec 24.0 MiBoc_share
    0 [>---------------------------] < 1 sec 24.0 MiBoc_jobs
 33/33 [============================] 100% 3 secs/3 secs 24.0 MiBoc_bruteforce_attempts
 25/25 [============================] 100% 2 secs/2 secs 24.0 MiBoc_authtoken
 5/5 [============================] 100% < 1 sec/< 1 sec 24.0 MiBoc_vcategory
    0 [>---------------------------] < 1 sec 24.0 MiBoc_vcategory_to_object
    0 [>---------------------------] < 1 sec 24.0 MiBoc_systemtag
    0 [>---------------------------] < 1 sec 24.0 MiBoc_systemtag_object_mapping
    0 [>---------------------------] < 1 sec 24.0 MiBoc_systemtag_group
    0 [>---------------------------] < 1 sec 24.0 MiBoc_file_locks
    0 [>---------------------------] < 1 sec 24.0 MiBoc_comments
    0 [>---------------------------] < 1 sec 24.0 MiBoc_twofactor_providers
 2/2 [============================] 100% < 1 sec/< 1 sec 24.0 MiBoc_whats_new
    0 [>---------------------------] < 1 sec 24.0 MiBoc_collres_collections
    0 [>---------------------------] < 1 sec 24.0 MiBoc_collres_resources
    0 [>---------------------------] < 1 sec 24.0 MiBoc_login_flow_v2
    0 [>---------------------------] < 1 sec 24.0 MiBoc_collres_accesscache
    0 [>---------------------------] < 1 sec 24.0 MiBoc_filecache_extended
    0 [>---------------------------] < 1 sec 24.0 MiBoc_direct_edit
    0 [>---------------------------] < 1 sec 24.0 MiBoc_activity
 455/455 [============================] 100% 39 secs/39 secs 24.0 MiBoc_activity_mq
    0 [>---------------------------] < 1 sec 24.0 MiBoc_addressbooks
 2/2 [============================] 100% < 1 sec/< 1 sec 24.0 MiBoc_cards
 197/197 [============================] 100% 17 secs/17 secs 24.0 MiBoc_calendars
 7/7 [============================] 100%  1 sec/1 sec  24.0 MiBoc_addressbookchanges
 356/356 [============================] 100% 33 secs/33 secs 24.0 MiBoc_schedulingobjects
    0 [>---------------------------] < 1 sec 24.0 MiBoc_cards_properties
 857/857 [============================] 100%  1 min/1 min  24.0 MiBoc_calendarobjects_props
chunked query, 5 chunks
  100/4929 [>---------------------------]   2% 8 secs/6 mins 24.0 MiB
In AbstractPostgreSQLDriver.php line 79:

  [Doctrine\DBAL\Exception\DriverException]                                                                                                      
  An exception occurred while executing 'INSERT INTO "oc_calendarobjects_props" ("id", "calendarid", "objectid", "name", "parameter", "value",   
  "calendartype") VALUES(?, ?, ?, ?, ?, ?, ?)' with params ["558", "1", "118", "DESCRIPTION", null, "\x31\x30\x34\x20\xe5\xad\xb8\xe5\xb9\xb4\x  
  e5\xba\xa6\xe7\xac\xac\x20\x31\x20\xe5\xad\xb8\xe6\x9c\x9f\xe8\xb5\xb7\xef\xbc\x8c\xe7\xb6\x93\xe6\x95\x99\xe5\x8b\x99\xe6\x9c\x83\xe8\xad\xb  
  0\xe9\x80\x9a\xe9\x81\x8e\xe4\xbc\x91\xe5\xad\xb8\xe6\x9c\x9f\xe6\xbb\xbf\xe5\xad\xb8\xe7\x94\x9f\xef\xbc\x8c\xe4\xb8\x8d\xe9\xa0\x88\xe5\x86  
  \x8d\xe8\xbe\xa6\xe7\x90\x86\xe5\xbe\xa9\xe5\xad\xb8\xe6\x89\x8b\xe7\xba\x8c\xef\xbc\x8c\xe5\xad\xb8\xe7\xb1\x8d\xe7\x94\xb1\xe6\x9c\xac\xe7\  
  xb5\x84\xe6\x96\xbc\xe6\xaf\x8f\xe5\xad\xb8\xe6\x9c\x9f\xe9\x96\x8b\xe5\xa7\x8b\xe8\x87\xaa\xe5\x8b\x95\xe5\x9b\x9e\xe5\xbe\xa9\xe7\x82\xba\x  
  e5\x9c\xa8\xe5\xad\xb8\xe7\x8b\x80\xe6\x85\x8b\xe3\x80\x82\xe6\x83\x9f\xe5\xa6\x82\xe4\xbf\x82\xe6\x8f\x90\xe5\x89\x8d\xe5\xbe\xa9\xe5\xad\xb  
  8\xef\xbc\x8c\xe5\xbe\xa9\xe5\xad\xb8\xe7\xb3\xbb\xe7\xb5\xb1\xe5\xb0\x9a\xe6\x9c\xaa\xe9\x96\x8b\xe6\x94\xbe\xe8\x80\x85\xef\xbc\x8c\xe5\xbe  
  \x97\xe6\x96\xbc\xe5\x88\x9d\xe9\x81\xb8\xe4\xb8\x80\xe9\x9a\x8e\xe9\x96\x8b\xe5\xa7\x8b\xe6\x97\xa5\xe8\xb5\xb7\xe6\x9b\xb8\xe5", "0"]:       

  SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0xe5                                        


Exception trace:
  at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php:79
 Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException() at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:169                                                                                                                                   
 Doctrine\DBAL\DBALException::wrapException() at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:145
 Doctrine\DBAL\DBALException::driverExceptionDuringQuery() at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1063                                                                                                                                             
 Doctrine\DBAL\Connection->executeUpdate() at /usr/share/webapps/nextcloud/lib/private/DB/Connection.php:220
 OC\DB\Connection->executeUpdate() at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /usr/share/webapps/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:215
 OC\DB\QueryBuilder\QueryBuilder->execute() at /usr/share/webapps/nextcloud/core/Command/Db/ConvertType.php:369
 OC\Core\Command\Db\ConvertType->copyTable() at /usr/share/webapps/nextcloud/core/Command/Db/ConvertType.php:407
 OC\Core\Command\Db\ConvertType->convertDB() at /usr/share/webapps/nextcloud/core/Command/Db/ConvertType.php:226
 OC\Core\Command\Db\ConvertType->execute() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Application.php:915
 Symfony\Component\Console\Application->doRunCommand() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Application.php:272
 Symfony\Component\Console\Application->doRun() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Application.php:148
 Symfony\Component\Console\Application->run() at /usr/share/webapps/nextcloud/lib/private/Console/Application.php:214
 OC\Console\Application->run() at /usr/share/webapps/nextcloud/console.php:99
 require_once() at /usr/share/webapps/nextcloud/occ:11

In PDOStatement.php line 119:

  [Doctrine\DBAL\Driver\PDOException (22021)]                                                              
  SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0xe5  


Exception trace:
  at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119
 Doctrine\DBAL\Driver\PDOStatement->execute() at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1054
 Doctrine\DBAL\Connection->executeUpdate() at /usr/share/webapps/nextcloud/lib/private/DB/Connection.php:220
 OC\DB\Connection->executeUpdate() at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /usr/share/webapps/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:215
 OC\DB\QueryBuilder\QueryBuilder->execute() at /usr/share/webapps/nextcloud/core/Command/Db/ConvertType.php:369
 OC\Core\Command\Db\ConvertType->copyTable() at /usr/share/webapps/nextcloud/core/Command/Db/ConvertType.php:407
 OC\Core\Command\Db\ConvertType->convertDB() at /usr/share/webapps/nextcloud/core/Command/Db/ConvertType.php:226
 OC\Core\Command\Db\ConvertType->execute() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Application.php:915
 Symfony\Component\Console\Application->doRunCommand() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Application.php:272
 Symfony\Component\Console\Application->doRun() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Application.php:148
 Symfony\Component\Console\Application->run() at /usr/share/webapps/nextcloud/lib/private/Console/Application.php:214
 OC\Console\Application->run() at /usr/share/webapps/nextcloud/console.php:99
 require_once() at /usr/share/webapps/nextcloud/occ:11

In PDOStatement.php line 117:

  [PDOException (22021)]                                                                                   
  SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0xe5  


Exception trace:
  at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
 PDOStatement->execute() at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
 Doctrine\DBAL\Driver\PDOStatement->execute() at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1054
 Doctrine\DBAL\Connection->executeUpdate() at /usr/share/webapps/nextcloud/lib/private/DB/Connection.php:220
 OC\DB\Connection->executeUpdate() at /usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /usr/share/webapps/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:215
 OC\DB\QueryBuilder\QueryBuilder->execute() at /usr/share/webapps/nextcloud/core/Command/Db/ConvertType.php:369
 OC\Core\Command\Db\ConvertType->copyTable() at /usr/share/webapps/nextcloud/core/Command/Db/ConvertType.php:407
 OC\Core\Command\Db\ConvertType->convertDB() at /usr/share/webapps/nextcloud/core/Command/Db/ConvertType.php:226
 OC\Core\Command\Db\ConvertType->execute() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Application.php:915
 Symfony\Component\Console\Application->doRunCommand() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Application.php:272
 Symfony\Component\Console\Application->doRun() at /usr/share/webapps/nextcloud/3rdparty/symfony/console/Application.php:148
 Symfony\Component\Console\Application->run() at /usr/share/webapps/nextcloud/lib/private/Console/Application.php:214
 OC\Console\Application->run() at /usr/share/webapps/nextcloud/console.php:99
 require_once() at /usr/share/webapps/nextcloud/occ:11

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

From the log, the description is truncated to 254 bytes, thus the conversion failed.

Server configuration

Operating system: Linux nextcloud 5.5.1-arch1-1 #1 SMP PREEMPT Sat, 01 Feb 2020 16:38:40 +0000 x86_64 GNU/Linux

Webserver: Apache/2.4.41 (Unix) OpenSSL/1.1.1d

Database: sqlite3 3.31.1 => postgresql 12.2

PHP version: 7.4.3

Nextcloud version: 18.0.1

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

Where did you install Nextcloud from: Arch Linux official repo https://www.archlinux.org/packages/community/any/nextcloud/

Signing status

No errors have been found.

List of activated apps

Enabled:
  - activity: 2.11.0
  - admin_audit: 1.8.0
  - calendar: 2.0.2
  - cloud_federation_api: 1.1.0
  - contacts: 3.2.0
  - dav: 1.14.0
  - federatedfilesharing: 1.8.0
  - files: 1.13.1
  - logreader: 2.3.0
  - lookup_server_connector: 1.6.0
  - oauth2: 1.6.0
  - privacy: 1.2.0
  - provisioning_api: 1.8.0
  - recommendations: 0.6.0
  - serverinfo: 1.8.0
  - settings: 1.0.0
  - tasks: 0.11.3
  - twofactor_backupcodes: 1.7.0
  - twofactor_totp: 4.1.2
  - viewer: 1.2.0
  - workflowengine: 2.0.0
Disabled:
  - accessibility
  - comments
  - encryption
  - federation
  - files_external
  - files_pdfviewer
  - files_rightclick
  - files_sharing
  - files_trashbin
  - files_versions
  - files_videoplayer
  - firstrunwizard
  - nextcloud_announcements
  - notifications
  - password_policy
  - photos
  - sharebymail
  - support
  - survey_client
  - systemtags
  - text
  - theming
  - updatenotification
  - user_ldap

Configuration (config/config.php)

<?php
$CONFIG = array (
  'version' => '18.0.1.3',
  'installed' => true,
  'instanceid' => '<removed>',
  'passwordsalt' => '<removed>',
  'secret' => '<removed>',
  'trusted_domains' => 
  array (
    0 => '<removed>',
  ),
  'datadirectory' => '<removed>',
  'dbtype' => 'sqlite3',
  'defaultapp' => 'calendar',
  'appstoreenabled' => false,
  'log_type' => 'syslog',
  'config_is_read_only' => true,
  'loglevel' => 2,
  'maintenance' => false,
  'overwrite.cli.url' => '<removed>',
);

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: no

Client configuration

N/A

Logs

N/A

0. Needs triage bug install and update needs info

Source

yan12125

Most helpful comment

Well, for me this is no longer an issue. After upgrading nextcloud and then touching affected events, I can migrate to PostgreSQL. Feel free to close this if you believe there's nothing else to be done :)

yan12125 on 12 Apr 2020

❤1 🎉1

All 13 comments

cc @nickvergessen

MorrisJobke on 31 May 2018

As there is no feedback since a while I will close this ticket. If this is still happening please make sure to upgrade to the latest version. After that, feel free to reopen.

skjnldsv on 10 Mar 2020

Hi, thanks for the follow up! I tried the conversion again with the latest stable version (18.0.1). This time the process fails at a different calendar event with a different error message, while the stack trace is quite simiar, so I updated the original bug report instead of creating a new one.

yan12125 on 10 Mar 2020

Thanks for providing additional feedback :)
cc @rullzer, @nickvergessen & @blizzz

skjnldsv on 10 Mar 2020

👍1

Sorry I made a mistake - I forgot to set the database encoding to UNICODE following [1]. Now I get the original invalid byte sequence for encoding "UTF8" error - update the bug report again.

[1] https://docs.nextcloud.com/server/18/admin_manual/configuration_database/linux_database_configuration.html

yan12125 on 10 Mar 2020

The problem is sqlite has no length limit.
The actual bug should be fixed with https://github.com/nextcloud/server/pull/19634
And then afterwards the migration should also work fine. But for that you need to touch all affected events once before doing the migration.

So I guess we need to hardcode some checking in https://github.com/nextcloud/server/blob/6127c288e836a78a9cb77da5f05f131b019fe868/core/Command/Db/ConvertType.php#L362-L369

nickvergessen on 10 Mar 2020

Great, thanks a lot for the info! I will do that when 18.0.2 is out.

yan12125 on 10 Mar 2020

And then afterwards the migration should also work fine. But for that you need to touch all affected events once before doing the migration.

As a follow-up - after these steps, the conversion succeeded and my nextcloud instance still works fine after changing config.php to use the new pgsql database.

yan12125 on 12 Mar 2020

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

nextcloud-stale[bot] on 11 Apr 2020

yan12125 on 12 Apr 2020

❤1 🎉1

@yan12125 could you please elaborate on the steps you took to fix the problem? We're seeing similar issues in https://github.com/nextcloud/mail/issues/2930 where a Nextcloud app can't insert utf8 into postgres. It throws Character not in repertoire as well.

ChristophWurst on 20 Apr 2020

Christoph Wurst notifications@github.com 於 2020年4月20日週一下午5:15寫道：
>

@yan12125 could you please elaborate on the steps you took to fix the problem? We're seeing similar issues in nextcloud/mail#2930 where a Nextcloud app can't insert utf8 into postgres. It throws Character not in repertoire as well.

Sure! I went to the calendar app, opened the event with relevant
description (104 學年度第 1 學期起...), and simply click "Update" without
modifying the event content. After that the database migration script
run fine.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or unsubscribe.

yan12125 on 20 Apr 2020

I think this is related...though my issue is not with a calendar appointment title, but an image file. I'm trying to complete a Sqlite->MySql conversion, and was stopped with an error:

SQLSTATE[22001]: String data, right truncated: 140
` 6 Data too long for column 'name' at row 1

This was due to an image file which was stored with a ridiculously long file name (250 characters). Generally, I wonder if these kinds of checks could be made before the migration starts, or should be handled (truncated) properly during migration?