Upgrader to detect when innodb_large_prefix is not set

[PeterUpfold]

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.

[Osiris]

Log this into GitHub.com please.

[netniV]

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]

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