CLI installation fails if table-prefix contains hyphen; Upgrade fails as well in CLI and browser

[t-markmann]

Steps to reproduce

1. take fresh NC13 tar.bz2 archive from nextcloud.com, unzip
2. create database at your webhost (Hetzner in this case, i can not choose collation, or character set )
3. try CLI installation with command: sudo -u www-data php occ maintenance:install --database "mysql" --database-host "myserver.com:3306" --database-table-prefix "test-hyphen__" --database-name "db_test" --database-user "db_testusr" --database-pass "db_testpass" --admin-user "admin" --admin-pass "testadmin"

Expected behaviour

Nextcloud 13 should be installed; install routine worked fine with Nextcloud 12

Actual behaviour

CLI error message appears:
"Error while trying to create admin user: An exception occurred while executing 'CREATE TABLE test-hyphen__migrations (app VARCHAR(255) NOT NULL, version VARCHAR(255) NOT NULL, PRIMARY KEY(app, version)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB ROW_FORMAT = compressed':

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-hyphen__migrations (app VARCHAR(255) NOT NULL, version VARCHAR(255) NOT NULL, P' at line 1"

Nextcloud is not installed; no tables created; no data dir created (therefor no nextcloud log file)

Server configuration detail

Operating system: Linux 4.4.0-130-generic #156-Ubuntu SMP Thu Jun 14 08:53:28 UTC 2018 x86_64

Webserver: Apache/2.4.33 (Ubuntu) (apache2handler)

Database: mysql 5.7.22

PHP version: 7.0.30-0ubuntu0.16.04.1
Modules loaded: Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, session, standard, apache2handler, mysqlnd, PDO, xml, apcu, calendar, ctype, curl, dom, mbstring, fileinfo, ftp, gd, gettext, iconv, igbinary, intl, json, exif, mcrypt, mysqli, pdo_mysql, pdo_sqlite, Phar, posix, readline, redis, shmop, SimpleXML, sockets, sqlite3, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xmlreader, xmlwriter, xsl, zip, Zend OPcache

Nextcloud version: 13.0.4 - 13.0.4.0

Updated from an older Nextcloud/ownCloud or fresh install: no

Where did you install Nextcloud from: https://download.nextcloud.com/server/releases/nextcloud-13.0.4.tar.bz2

Signing status

Array

List of activated apps

Enabled:
 - activity: 2.6.1
 - admin_audit: 1.3.0
 - bookmarks: 0.11.0
 - bruteforcesettings: 1.1.0
 - calendar: 1.6.1
 - comments: 1.3.0
 - contacts: 2.1.5
 - dav: 1.4.7
 - external: 3.0.3
 - 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
 - goshortlinks: 0.0.4
 - issuetemplate: 0.3.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
 - richdocuments: 2.0.8
 - serverinfo: 1.3.0
 - sharebymail: 1.3.0
 - spreed: 3.2.3
 - survey_client: 1.1.0
 - systemtags: 1.3.0
 - tasks: 0.9.6
 - theming: 1.4.5
 - twofactor_backupcodes: 1.2.3
 - updatenotification: 1.3.0
 - workflowengine: 1.3.0
Disabled:
 - announcementcenter
 - cloudadministration
 - coursemanager
 - encryption
 - files_external
 - firstrunwizard
 - gallery
 - user_external
 - user_ldap

Configuration (config/config.php)

{
    "instanceid": "***REMOVED SENSITIVE VALUE***",
    "passwordsalt": "***REMOVED SENSITIVE VALUE***",
    "secret": "***REMOVED SENSITIVE VALUE***",
    "trusted_domains": [
        "mydomain.com",
        "*.mydomain.com",
    ],
    "datadirectory": "***REMOVED SENSITIVE VALUE***",
    "overwrite.cli.url": "<PRIVATE>",
    "dbtype": "mysql",
    "version": "13.0.4.0",
    "dbname": "***REMOVED SENSITIVE VALUE***",
    "dbhost": "***REMOVED SENSITIVE VALUE***",
    "dbport": "",
    "dbtableprefix": "<PRIVATE>",
    "mysql.utf8mb4": true,
    "dbuser": "***REMOVED SENSITIVE VALUE***",
    "dbpassword": "***REMOVED SENSITIVE VALUE***",
    "installed": true,
    "maintenance": false,
    "memcache.local": "\\OC\\Memcache\\APCu",
    "mail_from_address": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpmode": "smtp",
    "mail_smtpauthtype": "LOGIN",
    "mail_domain": "***REMOVED SENSITIVE VALUE***",
    "theme": "",
    "loglevel": 2,
    "mail_smtpsecure": "ssl",
    "mail_smtpauth": 1,
    "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpport": "465",
    "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
    "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
    "default_language": "de"
}

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: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:61.0) Gecko/20100101 Firefox/61.0

Operating system:

[nextcloud-bot]

GitMate.io thinks possibly related issues are #8963 (Alpine installation failed ), #4186 (installation fails due to "no app in context"), #2683 (Postgres installation with occ tool fails ), #631 (Ability to change Database prefix in Installation Process), and #7131 ( Sync fails when file/directory names contain commas ).

[Kramins]

I also get something similar on a fresh install.

Docker container: nextcloud:latest
MySql container: mysql:latest (8.0.11)

Error:

Error while trying to create admin user: An exception occurred while executing 'CREATE TABLE groups (gid VARCHAR(64) DEFAULT '' NOT NULL, PRIMARY KEY(gid)) DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ENGINE = InnoDB': SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups (gid VARCHAR(64) DEFAULT '' NOT NULL, PRIMARY KEY(gid)) DEFAULT CHARACTER' at line 1

[t-markmann]

Thanks for confirmation Kramins.

I would like to add that this error appears as well, if I upgrade an instance from NC12 to NC13.
In this situation it does not matter if I perform the upgrade on CLI or in the browser. Same error.

The problem only seems to be the hyphen in tableprefix.

[MorrisJobke]

cc @nickvergessen for DB checks

[nickvergessen]

Hyphens are not supported in table names I though. How did you manage to install in first place?

[t-markmann]

With NC12 occ install worked fine with hyphens. I did not think I would run into problems with this process. Would be cool if occ had warned me :)

Now I have about 5 customer instances with hyphens in DB-prefixes and would like to continue offering hyphens in new customer instances.

If you cannot repair this in the install process, I have to migrate all tables to tablenames without hyphens and in my install routine I have to replace the hyphen with an underscore.

Can I rename existing tables without running into problems with nextcloud (thinking about keys, uniques, indexes....)? Is there a best practice, maybe with a backup tool where I can set new DB prefix?

Thank you for your efforts.

[nickvergessen]

Now I have about 5 customer instances with hyphens in DB-prefixes and would like to continue offering hyphens in new customer instances.

The database prefix should not be visible to your customers at all 🙈

We do not recommend to change it, since it can lead to major troubles on various ends

[t-markmann]

please don't get me wrong :) the prefix (e.g. in config.php) is not visible to my customers. The customers only get the web gui, nothing else, no sftp access or even admin-account.
Customers choose their instance name. The table-prefix equals the instance name.

okay, thanks for pointing out that renaming tables is not recommended. My options are now:

1. leaving the instances on NC12 until it's not supported anymore
2. find out why NC12 accepted the hyphens and force NC13 to do it too
3. play around with export/import in my DBMS and hope renaming the tables works out somehow

no good way to go here..............

[MorrisJobke]

We do not recommend to change it, since it can lead to major troubles on various ends

Do you have anything specific in mind? Just rename it and change also the prefix in the config.php should do the trick, right?

[nickvergessen]

Yeah that should work

[t-markmann]

Nice, it worked :-) I tested a few features and apps and everything seems to run just fine.

I renamed the tables with phpmyadmin, it has a feature for changing table prefixes.
Thanks for the support!

[nickvergessen]

I'd like to remove this option for 15, what do you think @MorrisJobke

[MorrisJobke]

I'd like to remove this option for 15, what do you think @MorrisJobke

Which option? The prefix?

[nickvergessen]

Yeah removing the possibility to enter it at all and hard-code oc_

[MorrisJobke]

Yeah removing the possibility to enter it at all and hard-code oc_

But then we should add a setup check to 14 to already let people know about this and report to a ticket either here or in the forums.

[t-markmann]

From a customers point of view: Table prefixes give me the opportunity for easyer testing and installing. This applies to Nextcloud, Owncloud and plenty of other Software.
With Prefixes I can even just have 1 DB for each customer. This way a customer-DB can host different Software. This becomes even more important when hosting in a complex/expensive cloud environment like HA DB Clusters, where I have to pay for each DB ;-) It gives much more flexibility to customers and is found in a lot of other software as well. I consider it an industry standard to have prefixes.

I understand that removing this feature takes away complexity and you consider it therefore a good step. In the process of NC-app development it is not hard to implement the prefix. Is it too hard in development of the NC core?

[nickvergessen]

Well the thing is some of the indexes are not using the prefix and indexes need to have unique names. That was one little oversight when prefixes were introduced back then.
I think installing multiple NCs into one DB only works in mysql/maria