1

We are currently moving the DB to another Server but we got a problem. Our Database is quite big and when we want to Import it to our new MariaDB Server with PhpMyAdmin. We got an Error Code and don´t know how to solve it. Sorry that the Outcome is German, but it basically says that the key is too long The maximum key length is 767.

MariaDB Version: MariaDB-10.1.48Server Version: Ubuntu 18.04

Server Version: Ubuntu 18.04

If anyone could help with this Problem we would really appreciate an answer!

Example:

CREATE TABLE IF NOT EXISTS `accounts` (
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `money` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Outcome:

CREATE TABLE IF NOT EXISTS `accounts` (
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `money` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
MySQL meldet: Dokumentation

#1071 - Schlüssel ist zu lang. Die maximale Schlüssellänge beträgt 767
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
ZeroOne
  • 13
  • 2
  • I edited to make it clear you are using MariaDB, not MySQL. These are not the same product, even though they have some code in common. MariaDB forked from MySQL in 2010, and both products have been changing since then. You should not think of them as compatible. – Bill Karwin May 03 '22 at 17:22
  • 5 workarounds for the 767 error; each has drawbacks; you pick which one you can live with. -- http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes – Rick James May 05 '22 at 02:59
  • As for MySQL vs MariaDB -- They are very similar, except for when the real "fixes" were included in the product. – Rick James May 05 '22 at 03:01

2 Answers2

1

As far as I know, you are defining name VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL and setting it as Primary Key. It is embedded in utf8mb4_unicode_ciyou have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191.

Either you use VARCHAR(191) or not use it as your primary key

Asgar
  • 1,920
  • 2
  • 8
  • 17
  • Thanks for the answer! I think this works but the problem is, we got a bunch of these SQL Commands in our .sql File. Is there any way to export the File with a lower varchar? Cheers ZeroOne – ZeroOne May 03 '22 at 16:56
  • I am not sure about that. I once ran into the same problem but just for one table. I didn't dig deeper into the problem then. – Asgar May 03 '22 at 17:00
  • @ZeroOne - You can "generate" `ALTER` statement with a carefully crafted `SELECT ... FROM information_schema.tables ...` (or `columns`) One example is in http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes – Rick James May 05 '22 at 03:03
0

use

SET GLOBAL innodb_default_row_format='dynamic';

in your script or modify this parameter in my.ini

innodb-default-row-format = dynamic

with this change you can create index over columns up to 3072 bytes.

Sergio Martinez
  • 11
  • 1
  • 3