[debian-mysql] help fixing cacti to work with MariaDB
bar at mariadb.org
Fri Feb 26 05:51:20 UTC 2016
Hello Paul, all,
I noticed that the file "cacti.sql" which contains structures for all
tables does not explicitly mention the character set to be used for
the text string columns (e.g. char, varchar, text).
That means the character set is derived from the database.
- you tried to use Cacti with a database which has
utf8 as the default character set. You can check this using
"SHOW CREATE DATABASE dbname".
- while the Cacti developers use a latin1 database by default.
An index on a utf8 column requires 3 times more space that an index on
a latin1 column. Hence you get an error.
There is nothing MariaDB specific here. In a UTF8 database MySQL should
give the same error. The key factor here is the default settings in
my.cnf, not the choice between MariaDB and MySQL.
I can see 3 options how to fix this to stay under the MyISAM limit of
1000 bytes per index:
1. If Cacti does not need to store UTF8 data (which is most likely the
case, please consult to Cacti developers), then
1a. One of the options is to change the character set and the collation
in the CREATE DATABASE, as you proposed. I'd suggest to use character
set latin1 with its default collation latin1_swedish_ci instead of
ascii_general_ci though, as the former is more widespread and tested.
This is probably the best way to go if Cacti uses a dedicated database.
1b. Or, add explicit "CHARACTER SET latin1" clauses into every CREATE
TABLE statement in cacti.sql, so the Cacti tables do not
depend on the database default character set.
This is the best way to go if Cacti is going to share the database
with some other non-Cacti tables.
2. If Cacti really needs to store UTF8 data, then index sizes should be
On 02/26/2016 01:02 AM, Otto Kekäläinen wrote:
> Hello Paul!
> The "Specified key was too long; max key length is 1000 bytes" stems
> from the fact that in InnoDb the maximum key lengt is shorter than in
> MyISAM. I suspect Cacti is old and originally developed using MyISAM,
> and not tested with modern InnoDB. This backwards compatibility issue
> applies to both MariaDB and MySQL.
> The solution is to change the CREATE TABLE definition so that the key
> is shorter.
> Changing collation shortens the amount of bytes the key consumes, but
> that does not seem like a future-proof solution. Can you check if in
> Cacti the key length can be shortened and maybe file a bug on upstream
> 2016-02-25 22:14 GMT+02:00 Paul Gevers <elbrus at debian.org>:
>> Hi all,
>> [Please CC me, I am not subscribed].
>> Recently I started to look into how to fix bug 813422¹ "cacti can't be
>> installed on MariaDB server". It seems that I can work-around/fix the
>> issue "Specified key was too long; max key length is 1000 bytes" by
>> changing the sql template for the database by adding a "collate
>> ascii_general_ci" in three places (see attached patch).
>> Now I expect that this is the result of defaults for collation being
>> different on MariaDB than they were (and are?) on MySQL. Before I start
>> bugging upstream I like to know if I am correct in this understanding
>> and if this is something Debian specific or MariaDB specific.
>> Does the fix/work-around sound remotely sane?
>> Thanks for your time and effort.
>> ¹ https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=813422
>> pkg-mysql-maint mailing list
>> pkg-mysql-maint at lists.alioth.debian.org
More information about the pkg-mysql-maint