MySQL - Why is my BASH Script insert/update query not working correctly? [closed]

Question

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.

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):

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)

Result:

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

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

Query:

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

Result:

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

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

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)

Result:

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!

BASH Shell Script:

#!/bin/bash
file_path="/IP/"
while true; do
    file_name=`find $file_path -mount -maxdepth 1 -type f -name "*.IP*" -printf "%f\n" | head -1`
    if [ "$file_name" != "" ];then
        echo "$file_name"

        host_id=`cat $file_path$file_name | grep "HOST_ID" | sed 's/^.*=//' | tr '[:lower:]' '[:upper:]' | cut -c1-13`
        eth0=`cat $file_path$file_name | grep "ETH0" | sed 's/^.*=//'`
        if [ "$eth0" != "" ];then
            eth0="INET_ATON('$eth0')"
        else
            eth0=NULL
        fi
        eth1=`cat $file_path$file_name | grep "ETH1" | sed 's/^.*=//'`
        if [ "$eth1" != "" ];then
            eth1="INET_ATON('$eth1')"
        else
            eth1=NULL
        fi
        eth2=`cat $file_path$file_name | grep "ETH2" | sed 's/^.*=//'`
        if [ "$eth2" != "" ];then
            eth2="INET_ATON('$eth2')"
        else
            eth2=NULL
        fi
        eth3=`cat $file_path$file_name | grep "ETH3" | sed 's/^.*=//'`
        if [ "$eth3" != "" ];then
            eth3="INET_ATON('$eth3')"
        else
            eth3=NULL
        fi

mysql -v -v -u root -ppassword ccbu<<EOFMYSQL
INSERT INTO
ip
(host_id
,eth0
,eth1
,eth2
,eth3)
VALUES
('$host_id'
,$eth0
,$eth1
,$eth2
,$eth3)
ON DUPLICATE KEY UPDATE
eth0 = COALESCE($eth0,eth0)
,eth1 = COALESCE($eth1,eth1)
,eth2 = COALESCE($eth2,eth2)
,eth3 = COALESCE($eth3,eth3);
EOFMYSQL
        sudo rm -fv $file_path$file_name
    fi
done

Sample IP File:

HOST_ID=HOST1234
ETH3=172.16.5.83

SQL Fiddle:

http://sqlfiddle.com/#!9/3e56c/1

Answer 1

Solution: Reboot server

I hate my life...

Answer 2

The query you show us, can never do what you show as "2nd run". You would need something like

 insert into ip (host_id, eth3)
   values ('HOST1234', inet_aton('172.16.5.83'))
 ON DUPLICATE KEY UPDATE
  eth0 = COALESCE(eth0,eth1)
 ,eth1 = COALESCE(eth1,eth2)
 ,eth2 = COALESCE(eth2,eth3)
 ,eth3 = COALESCE(eth3,values(eth3))

Coalesce with a null parameter makes no sense whatsoever, since you know the outcome. I fill eth3 with a new value, shifting the old values to the front, until all four columns are filled. Thereafter, further inserts have no effect.

And an example how you might revv up your bash script (each file is read only once):

#!/bin/bash
file_path="/tmp/"
typeset -u host_id
while true; do
    for file_name in $file_path/*.IP*
    do
      if [ "$file_name" != "$file_path/*.IP*" ];then
        echo "$file_name"
        eth0=NULL
        eth1=NULL
        eth2=NULL
        eth3=NULL
        while read LINE
        do
          case "$LINE" in
            HOST_ID=*)
              host_id=${LINE/*=}
              ;;
            ETH0=*|ETH1=*|ETH2=*|ETH3=*)
              ethn=${LINE/=*/}
              ethn=${ethn/ETH/}
              typeset eth${ethn}="INET_ATON('${LINE/*=/}')"
              ;;
          esac
        done < $file_name
        echo $host_id $eth0 $eth1 $eth2 $eth3
      else
        echo "Here be no files! :-("
      fi
    done
    sleep 5
done

Add the mysql update after "echo $host_id $eth0 $eth1 $eth2 $eth3".