adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-10-30 21:52:00 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

mysql said: ERROR 1071 (42000) at line 107: Specified key was too long; max key length is 1000 bytes #694

New Issue
Closed
opened 2026-10-05 00:12:08 +02:00 by adam · 9 comments
adam commented 2026-10-05 00:12:08 +02:00
Owner
Copy link
Copy source

When creating the database for the first time I get the following error:
mysql said: ERROR 1071 (42000) at line 107: Specified key was too long; max key length is 1000 bytes

It comes from script from install/piwigo_structure-mysql.sql when creating the piwigo_groups table. It's due to the fact that I configured my MariaDB install with UTF support, so each character is 4-bytes length.

However, MyISAM engine limits index size to 1000 bytes! And UNIQUE KEY group_ui1 is a VARCHAR(255) which takes 255*4=1020 bytes here!

To circumvent error, I replaced UNIQUE KEY groups_ui1 (name) with UNIQUE KEY groups_ui1 (name(250)), which limits the index to the first 250 characters of name field.
I did the same to piwigo_image_format and piwigo_sites tables (PRIMARY KEY and UNIQUE KEY respectively).

Is there a chance Piwigo will work with UTF encoded database or should it enforce Latin1 at database creation time?

Best regards,
Eric.

When creating the database for the first time I get the following error: mysql said: ERROR 1071 (42000) at line 107: Specified key was too long; max key length is 1000 bytes It comes from script from install/piwigo_structure-mysql.sql when creating the `piwigo_groups` table. It's due to the fact that I configured my MariaDB install with UTF support, so each character is 4-bytes length. However, MyISAM engine limits index size to 1000 bytes! And UNIQUE KEY `group_ui1` is a VARCHAR(255) which takes 255*4=1020 bytes here! To circumvent error, I replaced UNIQUE KEY `groups_ui1` (`name`) with UNIQUE KEY `groups_ui1` (`name`(250)), which limits the index to the first 250 characters of `name` field. I did the same to `piwigo_image_format` and `piwigo_sites` tables (PRIMARY KEY and UNIQUE KEY respectively). Is there a chance Piwigo will work with UTF encoded database or should it enforce Latin1 at database creation time? Best regards, Eric. <!-- github-sync-id:254619507 -->
adam closed this issue 2026-10-05 00:12:08 +02:00
adam commented 2026-10-05 00:12:09 +02:00
Author
Owner
Copy link
Copy source

Hello? Which server? which mysql? which request on which table? which piwigo? which input?

Hello? Which server? which mysql? which request on which table? which piwigo? which input? <!-- github-comment-id:326563486 -->
adam commented 2026-10-05 00:12:10 +02:00
Author
Owner
Copy link
Copy source

I used latest Piwigo 2.9.1 on Debian Stretch (9.1) with MariaDB 10.1.

I used latest Piwigo 2.9.1 on Debian Stretch (9.1) with MariaDB 10.1. <!-- github-comment-id:326564469 -->
adam commented 2026-10-05 00:12:10 +02:00
Author
Owner
Copy link
Copy source

Thank you for having edited your first post

Thank you for having edited your first post <!-- github-comment-id:326565076 -->
adam commented 2026-10-05 00:12:11 +02:00
Author
Owner
Copy link
Copy source

Piwigo defines and uses utf8 not utf8mb4

Piwigo defines and uses utf8 not utf8mb4 <!-- github-comment-id:326566003 -->
adam commented 2026-10-05 00:12:12 +02:00
Author
Owner
Copy link
Copy source

I will open a new ticket asking for support of utf8mb4. The bug itself is closed

I will open a new ticket asking for support of utf8mb4. The bug itself is closed <!-- github-comment-id:326567644 -->
adam commented 2026-10-05 00:12:12 +02:00
Author
Owner
Copy link
Copy source

You may put ALTER DATABASE piwigo CHARACTER SET utf8; statement at top of install/piwigo_structure-mysql.sql to force utf8 encoding if db is configured for utf8mb4 by default. Or you can specify CHARACTER SET in each CREATE TABLE.

You may put ALTER DATABASE piwigo CHARACTER SET utf8; statement at top of install/piwigo_structure-mysql.sql to force utf8 encoding if db is configured for utf8mb4 by default. Or you can specify CHARACTER SET in each CREATE TABLE. <!-- github-comment-id:326581708 -->
adam commented 2026-10-05 00:12:13 +02:00
Author
Owner
Copy link
Copy source

CHARACTER SET is already set
https://github.com/Piwigo/Piwigo/blob/master/install.php#L349
https://github.com/Piwigo/Piwigo/blob/master/admin/include/functions_install.inc.php#L64

CHARACTER SET is already set https://github.com/Piwigo/Piwigo/blob/master/install.php#L349 https://github.com/Piwigo/Piwigo/blob/master/admin/include/functions_install.inc.php#L64 <!-- github-comment-id:326584043 -->
adam commented 2026-10-05 00:12:14 +02:00
Author
Owner
Copy link
Copy source

Ah ok. I'm taking over the Debian Piwigo package made years ago by Nicolas Roudaire and it creates the database at Debian postinst through dbconfig-common. I will inject charset selection here.
Best regards.

Ah ok. I'm taking over the Debian Piwigo package made years ago by Nicolas Roudaire and it creates the database at Debian postinst through dbconfig-common. I will inject charset selection here. Best regards. <!-- github-comment-id:326586159 -->
adam commented 2026-10-05 00:12:15 +02:00
Author
Owner
Copy link
Copy source

oh I see ; thanks for your answer
His nickname is @nikrou and he sis on Github with a fork of Piwigo for PostgreSql

oh I see ; thanks for your answer His nickname is @nikrou and he sis on Github with a fork of Piwigo for PostgreSql <!-- github-comment-id:326587621 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#694