MySQL ERROR 1064 (42000) 通過 bash 腳本 (RHEL) 插入

MySQL 伺服器：8.0.16 商業作業系統：RHEL 7.5

我編寫了一個簡單的腳本，將某些資訊插入到數據庫表中。

以下是我的腳本

# 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}

該腳本應該在每次執行時在數據庫表中插入一行。

以下是數據庫表：

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

當我執行腳本時，它確實執行成功並在表中插入一行。

   # 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)' 

它確實在表中插入了一行，但是，當我在 crontab 中安排它時，當 crontab 執行它時，它會拋出一個錯誤，如下所示：

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

如果我只是選擇 INSERT 語句並在數據庫上執行它，它確實會執行。如果我只是選擇所有 mysql 命令並從命令行單獨執行它，它確實會執行，唯一的問題是當我從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)

佩羅斯 說

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

我已經花了將近 3 天的時間，沒有任何進展。

我通過刪除整條線（空閒）並重寫了這條線來糾正它，它開始工作了。我猜可能有一個未辨識的空格或某些字元未被解析，因此出現錯誤 1064。我刪除了該行並對其進行了更改並將其放在腳本的開頭。

引用自：https://dba.stackexchange.com/questions/259690