Recently in SQL Category

Environment: CentOS 5.5 x64

1. Download Oracle Instant Client basic and devel rpm and install
(You need to register an oracle account first)
rpm -ivh oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-devel-11.2.0.1.0-1.x86_64.rpm

2. Downlaod Extension for Oracle Database from PECL and install
wget http://pecl.php.net/get/oci8-1.4.3.tgz
pecl install oci8-1.4.3.tgz

3. Move oci8.so to extension_dir (see your php.ini)
cp /usr/local/lib/php/extensions/no-debug-non-zts-20060613/oci8.so /usr/lib64/php/modules

4. add extension=oci8.so to php.ini
echo extension=oci8.so >> /usr/local/etc/php.ini

Verifying the oci8 extension is work
# php -i | grep OCI
OCI8 Support => enabled

Easy Connect Example:
$conn = oci_connect('user', 'password', '//172.29.2.2/service_name');
// Use lsnrctl services in Oracle to list existing service name
$stid = oci_parse($conn, "select id from table");
oci_execute($stid, OCI_DEFAULT);
while ($arr = oci_fetch_array($stid, OCI_ASSOC)) {
echo "$arr[id].\n";
}

ref. Installing PHP and the Oracle Instant Client for Linux and Windows
PHP Oracle FAQThis is a featured page
PHP: oci_connect - Manual

MySQL Store Procedure 可以預先定義一些 function, 在 SQL Server 執行, 簡少程式的複雜性.

範例: 帶使用者 id, 取得使用者名稱
DELIMITER //
CREATE PROCEDURE test_proc(id CHAR(15))
BEGIN
SELECT name from nms_reso_user where uid=id;
END//

在使用時只要 call
mysql> call test_proc('jack');
+-----------+
| name |
+-----------+
| 林傑克 |
+-----------+
1 row in set (0.00 sec)

show procedure status; 查看現有的 stored procedures
用 drop procedure 來刪除 stored procedures, 例如: drop procedure test_proc;

call 只需開放 execute 權限
grant execute on dbname.* to username@'192.168.0.%' identified by "password";

PostgreSQL 預設只 Listen locahost
編輯 postgresql.conf, 檔案的位置應該跟 datadir 一樣
listen_addresses = '*'

編輯 pg_hba.conf, 加入 trust host
host all all 192.168.0.0/24 trust

service postgresql restart

netstat -nlt 應該要有一行
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN

How Do I Enable remote access to PostgreSQL database server?

若 PHP 要連 PostgreSQL,
需先裝 postgresql-devel 套件, 在編譯 configure 時加 --with-pgsql
就可以使用 pg* 的 PostgreSQL Functions

參考 gslin 這篇「無論如何都應該修改的 MySQL 預設值」
max_connect_errors=100
max_connections=1000
thread_cache_size=200
connect_timeout=20
skip-name-resolve
slave_net_timeout=30

max_connections 太小若連線數滿就會出現 Too many connections
show variables where Variable_name='max_connections'; 可以看目前的值
show status where Variable_name='Threads_cached'; 可以看目前的連線數

from MySQL Server A
mysqldump -uroot -p db_name | sed -e 's/CHARSET=latin1/CHARSET=utf8' -e 's/\\//' | iconv -cfbig5 -tutf8 > db_name.sql

to MySQL Server B
echo SET NAMES utf8; >> new_db_name.sql
cat db_name.sql >> new_db_name.sql
mysql -uroot -p < new_db_name.sql

若啟動 MySQL 時遇到上述訊息, 很可能是被 SELinux denied
egrep "denied.*mysqld" /var/log/audit/audit.log
若有結果, 就是被 SELinux denied

最簡單的做法是把 SELinux disable
echo 0 > /selinux/enforce
再編輯 /etc/selinux/config, SELINUX=disabled

若不想把 SELinux disable, 需把 allow rule 加入
> /var/log/audit/audit.log (先把 audit.log 清空)
/etc/init.d/mysql start (啟動 MySQL, 此時會有一些 log 存入 audit.log)
audit2allow -a -M mysqld (用 audit2allow 根據 audit.log 產生 rule, 並建立模組)
semodule -i mysqld.pp (載入 mysqld.pp 模組)
再啟動 MySQL, 若還是無法啟動, 再重複 audit2allow, semodule 的動作

ref. 小州的 SELinux 管理配置 (PDF) | Albert's SELinux 設定範例 | MacBlog3: 關於 SELinux

show full fields from table_name
可以取得 MySQL 的欄位註解,
註解輸入中文名稱, 在寫程式時用上面的方式取得註解, 非常方便.
ref. 取得MySQL表單的註解

MySQL 原創者 Michael Widenius 在 Blog 貼了一篇 Oops, we did it again (MySQL 5.1 released as GA with crashing bugs)
列舉了一些 5.1.30 未修復的漏洞, 在尚未完整測試前, 不建議在線上系統使用 5.1.30
還是繼續用 5.0 GA 好了.

Export from MySQL to CSV
這篇提到的轉 CSV 方法滿不錯的
方法一
mysql -uusername -ppassword dbname -B -e "select * from tablename`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv
方法二
SELECT * INTO OUTFILE '/tmp/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM tablename;
此方法是存在 Server 那台機器

DbVisualizer 是一套全方位的資料庫管理工具, 各種資料庫都支援.
會注意到這套主要是它有 for Linux 的版本, 用來在 Linux 下管理 MSSQL 還滿好用的.
DbVisualizer 是用 Java 寫的, 需 copy 中文字型到 $JAVA_HOME/jre/lib/fonts 中文才不會秀出方塊,
cd $JAVA_HOME/jre/lib/fonts
mkdir fallback
cp path/msjh.ttf fallback (複製微軟正黑體)
cd fallback
mkfontscale
mkfontdir
試用的 Free Edition 好像也沒什麼限制.

ref. Linux操作系统JDK中文字体设置方法介绍