中年人之碎碎念

2010年11月18日星期四

最近Pidgin 登入MSN時 SSL憑證錯誤的解決方法

最近用Pidgin登入MSN會出現SSL憑證問題.
解決方法:
當然要先把Pidgin關掉.
download 新的授權憑證檔

here ===> http://files.andreineculau.com/projects/pidgin/omega.contacts.msn.com.txt

將 .txt 的副檔名拿掉, 存成 omega.contacts.msn.com 即可.
然後蓋掉原本在 ~/.purple/certificates/x509/tls_peers 目錄下的 omega.contacts.msn.com 檔案

然後再啟動Pidgin

2010年10月6日星期三

有意思的sql問題

在藍色小舖看到有人問一個sql問題.
http://www.blueshop.com.tw/board/FUM20041006152746MYF/BRD201010051423497Z5.html
我已經回答了.

use test;

CREATE TABLE `blue1` (
`data1` smallint(6) NOT NULL,
PRIMARY KEY (`data1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE `blue2` (
`q1` smallint(6) NOT NULL,
`q2` smallint(6) NOT NULL,
`q3` smallint(6) NOT NULL,
KEY `fk1` (`q1`),
KEY `fk2` (`q2`),
KEY `fk3` (`q3`),
CONSTRAINT `fk1` FOREIGN KEY (`q1`) REFERENCES `blue1` (`data1`),
CONSTRAINT `fk2` FOREIGN KEY (`q2`) REFERENCES `blue1` (`data1`),
CONSTRAINT `fk3` FOREIGN KEY (`q3`) REFERENCES `blue1` (`data1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

insert into blue1(data1) values (1),(2),(3),(4),(5);

insert into blue2 values
(1,3,4),
(2,1,5),
(3,4,1);

root@[test]>select count(q1) into @total from blue2;
Query OK, 1 row affected (0.00 sec)

root@[test]>select @total;
+--------+
| @total |
+--------+
| 3 |
+--------+

root@[test]>select q1, count(q1)/@total
-> from blue2
-> group by q1
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q1 from blue2);
+----+------------------+
| q1 | count(q1)/@total |
+----+------------------+
| 1 | 0.3333 |
| 2 | 0.3333 |
| 3 | 0.3333 |
| 4 | 0.0000 |
| 5 | 0.0000 |
+----+------------------+

root@[test]>select q2, count(q2)/@total
-> from blue2
-> group by q2
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q2 from blue2);
+----+------------------+
| q2 | count(q2)/@total |
+----+------------------+
| 1 | 0.3333 |
| 3 | 0.3333 |
| 4 | 0.3333 |
| 2 | 0.0000 |
| 5 | 0.0000 |
+----+------------------+

root@[test]>select q3, count(q3)/@total
-> from blue2
-> group by q3
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q3 from blue2);
+----+------------------+
| q3 | count(q3)/@total |
+----+------------------+
| 1 | 0.3333 |
| 4 | 0.3333 |
| 5 | 0.3333 |
| 2 | 0.0000 |
| 3 | 0.0000 |
+----+------------------+

subquery 部份 select q3 from blue2 加上 distinct執行速度會比較快.
當資料量大的時候最好加上,現在三筆資料,就不加了.

2010年9月16日星期四

Powerful tool for MySQL stored procedure developer.
我剛試用.
網址在:
http://www.mydebugger.com/index.php

2010年9月1日星期三

MySQL Database Quota Control

之前在酷學園有人問到,所以後來就開發了這功能.
詳細的資料整理成pdf了.
http://www.filesonic.com/file/17520903/mysqlquotabunko.rar

MySQL 讀取及輸出文字檔

在酷學園看到有人問到排序的問題,有兩欄位.
http://phorum.study-area.org/index.php/topic,62396.msg315492.html#new

推測是讀取儀表獲得的數據,要進行後續處理.若這類的需求只是偶爾,其實用試算表就很方便.但若是常態性需求,用程式是比較方便,排序/分組這些是資料庫的強項.
問題在於處理文字檔的方法,一般人較少知道.思考時就被侷限了,另外MySQL的文件
或是很多網站上的例子,輸入檔及輸出檔的檔名都是手動輸入固定的,顯得就是手工
作業. MySQL 的LOAD DATA指令不能在Stored Procedure,也不能prepare,彈性
受到限制,但是我們可以利用Shell Script.以下就是連grant privilege都有說明的範例,我也一併發到酷學園供發問者參考.

MySQL 讀取及輸出文字檔
===============================
1. 基本環境建立與初始測試

1.1 用 roo登入 mysql,建立作業用的資料庫與使用者,並賦予相關權限.

root@[(none)]>create database sorter;

root@[(none)]>grant all on sorter.* to 'sorter'@'%' identified by 'sorter';

root@[(none)]>grant file on *.* to 'sorter'@'%';

1.2 在OS中建立目錄供MySQL存取,並建立預備輸入的文字檔

ps. 目錄必須設定讓mysql有權限讀寫,不一定是讓mysql當owner

# cd /
# mkdir myimpexp
# chown mysql:mysql myimpexp

# cd myimpexp
# vim raw-2010-09-02
# cat raw-2010-09-02
10.2    25.2
10.2    50.4
10.2    100.8
20.4    25.2
20.4    50.4
20.4    100.8

ps. txt file裡面用tab隔開

1.3 用sorter登入並建立table

sorter@[sorter]>create table sortforge (
col1 decimal(8,2),
col2 decimal(8,2)
);

1.4 輸入文字檔

sorter@[sorter]>LOAD DATA INFILE '/myimpexp/raw-2010-09-02' INTO TABLE sortforge
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

sorter@[sorter]>select * from sortforge;
+-------+--------+
| col1 | col2   |
+-------+--------+
| 10.20 | 25.20 |
| 10.20 | 50.40 |
| 10.20 | 100.80 |
| 20.40 | 25.20 |
| 20.40 | 50.40 |
| 20.40 | 100.80 |
+-------+--------+

sorter@[sorter]>select *
    -> from sortforge
    -> order by col2,col1 desc;
+-------+--------+
| col1 | col2   |
+-------+--------+
| 20.40 | 25.20 |
| 10.20 | 25.20 |
| 20.40 | 50.40 |
| 10.20 | 50.40 |
| 20.40 | 100.80 |
| 10.20 | 100.80 |
+-------+--------+

1.5 排序後輸出為文字檔

sorter@[sorter]>SET @OutfileCmd := concat("SELECT col1,col2 FROM sortforge ORDER BY col2,col1 DESC INTO OUTFILE '/myimpexp/sort-", DATE_FORMAT(now(),'%Y-%m-%d_%H%i%s'), ".txt' FIELDS TERMINATED BY '\t' ESCAPED BY '\"' LINES TERMINATED BY '\n';");
Query OK, 0 rows affected (0.00 sec)

sorter@[sorter]>PREPARE statement FROM @OutfileCmd;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

sorter@[sorter]>EXECUTE statement;
Query OK, 6 rows affected (0.00 sec)

# cat sort-2010-09-02_063030.txt
20.40   25.20
10.20   25.20
20.40   50.40
10.20   50.40
20.40   100.80
10.20   100.80

2. 使用Shell Script 呼叫處理
將上面的操作方式改用Shell Script並配合使用date.如欲排序之原始檔為儀器等輸出,
可以將檔案改名為raw-yyyy-mm-dd格式,並可將Shell Script配合cron進行自動化處理.

程式碼如下:
-------- script start ----------
#!/bin/bash
# ---- Basic info set up ----
MYSQL_PATH="/opt/mysql554/bin/mysql"
MYSQL_USER="sorter"
MYSQL_PASS="sorter"
MYSQL_DB="sorter"
# ---- Chnage the basic info for your environment

# Query MySQL database function
query() {
echo "$1" | ${MYSQL_PATH} -s --user=${MYSQL_USER} --password=${MYSQL_PASS} ${MYSQL_DB}
}

today=$( date +%F )
in_file="'/myimpexp/raw-$today'"

moment=$( date +%F_%H%M%S )
out_file="'/myimpexp/sort-$moment'"

sql="truncate sortforge; LOAD DATA INFILE $in_file INTO TABLE sortforge FIELDS TERMINATED BY '\t' ESCAPED BY '\"' LINES TERMINATED BY '\n'; SELECT col1,col2 FROM sortforge ORDER BY col2,col1 DESC INTO OUTFILE $out_file FIELDS TERMINATED BY '\t' ESCAPED BY '\"' LINES TERMINATED BY '\n';"

RESULT=$(query "${sql}")

----------script end ----------------

# ./sh1.sh
# cat sort-2010-09-02_064313
20.40   25.20
10.20   25.20
20.40   50.40
10.20   50.40
20.40   100.80
10.20   100.80

得到排序後的輸出

2010年8月7日星期六

MySQL database size part2

3. MySQL metadata 的存放空間 information_schema 在database size上的應用

user:mysample 有權限的database有三個,其中information_schema是系統metadata的存放空間,
我們就可以存取他來獲得一些資訊.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysample           |
| test               |
+--------------------+

我們可以執行下面的指令:
select table_schema,sum(data_length),sum(index_length),sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_type='BASE TABLE'
and engine='MyISAM'
group by table_schema;
+--------------+------------------+-------------------+---------+
| table_schema | sum(data_length) | sum(index_length) | totsize |
+--------------+------------------+-------------------+---------+
| mysample     |            37750 |             38912 |   76662 |
| test         |              152 |              2048 |    2200 |
+--------------+------------------+-------------------+---------+
2 rows in set (0.01 sec)

結果均相符,因為來源都一樣. 這裡可以看到MySQL方便的地方,我們不需要將information_schema排除.
到目前都是以user:mysample來作例子.當我們要以整個instance為範圍來管理就需要用root權限登入了.
以root的視野來看:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| impexp_text        |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pandora            |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+

mysql> select table_schema,sum(data_length),sum(index_length),sum(data_length)+sum(index_length) as totsize
    -> from information_schema.tables
    -> where table_type='BASE TABLE'
    -> and engine='MyISAM'
    -> group by table_schema;
+--------------+------------------+-------------------+---------+
| table_schema | sum(data_length) | sum(index_length) | totsize |
+--------------+------------------+-------------------+---------+
| auth         |              231 |              4096 |    4327 |
| bookmarks    |              152 |              3072 |    3224 |
| books        |             6094 |             10240 |   16334 |
| joomla       |           105223 |            118784 | 224007 |
| joomla2      |           108588 |            118784 | 227372 |
| mail         |             3545 |              4096 |    7641 |
| mysample     |            37750 |             38912 |   76662 |
| mysql        |           492362 |             72704 | 565066 |
| pureftpd     |               88 |              3072 |    3160 |
| simple_blog |               44 |              3072 |    3116 |
| test         |              152 |              2048 |    2200 |
| test2        |              476 |              7168 |    7644 |
+--------------+------------------+-------------------+---------+

有沒有發覺到上面 show databases的結果扣掉 information_schema,共有14個databses;
而我們依據 information_schema.tables 計算的資料卻只有12筆.

因為有些database建立了,但是都沒有在裡面建立資料庫物件(table/view等),所以在
information_schema.tables 裡面就沒有資料.
接下來我們看一下 information_schema.schemata

mysql> select distinct schema_name
    -> from information_schema.schemata;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| impexp_text        |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pandora            |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+
15 rows in set (0.00 sec)

mysql> select distinct table_schema
    -> from information_schema.tables;
+--------------------+
| table_schema       |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+
13 rows in set (0.03 sec)

現在應該很清楚的看到了 information_schema.schemata, information_schema.tables
與show databases, show table status, show tables的關係了.

***********************************************************************************
4. 建立控制用database及其附屬table/view
接下來我們建立一個database 來作控制之用.

login as root

mysql> create database quotadb;

mysql> use quotadb;
Database changed

CREATE TABLE quota (
dbname CHAR(64) NOT NULL PRIMARY KEY,
limitbyte BIGINT NOT NULL,
exceeded ENUM('Y','N') DEFAULT 'N' NOT NULL);

insert into quota
select distinct schema_name,20971520,'N'
from information_schema.schemata
where schema_name != 'information_schema'
and schema_name != 'mysql'
and schema_name != 'quotadb';

mysql> select * from quota;
+-------------+-----------+----------+
| dbname      | limitbyte | exceeded |
+-------------+-----------+----------+
| auth        | 20971520 | N        |
| bookmarks   | 20971520 | N        |
| books       | 20971520 | N        |
| impexp_text | 20971520 | N        |
| joomla      | 20971520 | N        |
| joomla2     | 20971520 | N        |
| mail        | 20971520 | N        |
| mysample    | 20971520 | N        |
| pandora     | 20971520 | N        |
| pureftpd    | 20971520 | N        |
| simple_blog | 20971520 | N        |
| test        | 20971520 | N        |
| test2       | 20971520 | N        |
+-------------+-----------+----------+
13 rows in set (0.00 sec)

我們需要將mysql這個系統database排除,information_schema系統metadata也需要排除,
quotadb自己也要排除,以免限制住自己.

接下來建立一個view以方便獲取database size,並且在view中事先將mysql,quotadb排除.

create view v_dbsize as
select table_schema as dbname,sum(data_length) as data_size,sum(index_length) as index_size,sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema != 'mysql'
and table_schema != 'quotadb'
and table_type='BASE TABLE'
and engine='MyISAM'
group by table_schema;

mysql> select * from v_dbsize;
+-------------+-----------+------------+---------+
| dbname      | data_size | index_size | totsize |
+-------------+-----------+------------+---------+
| auth        |       231 |       4096 |    4327 |
| bookmarks   |       152 |       3072 |    3224 |
| books       |      6094 |      10240 |   16334 |
| joomla      |    105223 |     118784 | 224007 |
| joomla2     |    108588 |     118784 | 227372 |
| mail        |      3545 |       4096 |    7641 |
| mysample    |     37750 |      38912 |   76662 |
| pureftpd    |        88 |       3072 |    3160 |
| simple_blog |        44 |       3072 |    3116 |
| test        |       152 |       2048 |    2200 |
| test2       |       476 |       7168 |    7644 |
+-------------+-----------+------------+---------+
11 rows in set (0.03 sec)

ps:因為這個instance裡面有兩個database impexp_text與pandora目前都是沒有任何資料的,
所以quota table有13筆record, v_dbsize view裡面有11筆record,是正常的.
運用quota與v_dbsize作一下比較.

select q.dbname, q.limitbyte, d.totsize, q.limitbyte - d.totsize as diff, d.totsize / q.limitbyte as percent
from quota q, v_dbsize d
where q.dbname = d.dbname;
+-------------+-----------+---------+----------+---------+
| dbname      | limitbyte | totsize | diff     | percent |
+-------------+-----------+---------+----------+---------+
| auth        | 20971520 |    4327 | 20967193 | 0.0002 |
| bookmarks   | 20971520 |    3224 | 20968296 | 0.0002 |
| books       | 20971520 |   16334 | 20955186 | 0.0008 |
| joomla      | 20971520 | 224007 | 20747513 | 0.0107 |
| joomla2     | 20971520 | 227372 | 20744148 | 0.0108 |
| mail        | 20971520 |    7641 | 20963879 | 0.0004 |
| mysample    | 20971520 |   76662 | 20894858 | 0.0037 |
| pureftpd    | 20971520 |    3160 | 20968360 | 0.0002 |
| simple_blog | 20971520 |    3116 | 20968404 | 0.0001 |
| test        | 20971520 |    2200 | 20969320 | 0.0001 |
| test2       | 20971520 |    7644 | 20963876 | 0.0004 |
+-------------+-----------+---------+----------+---------+

MySQL database size part1

MySQL Database size limit
======================================
1. 基本的檔案大小計算
in mysample
# pwd
/var/lib/mysql/mysample
# ls -l *.MYD | awk '{sum += $5} END {print sum}'
37750
# ls -l *.MYI | awk '{sum += $5} END {print sum}'
38912

所以我們計算得到76662 bytes.
注意:這個數字不包含 *.frm (Table Cache), *.TRN (Trigger), *.TRG (Function)等各式檔案.
僅是MyISAM的 data/index 的檔案.

然後我們寫了以下的php 程式:
----------------------------------------------------------------
#!/usr/bin/php -q
mysql_connect("localhost","mysample","mysample");

$result = mysql_query("SHOW TABLE STATUS FROM mysample;");

$sum = 0;

while($array = mysql_fetch_array($result)) {
    $total = $array['Data_length'] + $array['Index_length'];
   $sum += $total;
    print "--------------------------\n";
    printf("Table:%s\n", $array['Name']);
    printf("Data Size:%d\n", $array['Data_length']);
    printf("Index Size:%d\n", $array['Index_length']);
    printf("Total Size:%d\n", $total);
    printf("Total Rows:%d\n", $array['Rows']);
    printf("Engine:%s\n", $array['Engine']);
    printf("Avg Size Per Row:%d\n", $array['Avg_row_length']);
    print "--------------------------\n";
}

print "*********************\n";
printf("The Sum of all Tables:%d bytes\n", $sum);
printf("The Size of database in K bytes:%f\n", $sum/1024);
?>
----------------------------------------------------------------
計算結果
The Sum of all Tables:93046 bytes
The Size of database in K nytes:90.865234

因為在這個mysample還有table是Engine:InnoDB,因為InnoDB是Table Space的方式,與MyISAM分別用檔案的方式不同.
我們修改一下上面的程式.只計算Engine:MyISAM的Table與Index的總和.

-------------------------------------------------------------------
#!/usr/bin/php -q
# MySQL database size summary version 2
# Only count MyISAM tables

mysql_connect("localhost","mysample","mysample");

$result = mysql_query("SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM';");

$sum = 0;

while($array = mysql_fetch_array($result)) {
    $total = $array['Data_length'] + $array['Index_length'];
    $sum += $total;
    print "--------------------------\n";
    printf("Table:%s\n", $array['Name']);
    printf("Data Size:%d\n", $array['Data_length']);
    printf("Index Size:%d\n", $array['Index_length']);
    printf("Total Size:%d\n", $total);
    printf("Total Rows:%d\n", $array['Rows']);
    printf("Engine:%s\n", $array['Engine']);
    printf("Avg Size Per Row:%d\n", $array['Avg_row_length']);
    print "--------------------------\n";
}

print "*********************\n";
printf("The Sum of all Tables:%d bytes\n", $sum);
printf("The Size of database in K bytes:%f\n", $sum/1024);
?>
------------------------------------------------------------------
計算結果
The Sum of all Tables:76662 bytes
The Size of database in K bytes:74.865234

與上面計算檔案的方式結果相同.

小結:因為InnoDB的方式不同,我們就針對MyISAM的data file,index file計算.可以使用
SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM' 的語法.

*************************************************************************************
2. 使用function來計算

剛才我們是使用mysql client裡面的 show table status 來計算,但是要使用function的話,要用
cursor,不能使用show table status.但是不用擔心,在 information_schema.tables 裡面有需要的資訊.

將mysample各個MyISAM的Table列出table_name,data_length,index_length

select table_name,data_length,index_length
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';

計算總和:

select sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';

執行結果:
+---------+
| totsize |
+---------+
|   76662 |
+---------+

跟上面的資料相符.

接下來就寫一個 dbszie function.
----------------------------------------------------------
DELIMITER $$

DROP FUNCTION IF EXISTS `mysample`.`dbsize`$$
CREATE FUNCTION `mysample`.`dbsize` (in_dbname varchar(64)) RETURNS BIGINT UNSIGNED
BEGIN
DECLARE rtnSize BIGINT UNSIGNED;
DECLARE c CURSOR FOR select sum(data_length)+sum(index_length) as totsize
    from information_schema.tables
    where table_schema = in_dbname
    and table_type = 'BASE TABLE'
    and engine = 'MyISAM';

open c;
fetch c into rtnSize;
close c;

RETURN rtnSize;

END$$

DELIMITER ;
-----------------------------------------------------------

這個測試用的user: mysample,除了對database:mysample有權限外,對database:test也有權限.
我們測試一下

mysql> select dbsize('mysample');
+--------------------+
| dbsize('mysample') |
+--------------------+
|              76662 |
+--------------------+
1 row in set (0.07 sec)

mysql> select dbsize('test');
+----------------+
| dbsize('test') |
+----------------+
|           2200 |
+----------------+
1 row in set (0.00 sec)

可以看到可以依照輸入的database name計算出使用的size.

訂閱：文章 (Atom)

2010年11月18日 星期四

最近Pidgin 登入MSN時 SSL憑證錯誤的解決方法

2010年10月6日 星期三