Unable to convert from SQlite to MySQL because of DB server errors

Theodore · February 3, 2020, 6:44pm

Nextcloud version: 17,0,1,1
Operating system and version: Ubuntu 18.04.4 LTS
Apache or nginx version: Apache 2.4.29
PHP version: PHP 7.2.24

The issue you are facing: Errors occurred whet trying to convert NextCloud database format from SQlite to MySQL.

Is this the first time you’ve seen this error? (Y/N): Yes

Steps to replicate it:

I don’t think it’s possible to replicate it in some other place except my server.
See item 1.

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

$CONFIG = array (
  'instanceid' => 'XXX',
  'passwordsalt' => 'XXX',
  'secret' => 'XXX',
  'trusted_domains' =>
  array (
    0 => 'site.tld',
  ),
  'datadirectory' => '/var/www/cloud/nextcloud/data',
  'dbtype' => 'sqlite3',
  'version' => '17.0.1.1',
  'overwrite.cli.url' => 'https://XXX',
  'installed' => true,
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'memcache.distributed' => '\\OC\\Memcache\\Memcached',
  'memcached_servers' =>
  array (
    0 =>
    array (
      0 => 'localhost',
      1 => 11211,
    ),
  ),
  'maintenance' => false,
  'updater.secret' => 'XXX',
  'theme' => '',
  'loglevel' => 2,
  'onlyoffice' =>
  array (
    'verify_peer_off' => true,
  ),
  'mysql.utf8mb4' => true,  // Inserted some later
);

The output of your Apache/nginx/system log in /var/log/____:

<<NOT APPLICABLE>>

During the installation of NectCloud, I chose the SQlite database format because I did not plan to store many files in my cloud. But after a while the number of files became huge, and file operations became a little longer than expected. I thought I could easily convert the database format to a more suitable one for greater speed, but I was a bit wrong.

First I issued the following command with the primary config file (without 'mysql.utf8mb4' => true in config):

./occ db:convert-type --clear-schema --all-apps -v -- mysql username localhost nextcloud

After some time I had the following error message:

Clearing schema in new database
Creating schema in new database
The following tables will not be converted:
oc_audioplayer_albums
oc_audioplayer_artists
oc_audioplayer_genre
oc_audioplayer_playlist_tracks
oc_audioplayer_playlists
oc_audioplayer_stats
oc_audioplayer_streams
oc_audioplayer_tracks
oc_passman_credentials
oc_passman_delete_vault_request
oc_passman_files
oc_passman_revisions
oc_passman_share_request
oc_passman_sharing_acl
oc_passman_vaults
Continue with the conversion (y/n)? [n] y
oc_accounts
 2/2 [============================] 100% < 1 secoc_activity
chunked query, 706 chunks
 235500/705701 [=========>------------------]  33%  2 hrs
In AbstractMySQLDriver.php line 106:

  [Doctrine\DBAL\Exception\DriverException]
  An exception occurred while executing 'INSERT INTO `oc_activity` (`activity_id`, `timestamp`, `priority`, `type`, `user`,
   `affecteduser`, `app`, `subject`, `subjectparams`, `message`, `messageparams`, `file`, `link`, `object_type`, `object_id
  `) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["235592", "1565676679", "30", "file_created", "ZERG"
  , "ZERG", "files", "created_self", "[{\"147776\":\"\\\/Images2\\\/HUN_Hajd\\uda46\\udfa1s_COA.jpg\"}]", "", "[]", "\/Imag
  es2\/HUN_Hajd\uda46\udfa1s_COA.jpg", "https:\/\/site.tld\/index.php\/apps\/files\/?dir=\/Images2", "files", "1477
  76"]:

  SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF2\xA1\xAE\xA1s_...' for column 'file' at row 1


Exception trace:
 () at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:106
 Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:169
 Doctrine\DBAL\DBALException::wrapException() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:145
 Doctrine\DBAL\DBALException::driverExceptionDuringQuery() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1063
 Doctrine\DBAL\Connection->executeUpdate() at /var/www/cloud/nextcloud/lib/private/DB/Connection.php:216
 OC\DB\Connection->executeUpdate() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /var/www/cloud/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:214
 OC\DB\QueryBuilder\QueryBuilder->execute() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:366
 OC\Core\Command\Db\ConvertType->copyTable() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:401
 OC\Core\Command\Db\ConvertType->convertDB() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:223
 OC\Core\Command\Db\ConvertType->execute() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:908
 Symfony\Component\Console\Application->doRunCommand() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:269
 Symfony\Component\Console\Application->doRun() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:145
 Symfony\Component\Console\Application->run() at /var/www/cloud/nextcloud/lib/private/Console/Application.php:213
 OC\Console\Application->run() at /var/www/cloud/nextcloud/console.php:97
 require_once() at /var/www/cloud/nextcloud/occ:11

In PDOStatement.php line 119:

  [Doctrine\DBAL\Driver\PDOException (HY000)]
  SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF2\xA1\xAE\xA1s_...' for column 'file' at row 1


Exception trace:
 () at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119
 Doctrine\DBAL\Driver\PDOStatement->execute() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1054
 Doctrine\DBAL\Connection->executeUpdate() at /var/www/cloud/nextcloud/lib/private/DB/Connection.php:216
 OC\DB\Connection->executeUpdate() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /var/www/cloud/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:214
 OC\DB\QueryBuilder\QueryBuilder->execute() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:366
 OC\Core\Command\Db\ConvertType->copyTable() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:401
 OC\Core\Command\Db\ConvertType->convertDB() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:223
 OC\Core\Command\Db\ConvertType->execute() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:908
 Symfony\Component\Console\Application->doRunCommand() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:269
 Symfony\Component\Console\Application->doRun() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:145
 Symfony\Component\Console\Application->run() at /var/www/cloud/nextcloud/lib/private/Console/Application.php:213
 OC\Console\Application->run() at /var/www/cloud/nextcloud/console.php:97
 require_once() at /var/www/cloud/nextcloud/occ:11

In PDOStatement.php line 117:

  [PDOException (HY000)]
  SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF2\xA1\xAE\xA1s_...' for column 'file' at row 1


Exception trace:
 () at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
 PDOStatement->execute() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
 Doctrine\DBAL\Driver\PDOStatement->execute() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1054
 Doctrine\DBAL\Connection->executeUpdate() at /var/www/cloud/nextcloud/lib/private/DB/Connection.php:216
 OC\DB\Connection->executeUpdate() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /var/www/cloud/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:214
 OC\DB\QueryBuilder\QueryBuilder->execute() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:366
 OC\Core\Command\Db\ConvertType->copyTable() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:401
 OC\Core\Command\Db\ConvertType->convertDB() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:223
 OC\Core\Command\Db\ConvertType->execute() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:908
 Symfony\Component\Console\Application->doRunCommand() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:269
 Symfony\Component\Console\Application->doRun() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:145
 Symfony\Component\Console\Application->run() at /var/www/cloud/nextcloud/lib/private/Console/Application.php:213
 OC\Console\Application->run() at /var/www/cloud/nextcloud/console.php:97
 require_once() at /var/www/cloud/nextcloud/occ:11

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

After cirtain investigations in the Internet I have found recommendation to change database charset from utf8 to utf8mb4 and add 'mysql.utf8mb4' => true to config file. That seemed to solve the problem described above but after long time the following error occurred:

Clearing schema in new database
Creating schema in new database
The following tables will not be converted:
oc_audioplayer_albums
oc_audioplayer_artists
oc_audioplayer_genre
oc_audioplayer_playlist_tracks
oc_audioplayer_playlists
oc_audioplayer_stats
oc_audioplayer_streams
oc_audioplayer_tracks
oc_passman_credentials
oc_passman_delete_vault_request
oc_passman_files
oc_passman_revisions
oc_passman_share_request
oc_passman_sharing_acl
oc_passman_vaults
Continue with the conversion (y/n)? [n] y
oc_accounts
 2/2 [============================] 100%  1 secoc_activity
chunked query, 706 chunks
 705701/705701 [============================] 100%  6 hrsoc_activity_mq
 1/1 [============================] 100%  1 secoc_addressbookchanges
 5/5 [============================] 100% < 1 secoc_addressbooks
 2/2 [============================] 100% < 1 secoc_appconfig
 294/294 [============================] 100% 9 secsoc_authtoken
 55/55 [============================] 100% 4 secsoc_bookmarks
 182/182 [============================] 100% 6 secsoc_bookmarks_folders
 11/11 [============================] 100%  1 secoc_bookmarks_folders_bookmarks
 182/182 [============================] 100% 5 secsoc_bookmarks_tags
 543/543 [============================] 100% 18 secsoc_bruteforce_attempts
 9/9 [============================] 100% < 1 secoc_calendar_invitations
    0 [>---------------------------] < 1 secoc_calendar_reminders
 3/3 [============================] 100% < 1 secoc_calendar_resources

<<SKIPPED>>

    0 [>---------------------------] < 1 secoc_polls_polls
    0 [>---------------------------] < 1 secoc_polls_share
    0 [>---------------------------] < 1 secoc_polls_votes
    0 [>---------------------------] < 1 secoc_preferences
 50/50 [============================] 100% 2 secsoc_privacy_admins
    0 [>---------------------------] < 1 secoc_properties
chunked query, 843 chunks
 771000/842369 [=========================>--]  91%  6 hrs
In AbstractMySQLDriver.php line 106:

  [Doctrine\DBAL\Exception\DriverException]
  An exception occurred while executing 'INSERT INTO `oc_properties` (`id`, `userid`, `propertypath`, `propertyname`, `propertyvalue`) VALUES(?, ?, ?, ?, ?)' with params ["771108", "USER", "\/!TEMP\/udemy - Master Python Programming The Complete 2019 Python Bootcamp (2019)\/20. Working with SQL Databases in Python\/1.2 Dr. Richard Hipp - creator of SQLite - provides a great view into the history of SQLite and situations where it really shines..html", "{urn:schemas-microsoft-com:}Win32CreationTime", "Wed, 18 Dec 2019 23:00:20 GMT"]:

  SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'propertypath' at row 1


Exception trace:
 () at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:106
 Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:169
 Doctrine\DBAL\DBALException::wrapException() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:145
 Doctrine\DBAL\DBALException::driverExceptionDuringQuery() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1063
 Doctrine\DBAL\Connection->executeUpdate() at /var/www/cloud/nextcloud/lib/private/DB/Connection.php:216
 OC\DB\Connection->executeUpdate() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /var/www/cloud/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:214
 OC\DB\QueryBuilder\QueryBuilder->execute() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:366
 OC\Core\Command\Db\ConvertType->copyTable() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:401
 OC\Core\Command\Db\ConvertType->convertDB() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:223
 OC\Core\Command\Db\ConvertType->execute() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:908
 Symfony\Component\Console\Application->doRunCommand() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:269
 Symfony\Component\Console\Application->doRun() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:145
 Symfony\Component\Console\Application->run() at /var/www/cloud/nextcloud/lib/private/Console/Application.php:213
 OC\Console\Application->run() at /var/www/cloud/nextcloud/console.php:97
 require_once() at /var/www/cloud/nextcloud/occ:11

In PDOStatement.php line 119:

  [Doctrine\DBAL\Driver\PDOException (22001)]
  SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'propertypath' at row 1


Exception trace:
 () at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119
 Doctrine\DBAL\Driver\PDOStatement->execute() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1054
 Doctrine\DBAL\Connection->executeUpdate() at /var/www/cloud/nextcloud/lib/private/DB/Connection.php:216
 OC\DB\Connection->executeUpdate() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /var/www/cloud/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:214
 OC\DB\QueryBuilder\QueryBuilder->execute() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:366
 OC\Core\Command\Db\ConvertType->copyTable() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:401
 OC\Core\Command\Db\ConvertType->convertDB() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:223
 OC\Core\Command\Db\ConvertType->execute() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:908
 Symfony\Component\Console\Application->doRunCommand() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:269
 Symfony\Component\Console\Application->doRun() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:145
 Symfony\Component\Console\Application->run() at /var/www/cloud/nextcloud/lib/private/Console/Application.php:213
 OC\Console\Application->run() at /var/www/cloud/nextcloud/console.php:97
 require_once() at /var/www/cloud/nextcloud/occ:11

In PDOStatement.php line 117:

  [PDOException (22001)]
  SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'propertypath' at row 1


Exception trace:
 () at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
 PDOStatement->execute() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
 Doctrine\DBAL\Driver\PDOStatement->execute() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1054
 Doctrine\DBAL\Connection->executeUpdate() at /var/www/cloud/nextcloud/lib/private/DB/Connection.php:216
 OC\DB\Connection->executeUpdate() at /var/www/cloud/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /var/www/cloud/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:214
 OC\DB\QueryBuilder\QueryBuilder->execute() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:366
 OC\Core\Command\Db\ConvertType->copyTable() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:401
 OC\Core\Command\Db\ConvertType->convertDB() at /var/www/cloud/nextcloud/core/Command/Db/ConvertType.php:223
 OC\Core\Command\Db\ConvertType->execute() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:908
 Symfony\Component\Console\Application->doRunCommand() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:269
 Symfony\Component\Console\Application->doRun() at /var/www/cloud/nextcloud/3rdparty/symfony/console/Application.php:145
 Symfony\Component\Console\Application->run() at /var/www/cloud/nextcloud/lib/private/Console/Application.php:213
 OC\Console\Application->run() at /var/www/cloud/nextcloud/console.php:97
 require_once() at /var/www/cloud/nextcloud/occ:11

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

I suspect that field ‘propertypath’ in table ‘oc_properties’ is not wide enough to store the value though the SQlite’s table stored it without a problem…

I still have strong intention to convert my cloud database format but now I do not know what to do next to complete convertion successfully and I would be happy to have any assistence that helps me to move cloud data to MySQL database.

Thanks in advance!

Best wishes, Theodore

tflidd · February 7, 2020, 8:33am

It looks like a known issue:

github.com/nextcloud/server

Error reporting for too long filepaths (>255 chars)

opened 07:07AM - 19 Jun 18 UTC

jzaers

bug 1. to develop feature: dav 25-feedback

### Steps to reproduce 1. Use filenames with a resulting path longer than 255 c…haracters 2. This seems to result in various strange side effects like move not executed correctly and the following error message: ``` {"reqId":"xZAVDUDUMFn0yANyU6rA","level":4,"time":"2018-06-14T06:52:08+00:00","remoteAddr":"10.0.50.4","user":"87c48f84-4b31-4c8e-aac8-1c554f1b500b","app":"webdav","method":"MOVE","url":"\/owncloud\/remote.php\/dav\/files\/LONG_FILENAME_REPLACED_WITH_247_CHARACTERS","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'UPDATE \\\"oc_properties\\\" SET \\\"propertypath\\\" = ? WHERE \\\"userid\\\" = ? AND \\\"propertypath\\\" = ?' with params [\\\"files\\\\\\\LONG_FILENAME_REPLACED_WITH_247_CHARACTERS", \\\"LONG_FILENAME_REPLACED_WITH_247_CHARACTERS"]:\\n\\nSQLSTATE[22001]: String data, right truncated: **7 ERROR: value too long for type character varying(255)**\",\"Code\":0,\"Trace\":\"#0 \\\/opt\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(128): Doctrine\\\\DBAL\\\\Driver\\\\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/opt\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Statement.php(178): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOPgSql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'UPDATE \\\"oc_prop...', Array)\\n#2 \\\/opt\\\/nextcloud\\\/apps\\\/dav\\\/lib\\\/DAV\\\/CustomPropertiesBackend.php(179): Doctrine\\\\DBAL\\\\Statement->execute(Array)\\n#3 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/PropertyStorage\\\/Plugin.php(147): OCA\\\\DAV\\\\DAV\\\\CustomPropertiesBackend->move('files\\\/87c48f84-...', 'files\\\/87c48f84-...')\\n#4 [internal function]: Sabre\\\\DAV\\\\PropertyStorage\\\\Plugin->afterMove('files\\\/87c48f84-...', 'files\\\/87c48f84-...')\\n#5 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/event\\\/lib\\\/EventEmitterTrait.php(105): call_user_func_array(Array, Array)\\n#6 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/CorePlugin.php(648): Sabre\\\\Event\\\\EventEmitter->emit('afterMove', Array)\\n#7 [internal function]: Sabre\\\\DAV\\\\CorePlugin->httpMove(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#8 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/event\\\/lib\\\/EventEmitterTrait.php(105): call_user_func_array(Array, Array)\\n#9 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(479): Sabre\\\\Event\\\\EventEmitter->emit('method:MOVE', Array)\\n#10 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(254): Sabre\\\\DAV\\\\Server->invokeMethod(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#11 \\\/opt\\\/nextcloud\\\/apps\\\/dav\\\/lib\\\/Server.php(258): Sabre\\\\DAV\\\\Server->exec()\\n#12 \\\/opt\\\/nextcloud\\\/apps\\\/dav\\\/appinfo\\\/v2\\\/remote.php(33): OCA\\\\DAV\\\\Server->exec()\\n#13 \\\/opt\\\/nextcloud\\\/remote.php(164): require_once('\\\/opt\\\/nextcloud\\\/...')\\n#14 {main}\",\"File\":\"\\\/opt\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractPostgreSQLDriver.php\",\"Line\":91}","userAgent":"Mozilla\/5.0 (Macintosh) mirall\/2.4.1 (build 9367)","version":"12.0.6.1"} ``` ### Expected behaviour I would like to - receive an error in the client during the initial sync of a file with a too long path, so that I can correct this on the client. - receive an sync error in the client when the move failed due to a too long filename - maybe a nice little helper routine that checks that local filenames are not exceeding the expected length. Upps, this depends on the Username in the server, the username can be long in scenarios with synced useraccounts ### Actual behaviour Files with too long paths are synced to the server and encounter problems on later move operations. It looks like the move is executed like a copy and the original file stays in the source directory. But this is not happening always. I assume that it depends on the resulting file path in the destination directory. ### Server configuration **Operating system**: ubuntu 14.04 LTS **Web server:** Apache 2.4 **Database:** postgresql 9.4 **PHP version:** php 5.6 **Nextcloud version:** (see Nextcloud admin page) see above, 12.0.6.1, (I updated the server to 13 afterwards, a few server configs below are from the new server, I try to reproduce this under 13.) **Updated from an older Nextcloud/ownCloud or fresh install:** Migration from owncloud 9 or 10. **Where did you install Nextcloud from:** **Signing status:** <details> <summary>Signing status</summary> No errors have been found. </details> **List of activated apps:** This is from the server after upgrade to v 13. Enabled: - activity: 2.6.1 - admin_audit: 1.3.0 - comments: 1.3.0 - dav: 1.4.7 - federatedfilesharing: 1.3.1 - federation: 1.3.0 - files: 1.8.0 - files_pdfviewer: 1.2.1 - files_sharing: 1.5.0 - files_texteditor: 2.5.1 - files_trashbin: 1.3.0 - files_versions: 1.6.0 - files_videoplayer: 1.2.0 - firstrunwizard: 2.2.1 - gallery: 18.0.0 - logreader: 2.0.0 - lookup_server_connector: 1.1.0 - nextcloud_announcements: 1.2.0 - notifications: 2.1.2 - oauth2: 1.1.1 - password_policy: 1.3.0 - provisioning_api: 1.3.0 - serverinfo: 1.3.0 - sharebymail: 1.3.0 - survey_client: 1.1.0 - systemtags: 1.3.0 - theming: 1.4.5 - twofactor_backupcodes: 1.2.3 - updatenotification: 1.3.0 - user_ldap: 1.3.1 - workflowengine: 1.3.0 Disabled: - bruteforcesettings - encryption - files_antivirus - files_external - user_external </details> **Nextcloud configuration:** <details> <summary>Config report</summary> { "system": { "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "***REMOVED SENSITIVE VALUE***", "***REMOVED SENSITIVE VALUE***" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***", "version": "13.0.4.0", "dbtype": "pgsql", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbname": "***REMOVED SENSITIVE VALUE***", "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "dbtableprefix": "oc_", "logtimezone": "UTC", "installed": true, "ldapIgnoreNamingRules": false, "loglevel": 3, "log_type": "owncloud", "logfile": "\/opt\/oc-data\/nextcloud.log", "maintenance": false, "singleuser": false, "memcache.local": "\\OC\\Memcache\\Redis", "filelocking.enabled": "true", "memcache.distributed": "\\OC\\Memcache\\Redis", "memcache.locking": "\\OC\\Memcache\\Redis", "redis": { "host": "***REMOVED SENSITIVE VALUE***", "port": 6379, "timeout": 0, "dbindex": 0 }, "trashbin_retention_obligation": "auto, 60", "versions_retention_obligation": "auto", "theme": "", "updater.release.channel": "production", "ldapProviderFactory": "\\OCA\\User_LDAP\\LDAPProviderFactory", "updater.secret": "***REMOVED SENSITIVE VALUE***" } } </details> **Are you using external storage, if yes which one:** local/smb/sftp/... NO **Are you using encryption:** yes/no NO **Are you using an external user-backend, if yes which one:** LDAP/ActiveDirectory/Webdav/... YES, LDAP Apple OD, config not pasted, as not relevant. </details> ### Client configuration **Browser:** **Operating system:** MACOS, nextcloud client 2.3.3 ### Logs #### Web server error log not relevant #### Nextcloud log (data/nextcloud.log) see above #### Browser log not relevant </details>

You could add to the discussion that you also have this problem during migration from sqlite. There is also a possible solution for you if you make this change manually:

github.com/nextcloud/server

Error reporting for too long filepaths (>255 chars)

opened 07:07AM - 19 Jun 18 UTC

jzaers

bug 1. to develop feature: dav 25-feedback

### Steps to reproduce 1. Use filenames with a resulting path longer than 255 c…haracters 2. This seems to result in various strange side effects like move not executed correctly and the following error message: ``` {"reqId":"xZAVDUDUMFn0yANyU6rA","level":4,"time":"2018-06-14T06:52:08+00:00","remoteAddr":"10.0.50.4","user":"87c48f84-4b31-4c8e-aac8-1c554f1b500b","app":"webdav","method":"MOVE","url":"\/owncloud\/remote.php\/dav\/files\/LONG_FILENAME_REPLACED_WITH_247_CHARACTERS","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'UPDATE \\\"oc_properties\\\" SET \\\"propertypath\\\" = ? WHERE \\\"userid\\\" = ? AND \\\"propertypath\\\" = ?' with params [\\\"files\\\\\\\LONG_FILENAME_REPLACED_WITH_247_CHARACTERS", \\\"LONG_FILENAME_REPLACED_WITH_247_CHARACTERS"]:\\n\\nSQLSTATE[22001]: String data, right truncated: **7 ERROR: value too long for type character varying(255)**\",\"Code\":0,\"Trace\":\"#0 \\\/opt\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(128): Doctrine\\\\DBAL\\\\Driver\\\\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/opt\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Statement.php(178): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOPgSql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'UPDATE \\\"oc_prop...', Array)\\n#2 \\\/opt\\\/nextcloud\\\/apps\\\/dav\\\/lib\\\/DAV\\\/CustomPropertiesBackend.php(179): Doctrine\\\\DBAL\\\\Statement->execute(Array)\\n#3 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/PropertyStorage\\\/Plugin.php(147): OCA\\\\DAV\\\\DAV\\\\CustomPropertiesBackend->move('files\\\/87c48f84-...', 'files\\\/87c48f84-...')\\n#4 [internal function]: Sabre\\\\DAV\\\\PropertyStorage\\\\Plugin->afterMove('files\\\/87c48f84-...', 'files\\\/87c48f84-...')\\n#5 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/event\\\/lib\\\/EventEmitterTrait.php(105): call_user_func_array(Array, Array)\\n#6 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/CorePlugin.php(648): Sabre\\\\Event\\\\EventEmitter->emit('afterMove', Array)\\n#7 [internal function]: Sabre\\\\DAV\\\\CorePlugin->httpMove(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#8 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/event\\\/lib\\\/EventEmitterTrait.php(105): call_user_func_array(Array, Array)\\n#9 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(479): Sabre\\\\Event\\\\EventEmitter->emit('method:MOVE', Array)\\n#10 \\\/opt\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(254): Sabre\\\\DAV\\\\Server->invokeMethod(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#11 \\\/opt\\\/nextcloud\\\/apps\\\/dav\\\/lib\\\/Server.php(258): Sabre\\\\DAV\\\\Server->exec()\\n#12 \\\/opt\\\/nextcloud\\\/apps\\\/dav\\\/appinfo\\\/v2\\\/remote.php(33): OCA\\\\DAV\\\\Server->exec()\\n#13 \\\/opt\\\/nextcloud\\\/remote.php(164): require_once('\\\/opt\\\/nextcloud\\\/...')\\n#14 {main}\",\"File\":\"\\\/opt\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractPostgreSQLDriver.php\",\"Line\":91}","userAgent":"Mozilla\/5.0 (Macintosh) mirall\/2.4.1 (build 9367)","version":"12.0.6.1"} ``` ### Expected behaviour I would like to - receive an error in the client during the initial sync of a file with a too long path, so that I can correct this on the client. - receive an sync error in the client when the move failed due to a too long filename - maybe a nice little helper routine that checks that local filenames are not exceeding the expected length. Upps, this depends on the Username in the server, the username can be long in scenarios with synced useraccounts ### Actual behaviour Files with too long paths are synced to the server and encounter problems on later move operations. It looks like the move is executed like a copy and the original file stays in the source directory. But this is not happening always. I assume that it depends on the resulting file path in the destination directory. ### Server configuration **Operating system**: ubuntu 14.04 LTS **Web server:** Apache 2.4 **Database:** postgresql 9.4 **PHP version:** php 5.6 **Nextcloud version:** (see Nextcloud admin page) see above, 12.0.6.1, (I updated the server to 13 afterwards, a few server configs below are from the new server, I try to reproduce this under 13.) **Updated from an older Nextcloud/ownCloud or fresh install:** Migration from owncloud 9 or 10. **Where did you install Nextcloud from:** **Signing status:** <details> <summary>Signing status</summary> No errors have been found. </details> **List of activated apps:** This is from the server after upgrade to v 13. Enabled: - activity: 2.6.1 - admin_audit: 1.3.0 - comments: 1.3.0 - dav: 1.4.7 - federatedfilesharing: 1.3.1 - federation: 1.3.0 - files: 1.8.0 - files_pdfviewer: 1.2.1 - files_sharing: 1.5.0 - files_texteditor: 2.5.1 - files_trashbin: 1.3.0 - files_versions: 1.6.0 - files_videoplayer: 1.2.0 - firstrunwizard: 2.2.1 - gallery: 18.0.0 - logreader: 2.0.0 - lookup_server_connector: 1.1.0 - nextcloud_announcements: 1.2.0 - notifications: 2.1.2 - oauth2: 1.1.1 - password_policy: 1.3.0 - provisioning_api: 1.3.0 - serverinfo: 1.3.0 - sharebymail: 1.3.0 - survey_client: 1.1.0 - systemtags: 1.3.0 - theming: 1.4.5 - twofactor_backupcodes: 1.2.3 - updatenotification: 1.3.0 - user_ldap: 1.3.1 - workflowengine: 1.3.0 Disabled: - bruteforcesettings - encryption - files_antivirus - files_external - user_external </details> **Nextcloud configuration:** <details> <summary>Config report</summary> { "system": { "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "***REMOVED SENSITIVE VALUE***", "***REMOVED SENSITIVE VALUE***" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***", "version": "13.0.4.0", "dbtype": "pgsql", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbname": "***REMOVED SENSITIVE VALUE***", "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "dbtableprefix": "oc_", "logtimezone": "UTC", "installed": true, "ldapIgnoreNamingRules": false, "loglevel": 3, "log_type": "owncloud", "logfile": "\/opt\/oc-data\/nextcloud.log", "maintenance": false, "singleuser": false, "memcache.local": "\\OC\\Memcache\\Redis", "filelocking.enabled": "true", "memcache.distributed": "\\OC\\Memcache\\Redis", "memcache.locking": "\\OC\\Memcache\\Redis", "redis": { "host": "***REMOVED SENSITIVE VALUE***", "port": 6379, "timeout": 0, "dbindex": 0 }, "trashbin_retention_obligation": "auto, 60", "versions_retention_obligation": "auto", "theme": "", "updater.release.channel": "production", "ldapProviderFactory": "\\OCA\\User_LDAP\\LDAPProviderFactory", "updater.secret": "***REMOVED SENSITIVE VALUE***" } } </details> **Are you using external storage, if yes which one:** local/smb/sftp/... NO **Are you using encryption:** yes/no NO **Are you using an external user-backend, if yes which one:** LDAP/ActiveDirectory/Webdav/... YES, LDAP Apple OD, config not pasted, as not relevant. </details> ### Client configuration **Browser:** **Operating system:** MACOS, nextcloud client 2.3.3 ### Logs #### Web server error log not relevant #### Nextcloud log (data/nextcloud.log) see above #### Browser log not relevant </details>

Before you do modifications and change the database, don’t forget to make a full backup.