Specified key too long

[RoberL72]

[Potherca]

Hi! Thank you for reporting this issue.

This looks to be the same problem as reported in #107.

That issue was resolved with the release of version v0.7.1. The current release in the Nextcloud App Store is 0.7.2 (available for NC24 and NC25) so it shouldn't occur.

Could you provide more details (PHP version, NC version, what you were doing when this error occurred, etc.) so we can try to figure out what is going on?

[RoberL72]

Hi Ben, Sorry for my late response. Nextcloud is version 25.0.4. Solid 0.7.2 I wasn’t running PHP yet, just started a clean VM with Nextcloud only. Just installed PHP 8.1.17 Linux Ubuntu 22.04 Rebooted. Installed Solid App through the App option inside Nextcloud. When I hit “Inschakelen” it comes up with: Er is een fout opgetreden tijdens het verzoek. Kan niet doorgaan. An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was > too long; max key length is 3072 bytes Robert Jan

[Potherca]

I started work to try and reproduce this issue. I was using docker, but as it turns out, one can't simply run snap in docker.

So instead of creating a reproducible case using docker, I will try again with an actual VM.

I've used VirtualBox in the past, but I'll have to touch up on my knowledge, as that was almost 10 years ago.

I'll report back when I have any results.

[eddriesen]

Hey, just to chime in; same problem. Ask me anything that could help the issue.

on Ubuntu 20.04.5 LTS, nc 25.0.4, trying to install solid 0.7.2

Do you fancy a logfile?

Full log

Fout	settings	Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php - line 1780:
    Doctrine\DBAL\Driver\API\MySQL\ExceptionConverter->convert()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php - line 1719:
    Doctrine\DBAL\Connection->handleDriverException()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php - line 1067:
    Doctrine\DBAL\Connection->convertExceptionDuringQuery()

    /var/www/html/nextcloud/lib/private/DB/Connection.php - line 261:
    Doctrine\DBAL\Connection->executeQuery()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php - line 1809:
    OC\DB\Connection->executeQuery()

    /var/www/html/nextcloud/lib/private/DB/Migrator.php - line 162:
    Doctrine\DBAL\Connection->query()

    /var/www/html/nextcloud/lib/private/DB/Migrator.php - line 69:
    OC\DB\Migrator->applySchema()

    /var/www/html/nextcloud/lib/private/DB/Connection.php - line 582:
    OC\DB\Migrator->migrate()

    /var/www/html/nextcloud/lib/private/DB/MigrationService.php - line 454:
    OC\DB\Connection->migrateToSchema()

    /var/www/html/nextcloud/lib/private/DB/MigrationService.php - line 408:
    OC\DB\MigrationService->migrateSchemaOnly()

    /var/www/html/nextcloud/lib/private/Installer.php - line 153:
    OC\DB\MigrationService->migrate()

    /var/www/html/nextcloud/apps/settings/lib/Controller/AppSettingsController.php - line 451:
    OC\Installer->installApp()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 225:
    OCA\Settings\Controller\AppSettingsController->enableApps()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 133:
    OC\AppFramework\Http\Dispatcher->executeController()

    /var/www/html/nextcloud/lib/private/AppFramework/App.php - line 172:
    OC\AppFramework\Http\Dispatcher->dispatch()

    /var/www/html/nextcloud/lib/private/Route/Router.php - line 298:
    OC\AppFramework\App::main()

    /var/www/html/nextcloud/lib/base.php - line 1047:
    OC\Route\Router->match()

    /var/www/html/nextcloud/index.php - line 36:
    OC::handleRequest()

Veroorzaakt door Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php - line 87:
    Doctrine\DBAL\Driver\PDO\Exception::new()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php - line 1062:
    Doctrine\DBAL\Driver\PDO\Connection->query()

    /var/www/html/nextcloud/lib/private/DB/Connection.php - line 261:
    Doctrine\DBAL\Connection->executeQuery()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php - line 1809:
    OC\DB\Connection->executeQuery()

    /var/www/html/nextcloud/lib/private/DB/Migrator.php - line 162:
    Doctrine\DBAL\Connection->query()

    /var/www/html/nextcloud/lib/private/DB/Migrator.php - line 69:
    OC\DB\Migrator->applySchema()

    /var/www/html/nextcloud/lib/private/DB/Connection.php - line 582:
    OC\DB\Migrator->migrate()

    /var/www/html/nextcloud/lib/private/DB/MigrationService.php - line 454:
    OC\DB\Connection->migrateToSchema()

    /var/www/html/nextcloud/lib/private/DB/MigrationService.php - line 408:
    OC\DB\MigrationService->migrateSchemaOnly()

    /var/www/html/nextcloud/lib/private/Installer.php - line 153:
    OC\DB\MigrationService->migrate()

    /var/www/html/nextcloud/apps/settings/lib/Controller/AppSettingsController.php - line 451:
    OC\Installer->installApp()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 225:
    OCA\Settings\Controller\AppSettingsController->enableApps()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 133:
    OC\AppFramework\Http\Dispatcher->executeController()

    /var/www/html/nextcloud/lib/private/AppFramework/App.php - line 172:
    OC\AppFramework\Http\Dispatcher->dispatch()

    /var/www/html/nextcloud/lib/private/Route/Router.php - line 298:
    OC\AppFramework\App::main()

    /var/www/html/nextcloud/lib/base.php - line 1047:
    OC\Route\Router->match()

    /var/www/html/nextcloud/index.php - line 36:
    OC::handleRequest()

Veroorzaakt door PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php - line 82:
    PDO->query()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php - line 1062:
    Doctrine\DBAL\Driver\PDO\Connection->query()

    /var/www/html/nextcloud/lib/private/DB/Connection.php - line 261:
    Doctrine\DBAL\Connection->executeQuery()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php - line 1809:
    OC\DB\Connection->executeQuery()

    /var/www/html/nextcloud/lib/private/DB/Migrator.php - line 162:
    Doctrine\DBAL\Connection->query()

    /var/www/html/nextcloud/lib/private/DB/Migrator.php - line 69:
    OC\DB\Migrator->applySchema()

    /var/www/html/nextcloud/lib/private/DB/Connection.php - line 582:
    OC\DB\Migrator->migrate()

    /var/www/html/nextcloud/lib/private/DB/MigrationService.php - line 454:
    OC\DB\Connection->migrateToSchema()

    /var/www/html/nextcloud/lib/private/DB/MigrationService.php - line 408:
    OC\DB\MigrationService->migrateSchemaOnly()

    /var/www/html/nextcloud/lib/private/Installer.php - line 153:
    OC\DB\MigrationService->migrate()

    /var/www/html/nextcloud/apps/settings/lib/Controller/AppSettingsController.php - line 451:
    OC\Installer->installApp()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 225:
    OCA\Settings\Controller\AppSettingsController->enableApps()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 133:
    OC\AppFramework\Http\Dispatcher->executeController()

    /var/www/html/nextcloud/lib/private/AppFramework/App.php - line 172:
    OC\AppFramework\Http\Dispatcher->dispatch()

    /var/www/html/nextcloud/lib/private/Route/Router.php - line 298:
    OC\AppFramework\App::main()

    /var/www/html/nextcloud/lib/base.php - line 1047:
    OC\Route\Router->match()

    /var/www/html/nextcloud/index.php - line 36:
    OC::handleRequest()`

[Potherca]

@eddriesen thank you very much!

[Potherca]

Looking back at the research done for #107, I realized there are a few factors we are currently missing information about...

1. The MySQL / MariaDB version. Various sources regarding this problem noted that this problem was most prominent on MySQL older than the 5.7.7 or MariaDB older than 10.2.2. So knowing which DB version is used might be helpful.

2. The DB engine used (InnoDB vs. MyISAM).

3. The character set (charset) and collation that are used. Various reports specify this problem being more prevalent with utf8mb4 than with utf8. Also, various collations seem to have an impact.

I'll try and set up a test case for with various versions.

I think the OS, Plugin version, and PHP version are less relevant.

What we already know is:

User	OS	NextCloud	Plugin	PHP	DB	Engine	Charset	Allocation
@eddriesen	Ubuntu 20.04.5	25.0.4	0.7.2	7.4.3	MySQL 8.0.32	innodb	utf8mb4	(?)
@JurgenG	Debian			8.1.11	MariaDB 10.5.15	(?)	(?)	(?)
@RoberL72	Ubuntu 22.04	25.0.4	0.7.2	8.1.16	Mysql 8.0.32	(?)	(?)	(?)

So... @eddriesen, @JurgenG, and @RoberL72: Is it possible for you to report which DB and which Engine you use? I think the Charset, an Allocation could be inferred either from the default settings for the DB or NC itself, if they are harder to locate/report.

---

Another thing I noticed is that, even though uri was confirmed to be the trigger, this does not automatically exclude jit from also being an issue (as it could be up to 255 chars long).

But this was never confirmed/disputed, so I'll need to double-check that as well.

[RoberL72]

Hi Ben, Mysql 8.0.32 I’m no expert on VM’s, if you need me to check the others like engine and character set, please give me a (simple) guidance of howto find this. Cheers, Robert Jan

[RoberL72]

PS. Noticed my PHP is 8.1.16

[eddriesen]

@eddriesen https://github.com/eddriesen

• Ubuntu 20.04.5
• 25.0.4
• 0.7.2
• PHP 7.4.3-4ubuntu2.18.
• MySQL version 8.0.32-0ubuntu0.20.04.2
• innodb
• utf8mb4
• 544mb

Fun fact, I've been upgrading this instance since early owncloud days....

[Potherca]

I've managed to create a docker setup in which I can reproduce the error. Currently debugging to find out the source of the problem.

[Potherca]

The reproducable case was created using a docker-compose.yml.

see file

version: '3'

services:
  db:
    environment:
      MYSQL_DATABASE: nextcloud
      MYSQL_PASSWORD: nextcloud
      MYSQL_ROOT_PASSWORD: root
      MYSQL_USER: nextcloud
    image: mysql:8.0.32
    ports:
      - "3306:3306"
  # To start with a populated database, mount an SQL file
  #  volumes:
  #    - ./init.sql:/docker-entrypoint-initdb.d/init.sql

  nextcloud:
    depends_on:
      - db
    environment:
      - MARIADB_ROOT_PASSWORD=nextcloud
      - MYSQL_HOST=db
    expose:
      - 443
    image: ghcr.io/pdsinterop/solid-nextcloud:main-25
    ports:
      - "443:443"
    volumes:
      - ./solid/:/var/www/html/apps/solid/

  pubsub:
    depends_on:
      - nextcloud
    expose:
      - 8080
    image: ghcr.io/pdsinterop/php-solid-pubsub-server
    ports:
      - "8080:8080"

Reading along with the stack trace provided by eddriesen, things start being interesting from calls to lib/private/DB/Migrator.php.

Adding var_dump($sql) to lib/private/DB/Migrator.php:162 give something similar to:

CREATE TABLE oc_solid_jti (
    id INT AUTO_INCREMENT NOT NULL,
    jti VARCHAR(255) NOT NULL,
    uri VARCHAR(255) NOT NULL,
    request_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    INDEX IDX_C693C86FC53CF2EA841CB121 (jti, uri),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_bin` ENGINE = InnoDB

So the current assumption is that the defaults for MySQL are InnoDB, utf8mb4, and utf8mb4_bin.

Will dig deeper later (most likely Friday).

[timbot1789]

Just want to chime in here, as I've been having the same issue. Installed Nextcloud through snap, installed the app through the app store.

• Nextcloud: 25.0.7
• Ubuntu: 22.04.2
• plugin: 0.7.2
• MySql: 8.0.33
• PHP: 8.1.2

Oddly, I ran the install on NextCloud version 27 as well and did not encounter this issue.

[Chris-Fourie]

Hi there (",) I seem to be getting the same error when trying to install the app via the App Store.

Interesting that the ticket is still open and there have been no other mentions of this in the last few years? (unless I am missing something)

To note the fix below does get the app installed - but I haven't yet validated that it is working fully.

---

Environment:
Deployed via https://elest.io/
Nextcloud 30.0.16.1
MySQL 8.0.43 (Docker)
utf8mb4_bin collation

Error:
SQLSTATE[42000]: 1071 Specified key was too long; max key length is 3072 bytes`

Workaround:
Manually create the table before installing:

CREATE TABLE oc_solid_webhooks (
  id INT AUTO_INCREMENT NOT NULL,
  web_id VARCHAR(2048) NOT NULL,
  topic VARCHAR(2048) NOT NULL,
  target VARCHAR(2048) NOT NULL,
  INDEX solid_webhooks_web_id_index (web_id(768)),
  INDEX solid_webhooks_target_index (target(768)),
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB;

Fix:
Update database migrations to use index prefixes: INDEX name (column(768)) for large VARCHAR columns.