mariadb index limitaion (ERROR 1071 (42000))

Message boards : Server programs : mariadb index limitaion (ERROR 1071 (42000))
Message board moderation

To post messages, you must log in.

AuthorMessage
USTL-FIL (Lille Fr)

Send message
Joined: 3 Jun 13
Posts: 12
France
Message 91279 - Posted: 30 Apr 2019, 13:43:00 UTC
Last modified: 30 Apr 2019, 13:44:28 UTC

Hello,
I'm trying to install the last server version(1.1.0) from source on debian buster.
Compilation was ok but i have problem with db creation.

mysql report an error with index limitation: "Specified key was too long; max key length is 1000 bytes"
mysql version: Ver 15.1 Distrib 10.3.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

There is a workaround for this?
Thanks!
ID: 91279 · Report as offensive
Richard Haselgrove
Volunteer tester
Help desk expert

Send message
Joined: 5 Oct 06
Posts: 5077
United Kingdom
Message 91280 - Posted: 30 Apr 2019, 13:50:34 UTC

There's no definitive fix, but it's a known problem - some discussion and suggestions in #2140.

Also an external reference to StackOveflow: https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes
ID: 91280 · Report as offensive
USTL-FIL (Lille Fr)

Send message
Joined: 3 Jun 13
Posts: 12
France
Message 91285 - Posted: 30 Apr 2019, 14:18:20 UTC

Hi!
Thank you for your very quick answer!

I try one of the simplest solution i found on your stackoverflow link.

1/ delete current database
2/ manually create database with 'latin1_general_ci" encoding.
3/ import db/schema.sql and db/constraints.sql

This can successfully create database, but is this a good thing to have the db in 'latin1_general_ci'
Is this not make new issue with daemons and web pages expecting utf8 db?
Thanks!
ID: 91285 · Report as offensive
Richard Haselgrove
Volunteer tester
Help desk expert

Send message
Joined: 5 Oct 06
Posts: 5077
United Kingdom
Message 91293 - Posted: 30 Apr 2019, 15:30:21 UTC

It depends what you're planning to use the database for. If it's for private, in-house usage only, and you can control exactly what's written into it, then you could be fine.

But if it's facing an international public audience, then you have to use a character set which can handle everything which is being thrown at it, currently up to 4-byte emojis. I was sent a PM here yesterday, which appeared to contain ðŸ‘�: it turned out to be a simple 👍. You've probably seen some curious versions of your (accented) name over the years.

Further discussion of the character sets used in an international setting at #2117.
ID: 91293 · Report as offensive
USTL-FIL (Lille Fr)

Send message
Joined: 3 Jun 13
Posts: 12
France
Message 91294 - Posted: 30 Apr 2019, 15:41:49 UTC

It's for private use in my university (Lille - France) to provide researchers computing resources with computer lab.
So it could be fine :-)
Thank you!
ID: 91294 · Report as offensive
ChristianB
Volunteer developer
Volunteer tester

Send message
Joined: 4 Jul 12
Posts: 321
Germany
Message 91298 - Posted: 30 Apr 2019, 18:05:59 UTC

It's in general not a good idea to start a DB with latin1 nowadays. Tis is one thing you can't easily change afterwards. The problem on Debian buster is that they use utf8mb4 as the default whereas BOINC just needs utf8 to work correctly. So if you still can please change to UTF8 (without the mb4).
ID: 91298 · Report as offensive

Message boards : Server programs : mariadb index limitaion (ERROR 1071 (42000))

Copyright © 2024 University of California. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation.