Investigate support for MariaDB Xpand

[TheWitness]

Feature Request

Is your feature request related to a problem? Please describe

MariaDB Xpand allows for hyper scalability of Cacti but it's not 100% MySQL/MariaDB compatible. There are definitely issues that have been identified thus far that need more research.

Describe the solution you'd like

Evaluate and identify gaps in MariaDB Xpand that would prevent a user from using it as a Cacti backend.

Describe alternatives you've considered

N/A

[TheWitness]

So far, we have identified the following issues:

1. Issues importing plugin schema data

[root@vmhost8 apps]# clustrix_import -u cactiuser -p cactiuser --database cacti -i flowraw1.sql
clustrix_import: OperationalError(1054, '[31745] Unknown column: SQL-ERROR: unable to resolve column "eth0"') 

2. DELETE IGNORE not working lib/boost.php[911]
3. INFORMATION SCHEMA lacks TABLE_ROWS and other support
4. A single Query using GROUP_CONCAT can only use a single ORDER BY column for the entire query lib/template.php[2248]
5. Some UI components seem to lag, though it's not know yet the source of the lagging. This may be a red herring
6. Cacti Database Recommendations don't recognize MariaDB Xpand

This is what I have found thus far. It's a bit of a show stopper, but will continue to document as I go.

[TheWitness]

ORDER BY in a GROUP_CONCAT() issue.

Query:

SELECT dl.*,
GROUP_CONCAT(DISTINCT snmp_field_name ORDER BY snmp_field_name) AS input_fields,
GROUP_CONCAT(DISTINCT data_source_name ORDER BY data_source_name) AS data_source_names
FROM data_local AS dl
INNER JOIN data_template_data AS dtd
ON [dl.id](http://dl.id/) = dtd.local_data_id
INNER JOIN data_template_rrd AS dtr
ON dtd.local_data_id = dtr.local_data_id
INNER JOIN data_input_fields AS dif
ON dif.data_input_id = dtd.data_input_id
INNER JOIN snmp_query_graph_rrd AS sqgr
ON sqgr.data_template_id = dtd.data_template_id
WHERE input_output = 'in'
AND type_code = 'output_type'
AND dl.host_id = ?
AND dl.data_template_id = ?
AND dl.snmp_query_id = ?
AND dl.snmp_index = ?
GROUP BY dtd.local_data_id
HAVING local_data_id IS NOT NULL
AND input_fields = ?
AND data_source_names = ?

[TheWitness]

DELETE IGNORE issue:

[TheWitness]

Link to compatibility issues:

https://docs.clustrix.com/xpand/latest/using-xpand/mariadb-compatibility/xpand-version-and-mysql-version-compatibility

[TheWitness]

More interesting information:

https://docs.clustrix.com/xpand/latest/using-xpand/mariadb-compatibility

[TheWitness]

Found that the Devices page performance quite slow when performing LEFT JOIN for Graph and Data Source counts. It showed 100x slowdown compared to MariaDB 10.5.15. Remove the two JOIN statements and performance was acceptable.

[TheWitness]

MariaDB Xpand is currently not compatible with Cacti. We will revisit this analysis at a later date after MariaDB closes some of the gaps in the SQL support there.