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

[Paul Gevers]

Hi all,

Thanks already for the responses.

On 26-02-16 06:51, Alexander Barkov wrote:
> 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.

My original question was indeed, is the default for MySQL in Debian
different then the default for MariaDB?

> 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.

Until now, cacti in Debian didn't have to do anything special, it just
worked, so my idea is that the default in Debian (MySQL) has always been
an 8-bit character set.

> An index on a utf8 column requires 3 times more space that an index on
> a latin1 column. Hence you get an error.

That is exactly how I understood the issue.

> 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.

Sure: so the defaults are different in the Debian packages?

> 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.

I now see that dbconfig-common (that Cacti uses to create the database)
probably has this facility. Ironically I maintain dbconfig-common since
last year.

> 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.

cacti has it's own database, so I guess that 1a is the way forward.

> 2. If Cacti really needs to store UTF8 data, then index sizes should be
> reduced.

On 26-02-16 07:36, Otto Kekäläinen wrote:
> Hello!
>
> 2016-02-25 23:02 GMT+02:00 Otto Kekäläinen <otto at seravo.fi>:
>> The solution is to change the CREATE TABLE definition so that the key
>> is shorter.
>
> Alexander's advice is better. Don't do the above, rather create the
> database with latin1 as default as in Alexander's mail option 1a.
>
> MariaDB in Debian uses UTF-8 by default, which makes sense for most
> apps (WordPress etc) but it does indeed have the drawback of using
> more space.

So indeed, I read this as: MariaDB changed the default from latin1 (is
this true?) to utf8. I fully understand (and agree, if that matters),
but I'll need to work around that for cacti.

Paul

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: <http://lists.alioth.debian.org/pipermail/pkg-mysql-maint/attachments/20160226/bbd9b93d/attachment.sig>