MySQL errors after upgrading from 3.6.0 to 20.2.10 and then to 21.2.10

Products

• DX NetOps
• CA Performance Management - Usage and Administration

Issue/Introduction

Since the PM was upgraded to 21.2.10, the following errors are stored in the /opt/CA/MySql/data/PerformanceCenter.gestion.enaire.es.err file:

2023-12-06T23:00:00.982057Z 613 [ERROR] [MY-010045] [Server] Event Scheduler: [netqos@localhost][em.e_rotate_event_partitions] Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_tolower_ci,IMPLICIT) for operation '='
2023-12-06T23:00:00.982092Z 613 [Note] [MY-010046] [Server] Event Scheduler: [netqos@localhost].[em.e_rotate_event_partitions] event execution failed.

 

There is a version mismatch between em and netqosportal database schemas:

mysql> select * from em.revision_info;
+----+---------+---------------------+-----------------+
| ID | Version | InstallDate         | DbSchemaVersion |
+----+---------+---------------------+-----------------+
|  1 | 2.0.0.2 | 2015-09-23 15:27:29 | 2.0.9.1         |
|  2 | 2.0.0.2 | 2016-11-10 11:33:28 | 2.5.0.0         |
|  3 | 2.0.0.2 | 2017-06-08 07:53:49 | 3.1.0.0         |
|  4 | 2.0.0.2 | 2022-06-21 12:01:34 | 3.5.0.0         |
|  5 | 2.0.0.2 | 2022-06-21 12:43:42 | 3.6.0.0         |
+----+---------+---------------------+-----------------+

mysql> select * from netqosportal.revision_info;
+---------------+-------------+---------------------+-----------------+------------------------------+-------------+-------------+
| NetOPsVersion | Version     | InstallDate         | DbSchemaVersion | BuildDate                    | BuildNumber | Description |
+---------------+-------------+---------------------+-----------------+------------------------------+-------------+-------------+
|               | 2.4.1.89    | 2015-09-23 15:25:40 | 2.4.0.0         | Thu Jan 22 22:44:49 EST 2015 |          89 | CAPC        |
|               | 2.8.0.171   | 2016-11-10 11:09:59 | 2.8.0.0         | Wed Jun 01 00:59:46 EDT 2016 |         171 | CAPC        |
|               | 3.1.0.257   | 2017-06-08 07:45:38 | 3.1.0.0         | Tue Mar 21 23:02:50 EDT 2017 |         257 | CAPC        |
|               | 3.5.0.212   | 2022-06-21 11:58:51 | 3.5.0.0         | Wed Dec 13 13:53:05 EST 2017 |         212 | CAPC        |
|               | 3.6.0.349   | 2022-06-21 12:29:07 | 3.6.0.0         | Tue Jun 19 20:49:39 EDT 2018 |         349 | CAPC        |
| 20.2.10       | 20.2.10.636 | 2022-06-22 10:09:40 | 20.2.10.0       | 49                           |         636 | CAPC        |
| 21.2.10       | 21.2.10.541 | 2022-06-23 12:33:00 | 21.2.10.0       | 1024                         |         541 | CAPC        |
+---------------+-------------+---------------------+-----------------+------------------------------+-------------+-------------+

Environment

DX NetOps Performance Management 21.2.10

Cause

Stored procedures didn't get installed properly.

Resolution

1. Download the 2 stored procedures files from this KB article.

2. Copy the 2 stored procedures files into the /opt/CA/PerformanceCenter/EM/ directory.

3. Run this command and save the output to a text file:
ls -lah /opt/CA/MySql/data/em/*.ibd

4. Stop the Event Manager service. Other services can remain running. Run:

systemctl stop caperfcenter_eventmanager

5. Run the following, from the /opt/CA/MySql/bin/ folder (this should be quick):

cd /opt/CA/MySql/bin/

./mysql em -p < /opt/CA/PerformanceCenter/EM/SQL/update_rotate_partitions_collation.sql

6. Run the following, from the /opt/CA/MySql/bin/ folder (this should take a while):

./mysql em -p < /opt/CA/PerformanceCenter/EM/SQL/merge_old_event_partitions.sql

7. When the command completes restart the Event Manager service. Run:

systemctl start caperfcenter_eventmanager

8. Run this command and save the output to a text file:
ls -lah /opt/CA/MySql/data/em/*.ibd

You may want to compare the ls outputs.

Additional Information

In DX NetOps PM 21.2.8 we enhanced to support for MySQL 8.

We used to use daily partitions to go with pmax. The merge_old_event_partitions.sql stored procedure moves all the p<date> partitions into pmax so that the partition maintenance would operate on all data.

The stored procedures will not update the em.revision info table.