exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes' in [#2815099]

drush en redirect get the following error but when I run the drush command again it says that redirect is already installed

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes' in
drupal/includes/database/database.inc:2204
Stack trace:
#0
drupal/includes/database/database.inc(2204):
PDOStatement->execute(Array)
#1
drupal/includes/database/database.inc(683):
DatabaseStatementBase->execute(Array, Array)
#2
drupal/includes/database/schema.inc(664):
DatabaseConnection->query('CREATE TABLE {r...')
#3
drupal/includes/database/database.inc(2753):
DatabaseSchema->createTable('redirect', Array)
#4
drupal/includes/common.inc(7334):
db_create_table('redirect', Array)
#5
drupal/includes/module.inc(479):
drupal_install_schema('redirect')
#6
phar:///usr/local/bin/drush/commands/core/drupal/environment_7.inc(143):
module_enable(Array)
#7 phar:///usr/local/bin/drush/commands/pm/pm.drush.inc(1167):
drush_module_enable(Array)
#8 [internal function]: drush_pm_enable('redirect')
#9 phar:///usr/local/bin/drush/includes/command.inc(371):
call_user_func_array('drush_pm_enable', Array)
#10 phar:///usr/local/bin/drush/includes/command.inc(222):
_drush_invoke_hooks(Array, Array)
#11 [internal function]: drush_command('redirect')
#12 phar:///usr/local/bin/drush/includes/command.inc(190):
call_user_func_array('drush_command', Array)
#13 phar:///usr/local/bin/drush/lib/Drush/Boot/BaseBoot.php(67):
drush_dispatch(Array)
#14 phar:///usr/local/bin/drush/includes/preflight.inc(66):
Drush\Boot\BaseBoot->bootstrap_and_dispatch()
#15 phar:///usr/local/bin/drush/includes/startup.inc(458):
drush_main()
#16 phar:///usr/local/bin/drush/includes/startup.inc(365):
drush_run_main(false, '/', 'Phar detected. ...')
#17 phar:///usr/local/bin/drush/drush(114): drush_startup(Array)
#18 /usr/local/bin/drush(10): require('phar:///usr/loc...')
#19 {main}

Comment	File	Size	Author
#11	redirect-index-length-2815099-11.patch	896 bytes	andralex
#11	7.x-1.x: PHP 5.3 & MySQL 5.5, D7 11 pass PHP 5.6 & MySQL 5.5, D7 11 pass
#6	Выделение_199.png	24.76 KB	Tolyan4ik
#6	Выделение_200.png	29.38 KB	Tolyan4ik

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

8 October 2016 at 14:32

SocialNicheGuru created an issue. See original summary.

Comment #2

malcomio CreditAttribution: malcomio at Capgemini commented 29 November 2016 at 15:44

Issue summary:

View changes

Comment #3

malcomio CreditAttribution: malcomio at Capgemini commented 29 November 2016 at 15:48

On several sites that were running 7.x-1.0-rc1, I've observed a similar issue trying to run update 7101 after upgrading to 7.x-1.0-rc3, even after applying the changes in #2541304: update 7101 failing.

Comment #4

malcomio CreditAttribution: malcomio at Capgemini commented 29 November 2016 at 17:13

Still need to investigate further, but the error I've observed seems to relate to an instance where the size of the redirect and source columns had been increased at some point in the history of the site.

After reducing them to match the module schema (i.e. varchar 255), the updates completed successfully.

Comment #5

anrikun CreditAttribution: anrikun commented 19 February 2020 at 16:54

Priority:

Normal

» Critical

Still an issue in 7.x-1.x.
New install crashes with exception:
PDOException : SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes: CREATE TABLE {redirect} ( `rid` INT NOT NULL auto_increment COMMENT 'Primary Key: Unique redirect ID.', `hash` VARCHAR(64) NOT NULL COMMENT 'A unique hash based on source, source_options, and language.', `type` VARCHAR(64) NOT NULL DEFAULT '' COMMENT 'The redirect type; if value is ’redirect’ it is a normal redirect handled by the module.', `uid` INT unsigned NOT NULL DEFAULT 0 COMMENT 'The users.uid of the user who created the redirect.', `source` VARCHAR(900) NOT NULL COMMENT 'The source path to redirect from.', `source_options` BLOB NOT NULL COMMENT 'A serialized array of source options.', `redirect` VARCHAR(900) NOT NULL COMMENT 'The destination path to redirect to.', `redirect_options` BLOB NOT NULL COMMENT 'A serialized array of redirect options.', `language` VARCHAR(12) NOT NULL DEFAULT 'und' COMMENT 'The language this redirect is for; if blank, the alias will be used for unknown languages.', `status_code` SMALLINT NOT NULL COMMENT 'The HTTP status code to use for the redirect.', `count` INT unsigned NOT NULL DEFAULT 0 COMMENT 'The number of times the redirect has been used.', `access` INT unsigned NOT NULL DEFAULT 0 COMMENT 'The timestamp of when the redirect was last accessed.', `status` SMALLINT NOT NULL DEFAULT 1 COMMENT 'Boolean indicating whether the redirect is enabled (visible to non-administrators).', PRIMARY KEY (`rid`), UNIQUE KEY `hash` (`hash`), INDEX `expires` (`type`, `access`), INDEX `status_source_language` (`status`, `source`(255), `language`), INDEX `redirect` (`redirect`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT 'Stores information on redirects.'; Array ( ) dans db_create_table() (ligne 2776 dans [...]/includes/database/database.inc).

Comment #6

Tolyan4ik CreditAttribution: Tolyan4ik at EPAM Systems commented 11 March 2020 at 11:24

Status:

Active

» Postponed (maintainer needs more info)

File	Size
Выделение_200.png	29.38 KB
Выделение_199.png	24.76 KB

I installed the module using the drash command on branch 7.x-1.x, 7.x-2.x and everything is working fine, can you provide more information how I can reproduce the error.

Comment #7

anrikun CreditAttribution: anrikun commented 11 March 2020 at 20:48

Status:

Postponed (maintainer needs more info)

» Active

You must use collate utf8mb4_general_ci to reproduce this bug.

Comment #8

andralex CreditAttribution: andralex at EPAM Systems for EPAM Systems commented 18 March 2020 at 16:09

Status:

Active

» Closed (cannot reproduce)

I've used collate utf8mb4_general_ci on the database:
Tested on commit 4fdcb8fe519f1c25b8a2ab0b2d883d9c6ebaaf09 from branch 7.x-1.x and it works for me.

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+

mysql> SHOW TABLE STATUS where name like 'redirect';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment                          |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------+
| redirect | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        65536 |         0 |              1 | 2020-03-18 17:58:28 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores information on redirects. |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------+

The same result is on 7.x-2.x branch for 6d8aa2aa85752f37e2b45628a1d12717cc652b04 commit.

Comment #9

anrikun CreditAttribution: anrikun commented 18 March 2020 at 19:00

Status:

Closed (cannot reproduce)

» Active

Your query reports utf8_general_ci, not utf8mb4_general_ci!
To reproduce the bug, you must try to install the module on a utf8mb4_general_ci installation.
See https://www.drupal.org/node/2754539

Comment #10

andralex CreditAttribution: andralex at EPAM Systems for EPAM Systems commented 19 March 2020 at 10:17

2 files were hidden/shown/deleted

File	Size
Выделение_200.png	29.38 KB
Выделение_199.png	24.76 KB

Sorry, my bad. It's reproduced now with exact collation.

Comment #11

andralex CreditAttribution: andralex at EPAM Systems for EPAM Systems commented 19 March 2020 at 11:40

File	Size
redirect-index-length-2815099-11.patch	896 bytes
7.x-1.x: PHP 5.3 & MySQL 5.5, D7 11 pass PHP 5.6 & MySQL 5.5, D7 11 pass

I've fixed the issue by reducing the index length for 'redirect' and 'source' columns.

Comment #12

andralex CreditAttribution: andralex at EPAM Systems for EPAM Systems commented 19 March 2020 at 11:43

Status:

Active

» Needs review

Comment #13

anrikun CreditAttribution: anrikun commented 27 March 2020 at 14:42

Status:

Needs review

» Reviewed & tested by the community

It works thanks!

Comment #14

pifagor

🇺🇦 Rivne

CreditAttribution: pifagor at Drupal Ukraine Community, GOLEMS GABB for Drupal Ukraine Community, GOLEMS GABB commented 6 May 2020 at 23:14

Comment #15

6 May 2020 at 23:15

pifagor committed 0ababc3 on 7.x-1.x authored by andralex

Issue #2815099 by andralex, Tolyan4ik, anrikun, pifagor: exception '...

Comment #16

pifagor

🇺🇦 Rivne

CreditAttribution: pifagor at Drupal Ukraine Community, GOLEMS GABB for Drupal Ukraine Community, GOLEMS GABB commented 6 May 2020 at 23:15

Status:

Reviewed & tested by the community

» Fixed

Comment #17

20 May 2020 at 23:19

Status:

Fixed

» Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes' in

Comments

Comment #1

Comment #2

Comment #3

Comment #4

Comment #5

Comment #6

Comment #7

Comment #8

Comment #9

Comment #10

Comment #11

Comment #12

Comment #13

Comment #14

Comment #15

Comment #16

Comment #17

Referenced by

Thank you to these Drupal contributors

News items

Our community

Documentation

Drupal code base

Governance of community