MySQL ERROR 1064 (42000) During Bash Script Insert on RHEL

linuxMySQLmysql-8.0scripting

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.

Best Answer

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.

OBSERVATION #1

It is not recognized because of a typo

You have

  if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
        additional_mysqldump_params1="--single-transaction"
  else
        additional_mysqldump_params=""
  fi

It should be

  if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
        additional_mysqldump_params="--single-transaction"
  else
        additional_mysqldump_params=""
  fi

Please change the additional_mysqldump_params1 to additional_mysqldump_params

OBSERVATION #2

You have

chmod 600 "${backup_dir}/${database}.gz" ${additional_mysqldump_params1}

It should read

chmod 600 "${backup_dir}/${database}.gz"

Please remove additional_mysqldump_params1 from that line of code

OBSERVATION #3

There is no need to mysqldump information_schema or performance_schema databases

The INFORMATION_SCHEMA database is made of up in-memory temporary tables. They are dynamically generated and populated on mysqld startyo.
The PERFORMANCE_SCHEMA database is for the instrumentation of measuring performance. Each individual MySQL instance is responsible for populating it with instrumentation data when it is in use.

Connecting to MySQL on an EC2 Instance

Since Zapier is coming in from outside your instance, a possible cause of your problem is the security group for your instance. Most ports on EC2 instances (except the SSH port) are normally blocked by default unless you explicitly open them.

So you would need to open the mysql port 3306 on your instance up to the ip address Zapier is coming in from. (preferably you would not open it up to everyone in the world.)

See Using Amazon RDS/EC2 on the Zapier site, starting from the section where it says Can't Connect to My Database. They continue on to provide specific instructions for the security group settings.

You will also probably need to grant permission on the database using the mysql GRANT command to the user coming in from the Zapier ip address.

Something like this should work :

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'54.86.9.50'
IDENTIFIED BY 'newpassword';

Or you can allow that user to come in from any ip adress like this:

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'%'
IDENTIFIED BY 'newpassword';

(Incoming requests would still be limited to Zapier ip addresses due to the security group settings.)

Best Answer

Related Solutions

Thesqldump error on bash script

OBSERVATION #1

OBSERVATION #2

OBSERVATION #3

Connecting to MySQL on an EC2 Instance

Related Question