On large systems, Primary ID usage on heavily used tables will overflow due to default MySQL variable size

[TheWitness]

Describe the bug

On Cacti systems that have been running for years and who have experienced quite a bit of change, the database will run out of auto_increment variables for several tables. I've just come across this and the damage is pretty significant.

Expected behavior

Cacti should be able to have millions of graphs over years of use without issue.

[bmfmancini]

Would this affect systems that started from 1.2.x or systems that started from 0.8 and upgraded since then ?

[bmfmancini]

Quick question would mysql safe just fail to do the insert and it would error out or is cacti not showing any signs of the issue unless you look at the db side I have a few large systems so want to check on what the impact would be

[TheWitness]

It starts erroring somewhere about 3m graphs over it's lifetime.

[TheWitness]

@netniV, the only question on this one is do we do it be default in the 1.2.17 upgrade for all systems. For new systems, it won't be required.

[bmfmancini]

@TheWitness

I just ran a quick test on this and found a small bug

root@cacti-1:/var/www/html/cacti/cli# php fix_mediumint.php -h
!#/usr/bin/env php
PHP Warning:  include(../../include/cli_check.php): failed to open stream: No such file or directory in /var/www/html/cacti/cli/fix_mediumint.php on line 26
PHP Warning:  include(): Failed opening '../../include/cli_check.php' for inclusion (include_path='.:/usr/share/php') in /var/www/html/cacti/cli/fix_mediumint.php on line 26
PHP Fatal error:  Uncaught Error: Call to undefined function cacti_sizeof() in /var/www/html/cacti/cli/fix_mediumint.php:36
Stack trace:
#0 {main}
  thrown in /var/www/html/cacti/cli/fix_mediumint.php on line 36

I had to change

include('../../include/cli_check.php');
to
include('../include/cli_check.php');

[bmfmancini]

Actually I think that fixes the error but breaks the script

This is the output I get

root@cacti-1:/var/www/html/cacti/cli# php fix_mediumint.php --debug
!#/usr/bin/env php

And the DB still looks the same

MariaDB [cacti]> describe data_input;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| hash         | varchar(32)           | NO   |     |         |                |
| name         | varchar(200)          | NO   | MUL |         |                |
| input_string | varchar(512)          | YES  |     | NULL    |                |
| type_id      | tinyint(2)            | NO   |     | 0       |                |
+--------------+-----------------------+------+-----+---------+----------------+

Unless I am missing a file or something

[TheWitness]

lol, bangsh!

[TheWitness]

Try again.

[TheWitness]

Should look like this on the 2nd pass anyway

[root@vmhost3 cli]# php -q fix_mediumint.php --debug
Fixing Column widths
DEBUG: Column data_template_data_id in Table data_input_data already converted.
DEBUG: Column id in Table data_template_data already converted.
DEBUG: Column local_data_template_data_id in Table data_template_data already converted.
DEBUG: Column local_data_id in Table data_template_data already converted.
DEBUG: Column id in Table data_template_rrd already converted.
DEBUG: Column local_data_template_rrd_id in Table data_template_rrd already converted.
DEBUG: Column local_data_id in Table data_template_rrd already converted.
DEBUG: Column id in Table graph_local already converted.
DEBUG: Column id in Table data_local already converted.
DEBUG: Column local_data_id in Table data_source_purge_action already converted.
DEBUG: Column local_data_id in Table data_source_purge_temp already converted.
DEBUG: Column local_data_id in Table data_source_stats_daily already converted.
DEBUG: Column local_data_id in Table data_source_stats_hourly already converted.
DEBUG: Column local_data_id in Table data_source_stats_hourly_cache already converted.
DEBUG: Column local_data_id in Table data_source_stats_hourly_last already converted.
DEBUG: Column local_data_id in Table data_source_stats_monthly already converted.
DEBUG: Column local_data_id in Table data_source_stats_weekly already converted.
DEBUG: Column local_data_id in Table data_source_stats_yearly already converted.
DEBUG: Column id in Table graph_templates_graph already converted.
DEBUG: Column local_graph_id in Table graph_templates_graph already converted.
DEBUG: Column local_graph_template_graph_id in Table graph_templates_graph already converted.
DEBUG: Column graph_template_item_id in Table graph_template_input_defs already converted.
DEBUG: Column id in Table graph_templates_item already converted.
DEBUG: Column local_graph_template_item_id in Table graph_templates_item already converted.
DEBUG: Column local_graph_id in Table graph_templates_item already converted.
DEBUG: Column task_item_id in Table graph_templates_item already converted.
DEBUG: Column local_graph_id in Table graph_tree_items already converted.
DEBUG: Column local_data_id in Table poller_item already converted.
DEBUG: Column local_data_id in Table poller_output already converted.
DEBUG: Column local_data_id in Table poller_output_boost already converted.
DEBUG: Column local_data_id in Table poller_output_realtime already converted.
DEBUG: Column graph_tree_item_id in Table settings_tree already converted.
DEBUG: Column data_template_rrd_id in Table snmp_query_graph_rrd already converted.
Column widths adjusted on 0 Tables!