#1709 - Index column size too large. The maximum column size is 767 bytes, when trying to restore automated backup

ERPNext
1

This is the error message that I get when trying to restore the automated dropbox backup to a mariadb 5.5.43 server, using phpmyadmin:

SQL query:
CREATE TABLE __Auth (
user varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (user),
KEY user (user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;
MySQL said: #1709 - Index column size too large. The maximum column size is 767 bytes.

Any help would be greatly appreciated.

2

This seems to be because innodb_large_prefix is not set. Can you add this to your MariaDB config?



[mysqld]
innodb-file-format=barracuda
innodb-file-per-table=1
innodb-large-prefix=1
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

Also, you can restore using the command line

bench --force restore /abs/path/to/sql
4

That worked! Thanks so much for the very helpful instructions.

1 Like
5

But, When I restore using the command line as you suggest:
bench --force --site sitename restore /home/ubuntu/en_test_may2015.sql
Then, enter mysql password
It does the restore, but I get “internal server error” when I try run the site