Description
MySQL database tables can grow unusually large if there are continuous port changes reported.
A port change entry is written when the port’s VLAN changes or port-based CLI configuration is applied (based on Undo commands in a CLI configuration). If VLAN or CLI configurations are made based on link state change, a port flapping condition would be constantly generating port change entries. Table entries would grow into the millions.
Version 8.3.5 and below does not purge and archive this information. If the database grows large enough, it can negatively impact High Availability restore and fail over operations. Symptoms include but are not limited to:
- Unable to list Port Changes when navigating to Logs > Port Changes and clicking Update (too many entries for UI to display)
- Very large database backups
- Failed attempts to execute an High Availability restore
- Failed attempts to execute an High Availability fail over
Scope
Version: 8.3.5 and below
Solution
Workaround: Reduce the database table size.
- Identify the ports that are flapping at the highest rate.
- Delete the appropriate port change entries.
Contact support for assistance.
Solution: The table size management will be improved in a future release.
Port changes are recorded in the MySQL PORTCHANGES table.
***NOTE: As of FortiNAC 8.8.11, the Port Change entries are recorded in NetworkSessionEvent table.
For FortiNAC 8.8.11 and above replace PORTCHANGES in all steps below with NetworkSessionEvent
- Verify PORTCHANGES table size.
Run the following command in the Control Server CLI to view the table size:
mysql bsc -e "select count(*) from PORTCHANGES;"
Output example from an affected customer site:
+----------+
| count(*) |
+----------+
| 26059345 |
+----------+
1 row in set (0.00 sec)
- Identify the offending ports
- Login to the Control Server CLI as root and run the following commands:
mysql bsc -e "select portID from PORTCHANGES;" > portIDs
cat portIDs | cut -d ' ' -f2 > portIDS
cat portIDS | uniq -c > portIDS-C
cat portIDS-C | sort -nr > portIDS-CS
cat portIDS-CS
The resulting file creates a table that references how many times a port id was referenced in the PORTCHANGES table, sorted with the largest first.
The first column is how many times the port was referenced. The second column is the database ID of the port.
Example:
808 431
232 577
34 505
28 425
26 506
25 479
23 584
22 1301
18 586
18 536
16 438
13 508
13 1282
12 535
12 1283
11 421
11 1532
10 507
8 429
6 408
5 1358
4 529
4 428
3 406
2 6510
2 571
2 558
2 534
2 430
1 564
1 533
1 1362
1 1360
1 1296
- Identify the port and switch using the database ID from the previous step:
device -dbid <database ID>
Example:
device -dbid 431
************************* Concord-3750 ********************** <- Name of the switch.
Landscape = 345051187939 00:50:56:A7:4A:E3
Pollable = false, Poll interval = 5 Minutes
Type = 6
Group = INTERFACE
MAC = 00:22:56:F6:FC:1F
Protocol = SnmpV1
Description = Concord-3750 Fa3/0/27 <- Name of the port.
IP = 192.168.10.1
State = Active
Status = Established
DBID = 431
Workaround 1: Delete the offending port's statistics from the table.
mysql bsc
delete from PORTCHANGES where portID = <database ID>;
exit
Example:
mysql bsc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9651
Server version: 5.0.95-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> delete from PORTCHANGES where portID = 431;
Query OK, 808 rows affected (0.02 sec)
mysql> exit
Workaround 2: Delete the entire table.
mysql bsc
delete from PORTCHANGES;
exit
Example:
mysql bsc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9651
Server version: 5.0.95-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> delete from PORTCHANGES;
Query OK, 569 rows affected (0.02 sec)
mysql> exit
