Import DB error

[craigesmith]

Hello,

I'm getting the following error when I'm trying to import the default cacti database:

ERROR 1071 (42000) at line 2560: Specified key was too long; max key length is 767 bytes

I'm running MariaDB 10.0, and am not quite sure how to get around this issue.

Craig

[vas]

same thing here running
Arch Linux
mysql Ver 15.1 Distrib 10.1.21-MariaDB, for Linux (x86_64) using readline 5.1


ERROR 1071 (42000) at line 2560: Specified key was too long; max key length is 767 bytes

[vas]

update : use utf8 instead of utf8mb4

[JMoMo]

update : use utf8 instead of utf8mb4

UPDATE: I tried to clean up their database template so that it could be imported, but gave up. The Cacti devs though it would be a great idea to make everything an index/key. They are going to have a fun time converting their database some day.

Best thing to do is to set the database creation command with the right values. Don't set your global configuration with "utf8":

echo "create database cacti DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;" | sudo mysql -u root -p

--

The MariaDB/MySQL "uf8" character set is a dirty bastard 3-byte proprietary half-assed implementation of utf8 that should have been aborted by means of violence by the idiots who conceived it.

That's what "utf8mb4" is. It's "real" UTF8 according to accepted standards.

Once you import and create those DBs, you will have to later convert ALL your databases to "real" UTF8, and that will be very painful for you. Don't do it.

[JMoMo]

https://medium.com/@adamhooper/in-mysql ... 761243e434

[paulbeard206]

Well, this is nice to find out.

What's the best way to fix this permanently? Dump the database and reimport into a correctly formatted one?

Or does someone (perhaps one of the cacti devs who shat the bed here?) have a script with the right syntax that incorporates the variables detailed here?

https://medium.com/@adamhooper/in-mysql ... 761243e434

Seems like anyone who can write this as part of the upgrade process could also clean up the lingering smell of UTF8…
ALTER TABLE poller_output_realtime DROP PRIMARY KEY, ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id)

[netniV]

The format should use utf8mb4 to properly support international characters. Not doing so is wrong.

The problem actually lies with the innodb format and needs some settings to allow large prefixes. This has been reported and closed on the github repo in the past few weeks.