Mysql errors after upgrading form 21.2.7 to 21.2.8

Products

• CA Performance Management - Usage and Administration
• DX NetOps

Issue/Introduction

****************************
Replaced by KB 265673
****************************

 

I just looked into the MySQL error log on Portal DB server. There are some messages since we upgraded the environment the day before yesterday.

2022-04-12T22:00:00.524057Z 3498 [ERROR] [MY-010045] [Server] Event Scheduler: [netqos@%][em.e_rotate_event_partitions] VALUES value for partition 'p20220413' must have type INT
2022-04-12T22:00:00.524121Z 3498 [Note] [MY-010046] [Server] Event Scheduler: [netqos@%].[em.e_rotate_event_partitions] event execution failed.

Or:

2022-06-09T07:00:00.697638Z 123 [ERROR] [MY-010045] [Server] Event Scheduler: [root@localhost][em.e_rotate_event_partitions] Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_tolower_ci,IMPLICIT) for operation '='
2022-06-09T07:00:00.697686Z 123 [Note] [MY-010046] [Server] Event Scheduler: [root@localhost].[em.e_rotate_event_partitions] event execution failed.

The Event Manager (EM) Data Source (DS) in DX NetOps Performance Management Portal web UI may show failure for Test or Synchronization.

The Event Manager MySql DB (named em) may show excessive disk space usage.

This might also exhibit with an error in one of the Portal install log files. It would be seen in the (default path shown) /opt/CA/PerformanceCenter/InstallLogs/emmigrate.log.<Date> file. The error seen would be:

SEVERE: com.ca.im.installanywhere.util.DbMigrateException: com.ca.im.installanywhere.util.DbMigrateException: An exception occurred on step 0 (event_partitions.sql) of loading /app/opt/CA/PerformanceCenter/Tools/bin/../../EM/SQL/upgrade/21.2.11.0/_upgrade.21.2.11.0.xml
java.sql.SQLException: Error executing command: CALL em.rotate_event_partitions() 

Environment

All supported DX NetOps Performance Management releases

Cause

Stored procedures didn't get installed properly.

Resolution

Here are the steps to fix the issue and update the procedures.

1. Download the merge_old_event_partitions.sql file attached to this article.
2. Move it to the MySql server host. In most cases this is the same host the Portal web server is installed on but it can be different.
3. Place it where it's accessible such as a temporary folder like /tmp.
4. Stop the Event Manager service. Other services can remain running. Run:
   • systemctl stop caperfcenter_eventmanager
5. Run the following (default path shown):
   1. /opt/CA/MySql/bin/mysql -unetqos -p em < /tmp/merge_old_event_partitions.sql
   2. Enter the password when prompted
6. When the command completes restart the Event Manager service. Run:
   • systemctl start caperfcenter_eventmanager

This will clean up old partitions that are left hanging around due to the cause. The disk space usage issues should resolve right after the merge_old_event_partitions.sql completes.

 

Or you can run this syntax from the upgrade directory:

# /opt/CA/MySql/bin/mysql -unetqos -p em < /opt/CA/PerformanceCenter/EM/SQL/upgrade/22.2.2.0/merge_old_event_partitions.sql

Additional Information

Note that the MySql 8 update introduced in 21.2.8 prevents this due to it's different partitioning usage. This would only be seen on systems that have run 21.2.7 and earlier releases before upgrading to a 21.2.8 or newer release. Systems running 21.2.8 or newer releases only would not be subject to this issue.

Further evidence of the problem may be seen with many rows returned to this query in a 21.2.7 and earlier originating system. After the fix there should be few entries.

• /opt/CA/MySql/bin/mysql -uroot -p -e "select partition_name, partition_description from information_schema.partitions where partition_name like 'p%';"

After 21.2.8 and MySql 8 that command would only show the following (example from an internal lab):