Skip to content

Long data are not truncated correctly during database conversion #7876

Description

@yan12125

Steps to reproduce

  1. Create a Nextcloud instance with SQLite3 as database backend
  2. 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)

  1. 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

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions