MySQL notes

Notes for working with MySQL

I am deliberately using a VERY old version of MySQL. 99% of the logic and commands will "just work" against other database types, and still give good performance. I feel this is a better solution instead of going for max performance that uses version or database specific enhancements. Likely later versions of the software will be done against updated databases looking more at performance. However right now, I want this to work on whatever people have available and not require heavy machines for monitoring.

My current priority list:

• works on MySQL out of the box and can be used against PSQL easily (untested)
• accurate
• reasonably fast
• can be added to easily by non-developers

Table Join logic

Basic table join for update from one table to another with where clause

UPDATE tableA a
  JOIN tableB b
    ON a.a_id = b.a_id
  JOIN tableC c
    ON b.b_id = c.b_id
   SET b.val = a.val+c.val
 WHERE a.val > 10
   AND c.val > 10;

Another way

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

Full Text search

SELECT 
	productName 
FROM 
	products 
WHERE 
	MATCH(productName) 
    AGAINST('1992' WITH QUERY EXPANSION);

Simple JOIN

Also called an INNER JOIN clause using the event database on backup01 This assumes that there will ALWAYS be a match between the two tables. If there is not, then no rows returned.

 SELECT d.hostname,d.address,p.Properties FROM Device d JOIN DeviceProperties p on p.DeviceId=d.id  WHERE d.id=14 \G
*************************** 1. row ***************************
  hostname: nas01.iwillfearnoevil.com
   address: 192.168.15.125
Properties: {"snmpEnable":"true","snmpVersions":["2","1","3"],"snmpVersion":"2","snmpPort":161,"snmpTries":2,"snmpMonitorTimeout":1000000,"snmpV3SecurityName":"123Fake","snmpV3Hash":"456StillFake","snmpV3Password":"789Fake","snmpCommunities":["public","private","foobar"],"snmpCommunity":"public","snmpInterfaceIgnore":"lo vmnet[0-9]","snmpFilesystemIgnoreType":"romram virtualMemory loop","snmpfilesystemIgnoreMatch":"\/snap \/blah\/lxd rando\/foobar","snmpMaxOidPerRequest":"40","winUser":"admin administrator","winPassword":"password password123","winDomain":"DC:\\Domain","winEventLogSeverity":"2","winEventLogMonitor":"False","winWmiMonitorIgnore":"True","nrpeMonitorTimeout":"20","nmapDiscoverPortArgs":"-p 1-10024 -sT --open -oG -","nmapUpdatePortList":"False","nrpeCommandPath":"\/usr\/local\/bin\/nrpe","nmapCommandPath":"\/usr\/bin","sshPort":"22","sshKeyBase64":"LS0tLS1CRUdJTiBSU0EgUFJJVkFURSBLRVktLS0tLQpNSUlFcEFJQkFBS0NBUUVBdHRON05jVmtl","sshUser":"failSafeUser","sshPassword":"notAGoodIdea","sshPassowrdEncrypted":"stillNotAGreatIdea","pingMonitorIgnore":"False","comments":"","links":"https:\/\/linux.org"}
1 row in set (0.00 sec)

LEFT Join:

This makes no assumption on the second table, just that there will be a match in the first one.

SELECT d.hostname,d.address,p.Properties FROM Device d LEFT JOIN DeviceProperties p on p.DeviceId=d.id  WHERE d.id=26 \G
*************************** 1. row ***************************
  hostname: pipeload.com
   address: 172.92.156.161
Properties: NULL
1 row in set (0.00 sec)

Group and sorts

Just a simple example of grouping by some column along with a column rename to something more "pretty" and then returning in descending order

mysql> select eventSeverity, count(*) as count from history group by eventSeverity order by eventSeverity DESC;
+---------------+-------+
| eventSeverity | count |
+---------------+-------+
|             5 | 27170 |
|             4 |  8376 |
|             3 | 19299 |
|             2 |  1496 |
|             1 |  1995 |
|             0 |    13 |
+---------------+-------+
6 rows in set (0.19 sec)

In String

Looks like a substring match that can be easily used for filtering down result sets. Looks like the result is case insensitive, and we need a binary swtich of we want to be case sensitive

mysql> select display_name, INSTR(display_name, 'lo') from trapEventMap where INSTR(display_name, 'lo') > 0;
+-------------------------------+---------------------------+
| display_name                  | INSTR(display_name, 'lo') |
+-------------------------------+---------------------------+
| network-port-block            |                        15 |
| failedLoginFromHost           |                         7 |
| invalidUserLoginAttempted     |                        12 |
| stpInstanceTopologyChangeTrap |                        16 |
| failedUserLoginTrap           |                        11 |
| egpNeighborLoss               |                        12 |
| checkPortLocal-22             |                        10 |
| check_load                    |                         7 |
+-------------------------------+---------------------------+
8 rows in set (0.00 sec)

mysql> select display_name, INSTR(display_name, 'lo') from trapEventMap where INSTR(binary display_name, 'lo') > 0;
+-------------------------------+---------------------------+
| display_name                  | INSTR(display_name, 'lo') |
+-------------------------------+---------------------------+
| network-port-block            |                        15 |
| stpInstanceTopologyChangeTrap |                        16 |
| check_load                    |                         7 |
+-------------------------------+---------------------------+
3 rows in set (0.00 sec)

Just an OR example

mysql> SELECT m.id, m.checkName, m.type, m.iteration, m.storage, m.hostId, m.hostGroup FROM monitoringDevicePoller m WHERE find_in_set(10, m.hostId) OR m.hostgroup in ("nrpeGeneric", "linuxGenericPhysical", "ping2") GROUP BY m.id;
+----+---------------------------------+-------+-----------+----------+----------------------------------------------------------------------------------------------------------+----------------------+
| id | checkName                       | type  | iteration | storage  | hostId                                                                                                   | hostGroup            |
+----+---------------------------------+-------+-----------+----------+----------------------------------------------------------------------------------------------------------+----------------------+
| 17 | lm-sensors                      | walk  |       300 | rrd      |                                                                                                          | linuxGenericPhysical |
| 27 | check_memory                    | nrpe  |       300 | graphite |                                                                                                          | nrpeGeneric          |
| 28 | check_postfix_queue             | nrpe  |       300 | graphite |                                                                                                          | nrpeGeneric          |
| 29 | check_crontab                   | nrpe  |       300 | graphite |                                                                                                          | nrpeGeneric          |
| 30 | checkOpenFiles                  | nrpe  |       300 | graphite |                                                                                                          | nrpeGeneric          |
| 31 | checkIdleServer                 | nrpe  |       300 | graphite |                                                                                                          | nrpeGeneric          |
| 32 | check_zombie_procs              | nrpe  |       300 | graphite |                                                                                                          | nrpeGeneric          |
| 33 | check_disk                      | nrpe  |       300 | graphite |                                                                                                          | nrpeGeneric          |
| 34 | check_load                      | nrpe  |       300 | graphite |                                                                                                          | nrpeGeneric          |
| 35 | check_faults                    | nrpe  |       300 | graphite |                                                                                                          | nrpeGeneric          |
| 36 | checkSwap                       | nrpe  |       300 | graphite |                                                                                                          | nrpeGeneric          |
| 37 | check_users                     | nrpe  |       300 | graphite | 10                                                                                                       | nrpeGeneric          |
| 38 | checkNetworkConnections-443     | nrpe  |       300 | graphite | 14                                                                                                       | nrpeGeneric          |
| 39 | checkPortLocal-22               | nrpe  |       300 | graphite | 14                                                                                                       | nrpeGeneric          |
| 40 | checkNetworkConnections-test442 | nrpe  |       300 | graphite | 14                                                                                                       | nrpeGeneric          |
| 47 | ping                            | alive |        60 | database | 29,34,33,12,30,19,24,13,31,11,10,16,23,21,14,26,40,22,15,36,27,17,18,28,76,77,84,85,83,82,81,78,86,88,40 | none                 |
+----+---------------------------------+-------+-----------+----------+----------------------------------------------------------------------------------------------------------+----------------------+
16 rows in set (0.00 sec)