[debian-mysql] help fixing cacti to work with MariaDB

[Alexander Barkov]

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.

Most likely:
- 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 
reduced.


Greetings.


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.
>
> http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes
>
> 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
> Cacti?
>
>
>
> 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.
>>
>> Paul
>>
>> ¹ https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=813422
>>
>> _______________________________________________
>> pkg-mysql-maint mailing list
>> pkg-mysql-maint at lists.alioth.debian.org
>> http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-mysql-maint
>
>
>