Specified key too long #118

RoberL72 · 2023-03-16T19:37:22Z

Potherca · 2023-03-17T09:40:00Z

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 · 2023-03-17T20:05:44Z

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 · 2023-03-20T12:19:45Z

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 · 2023-03-20T12:27:07Z

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 · 2023-03-20T13:17:05Z

@eddriesen thank you very much!

Potherca · 2023-03-20T13:21:26Z

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

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.
The DB engine used (InnoDB vs. MyISAM).
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 · 2023-03-20T20:10:47Z

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 · 2023-03-20T20:14:33Z

PS. Noticed my PHP is 8.1.16

eddriesen · 2023-03-21T14:04:55Z

@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 · 2023-03-22T13:35:47Z

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 · 2023-03-22T15:08:55Z

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 · 2023-06-15T04:16:57Z

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.

Potherca self-assigned this Mar 17, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Specified key too long #118

Specified key too long #118

RoberL72 commented Mar 16, 2023

Potherca commented Mar 17, 2023

RoberL72 commented Mar 17, 2023 via email •

edited by Potherca

Loading

Potherca commented Mar 20, 2023

eddriesen commented Mar 20, 2023 •

edited by Potherca

Loading

Potherca commented Mar 20, 2023

Potherca commented Mar 20, 2023 •

edited

Loading

RoberL72 commented Mar 20, 2023 via email •

edited by Potherca

Loading

RoberL72 commented Mar 20, 2023 via email •

edited by Potherca

Loading

eddriesen commented Mar 21, 2023 •

edited by Potherca

Loading

Potherca commented Mar 22, 2023

Potherca commented Mar 22, 2023

timbot1789 commented Jun 15, 2023 •

edited

Loading

Specified key too long #118

Specified key too long #118

Comments

RoberL72 commented Mar 16, 2023

Potherca commented Mar 17, 2023

RoberL72 commented Mar 17, 2023 via email • edited by Potherca Loading

Potherca commented Mar 20, 2023

eddriesen commented Mar 20, 2023 • edited by Potherca Loading

Potherca commented Mar 20, 2023

Potherca commented Mar 20, 2023 • edited Loading

RoberL72 commented Mar 20, 2023 via email • edited by Potherca Loading

RoberL72 commented Mar 20, 2023 via email • edited by Potherca Loading

eddriesen commented Mar 21, 2023 • edited by Potherca Loading

Potherca commented Mar 22, 2023

Potherca commented Mar 22, 2023

timbot1789 commented Jun 15, 2023 • edited Loading

RoberL72 commented Mar 17, 2023 via email •

edited by Potherca

Loading

eddriesen commented Mar 20, 2023 •

edited by Potherca

Loading

Potherca commented Mar 20, 2023 •

edited

Loading

RoberL72 commented Mar 20, 2023 via email •

edited by Potherca

Loading

RoberL72 commented Mar 20, 2023 via email •

edited by Potherca

Loading

eddriesen commented Mar 21, 2023 •

edited by Potherca

Loading

timbot1789 commented Jun 15, 2023 •

edited

Loading