0

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

closed as off-topic by Paul White Feb 12 '18 at 5:14

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Is there an INSERT or UPDATE trigger on the table? – ypercubeᵀᴹ Feb 12 '18 at 9:18
  • Perhaps that else eth3=NULL should be else eth3="NULL" You could print the SQL statement that is produced to see what exactly is being sent to the dtabase. – ypercubeᵀᴹ Feb 12 '18 at 9:24
1

Solution: Reboot server

I hate my life...

  • That's no explanation :-( Do you prefer rebooting regularly? I've addes some hints for your script. – Gerard H. Pille Feb 12 '18 at 8:32
  • 1
    I feel for you, brother. Sadly this isn't a good match for the site, and will be removed. – Michael Green Feb 12 '18 at 9:07
1

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".

  • Thanks again. I just built the query explicitly in the bash script and every update wipes out the other columns. Something outside of all of this is wrong. Something is wrong with the way the bash script runs an update query vs mysql runs an update query. Thanks so much for all of your help so far. I'll keep this thread updated when I figure out what's going on. – Adam Kennedy Feb 11 '18 at 22:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.