Upgrader to detect when innodb_large_prefix is not set

Anything that you think should be in Cacti.

Moderators: Moderators, Developers

Post Reply
Author
Message
PeterUpfold
Posts: 2
Joined: Thu Jul 18, 2019 4:11 pm
Contact:

Upgrader to detect when innodb_large_prefix is not set

#1 Post by PeterUpfold » Thu Jul 18, 2019 4:24 pm

I've just upgraded a Cacti instance from 1.2.4 to 1.2.5.

I ran into an issue during the upgrade process. Specifically, this query was failing:

Code: Select all

ALTER TABLE poller_output_realtime DROP PRIMARY KEY, ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id)
With the following:

Code: Select all

2019/07/18 21:44:04 - CMDPHP ERROR: A DB Exec Failed!, Error: Specified key was too long; max key length is 767 bytes
2019/07/18 21:44:04 - CMDPHP SQL Backtrace: (/install/background.php[52]:Installer::beginInstall(), /lib/installer.php[3200]:Installer->install(), /lib/installer.php[2723]:Installer->upgradeDatabase(), /lib/installer.php[3099]:upgrade_to_1_2_5(), /install/upgrades/1_2_5.php[59]:db_install_execute(), /install/functions.php[119]:db_execute_prepared())
The server was configured with utf8mb4 and the Cacti tables were also already using this charset.

Code: Select all

character-set-server  = utf8mb4
collation-server      = utf8mb4_unicode_ci
However, my server.cnf did not have innodb_large_prefix (and prerequisite options) and this was causing the issue where that ALTER TABLE would fail, because the long index was not supported.

I was able to resolve it by setting these in the server.cnf:

Code: Select all

innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_large_prefix=1
I then rolled back the previous mysqldump database backup, which re-created the tables with file_per_table and in Barracuda format to support the longer index length. A subsequent run of the upgrade process succeeded.


My feature request is:
could the installer/upgrader detect when a database is using utf8mb4, but does not have innodb_large_prefix enabled to avoid the upgrade process failing as it did for me here?

I'm proficient in PHP, so I'm happy to have a shot at adding this detection to the codebase myself if someone is willing to roughly point me in the right direction.

User avatar
Osiris
Cacti Pro User
Posts: 887
Joined: Mon Jan 05, 2015 10:10 am

Re: Upgrader to detect when innodb_large_prefix is not set

#2 Post by Osiris » Thu Jul 18, 2019 8:56 pm

Log this into GitHub.com please.
Before history, there was a paradise, now dust.

netniV
Cacti Guru User
Posts: 3025
Joined: Sun Aug 27, 2017 12:05 am

Re: Upgrader to detect when innodb_large_prefix is not set

#3 Post by netniV » Fri Jul 19, 2019 11:02 am

I'm pretty sure that having large_prefix is one of the MySQL recommendations. However, because database administrators often like to have their own custom setup specific to their own designs, the setup will allow you to continue despite those recommendations not being met for MySQL. For example, having doublewrite on or off.

When it comes to the database table checks, making sure that the large prefix etc are on for utf8 enabled databases should probably become a failure if they are not and should appear on that page as its before you even begin the upgrade.

That would be best raised as a request on GitHub.

PeterUpfold
Posts: 2
Joined: Thu Jul 18, 2019 4:11 pm
Contact:

Re: Upgrader to detect when innodb_large_prefix is not set

#4 Post by PeterUpfold » Fri Jul 19, 2019 2:09 pm

I have opened issue 2836 for this. https://github.com/Cacti/cacti/issues/2836

Post Reply