MySQL 8 does not support table partitioning, due to removal of MySQL partitioning plugin

[GOTO75]

Hello,

I try the Perl script on RHEL 7.5 and MySQL8 and I have the following error: " Your installation of MySQL does not support table partitioning "

Before I did the same steps on a CentOS 7 and MySQL8 and all is working normally.

I don't know yet why it is not working.

In the case you have an idea :-)

Thank you.

[macosta1]

Same issue here but on Ubuntu 20.04 and MySQL8, I also followed this video https://www.youtube.com/watch?v=ANQ3DHTr6eo where it's suggested that this would also work on MySQL8 commenting part of the script. but same result and the following on logs: root@zabbix:~# journalctl -t mysql_zbx_part -- Logs begin at Wed 2021-04-28 13:44:34 UTC, end at Wed 2021-04-28 20:58:26 UTC. -- Apr 28 19:42:42 zabbix mysql_zbx_part[1494]: Your installation of MySQL does not support table partitioning. Apr 28 19:45:10 zabbix mysql_zbx_part[1524]: Partitioning for "history" is not found! The table might be not partitioned. Apr 28 19:45:10 zabbix mysql_zbx_part[1524]: Partitioning for "history_log" is not found! The table might be not partitioned. Apr 28 19:45:10 zabbix mysql_zbx_part[1524]: Partitioning for "history_str" is not found! The table might be not partitioned. Apr 28 19:45:10 zabbix mysql_zbx_part[1524]: Partitioning for "history_text" is not found! The table might be not partitioned. Apr 28 19:45:10 zabbix mysql_zbx_part[1524]: Partitioning for "history_uint" is not found! The table might be not partitioned. Apr 28 19:45:10 zabbix mysql_zbx_part[1524]: Partitioning for "trends" is not found! The table might be not partitioned. Apr 28 19:45:10 zabbix mysql_zbx_part[1524]: Partitioning for "trends_uint" is not found! The table might be not partitioned. Apr 28 20:52:52 zabbix mysql_zbx_part[2290]: Partitioning for "history" is not found! The table might be not partitioned. Apr 28 20:52:52 zabbix mysql_zbx_part[2290]: Partitioning for "history_log" is not found! The table might be not partitioned. Apr 28 20:52:52 zabbix mysql_zbx_part[2290]: Partitioning for "history_str" is not found! The table might be not partitioned. Apr 28 20:52:52 zabbix mysql_zbx_part[2290]: Partitioning for "history_text" is not found! The table might be not partitioned. Apr 28 20:52:52 zabbix mysql_zbx_part[2290]: Partitioning for "history_uint" is not found! The table might be not partitioned. Apr 28 20:52:52 zabbix mysql_zbx_part[2290]: Partitioning for "trends" is not found! The table might be not partitioned. Apr 28 20:52:52 zabbix mysql_zbx_part[2290]: Partitioning for "trends_uint" is not found! The table might be not partitioned. Apr 28 20:58:26 zabbix mysql_zbx_part[2342]: Your installation of MySQL does not support table partitioning.

[larcorba]

Script seems to be broken in MySQL 8.0, investigating.

[larcorba]

Seems like MySQL 8 removed the Partitioning plugin, making this script completely obsolete for MySQL 8.

This script will not be updated further as it depends on that implementation. Script is still tested and working in the lastest MariaDB version, so my recomendation is running that or using stored procedures as detailed in the guide here:

https://blog.zabbix.com/partitioning-a-zabbix-mysql-database-with-perl-or-stored-procedures/13531/

[RafaelHGBotelho]

From what I saw in the video that @macosta1 mentioned, it was only necessary to comment on the functions that refer to the partitioning plugin, because MySQL 8.0 comes native.

But in my case the script just doesn't run on MySQL 8.0.25.

[RafaelHGBotelho]

@macosta1 @larcorba @GOTO75 For it to work in MySQL 8.0, you need to comment out the lines as per the code below:

unless ( check_have_partition() ) {...

unless (defined($part_tables->{$key})) {...

sub check_have_partition {...

[larcorba]

Hi Rafael,

Thanks for your comments, I'll check them out soon and do some additional testing.

[larcorba]

I have a semi-fixed version here, by commenting out the lines required. Little more complicated than just uncommenting three lines, but definitely a start to a workaround.

Bascially comment out:

#unless ( check_have_partition() ) {
#       print "Your installation of MySQL does not support table partitioning.\n";
#       syslog(LOG_CRIT, 'Your installation of MySQL does not support table partitioning.');
#       exit 1;
#}

and

#       unless (defined($part_tables->{$key})) {
#               syslog(LOG_ERR, 'Partitioning for "'.$key.'" is not found! The table might be not partitioned.');
#               next;
#       }

and

#sub check_have_partition {
#       my $result = 0;
# MySQL 5.5
#       #my $sth = $dbh->prepare(qq{SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'have_partitioning'});
# MySQL 5.6 + MariaDB
#       my $sth = $dbh->prepare(qq{SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition'});
#
#       $sth->execute();
#
#       my $row = $sth->fetchrow_array();
#
#       $sth->finish();
#
# MySQL 5.5
#       #return 1 if $row eq 'YES';
# MySQL 5.6 + MariaDB
#       return 1 if $row eq 'ACTIVE';
#}

One problem left, it does NOT remove old partitions. You will see the following: DBD::mysql::db do failed: Duplicate partition name p2021_08_10 at /usr/share/zabbix/mysql_zbx_part.pl line 113.

The script is now not able to recognize the partitions that are already created. Thus the function in the Perl script that deletes older partitions also cannot find them to delete them.

I will spend some more time on to figure out what commenting out the parts in this script breaks and why. Might need to add some Perl code later to get it solidly working.

FILE REMOVED, SEE BRANCH BELOW!

[larcorba]

Fix: #2 Link: https://github.com/OpensourceICTSolutions/zabbix-mysql-partitioning-perl/pull/2

New branch online with the fix! Seems like everything is working for MySQL version 8, even the creation and deletion of the partitioning.

What's left:

Branch needs to be tested on both MySQL 5.5 and MariaDB to make sure it works as expected in those versions. After that it will be pulled into Main and documented.

[RafaelHGBotelho]

@larcorba I realized that I really wasn't deleting, I'm going to test it with your changes.

Great job, and thanks a lot!

[larcorba]

Documented + merged into main.

@rafael No worries! Hope everything works for you now and the rest of the Zabbix community.

For anyone reading, feel free to open an issue or pull request if something is missing/broken and check out https://oicts.com for the best Zabbix consultancy around 👍🏼

[RafaelHGBotelho]

@larcorba Tested and validated, everything works perfectly! 🥇