MySQL ERROR 1064 (42000) insert via bash script (RHEL)

Question

MySQL Server: 8.0.16 Commercial OS: RHEL 7.5

I have written a simple script, to insert certain information into a database table.

Following is my script

# cat ~mysqldba/bin/connections.sh

   #!/bin/bash

log_file=~mysqldba/stats/connections_info.log


threads_connected=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf  -e "show global status like 'threads_connected';" | tr -d "| " | egrep -iv 'variable_name' | awk -F " " '{ print $2 }'`
echo "Number of open connections are ${threads_connected}." >> ${connections} 

threads_running=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e "show global status like 'threads_running';" | tr -d "| " | egrep -iv 'variable_name' | awk -F " " '{ print $2 }'`
echo "Number of connections that are not sleeping out of ${threads_connected} are ${threads_running}." >> ${connections} 

app1=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e "select host from information_schema.processlist;" | grep 'ip-address1'| wc -l`

sleep_app1=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e "select host from information_schema.processlist where command like 'sleep';" | grep 'ip-address1'|wc -l`

app2=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e "select host from information_schema.processlist;" | grep 'ip-address2'| wc -l`

sleep_app2=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e "select host from information_schema.processlist where command like 'sleep';" | grep 'ip-address2'|wc -l`

max_used_connections=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf  -e "show global status like 'max_used_connections';" | tr -d "| " | egrep -iv 'variable_name' | awk -F " " '{ print $2 }'`

max_used_connections_time=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf  -e "show global status like 'max_used_connections_time';" | tr -d "| " | egrep -iv 'variable_name' | awk -F " " '{ print $2 }'`

idle=`mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e "select count(*) from information_schema.processlist where command='sleep';" | grep -v '+'`

/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e "insert into backup_info.connection_info (serial_no, run_date, run_time, total_number_of_connections, number_of_running_connections, no_of_connections_from_app27, no_of_sleeping_from_app27, no_of_connections_from_app28, no_of_sleeping_from_app28, max_used_connections, number_of_idle_connections) values (0, curdate(), curtime(), ${threads_connected}, ${threads_running}, ${app1}, ${sleep_app1}, ${app2}, ${sleep_app2}, ${max_used_connections}, ${idle})" 2>> ${log_file}

This script is supposed to insert one row in a database table each time it runs.

Following is the database table:

CREATE TABLE `connection_info` (
  `SERIAL_NO` mediumint(9) NOT NULL AUTO_INCREMENT,
  `run_date` date DEFAULT NULL,
  `run_time` time DEFAULT NULL,
  `TOTAL_NUMBER_OF_CONNECTIONS` mediumint(9) DEFAULT NULL,
  `NUMBER_OF_RUNNING_CONNECTIONS` mediumint(9) DEFAULT NULL,
  `NO_OF_CONNECTIONS_FROM_APP1` mediumint(9) DEFAULT NULL,
  `NO_OF_SLEEPING_FROM_APP1` mediumint(9) DEFAULT NULL,
  `NO_OF_CONNECTIONS_FROM_APP2` mediumint(9) DEFAULT NULL,
  `NO_OF_SLEEPING_FROM_APP2` mediumint(9) DEFAULT NULL,
  `MAX_USED_CONNECTIONS` mediumint(9) DEFAULT NULL,
  `NUMBER_OF_IDLE_CONNECTIONS` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`SERIAL_NO`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED

When I run the script , it does run successfully and inserts a row in the table.

    # sh -x ~mysqldba/bin/connections.sh
+ log_file=/home/mysqldba/stats/connections_info.log
++ date +%F_%T
+ echo '--------##### -------- 2020-02-14_13:04:16 -------- #####-------'
+ echo '--------##### ------------------------------------------ #####-------'
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'show global status like '\''threads_connected'\'';'
++ tr -d '| '
++ egrep -iv variable_name
++ awk -F ' ' '{ print $2 }'
+ threads_connected=271
+ echo 'Number of open connections are 271.'
/home/mysqldba/bin/connections.sh: line 17: ${connections}: ambiguous redirect
++ tr -d '| '
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'show global status like '\''threads_running'\'';'
++ egrep -iv variable_name
++ awk -F ' ' '{ print $2 }'
+ threads_running=3
+ echo 'Number of connections that are not sleeping out of 271 are 3.'
/home/mysqldba/bin/connections.sh: line 20: ${connections}: ambiguous redirect
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e 'select host from information_schema.processlist;'
++ wc -l
++ grep ip-address1
+ app1=156
++ wc -l
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e 'select host from information_schema.processlist where command like '\''sleep'\'';'
++ grep ip-address1
+ sleep_app1=156
++ grep ip-address2
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e 'select host from information_schema.processlist;'
++ wc -l
+ app2=107
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e 'select host from information_schema.processlist where command like '\''sleep'\'';'
++ grep ip-address2
++ wc -l
+ sleep_app2=107
++ tr -d '| '
++ awk -F ' ' '{ print $2 }'
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'show global status like '\''max_used_connections'\'';'
++ egrep -iv variable_name
+ max_used_connections=282
++ tr -d '| '
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'show global status like '\''max_used_connections_time'\'';'
++ egrep -iv variable_name
++ awk -F ' ' '{ print $2 }'
+ max_used_connections_time=2020-02-1313:29:08
++ mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e 'select count(*) from information_schema.processlist where command='\''sleep'\'';'
++ grep -v +
+ idle=269
+ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'insert into backup_info.connection_info (serial_no, run_date, run_time, total_number_of_connections, number_of_running_connections, no_of_connections_from_app27, no_of_sleeping_from_app27, no_of_connections_from_app28, no_of_sleeping_from_app28, max_used_connections, number_of_idle_connections) values (0, curdate(), curtime(), 271, 3, 156, 156, 107, 107, 282, 269)' 

It did insert a row in the table, however, when I schedule this in the crontab, and when crontab executes it, it throws me an error which is as follows:

ERROR 1064 (42000) at line 1: 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 ', , 158, 158, 106, 106, , )' at line 1

If I just pick the INSERT statement and run it on the database, it does run. If I simply pick all the mysql command and run it individually from the command line, it does run, the only problem is when I run it from crontab.

/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'insert into backup_info.connection_info (serial_no, run_date, run_time, total_number_of_connections, number_of_running_connections, no_of_connections_from_app27, no_of_sleeping_from_app27, no_of_connections_from_app28, no_of_sleeping_from_app28, max_used_connections, number_of_idle_connections) values (0, curdate(), curtime(), 271, 3, 156, 156, 107, 107, 282, 269)

perros says

# perror 1064
MySQL error code MY-001064 (ER_PARSE_ERROR): %s near '%-.80s' at line %d

I have spent almost 3 days after it with no progress whatsoever.

Answer 1

I got it rectified by removing the entire line (idle) and rewrote the line, it started working. I guess there may have been an unidentified space or some character which was not being parsed hence the error 1064. I removed the line and changed it and put it in the beginning of the script.