Linux cron執行mysql失敗(編碼問題)

 

在接入一個渠道的時候,由於對方不給ROOT許可權,也沒有MYSQL的最高許可權,MYSQL的配置這些已經不受掌控.於是出現了一下問題.

1.首先,為了統計伺服器平均線上人數,於是寫了個SHELL指令碼,每天7點-0點之間每小時執行該命令,獲得當前線上使用者,以便統計最高線上使用者,以及平均線上使用者.
SHELL指令碼如下:
time08_23.sh
------------------------------------------------------------------------------------------------------------------------------------
#!/bin/bash
# crontab -e 57 7-22 * * * /home/nemo/crontab/time08_23.bash
DB_USER="root"
DB_PASS="***"
DB_HOST="localhost"
DB_PORT="3306"
# Others vars
BIN_DIR="/usr/bin"
# TODO
# 獲得8080連接埠
THETIME=`netstat -ant | grep 8080 | wc -l`
# 獲得當前小時
THEHOUR=`date +%H`

# 更新當前小時的現在人數
$BIN_DIR/mysql -u$DB_USER -p$DB_PASS -h$DB_HOST -P$DB_PORT << EOF
use wapgame4sky2;
UPDATE MgrSky SET time$THEHOUR = $THETIME WHERE TO_DAYS(date) = TO_DAYS(now());
EOF
------------------------------------------------------------------------------------------------------------------------------------

2.以上的SHELL指令碼,在本地以及遠程伺服器上,通過 source time08_23.sh 以及 ./time08_23.sh 都能正確運行,資料無誤,一切正常

3.但以上指令碼使用cron任務調度器執行資料庫卻沒有資料,這樣我一度認為是任務調度器配置錯誤,或者許可權問題,但查詢錯誤記錄檔獲得以下資訊:
Character set 'ascii' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml'

<img src='http://hi.csdn.net/attachment/201108/25/0_1314264038vkzR.gif' />

4.分析一下便清楚是編碼格式的問題.於是逐步查詢問題所在.

4.1查看當前mysql編碼集設定:

然後,在shell中加入:
set names latin1;
修改指令檔編碼格式;
修改linux系統預設編碼集;
等等等等方案都嘗試過了,都失敗,cron執行該指令碼任然報該錯誤.
同樣的指令檔.為什麼source 和 ./都執行正常,使用cron執行卻出現編碼問題???這個問題,我問了2個linux群,居然沒有一個人能幫忙解決(或者他們根本就不願意理睬這樣的小問題)

show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /opt/mysql/share/charsets/ |
+--------------------------+----------------------------+
查看本地mysql編碼集設定:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

然後,查詢mysql官網,對於該問題的解決辦法如下:http://dev.mysql.com/doc/refman/5.0/en/charset-configuration.html
------------------------------------------------------------------------------------------------------------------------------------
[client]
character-sets-dir=/usr/local/mysql/share/mysql/charsets

[client]
character-sets-dir="C:/Program Files/MySQL/MySQL Server 5.0/share/charsets"

Character set 'charset_name' is not a compiled character set and is not
specified in the '/usr/share/mysql/charsets/Index.xml' file

[client]
default-character-set=charset_name
------------------------------------------------------------------------------------------------------------------------------------

問題應該算是明確了.按照我的理解,linux cron 在編譯shell指令碼的時候,應該採用的是latin1字元集.而這裡卻使用UTF-8,導致編碼不支援.
遂聯絡那邊營運,取得了my.cnf,以及mysql的root許可權.

5.修改:
vi /etc/my.cnf
在[client]下添加
default-character-set=latin1
在[mysqld]下添加
default-character-set=latin1
重啟mysql
------------------------------------------------------------------------------------------------------------------------------------
開啟mysql命令：
/usr/local/mysql/bin
./mysqld_safe &
關閉mysql服務的命令是：
./mysqladmin -u root -p shutdown
------------------------------------------------------------------------------------------------------------------------------------

終於,問題解決了...