MySql synonym drop and create

Question

I am running into an issue that, when my script is run, the MySql section of it isn't working or is dropping the synonym. Is there something else I need to put into the command to have it log into the MySql server and do everything that is stated?

#! /bin/bash

#Subject: Please stage the following TFL09143 Locator Bulletin to all TF90 staging environments:#



#########################################################################
# This next section is to go to mysql server and make changes.          #
# you can drop and create synonyms                                      #
# truncate a table and insert into a different one.                     #
# you will be able to verify the counts to the different locations      #
#########################################################################

$ mysql -h=app03-bsi -u "" -p ""  "TF90BPS" -bse "drop_view TF90.BTXADDR drop_view TF90.BTXSUPB CREATE_VIEW TF90.BTXADDR FOR TF90BP.TFBPS2.BTXADDR CREATE_VIEW TF90.BTXSUPB FOR TF90BP.TFBPS3.BTXSUPB TRUNCATE TABLE TF90BP.TFBPS3.BTXSUPB SELECT (*) FROM TF90BP.TFBPS2.BTXSUPB; select count (*) from TF90BP.TF90.BTXADDR select count(*) from TF90BPS.TF90.BTXADDR; select count(*) from TF90BP.TF90.BTXSUPB; select count(*) from TF90BPS.TF90.BTXSUPB;"

$ mysql -h=app03-bsi -u "" -p ""  "TF90LMS" -bse "drop_view TF90.BTXADDR drop_view TF90.BTXSUPB CREATE_VIEW TF90.BTXADDR FOR TF90LM.TFBPS2.BTXADDR CREATE_VIEW TF90.BTXSUPB FOR TF90LM.TFBPS3.BTXSUPB; TRUNCATE TABLE TF90LM.TFLMS2.BTXADDR;TRUNCATE TABLE TF90LM.TFLMS3.BTXSUPB;INSERT INTO TF90LM.TFLMS3.BTXSUPB SELECT * FROM TF90LM.TFLMS2.BTXSUPB;Verify select count(*) from TF90LM.TF90.BTXADDR;select count(*) from TF90LMS.TF90.BTXADDR;select count(*) from TF90LM.TF90.BTXSUPB;select count(*) from TF90LMS.TF90.BTXSUPB"

$ mysql -h=app03-bsi -u "" -p ""  "TF90NCS" -bse "drop_view TF90.BTXADDR  drop_view TF90.BTXSUPB CREATE_VIEW TF90.BTXADDR FOR TF90NC.TFBPS2.BTXADDR CREATE_VIEW TF90.BTXSUPB FOR TF90NC.TFBPS3.BTXSUPB; TRUNCATE TABLE TF90NC.TFNCS2.BTXADDR; TRUNCATE TABLE TF90NC.TFNCS3.BTXSUPB; INSERT INTO TF90NC.TFNCS3.BTXSUPB SELECT * FROM TF90NC.TFNCS2.BTXSUPB; Verify select count(*) from TF90NC.TF90.BTXADDR; select count(*) from TF90NCS.TF90.BTXADDR;select count(*) from TF90NC.TF90.BTXSUPB;select count(*) from TF90NCS.TF90.BTXSUPB"

$ mysql -h=app03-bsi -u "" -p ""  "TF90PVS" -bse "drop_view TF90.BTXADDR drop_view TF90.BTXSUPB CREATE_VIEW TF90.BTXADDR FOR TF90PV.TFBPS2.BTXADDR CREATE_VIEW TF90.BTXSUPB FOR TF90PV.TFBPS3.BTXSUPB; TRUNCATE TABLE TF90PV.TFPVS2.BTXADDR;TRUNCATE TABLE TF90PV.TFPVS3.BTXSUPB;INSERT INTO TF90PV.TFPVS3.BTXSUPB SELECT * FROM TF90PV.TFPVS2.BTXSUPB;Verify select count(*) from TF90PV.TF90.BTXADDR;select count(*) from TF90PVS.TF90.BTXADDR;select count(*) from TF90PV.TF90.BTXSUPB;select count(*) from TF90PVS.TF90.BTXSUPB"

#TFL09143 Staging#

cd \\ntsrv\common\To\IT-CERT-TEST\TFL09143 #change to mapped network drive 

cp -p TFL09143.pkg /d:/tf90/code_stg /tf90bp/code_stg /tf90lm/code_stg /tf90pv/code_stg 

InvalidInput="true" 

if [ $# -eq 0 ] ; then
    echo "This script sets up TF90 Staging"
    echo -n "Which production do you want to run? (RB/TaxLocator/Cyclic)"
    read ProductionDistro
else
    ProductionDistro="$1"
fi

while [ "$InvalidInput" = "true" ]
do
    if [ "$ProductionDistro" = "RB" -o "$ProductionDistro" = "TaxLocator" -o "$ProductionDistro" = "Cyclic" ] ; then
                    InvalidInput="false"
        break
    else 
        echo "You have entered an error"
        echo "You must type RB or TaxLocator or Cyclic"
        echo "you typed $ProductionDistro"
        echo "This script sets up TF90 Staging"
        read ProductionDistro
    fi
done

InvalidInput="true"

if [ $# -eq 0 ] ; then
    echo "This script sets up RB TF90 Staging"
    echo -n "Which Element do you want to run? (TF90/TF90BP/TF90LM/TF90PV/ALL)"
    read ElementDistro
else
    ElementDistro="$1"
fi

    while [ "$InvalidInput" = "true" ]
do
    if [ "$ElementDistro" = "TF90" -o "$ElementDistro" = "TF90BP" -o "$ElementDistro" = "TF90LM" -o "$ElementDistro" = "TF90PV" -o "$ElementDistro" = "ALL" ] ; then
        InvalidInput="false"
        break
    else 
        echo "You have entered an error"
        echo "You must type TF90 or TF90BP or TF90LM or TF90PV"
        echo "you typed $ElementDistro"
        echo "This script sets up TF90 Staging"
        read ElementDistro
    fi


done 

if [ "$ElementDistro" = "TF90" ] ; then

    cd /d/tf90/code_stg
    vim TFL09143.pkg
    export var=TF90_CONNECT_STRING=DSN=TF90NCS;export Description=TF90NCS;export Trusted_Connection=Yes;export WSID=APP03-
    BSI;export DATABASE=TF90NCS;
    export DATASET=DEFAULT
    yum install -y ../TFL09143.pkg
fi

if [ "$ElementDistro" = "$TF90BP" ] ; then
    cd /d/tf90bp/code_stg
    vim TFL09143.pkg
    export TF90_CONNECT_STRING=DSN=TF90BPS;export Description=TF90BPS;export Trusted_Connection=Yes;export WSID=APP03-
    BSI;export DATABASE=TF90BPS;
    yum install -y ../TFL09143.pkg
fi

if [ "$ElementDistro" = "$TF90LM" ] ; then
    cd /d/tf90lm/code_stg
    vim TFL09143.pkg 
    export TF90_CONNECT_STRING=DSN=TF90LMS;export Description=TF90LMS;export Trusted_Connection=Yes;export WSID=APP03-
    BSI;export DATABASE=TF90LMS;
    yum install -y ../TFL09143.pkg 
fi

if [ "$ElementDistro" = "TF90PV" ] ; then
    cd /d/tf90pv/code_stg
    vim TFL09143.pkg
    export TF90_CONNECT_STRING=DSN=TF90PVS;Description=TF90PVS;Trusted_Connection=Yes;WSID=APP03-
    BSI;DATABASE=TF90PVS;
    yum install -y ../TFL09143.pkg
fi

exit 0

Answer 1

I think the major problem here is that MySQL doesn't support synonyms. That's an Oracle thing.

mysql> CREATE SYNONYM info2 FOR info;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SYNONYM info2 FOR info' at line 1

Here is a good link on how to mimic this behaviour (http://blog.mclaughlinsoftware.com/2013/11/24/mysql-synonym/) It uses the CREATE VIEW syntax from MySQL