Upgrade may fail between 1.2.7 and 1.2.8 if incompatible database format used

[hmorandell]

Describe the bug
When upgrading from 1.2.7, upgrade fails with the following error:

Expected behavior
When this error appears, nothing can be done? Is it possible to introduce a sort of a "dry-run" to check everything will work rather then converting tables and then running in issues.

The error is caused by the following mysql error (running it in command line: "ALTER TABLE host_snmp_cache ADD INDEX snmp_index(snmp_index), ADD INDEX field_value(field_value)").

Index column size too large. The maximum column size is 767 bytes

System Versions
Mysql:
mysql Ver 15.1 Distrib 10.0.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

PHP:
PHP 7.0.33-0ubuntu0.16.04.7 (cli) ( NTS )

[whsir]

I had the same problem，“Specified key was too long; max key length is 767 bytes”

[whsir]

System Versions
Msql：
mysql Ver 14.14 Distrib 5.5.62, for Linux (x86_64) using readline 5.1

[whsir]

1、my.cnf，add
[mysqld]
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_large_prefix=1
2、upgrade

[netniV]

Yes, the above is a requirement of the MySQL and should have shown up on the second step of the wizard.

[ddb4github]

Previous fixing #3060 updated some Pre-1.2.7 upgrade scripts.
But for 1.2.1+ env, 1_2_5.php and 1_2_8.php require more about row_format changing:

diff -ruEbwB cacti/install/upgrades/1_2_5.php cacti/install/upgrades/1_2_5.php
--- cacti/install/upgrades/1_2_5.php  2019-12-09 07:48:15.000000000 +0800
+++ cacti/install/upgrades/1_2_5.php  2019-12-09 11:16:02.000000000 +0800
@@ -56,7 +56,7 @@
        AND type_code = 'output_type'
        AND gl.graph_template_id IN (SELECT graph_template_id FROM snmp_query_graph)");

-   db_install_execute("ALTER TABLE poller_output_realtime DROP PRIMARY KEY, ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id)");
+   db_install_execute("ALTER TABLE poller_output_realtime ROW_FORMAT=Dynamic, DROP PRIMARY KEY, ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id)");

    repair_automation();
 }
diff -ruEbwB cacti/install/upgrades/1_2_8.php cacti/install/upgrades/1_2_8.php
--- cacti/install/upgrades/1_2_8.php  2019-12-09 07:48:15.000000000 +0800
+++ cacti/install/upgrades/1_2_8.php  2019-12-09 12:26:28.705843290 +0800
@@ -23,11 +23,11 @@
 */

 function upgrade_to_1_2_8() {
-   db_install_execute('ALTER TABLE host_snmp_cache DROP INDEX snmp_index, DROP INDEX field_value');
+   db_install_execute('ALTER TABLE host_snmp_cache ROW_FORMAT=Dynamic, DROP INDEX snmp_index, DROP INDEX field_value');
    db_install_execute('ALTER TABLE host_snmp_cache MODIFY COLUMN snmp_index VARCHAR(255) NOT NULL default ""');
    db_install_execute('ALTER TABLE host_snmp_cache ADD INDEX snmp_index(snmp_index), ADD INDEX field_value(field_value)');
-   db_install_execute('ALTER TABLE data_local DROP INDEX snmp_index, ADD INDEX snmp_index(snmp_index)');
-   db_install_execute('ALTER TABLE graph_local DROP INDEX snmp_index, ADD INDEX snmp_index(snmp_index)');
+   db_install_execute('ALTER TABLE data_local ROW_FORMAT=Dynamic, DROP INDEX snmp_index, ADD INDEX snmp_index(snmp_index)');
+   db_install_execute('ALTER TABLE graph_local ROW_FORMAT=Dynamic, DROP INDEX snmp_index, ADD INDEX snmp_index(snmp_index)');

    // Needed to fix aggregate bug
    if (!db_column_exists('aggregate_graphs', 'gprint_format')) {

[netniV]

On the face of it, I would agree with that. Are you going to do that as a pull?

[cigamit]

For some reason the installer did not re-run convert_tables.php as expected, or detect the issue with the ROW_FORMAT check. That's kind of unexpected.

[gdsotirov]

I had a similar problem with MySQL 5.7.28 (see https://forums.cacti.net/viewtopic.php?f=21&t=60346). The database upgrade script wasn't re-run by the installer, but I was able to run php cli/upgrade_database.php manually and finish the upgrade. My settings are as @whsir described since long ago.

[TuxedoOne]

I have a similar Problen when upgrading to 1.2.8 from 1.2.7.

Like @gdsotirov I tried to run upgrade_database.php from CLI but got also an error:

/data/cacti-1.2.8/cli# php upgrade_database.php
Upgrading from v1.2.7
Upgrading from v1.2.7 (DB 1.2.7) to v1.2.8
+++++x
################################
[Fail] - DB Exec Failed!, Error 1146: Table 'cacti.aggregate_graph_templates' doesn't exist
+------------------------------+
ALTER TABLE aggregate_graph_templates ADD COLUMN gprint_format CHAR(2) default "" AFTER gprint_prefix
################################

[netniV]

@TuxedoOne, if you haven't already, open that as a separate issue, though I thought there was one already open for the Aggregate table issue.

[TuxedoOne]

No I didn't open a seperate issue. I think I found the issue here:
#3146

Right?

[netniV]

That could be likely.

[netniV]

This should have been resolved with the above patch.