0

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.

  • I modified the script to rewrite the last insert statement and now the error is different ------- 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 ')' at line 1 – Avinash Pawar Feb 14 at 8:30
  • Add the option '-v' to mysql. In you logfile you will see the complete sql-statements, this will make debugging your problem a lot easier. – Luuk Feb 14 at 16:00
  • I added the option but that just gave me the exact query that was issued against the server. – Avinash Pawar 2 days ago
  • I could locate the problem using the cron log. --> 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 '+' --> The last line of the script ----- this value is not being accepted by crontab. – Avinash Pawar 2 days ago
  • (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(),266,3,156,156,106,106,282,)) --> The last value not being picked up by crontab. You can see after 282, there is supposed to be value for the variable named idle, but it is not being picked up by crontab. – Avinash Pawar 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.