Change collation for oc_ocsms_smsdatas fails

[xamindar]

Steps to reproduce

1. Click "Start update" on nextcloud web instance
2. Update fails with the following error: Repair info: Change collation for oc_ocsms_smsdatas ...

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE oc_ocsms_smsdatas CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;': SQLSTATE[42000]: Syntax error or access violation: 1074 Column length too big for column 'sms_msg' (max = 16383); use BLOB or TEXT instead

Expected behaviour

Update should complete without issue.

Actual behaviour

Update fails with above mentioned error.

Server configuration

Operating system: Linux version 5.2.0-arch2-1-ARCH (builduser@heftig-101614) (gcc version 9.1.0 (GCC)) nextcloud/server#1 SMP PREEMPT Mon Jul 8 18:18:54 UTC 2019 Web server: Server version: Apache/2.4.39 (Unix) Server built: Apr 2 2019 04:02:43

Database: Server version: 10.4.6-MariaDB Source distribution

PHP version: PHP 7.3.7 (cli) (built: Jul 3 2019 13:55:48) ( NTS ) Copyright (c) 1997-2018 The PHP Group Zend Engine v3.3.7, Copyright (c) 1998-2018 Zend Technologies with Zend OPcache v7.3.7, Copyright (c) 1999-2018, by Zend Technologies

Nextcloud version: (see Nextcloud admin page) version 16.0.3 Updated from an older Nextcloud/ownCloud or fresh install: Updated from a previous version.

Where did you install Nextcloud from: Originally Arch package, but have been updating with the built-in updater.

List of activated apps:

App list

Enabled: - accessibility: 1.2.0 - activity: 2.9.1 - admin_audit: 1.6.0 - audioplayer: 2.7.2 - bookmarks: 1.0.6 - cloud_federation_api: 0.2.0 - comments: 1.6.0 - contacts: 3.1.3 - dav: 1.9.2 - federatedfilesharing: 1.6.0 - federation: 1.6.0 - files: 1.11.0 - files_external: 1.7.0 - files_markdown: 2.0.6 - files_pdfviewer: 1.5.0 - files_rightclick: 0.13.0 - files_sharing: 1.8.0 - files_texteditor: 2.8.0 - files_trashbin: 1.6.0 - files_versions: 1.9.0 - files_videoplayer: 1.5.0 - firstrunwizard: 2.5.0 - gallery: 18.3.0 - logreader: 2.1.0 - lookup_server_connector: 1.4.0 - nextcloud_announcements: 1.5.0 - notes: 3.0.0 - notifications: 2.4.1 - oauth2: 1.4.2 - password_policy: 1.6.0 - privacy: 1.0.0 - provisioning_api: 1.6.0 - recommendations: 0.4.0 - serverinfo: 1.6.0 - sharebymail: 1.6.0 - support: 1.0.0 - survey_client: 1.4.0 - systemtags: 1.6.0 - theming: 1.7.0 - twofactor_backupcodes: 1.5.0 - updatenotification: 1.6.0 - viewer: 1.0.0 - workflowengine: 1.6.0 Disabled: - apporder - bruteforcesettings - calendar - encryption - ownbackup - qownnotesapi - richdocuments - tasks - user_ldap

Nextcloud configuration:

Config report

Nextcloud 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 { "system": { "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "jaredkidd.com", "nextcloud.jaredkidd.com", "172.16.0.13", "172.16.8.10" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "overwrite.cli.url": "https:\/\/jaredkidd.com\/nextcloud", "htaccess.RewriteBase": "\/nextcloud", "overwriteprotocol": "https", "dbtype": "mysql", "version": "16.0.1.1", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbport": "", "dbtableprefix": "oc_", "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "logtimezone": "UTC", "installed": true, "memcache.local": "\\OC\\Memcache\\APCu", "mail_smtpmode": "smtp", "mail_from_address": "***REMOVED SENSITIVE VALUE***", "mail_domain": "***REMOVED SENSITIVE VALUE***", "theme": "", "loglevel": 0, "maintenance": false, "updater.release.channel": "beta", "mail_smtpsecure": "tls", "mail_smtphost": "***REMOVED SENSITIVE VALUE***", "mail_smtpauthtype": "LOGIN", "mail_smtpauth": 1, "mail_smtpport": "587", "mail_sendmailmode": "smtp", "mail_smtpname": "***REMOVED SENSITIVE VALUE***", "mail_smtppassword": "***REMOVED SENSITIVE VALUE***", "mysql.utf8mb4": true, "updater.secret": "***REMOVED SENSITIVE VALUE***" } }

**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

Browser: firefox Operating system: Linux

Additionally, when going through the conversion mentioned here, I receive the errors listed below. I have tried backing up the database, recreating it, and restoring it. Same result: https://docs.nextcloud.com/server/13/admin_manual/configuration_database/mysql_4byte_support.html

sudo -u http php occ maintenance:repair

Nextcloud 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

• Repair MySQL collation
  • Change row format for oc_ocsms_smsdatas ...
  • Change collation for oc_ocsms_smsdatas ...

In AbstractMySQLDriver.php line 125:

An exception occurred while executing 'ALTER TABLE oc_ocsms_smsdatas CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':

SQLSTATE[42000]: Syntax error or access violation: 1074 Column length too big for column 'sms_msg' (max = 16383); use BLOB or TEXT instead

In PDOStatement.php line 143:

SQLSTATE[42000]: Syntax error or access violation: 1074 Column length too big for column 'sms_msg' (max = 16383); use BLOB or TEXT instead

In PDOStatement.php line 141:

SQLSTATE[42000]: Syntax error or access violation: 1074 Column length too big for column 'sms_msg' (max = 16383); use BLOB or TEXT instead

maintenance:repair [--include-expensive]

[kesselb]

Do you have an idea @nerzhul?

[nerzhul]

See https://github.com/nextcloud/ocsms/issues/169 that can help

[xamindar]

See nextcloud/ocsms#169 that can help

Looks like this can be avoided by not using the "sms app"? I can not find anything obvious in the app settings. Which app is this so I can disable it as I'm pretty sure I'm not using it at this time.

Could it be as simple as stopping/removing this sms app and dropping that table?

[xamindar]

Any fix for this? I haven't been able to update past 16.0.1

[e-alfred]

@xamindar Did you try the fix by @dienteperro: https://github.com/nextcloud/ocsms/issues/169#issuecomment-277397119

[kolaente]

I had the same problem after upgrading from 16.0 to 17.0, running

ALTER TABLE oc_ocsms_smsdatas MODIFY sms_msg TEXT;

manually in the db fixed the issue.

[xamindar]

@xamindar Did you try the fix by @dienteperro: #169 (comment)

Thanks, I tried that and couldn't get it working. It looks like I might need to wipe my install and start over without restoring the backup as every time I restore the backup I also get this same issue. Too bad but I guess that can happen when things change too much from an early version of Nextcloud. Though, it would be nice to have a warning. Sonething along the lines of "if you started out on Nextcloud version ~10, you will need to start fresh with a new database.

[xamindar]

ALTER TABLE oc_ocsms_smsdatas MODIFY sms_msg TEXT;

Thanks kolaente, but I believe that was listed in the upgrade docs if I remember correctly? I just tried again to be sure and nothing was changed: MariaDB [nextcloud]> ALTER TABLE oc_ocsms_smsdatas MODIFY sms_msg TEXT; Query OK, 0 rows affected (3.772 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [nextcloud]>

[xamindar]

I'm not sure what happened but the update finally worked. Is it possible kolaente's suggestion did something even though it said nothing was modified?

In any case, the upgrade to 17.0.2 went smoothly this time.