Update MySQL table by appending text to existing column value

Written by - 0 comments

Published on - Listed in MySQL DB Database

Today I decided to resort the blog category "Nagios/Monitoring" (which was pointing to www.claudiokuenzler.com/Nagios) into simply "Monitoring". Over the recent years I dove deeper into Icinga so to still access the whole category under the name "Nagios" would not be correct.

As you can imagine, all articles of this blog are in a database. Each article uses one or more tags which are specified in the column "tags". So I knew: When I replace the category name from Nagios to Monitoring, I need to adapt the tags or at least make sure that wherever "Nagios" appears as a tag, "Monitoring" must be added.

First I needed to find all articles which have the tag "Nagios" but not yet "Monitoring". Luckily there is the MySQL function NOT which helps a lot in that case:

mysql> select title,tags from news where tags LIKE "%Nagios%" AND tags NOT LIKE "%Monitoring%";
| title                                                             | tags
| Update of Nagios Plugin check_mysql_slavestatus.sh                | Nagios Shell MySQL DB 
| Nagios Plugin for Dell Equallogic                                 | Nagios Hardware
| Immediate Availability of check_equallogic                        | Nagios Hardware
| check_equallogic with more than one member                        | Nagios Hardware
| Dell's official answer and bugfix of Equallogic plugin            | Nagios Hardware
| New Nagios Plugin: Check Confixx License                          | Nagios Linux Internet
| New version of check_equallogic released                          | Nagios Hardware
| New (modified) Nagios plugin to check hardware on ESXi            | Nagios VMware Virtualization
| New version 20100526 of check_equallogic available                | Nagios Hardware
| check_confixxlicense does not check Confixx MAC-address           | Nagios
| New version 20100628 of check_esxi_wbem                           | Nagios VMware Hardware Virtualization
| New version 20100630 of check_equallogic released                 | Nagios Hardware
| New version 20100705 of check_esxi_wbem                           | Nagios VMware Hardware Virtualization
| Connection type fix in new version of check_equallogic            | Nagios Hardware
| New version 20101026 of check_equallogic                          | Nagios Hardware
| Using check_esxi_wbem with non-root user in ESXi 4.1              | Nagios VMware Virtualization
| Added 'fan' type in check_equallogic                              | Nagios Hardware
| How to create automatic PDF from Nagios Availability Reports?     | Nagios Linux
| Major Update of check_equallogic: Volumes check                   | Nagios Hardware 
| Nagiosgraph from NSClient++ (Windows) Checks                      | Nagios Windows
| When Nagiosgraph shows gaps/empty spaces in stats                 | Nagios
| Version 20110110 of check_esxi_wbem.py released                   | Nagios VMware Virtualization
| New version of check_esxi_wbem (Intel server bugfix)              | Nagios Hardware Virtualization
| check_esxi_wbem now handles timeouts                              | Nagios VMware Virtualization
| check_esxi_wbem is now check_esxi_hardware                        | Nagios VMware Virtualization
| Nagiosgraph map entries for check_esx plugin (by OP5)             | Nagios VMware Virtualization
| Some minor changes in check_equallogic plugin                     | Nagios Hardware
| Lost check type 'temp' added again to check_equallogic            | Nagios Hardware 
| check_equallogic now catches ethernet packet errors               | Nagios Hardware Network 
| check_equallogic: Bugfix in volumes and etherrors thresholds      | Nagios Hardware Network
| New/Rewritten temperature check in check_equallogic               | Nagios Hardware
| Nagios plugin check_esxi_hardware now also for IBM servers        | Nagios VMware Virtualization
| Major update of check_esxi_hardware Nagios plugin                 | Nagios VMware Virtualization
| Bugfixes and password from file possibility in check_esxi_hardware| Nagios VMware Virtualization
| Nagios plugin check_esxi_hardware updated and comments on blog    | Nagios VMware Internet Hardware
| Bugfix for IBM Blade Servers in check_esxi_hardware               | Nagios VMware Hardware Virtualization
| Modified Nagios Plugin check_dell_warranty (Adapted to euro websit| Nagios Internet Hardware
| HP's customized ESXi 4.1U1 fails on HP BL460C (Gen1)              | VMware Nagios Hardware Virtualization
| check_esxi_hardware now reads file for username AND password      | VMware Nagios Hardware Virtualization
| Perfdata now in check_mysql_slavestatus                           | Linux Nagios DB MySQL
| When Nagios notification mails show wrong host alias              | Nagios
| Improved disk check on check_equallogic Nagios plugin             | Nagios Hardware
| Possibility to check disk pool utilization on Equallogic          | Nagios Hardware
| New (and final) version of check_equallogic released              | Nagios Hardware
| New Nagios plugin: check_weblogic_heap                            | Nagios
| Ignore option now possible in check_esxi_hardware plugin          | Nagios VMware Virtualization
| Bugfix in check_equallogic and new Nagios plugin: check_storcenter| Nagios Hardware
| First minor update of check_storcenter                            | Nagios Hardware
| New versions of check_storcenter and check_equallogic             | Nagios Hardware
| check_esxi_hardware + ESXi 5.0 + DELL OMSA = FAIL                 | Nagios Hardware VMware Virtualization
| How to monitor Windows network traffic with Nagios                | Nagios Windows Network 
| New Nagios plugin: check_win_net_usage                            | Nagios Windows Network
| check_esx3.pl Nagios plugin error (Server version unavailable)    | Nagios VMware Linux Virtualization
| Check Kaspersky Server License Expiration with Nagios             | Nagios Windows
| Another bugfix in check_KAV.bat                                   | Nagios Windows
| Dell OMSA + ESXi 5.0 is now working!                              | VMware Nagios Hardware Virtualization
| Minor update of Nagios plugin check_mysql_slavestatus             | Nagios DB MySQL
| Minor update of check_equallogic (info and temp check)            | Nagios Hardware
| Nagios plugin check_procs misses perfdata and how you can add it  | Nagios Linux
| Bugfix in check_equallogic (volumes check)                        | Nagios Hardware 
| Performance data added to volumes check in check_equallogic       | Nagios Hardware
| Monitor Windows remote connections (RDP or ICA)                   | Nagios Windows Network Virtualization
| Nagios plugin check_disk_smb outputs Result from smbclient not su | Nagios Linux
| NetBackup errors (get_string() failed) appear in Report-Problems  | Windows Nagios Backup 
| New plugin to monitor IBM System Storage TS Tape Libraries        | Nagios Hardware
| Slow hardware discovery/check with ESXi 5.0 U1                    | VMware Nagios Virtualization
| Monitor how many (virtual) tapes are left available in Datadomain | Nagios Hardware Backup
| Nagios plugin check_esxi_hardware is now available as OpenBSD port| Nagios VMware Virtualization
| Updated Dell warranty urls in check_esxi_hardware plugin          | Nagios VMware Virtualization
| New/rewritten pool checks in check_equallogic                     | Nagios Hardware
| Monitor Equallogic Snapshots with check_equallogic                | Nagios Hardware
| Bugfix in check_esxi_hardware (Manufacturer discovery)            | Nagios VMware Hardware Virtualization
| Bugfix in check_equallogic (info check)                           | Nagios Hardware
| VMware fixes slow hardware status bug                             | VMware Nagios Virtualization
| check_equallogic shows status of raid reconstruction              | Nagios Hardware
| New version of check_ibm_ts_tape.pl released!                     | Nagios Hardware
| Fixed Cleaning Status check in check_ibm_ts_tape.pl               | Nagios Hardware
| Raid percentage bugfix in check_equallogic                        | Nagios Hardware
| check_esxi_hardware and Dell PowerEdge R620: Riser Config Err 0   | Nagios Hardware VMware Virtualization
| Bugfix for check_win_net_usage wrapper-plugin                     | Nagios Windows Network
| check_win_net_usage now handles connection errors                 | Nagios Windows Network
| Check/monitor SMTP server for TLS and SSL connections             | Linux Nagios Internet Mail
| Testing and comparing new Nagios version (3.2.3 to 4.0/3.99.95)   | Nagios
| New check_esxi_hardware.py version adds workaround for Dell PE x62| Nagios Hardware VMware Virtualization
| Raid verifying percentage in check_equallogic                     | Nagios Hardware
| Nagios/Monitoring Plugin check_esxi_hardware FAQ                  | VMware Nagios Virtualization
| Nagiosgraph not showing correct values for disk space             | Nagios Linux Internet Shell
| check_equallogic now also checks power supply fans                | Nagios Hardware
| ILO4 Firmware 1.20 changed the XML output                         | Nagios Hardware
| User quota disk space monitoring with check_disk                  | BSD Linux Shell Nagios 
| Adapting check_mem.pl to measure memory usage on FreeBSD          | Nagios BSD Perl
| Deprecated old style usage on check_esxi_hardware plugin          | Nagios VMware Virtualization
| Another Dell workaround in check_esxi_hardware                    | Nagios VMware Virtualization
| Updated check_mysql_slavestatus plugin and documentation          | Nagios MySQL DB 
| How to monitor qmail mail queue (check_mailq alternative)         | Nagios Linux Shell Mail
| Monitor zfs pools - which Nagios plugin should be used?           | Nagios BSD Hardware Unix
| Presenting check_zpools - a Nagios plugin to monitor zfs pools    | Nagios Solaris BSD Unix
| check_zpools with performance data                                | Nagios Solaris BSD Unix
| check_smart with support for hardware raid controllers            | Nagios Hardware Linux Perl
| Improved authentication error timeout in check_esxi_hardware      | Nagios VMware Hardware Virtualization
| Updated Dell warranty information check in check_esxi_hardware    | Nagios VMware Hardware Virtualization
| check_equallogic and copying to spare raid status                 | Hardware Nagios
| July 2013 in Switzerland - a hot month (graph)                    | Personal Nagios
| check_ilo2_health 1.58 fixes drive check for multiple backplanes  | Hardware Nagios
| New version of check_mysql_slave released (minor bugfixes)        | Nagios MySQL Database 
| Nagios-Plugins 1.5 out, check_procs finally with performance data | Nagios Linux 
| check_mysql_slavestatus plugin now handles IO Running in Connecti | Nagios MySQL Database 
| check_equallogic: Bugfix in temp check and optical cleanup        | Hardware Nagios
| check_smart.pl saves server lifes (defect hard drive detected)    | Hardware Nagios BSD
| check_smart plugin now has its own (documentation-) page          | Nagios Hardware
| check_equallogic: Bugfix in vol check and issue with firmware 7.0 | Hardware Nagios
| Fix negative temperature value alert in check_pcmeasure2 plugin   | Nagios
| Bugfix in etherror check type in check_equallogic                 | Nagios Hardware
113 rows in set (0.02 sec)

Of course I didn't want to rewrite the whole column of these found articles with only one word "Monitoring" and risk removing all other relevant tags. No, I needed to find a way to append the string "Monitoring" to the existing value.


I found the answer on StackOverflow and it's amazingly simple:

mysql> UPDATE news SET tags = tags + 'Monitoring' where tags LIKE "%Nagios%" AND tags NOT LIKE "%Monitoring%";
Query OK, 113 rows affected, 226 warnings (0.02 sec)
Rows matched: 113  Changed: 113  Warnings: 226

By using "tags = tags" MySQL is told to use the existing value and then append + 'Monitoring' to it. Why complicated when you can have it simple!

Verification with the same SQL query from before:

mysql> select title,tags from news where tags LIKE "%Nagios%" AND tags NOT LIKE "%Monitoring%";
Empty set (0.00 sec)


It turns out, that the query above did not work at all. Instead of adding the string "Monitoring" to the value, it set the whole column to "0":

mysql> select newsid,title,tags from news where tags = '0' limit 0,3;
| newsid | title                                                                     | tags |
|     14 | Update of Nagios Plugin check_mysql_slavestatus.sh                        | 0    |
|     47 | Nagios Plugin for Dell Equallogic                                         | 0    |
|     50 | Immediate Availability of check_equallogic                                | 0    |
3 rows in set (0.00 sec)

Darn it! Luckily I made a mysqldump just before so I was able to restore the table with all the original values again!

Finally found the correct answer which is using the CONCAT function on another Stackoverflow question (not exactly the way I needed it, but easily adaptable):

mysql> UPDATE news SET tags = CONCAT(tags, ' Monitoring') where tags LIKE "%Nagios%" AND tags NOT LIKE "%Monitoring%";
Query OK, 113 rows affected (0.00 sec)
Rows matched: 113  Changed: 113  Warnings: 0

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder