Import DB error

Post support questions that directly relate to Linux/Unix operating systems.

Moderators: Moderators, Developers

Post Reply
Author
Message
craigesmith
Posts: 4
Joined: Thu Feb 02, 2017 3:09 pm

Import DB error

#1 Post by craigesmith » Thu Feb 02, 2017 3:19 pm

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
Posts: 2
Joined: Fri Feb 10, 2017 10:58 pm

Re: Import DB error

#2 Post by vas » Fri Feb 10, 2017 11:00 pm

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
Posts: 2
Joined: Fri Feb 10, 2017 10:58 pm

Re: Import DB error

#3 Post by vas » Fri Feb 10, 2017 11:45 pm

update : use utf8 instead of utf8mb4

JMoMo
Cacti User
Posts: 60
Joined: Mon Nov 08, 2004 12:11 am

Re: Import DB error

#4 Post by JMoMo » Fri Mar 03, 2017 8:59 pm

vas wrote: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
Cacti User
Posts: 60
Joined: Mon Nov 08, 2004 12:11 am

Re: Import DB error

#5 Post by JMoMo » Sat Mar 04, 2017 10:49 pm


paulbeard206
Cacti User
Posts: 149
Joined: Sat Jul 30, 2005 2:15 pm

Re: Import DB error

#6 Post by paulbeard206 » Wed Aug 21, 2019 2:18 pm

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
Cacti Guru User
Posts: 2884
Joined: Sun Aug 27, 2017 12:05 am

Re: Import DB error

#7 Post by netniV » Wed Aug 21, 2019 3:07 pm

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.

Post Reply