Background: I have a MySQL table that stores ip addresses for a very large amount of hosts. It’s a simple table (host xyz -> ip1, ip2, ip3). These hosts will write a flat file with it’s ip addresses to the central server (where MySQL database is kept) and a simple BASH shell script will process that file and insert/update the info into the ip table of the MySQL database.

Where’s my old ip? Everything works fine as is except that I noticed that if a host fails to write one of its ip addresses to the flat file, the insert/update query in the BASH shell script will erase the old ip address. I want to keep that ip address.

I should use COALESCE right? Well, I rebuild my query with COALESCE and it works in phpMyAdmin and in the mysql terminal.

The issue: I put my shiny new query into the BASH shell script and it still erases my old ips (ips that existed for the host prior to the insert/update).

I can copy and paste the query all day into the mysql terminal or phpMyAdmin and it behaves exactly as I want. When I let the bash shell script do it, it wipes out anything that is not explicitly set in the query.

Here’s an example query:

INSERT INTO ip   (host_id   ,eth0   ,eth1   ,eth2   ,eth3) VALUES   ('HOST1234'   ,NULL   ,NULL   ,NULL   ,INET_ATON('172.16.5.83')) ON DUPLICATE KEY UPDATE   eth0 = COALESCE(NULL,eth0)   ,eth1 = COALESCE(NULL,eth1)   ,eth2 = COALESCE(NULL,eth2)   ,eth3 = COALESCE(INET_ATON('172.16.5.83'),eth3) 

I run the query as very verbose to watch everything. I move the same ip address around to different eth#s, but the old one keeps getting deleted when the query is ran through the bash shell script. I copy/paste the same query into phpMyAdmin or mysql terminal and it works fine–no ips get deleted.

1st run with eth3=ip (through mysql):

host_id     eth0        eth1        eth2        eth3 ========================================================== HOST1234    NULL        NULL        NULL        2886731091 

2nd run with eth2=ip (through mysql):

host_id     eth0        eth1        eth2        eth3 ========================================================== HOST1234    NULL        NULL        2886731091  2886731091 

3rd run with eth3=ip (through bash script):

host_id     eth0        eth1        eth2        eth3 ========================================================== HOST1234    NULL        NULL        NULL        2886731091 

I hope that makes sense. I’ve already spent about 16 solid hours trying different ways and I cannot figure this out. Please help. I’ll provide any additional qualifying information you may need to help lead me to an answer. Thanks!