error after installation at update database

[erichelch]

After installing the extension I have to log in the contao backend and should make the db-update - starting i get this error in my Logfile:
[2021-11-16 09:18:57] request.INFO: Matched route "contao_install". {"route":"contao_install","route_parameters":{"_route":"contao_install","_scope":"backend","_token_check":true,"_controller":"Contao\InstallationBundle\Controller\InstallationController::installAction"},"request_uri":"https://domain/contao/install","method":"POST"} []
[2021-11-16 09:18:58] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'CREATE TABLE tl_address (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, street VARCHAR(255) DEFAULT '' NOT NULL, number VARCHAR(255) DEFAULT '' NOT NULL, apartment VARCHAR(255) DEFAULT '' NOT NULL, postal VARCHAR(255) DEFAULT '' NOT NULL, city VARCHAR(255) DEFAULT '' NOT NULL, country VARCHAR(2) DEFAULT '' NOT NULL, UNIQUE INDEX pid_street_number_apartment_postal_city_country (pid, street, number, apartment, postal, city, country), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes" at /home/domain/public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 {"exception":"[object] (Doctrine\DBAL\Exception\DriverException(code: 0): An exception occurred while executing 'CREATE TABLE tl_address (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, street VARCHAR(255) DEFAULT '' NOT NULL, number VARCHAR(255) DEFAULT '' NOT NULL, apartment VARCHAR(255) DEFAULT '' NOT NULL, postal VARCHAR(255) DEFAULT '' NOT NULL, city VARCHAR(255) DEFAULT '' NOT NULL, country VARCHAR(2) DEFAULT '' NOT NULL, UNIQUE INDEX pid_street_number_apartment_postal_city_country (pid, street, number, apartment, postal, city, country), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC':\n\nSQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes at /home/domain/public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\DBAL\Driver\PDO\Exception(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes at /home/domain/public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes at /home/domain/public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141)"} []

Contao 4.9.16 / PHP 7.4.21

[fritzmg]

Which MySQL/MariaDB version are you using?

[erichelch]

mysql 5.7.34

[fritzmg]

Make sure the following settings are enabled in your MySQL sever: https://docs.contao.org/manual/en/installation/system-requirements/#mysql-configuration

[webstoney]

I'm the database guy in this issue. Problem is, mysql restricts the size of a key to 3072 bytes (not chars!).

In utf8mb4 each char counts as 4 bytes, so a varchar(255) uses 1k of keyspace.

2 possibilities:

1. Use smaller VARCHAR() sizes
2. Only use a certain amount of characters for the keys e.g. UNIQUE INDEX k_number (number(10))

Otherwise it won't work.

[fritzmg]

@webstoney did you make sure you are using theses settings? https://docs.contao.org/manual/en/installation/system-requirements/#mysql-configuration

[webstoney]

Yes, I did

[fritzmg]

Hm, I cannot reproduce the problem in MariaDB or MySQL. In theory it should work as long as you use Barracuda, innodb_large_prefix and innodb_file_per_table.

[webstoney]

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0,00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lar%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0,00 sec)

[fritzmg]

May be the limit is actually higher on 64-bit systems? Are you on 32-bit?

In any case you could adjust your setup this way for example:

// contao/dca/tl_address.php
$GLOBALS['TL_DCA']['tl_address']['fields']['number']['sql']['length'] = 32;
$GLOBALS['TL_DCA']['tl_address']['fields']['number']['eval']['maxlength'] = 32;

$GLOBALS['TL_DCA']['tl_address']['fields']['postal']['sql']['length'] = 32;
$GLOBALS['TL_DCA']['tl_address']['fields']['postal']['eval']['maxlength'] = 32;

$GLOBALS['TL_DCA']['tl_address']['fields']['city']['sql']['length'] = 128;
$GLOBALS['TL_DCA']['tl_address']['fields']['city']['eval']['maxlength'] = 128;

$GLOBALS['TL_DCA']['tl_address']['fields']['apartment']['sql']['length'] = 32;
$GLOBALS['TL_DCA']['tl_address']['fields']['apartment']['eval']['maxlength'] = 32;

That would bring the index down to under 3072 bytes.

[webstoney]

It's 64-bit (but the 3k limit is at both). I could do that easily, just wanted to get you informed about it.

[fritzmg]

What's the Index_length reported by

SHOW TABLE STATUS FROM dbname;

in your case?

[webstoney]

from 0 to 128kB, depends whether additional indexes besides PK are in

[fritzmg]

Hm, do you know of any way to figure out the max key length of a platform?

[webstoney]

With the settings of contao it should be 3072 bytes. As it depends on the Table format, there is no way to get it beforehand.

If it is below the 3k, people have set the Innodb Page Size below the standard 16k. In this case guys should know what they are doing. Rough estimation:

show global variables like 'innodb_page_size';

If it's 8k, then 1536 bytes
If it's 4k then 768 bytes
All other cases: 3072 bytes

[fritzmg]

But the index can be created in my local environment as well as all our customer's environments, where this extension is used, so something must be different (apart from the MySQL version).

[webstoney]

Was the key created with utf8mb4? Might be the charset.

Use

SELECT character_set_name FROM information_schema.COLUMNS WHERE table_schema = "dbname" AND table_name = "table" AND column_name = "col";

What mysql versions are in use there? Was InnoDB used?

If you don't need the Index for fast retrieving it might be applicable to just use a hash (stored function and unique on the column). But not worth the hassle I think.

[fritzmg]

Was the key created with utf8mb4? Might be the charset.

Yes, I am using the default, which is utf8mb4. Full table info:

/*Table: tl_address*/
---------------------

/*Column Information*/
----------------------

Field      Type              Collation           Null    Key     Default  Extra           Privileges                       Comment  
---------  ----------------  ------------------  ------  ------  -------  --------------  -------------------------------  ---------
id         int(10) unsigned  (NULL)              NO      PRI     (NULL)   auto_increment  select,insert,update,references           
pid        int(10) unsigned  (NULL)              NO      MUL     0                        select,insert,update,references           
tstamp     int(10) unsigned  (NULL)              NO              0                        select,insert,update,references           
street     varchar(255)      utf8mb4_unicode_ci  NO                                       select,insert,update,references           
number     varchar(255)      utf8mb4_unicode_ci  NO                                       select,insert,update,references           
postal     varchar(255)      utf8mb4_unicode_ci  NO                                       select,insert,update,references           
city       varchar(255)      utf8mb4_unicode_ci  NO                                       select,insert,update,references           
country    varchar(2)        utf8mb4_unicode_ci  NO                                       select,insert,update,references           
apartment  varchar(255)      utf8mb4_unicode_ci  NO                                       select,insert,update,references           

/*Index Information*/
---------------------

Table       Non_unique  Key_name                                         Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment  
----------  ----------  -----------------------------------------------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  ---------------
tl_address           0  PRIMARY                                                     1  id           A                    8    (NULL)  (NULL)          BTREE                               
tl_address           0  pid_street_number_apartment_postal_city_country             1  pid          A               (NULL)    (NULL)  (NULL)          HASH                                
tl_address           0  pid_street_number_apartment_postal_city_country             2  street       A               (NULL)    (NULL)  (NULL)          HASH                                
tl_address           0  pid_street_number_apartment_postal_city_country             3  number       A               (NULL)    (NULL)  (NULL)          HASH                                
tl_address           0  pid_street_number_apartment_postal_city_country             4  apartment    A               (NULL)    (NULL)  (NULL)          HASH                                
tl_address           0  pid_street_number_apartment_postal_city_country             5  postal       A               (NULL)    (NULL)  (NULL)          HASH                                
tl_address           0  pid_street_number_apartment_postal_city_country             6  city         A               (NULL)    (NULL)  (NULL)          HASH                                
tl_address           0  pid_street_number_apartment_postal_city_country             7  country      A               (NULL)    (NULL)  (NULL)          HASH                                

/*DDL Information*/
-------------------

CREATE TABLE `tl_address` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(10) unsigned NOT NULL DEFAULT 0,
  `tstamp` int(10) unsigned NOT NULL DEFAULT 0,
  `street` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `postal` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `city` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `country` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `apartment` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `pid_street_number_apartment_postal_city_country` (`pid`,`street`,`number`,`apartment`,`postal`,`city`,`country`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC

What mysql versions are in use there?

I am using MariaDB 10.5.11 locally. The MySQL version of one of our customers is 8.0.27.

Was InnoDB used?

Yes, I am using the default, which is InnoDB.

[webstoney]

Zust my 2ct: The DDL info seems awkward. InnoDB doesn't support hash indexes neither on mariaDB nor on mySQL. Was the table converted from Aria?
Can you check the select I posted above? The CHARSET definition in the database is only a suggestion for data columns, you can have UTF8 there but latin1 on the column for example.

Just try your create table statement with another table name and look if it works.

[fritzmg]

Was the table converted from Aria?

No, that's just what MariaDB generates and reports.

Can you check the select I posted above?

Yes, but for which table and field?

[webstoney]

for tl_address and street for example

[webstoney]

From a stock mariadb-installation:

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> CREATE TABLE `tl_address` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `pid` int(10) unsigned NOT NULL DEFAULT 0,
    ->   `tstamp` int(10) unsigned NOT NULL DEFAULT 0,
    ->   `street` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    ->   `number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    ->   `postal` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    ->   `city` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    ->   `country` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    ->   `apartment` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `pid_street_number_apartment_postal_city_country` (`pid`,`street`,`number`,`apartment`,`postal`,`city`,`country`) USING HASH
    -> ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

[fritzmg]

Was the key created with utf8mb4? Might be the charset.

Use

SELECT character_set_name FROM information_schema.COLUMNS WHERE table_schema = "dbname" AND table_name = "table" AND column_name = "col";

The result is utf8mb4 (Contao's default).

From a stock mariadb-installation:

Not sure what to tell you, works for me ;)

[webstoney]

For you, not for the customers though. cc.

[fritzmg]

I mean for me and our customers.

[webstoney]

apparently not.

[webstoney]

at least in the stock version.

[fritzmg]

apparently not.

I am not sure what you mean. As I said, it works for us and our customers. It would be interesting to figure out why it works in our environments (that we have access to), but not in yours. In any case, it should be no trouble to reduce the varchar length of some of the fields to a more sensible size, as suggested above. Unless you have another suggestion how to handle this better - in this case feel free to create a PR.

[webstoney]

As I come from a bit larger deployments, here my short views regarding indexes:

• Keep the indices as low as possible (including but not limited to shortening the columns). Large indexes make DML slow
• Avoid HASH-Indexes. Use only if you never need an index for select-query optimization. HASH-indexes are only good for a complete = or <> over all columns. loq_queries_not_using_indexes may help. Also using hash-keys may hinder the ALTER TABLE statement

I'd suggest checking, if the UNIQUE key really works (also with long strings). Might get some unexpected results there.

[fritzmg]

Keep the indices as low as possible (including but not limited to shortening the columns). Large indexes make DML slow

The unique index is just there to prevent duplicate addresses on a database level. It is however not technically necessary.

Avoid HASH-Indexes.

This is something that Contao/Doctrine defines and is out of my control (from the extension's perspective). However, you might want to report this either to Contao or Doctrine.

[webstoney]

The hash-index was only used on mariadb (no using hash in above query).

Some more info:

Unlimited size of UNIQUE KEY USING HASH was added somewhere in 10.4 series, although not documented.