カテゴリー : sql
このカテゴリーの登録数：6件 表示 : 1 - 6 / 6
2009/07/23
mprofile.php
2008/12/07
Q4MをPHP(PDO)で
Q4Mが無事インストールできたので、PHPから触ってみる。詳細なことは今度書く。とりあえず触りだけでも
テーブル定義
drop table if exists hoge_queue; create table hoge_queue( id int not null, name varchar(25) not null ) engine=queue;
こんなスクリプトをガンガン回して、監視
$conn = new PDO('mysql:host=localhost; dbname=hogetest', 'user', 'password'); $wait = $conn->prepare('SELECT queue_wait("hoge_queue", 10)'); $abort = $conn->prepare('SELECT queue_abort()'); $e = $conn->prepare('SELECT queue_end()'); $select = $conn->prepare('SELECT * FROM hoge_queue'); while(true){ $start = time(); // for epch tstmp // wait call query $wait->execute(); $end = time(); echo 'wait for: ', $end - $start, ' sec.', PHP_EOL; echo 'is_queue: ', $wait->fetch(PDO::FETCH_COLUMN), PHP_EOL; $select->execute(); while($row = $select->fetch(PDO::FETCH_OBJ)){ var_dump($row); // // delete しなくとも queue_end で消える // $e->execute(); continue 2; } // abort call $abort->execute(); echo 'next...', PHP_EOL, PHP_EOL; }
別の端末からmysqlの動いてるサーバに対して、insert
insert into hoge_queue values(1, 'hello world');
こんな感じになるよ
int(10) array(1) { ["queue_wait("hoge_queue", 10)"]=> string(1) "0" } next... int(10) array(1) { ["queue_wait("hoge_queue", 10)"]=> string(1) "0" } next... int(10) array(1) { ["queue_wait("hoge_queue", 10)"]=> string(1) "0" } next... int(2) array(1) { ["queue_wait("hoge_queue", 10)"]=> string(1) "1" } object(stdClass)#6 (2) { ["id"]=> string(2) "1" ["name"]=> string(11) "hello world" } next... int(10) array(1) { ["queue_wait("hoge_queue", 10)"]=> string(1) "0" } next... int(10) array(1) { ["queue_wait("hoge_queue", 10)"]=> string(1) "0" } next...
Q4Mの動きで、特徴的なの
- queue_wait で取れたレコードは、queue_end で消える。
- queue_wait で止まる時間を指定しても、queueが入ってきたときは、即レコードが取れる
- queue_wait を連発すると一行前のレコードが消える(?要確認だけど)
- queue_wait で取れたレコードは他のクライアントから queue_wait しても取れない
- queue_wait で取得したレコードは、他のクライアントからだとテーブル上に見えなくなってる
- queue_wait で、レコードが見つからない場合は、0 で、見つかったら 1 が帰ってくる
- queue_wait で取ったレコードは「ちゃんと処理をする」queue_abort すると消えるよ
Q4Mの動きですこしハマるやつ。その1
全レコード分 wait
mysql> select * from hoge_queue; +----+------+ | id | name | +----+------+ | 4 | foo4 | | 5 | foo5 | | 6 | foo6 | | 7 | foo7 | | 8 | foo8 | | 9 | foo9 | +----+------+ 6 rows in set (0.00 sec) mysql> select queue_wait('hoge_queue', 1); +-----------------------------+ | queue_wait('hoge_queue', 1) | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.00 sec) mysql> select queue_wait('hoge_queue', 1); +-----------------------------+ | queue_wait('hoge_queue', 1) | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.06 sec) mysql> select queue_wait('hoge_queue', 1); +-----------------------------+ | queue_wait('hoge_queue', 1) | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.06 sec) mysql> select queue_wait('hoge_queue', 1); +-----------------------------+ | queue_wait('hoge_queue', 1) | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.05 sec) mysql> select queue_wait('hoge_queue', 1); +-----------------------------+ | queue_wait('hoge_queue', 1) | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.05 sec) mysql> select queue_wait('hoge_queue', 1); +-----------------------------+ | queue_wait('hoge_queue', 1) | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.05 sec) mysql> select queue_wait('hoge_queue', 1); +-----------------------------+ | queue_wait('hoge_queue', 1) | +-----------------------------+ | 0 | +-----------------------------+ 1 row in set (1.05 sec)
最後は、waitで 1sec 止まって、結果は0に
selectすると
mysql> select * from hoge_queue; Empty set (0.00 sec)
abort すると...?
mysql> select queue_abort();
+---------------+
| queue_abort() |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
その後、select すると...
mysql> select * from hoge_queue; Empty set (0.00 sec)
全部消えてる...
wait して取り出せたレコードは、レスキューできないのか。残念。
Q4Mの動きですこしハマるやつ。その他
Q4Mは面白いので、別の機会にまとめて書く
Q4Mのビルド(CentOS 5.2 と OSX)
Q4Mのアイディアがとても面白そうなので、使ってみることにしました。
が、ビルドに色々つまづいたので、メモ。かれこれ2週間くらい前のログなので、微妙にバージョンが違ってたら申し訳ないです。
CentOS 5.2 に入れる
CentOS を使っているので、これに入れるログ
既にインストール済みのMySQLをアンインストール
とりあえず、キレイにしておきました。
hata@local ~/local/rpms/mysql5> sudo yum remove mysql Resolving Dependencies --> Running transaction check ---> Package mysql.x86_64 0:5.0.45-7.el5 set to be erased --> Processing Dependency: libmysqlclient.so.15()(64bit) for package: mysql-devel --> Processing Dependency: libmysqlclient.so.15()(64bit) for package: libdbi-dbd-mysql --> Processing Dependency: libmysqlclient.so.15()(64bit) for package: mysql-server --> Processing Dependency: libmysqlclient.so.15()(64bit) for package: perl-DBD-MySQL --> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: libdbi-dbd-mysql --> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: mysql-server --> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: perl-DBD-MySQL --> Processing Dependency: libmysqlclient_r.so.15()(64bit) for package: mysql-devel --> Processing Dependency: libmysqlclient_r.so.15()(64bit) for package: mysql-server --> Processing Dependency: libmysqlclient_r.so.15(libmysqlclient_15)(64bit) for package: mysql-server --> Processing Dependency: mysql = 5.0.45-7.el5 for package: mysql-devel --> Processing Dependency: mysql = 5.0.45-7.el5 for package: mysql-server --> Processing Dependency: mysql for package: libdbi-dbd-mysql --> Running transaction check ---> Package mysql-devel.x86_64 0:5.0.45-7.el5 set to be erased ---> Package mysql-server.x86_64 0:5.0.45-7.el5 set to be erased ---> Package libdbi-dbd-mysql.x86_64 0:0.8.1a-1.2.2 set to be erased ---> Package perl-DBD-MySQL.x86_64 0:3.0007-1.fc6 set to be erased --> Finished Dependency Resolution Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Removing: mysql x86_64 5.0.45-7.el5 installed 7.5 M Removing for dependencies: libdbi-dbd-mysql x86_64 0.8.1a-1.2.2 installed 54 k mysql-devel x86_64 5.0.45-7.el5 installed 6.3 M mysql-server x86_64 5.0.45-7.el5 installed 22 M perl-DBD-MySQL x86_64 3.0007-1.fc6 installed 328 k Transaction Summary ============================================================================= Install 0 Package(s) Update 0 Package(s) Remove 5 Package(s) Is this ok [y/N]: y Downloading Packages: Running rpm_check_debug Running Transaction Test Finished Transaction Test Transaction Test Succeeded Running Transaction Erasing : mysql-devel ######################### [1/5] Erasing : mysql ######################### [2/5] Erasing : mysql-server ######################### [3/5] warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave Erasing : libdbi-dbd-mysql ######################### [4/5] Erasing : perl-DBD-MySQL ######################### [5/5] Removed: mysql.x86_64 0:5.0.45-7.el5 Dependency Removed: libdbi-dbd-mysql.x86_64 0:0.8.1a-1.2.2 mysql-devel.x86_64 0:5.0.45-7.el5 mysql-server.x86_64 0:5.0.45-7.el5 perl-DBD-MySQL.x86_64 0:3.0007-1.fc6 Complete!
MySQL ABのバイナリをインストールする
MySQL ABはここから落としてきて、とりあえず rpm を使いました。
hata@local: ~/local/rpms/mysql5> sudo rpm -ivh MySQL-*.rpm 準備中... ########################################### [100%] 1:MySQL-shared-compat ########################################### [ 20%] 2:MySQL-client-community ########################################### [ 40%] 3:MySQL-devel-community ########################################### [ 60%] 4:MySQL-server-community ########################################### [ 80%] ERROR: 1136 Column count doesn't match value count at row 1 081121 10:59:56 [ERROR] Aborting 081121 10:59:56 [Note] /usr/sbin/mysqld: Shutdown complete Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. You can try to start the mysqld daemon with: shell> /usr/sbin/mysqld --skip-grant & and use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell> /usr/bin/mysql -u root mysql mysql> show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com/. Please consult the MySQL manual section 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source are the MySQL email archives available at http://lists.mysql.com/. Please check all of the above before mailing us! And remember, if you do mail us, you MUST use the /usr/bin/mysqlbug script! Starting MySQL.[ OK ] Giving mysqld 2 seconds to start 5:MySQL-shared-community ########################################### [100%]
Q4M-0.8.3を落としてきて make
q4m.31tools.comから無印の q4m-{version}.tar.gz だと、なぜかエラーが沢山（下記）でたので、mysql-{version}-*q4m-{version}.tar.gz を使った。
gcc/x86_64-redhat-linux/4.1.2/../../../../lib64 -L/lib/../lib64 -L/usr/lib/../lib64 -lstdc++ -lm -lgcc_s -lc -lgcc_s /usr/lib/gcc/x86_64-redhat-linux/4.1.2/crtendS.o /usr/lib/gcc/x86_64-redhat-linux/4.1.2/../../../../lib64/crtn.o -Wl,--hash-style=both -o .libs/libqueue_engine.so.0.0.0
/usr/lib/gcc/x86_64-redhat-linux/4.1.2/../../../../lib64/crti.o: In function `_init':
(.init+0x0): multiple definition of `_init'
/usr/lib/gcc/x86_64-redhat-linux/4.1.2/../../../../lib64/crti.o:(.init+0x0): first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.1.2/../../../../lib64/crti.o: In function `_fini':
(.fini+0x0): multiple definition of `_fini'
/usr/lib/gcc/x86_64-redhat-linux/4.1.2/../../../../lib64/crti.o:(.fini+0x0): first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.1.2/crtbeginS.o:(.data.rel.ro+0x0): multiple definition of `__dso_handle'
/usr/lib/gcc/x86_64-redhat-linux/4.1.2/crtbeginS.o:(.data.rel.ro+0x0): first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.1.2/crtendS.o:(.dtors+0x0): multiple definition of `__DTOR_END__'
/usr/lib/gcc/x86_64-redhat-linux/4.1.2/crtendS.o:(.dtors+0x0): first defined here
collect2: ld returned 1 exit status
make[2]: *** [libqueue_engine.la] Error 1
make[2]: Leaving directory `/home/vmdev/local/src/q4m-0.8.3/src'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/home/vmdev/local/src/q4m-0.8.3/src'
make: *** [all-recursive] Error 1
かなりハマった...。
んで、他の人も書いてるけど、configure オプションを注意しつつ、make
僕は、CXXFLAGS に /usr/include/mysql として、AB の rpm が入れたヘッダを指定するようにしました。
--with-mysql=/home/hata/local/src/mysql-5.1.29-rc CXXFLAGS="-I/usr/include/mysql
hata@local: ~/local/src> wget http://q4m.31tools.com/dist/mysql-5.1.28-rc-linux-x86_64-glibc23-with-fast-mutexes-q4m-0.8.3.tar.gz hata@local: ~/local/src> tar xzvf mysql-5.1.28-rc-linux-x86_64-glibc23-with-fast-mutexes-q4m-0.8.3.tar.gz q4m-0.8.3-linux-x86_64/ q4m-0.8.3-linux-x86_64/ChangeLog q4m-0.8.3-linux-x86_64/config/ q4m-0.8.3-linux-x86_64/config/config.sub q4m-0.8.3-linux-x86_64/config/depcomp q4m-0.8.3-linux-x86_64/config/missing q4m-0.8.3-linux-x86_64/config/compile q4m-0.8.3-linux-x86_64/config/install-sh q4m-0.8.3-linux-x86_64/config/config.guess q4m-0.8.3-linux-x86_64/config/ac_mysql.m4 q4m-0.8.3-linux-x86_64/config/ltmain.sh q4m-0.8.3-linux-x86_64/examples/ q4m-0.8.3-linux-x86_64/examples/crawler/ q4m-0.8.3-linux-x86_64/examples/crawler/crawler.pl q4m-0.8.3-linux-x86_64/examples/crawler/initdb.sql q4m-0.8.3-linux-x86_64/examples/crawler/README q4m-0.8.3-linux-x86_64/Makefile.in q4m-0.8.3-linux-x86_64/Makefile.am q4m-0.8.3-linux-x86_64/t/ q4m-0.8.3-linux-x86_64/t/02-queue.t q4m-0.8.3-linux-x86_64/t/05-multireader-read.c q4m-0.8.3-linux-x86_64/t/05-multiwait-core.c q4m-0.8.3-linux-x86_64/t/Makefile.in q4m-0.8.3-linux-x86_64/t/04-blob-cond.t q4m-0.8.3-linux-x86_64/t/Makefile.am q4m-0.8.3-linux-x86_64/t/08-forward.t q4m-0.8.3-linux-x86_64/t/02-queue-cond.t q4m-0.8.3-linux-x86_64/t/05-multireader.t q4m-0.8.3-linux-x86_64/t/09-pqueue-single-table.t q4m-0.8.3-linux-x86_64/t/07-trans.t q4m-0.8.3-linux-x86_64/t/02-queue-owned-delete.t q4m-0.8.3-linux-x86_64/t/05-multiwait.t q4m-0.8.3-linux-x86_64/t/06-multi.t q4m-0.8.3-linux-x86_64/t/03-queue-error-wait.t q4m-0.8.3-linux-x86_64/t/04-blob.t q4m-0.8.3-linux-x86_64/t/01-base-rnd_pos.t q4m-0.8.3-linux-x86_64/t/09-pqueue-single-table-wake-listener.t q4m-0.8.3-linux-x86_64/t/05-multirw-core.c q4m-0.8.3-linux-x86_64/t/05-multirw.t q4m-0.8.3-linux-x86_64/t/01-base.t q4m-0.8.3-linux-x86_64/t/03-queue-error.t q4m-0.8.3-linux-x86_64/run_tests.pl q4m-0.8.3-linux-x86_64/libqueue_engine.so q4m-0.8.3-linux-x86_64/configure.in q4m-0.8.3-linux-x86_64/aclocal.m4 q4m-0.8.3-linux-x86_64/configure q4m-0.8.3-linux-x86_64/INSTALL q4m-0.8.3-linux-x86_64/COPYING q4m-0.8.3-linux-x86_64/src/ q4m-0.8.3-linux-x86_64/src/dllist.h q4m-0.8.3-linux-x86_64/src/Makefile.in q4m-0.8.3-linux-x86_64/src/Makefile.am q4m-0.8.3-linux-x86_64/src/queue_cond.h q4m-0.8.3-linux-x86_64/src/queue_config.h.in q4m-0.8.3-linux-x86_64/src/ha_queue.h q4m-0.8.3-linux-x86_64/src/queue_cond.cc q4m-0.8.3-linux-x86_64/src/adler32.c q4m-0.8.3-linux-x86_64/src/ha_queue.cc q4m-0.8.3-linux-x86_64/TODO q4m-0.8.3-linux-x86_64/AUTHORS q4m-0.8.3-linux-x86_64/doc/ q4m-0.8.3-linux-x86_64/doc/style.css q4m-0.8.3-linux-x86_64/doc/top.jpg q4m-0.8.3-linux-x86_64/doc/install.html q4m-0.8.3-linux-x86_64/doc/index.html q4m-0.8.3-linux-x86_64/doc/q4m-modes.gif q4m-0.8.3-linux-x86_64/doc/tutorial.html q4m-0.8.3-linux-x86_64/README q4m-0.8.3-linux-x86_64/support-files/ q4m-0.8.3-linux-x86_64/support-files/install.sql q4m-0.8.3-linux-x86_64/support-files/install-exec-hook.txt q4m-0.8.3-linux-x86_64/support-files/q4m-forward q4m-0.8.3-linux-x86_64/NEWS
hata@local: ~/local/src> cd q4m-0.8.3-linux-x86_64/
hata@local: ~/local/src/q4m-0.8.3-linux-x86_64> ./configure --prefix=/usr --with-mysql=/home/hata/local/src/mysql-5.1.29-rc CXXFLAGS="-I/usr/include/mysql"
:
:
:
configure: creating ./config.status
config.status: creating Makefile
config.status: creating src/Makefile
config.status: creating t/Makefile
config.status: creating src/queue_config.h
config.status: executing depfiles commands
hata@local: ~/local/src/q4m-0.8.3-linux-x86_64> make
hata@local: ~/local/src/q4m-0.8.3-linux-x86_64> make install
プラグインのインストール
sudo make install後
/usr/lib64/mysql の下に、libqueue_engine.* がおかれているので、プラグインディレクトリにコピーする
もしくは、configure オプションの libdir を指定してみる
./configure --with-mysql=/home/vmdev/local/src/mysql-5.1.29-rc CXXFLAGS="-I/usr/include/mysql" --libdir=/usr/lib64/mysql/plugin
これでやると、なぜか mysql/plugin にコピーされる(libdirの動きとしていいのか...?)
OSX(10.5.5)に入れる
port から MySQL5.1.29を入れる
MySQL AB のものだとハマったので、今回は port を使う
hata@mac.local > sudo port install mysql5-devel
Q4M のインストール
linux と同じように無印は使わず、mysql-5.1.28-rc-osx10.4-i686-without-fast-mutexes-q4m-0.8.3.tar.gzを使う
hata@mac.local ~/local/src/q4m> ./configure --prefix=/opt/local --with-mysql=/Users/yusukehata/tmp/mysql-5.1.29-rc CFLAGS="-I/opt/local/include/mysql5/mysql -I/opt/local/include" CPPFLAGS="-I/opt/local/include/mysql5/mysql -I/opt/local/include"
また、以下のようにシンボリックリンク貼っておく必要がある
> sudo ln -s /opt/local/include/mysql5/mysql/ /opt/local/include/mysql > sudo ln -s /opt/local/lib/mysql5/mysql/ /opt/local/lib/mysql
MySQL AB のバイナリで、ビルド時に発生するエラーとか
もしかしたら、portのでも出たかも
source='05-multireader-read.c' object='05_multireader_read-05-multireader-read.o' libtool=no \ depfile='.deps/05_multireader_read-05-multireader-read.Po' tmpdepfile='.deps/05_multireader_read-05-multireader-read.TPo' \ depmode=gcc3 /bin/sh ../config/depcomp \ gcc -DHAVE_CONFIG_H -I. -I. -I../src -I/Users/yusukehata/tmp/mysql-5.1.29-rc/sql -I/Users/yusukehata/tmp/mysql-5.1.29-rc/include -I/Users/yusukehata/tmp/mysql-5.1.29-rc/regex -I/Users/yusukehata/tmp/mysql-5.1.29-rc -I/opt/local/include/mysql5/mysql -I/opt/local/include -I/opt/local/include/mysql5/mysql -I/opt/local/include -Wall -c -o 05_multireader_read-05-multireader-read.o `test -f '05-multireader-read.c' || echo './'`05-multireader-read.c /bin/sh ../libtool --preserve-dup-deps --mode=link gcc -I/opt/local/include/mysql5/mysql -I/opt/local/include -Wall -L/usr/local/mysql/lib -o 05-multireader-read 05_multireader_read-05-multireader-read.o -lmysqlclient mkdir .libs gcc -I/opt/local/include/mysql5/mysql -I/opt/local/include -Wall -o 05-multireader-read 05_multireader_read-05-multireader-read.o -L/usr/local/mysql/lib /usr/local/mysql/lib/libmysqlclient.dylib -lm ld: warning in /usr/local/mysql/lib/libmysqlclient.dylib, file is not of required architecture Undefined symbols: "_mysql_real_connect", referenced from: _main in 05_multireader_read-05-multireader-read.o "_mysql_store_result", referenced from: _do_select in 05_multireader_read-05-multireader-read.o "_mysql_init", referenced from: _main in 05_multireader_read-05-multireader-read.o "_mysql_free_result", referenced from: _main in 05_multireader_read-05-multireader-read.o _main in 05_multireader_read-05-multireader-read.o "_mysql_query", referenced from: _do_select in 05_multireader_read-05-multireader-read.o "_mysql_close", referenced from: _main in 05_multireader_read-05-multireader-read.o "_mysql_fetch_row", referenced from: _main in 05_multireader_read-05-multireader-read.o _main in 05_multireader_read-05-multireader-read.o "_mysql_num_rows", referenced from: _main in 05_multireader_read-05-multireader-read.o _main in 05_multireader_read-05-multireader-read.o ld: symbol(s) not found collect2: ld returned 1 exit status make[1]: *** [05-multireader-read] Error 1 make: *** [all-recursive] Error 1
この状態だと、PHPとかビルドするときにエラーになる...
ld: warning in /usr/local/mysql/lib/libmysqlclient.dylib, file is not of required architecture ld: warning in /usr/local/mysql/lib/libmygcc.a, file is not of required architecture Undefined symbols: "_mysql_get_server_info", referenced from: _pdo_mysql_get_attribute in mysql_driver.o "_mysql_stmt_close", referenced from: _pdo_mysql_stmt_dtor in mysql_statement.o _pdo_mysql_stmt_cursor_closer in mysql_statement.o "_mysql_stmt_fetch", referenced from: _pdo_mysql_stmt_fetch in mysql_statement.o "_mysql_next_result", referenced from: _pdo_mysql_stmt_dtor in mysql_statement.o _pdo_mysql_stmt_next_rowset in mysql_statement.o _pdo_mysql_stmt_cursor_closer in mysql_statement.o "_mysql_stmt_errno", referenced from: __pdo_mysql_error in mysql_driver.o "_mysql_stmt_store_result", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o "_mysql_real_escape_string", referenced from: _mysql_handle_quoter in mysql_driver.o "_mysql_stmt_init", referenced from: _mysql_handle_preparer in mysql_driver.o "_mysql_affected_rows", referenced from: _mysql_handle_doer in mysql_driver.o _pdo_mysql_stmt_execute in mysql_statement.o _pdo_mysql_stmt_next_rowset in mysql_statement.o "_mysql_stmt_prepare", referenced from: _mysql_handle_preparer in mysql_driver.o "_mysql_stmt_sqlstate", referenced from: __pdo_mysql_error in mysql_driver.o "_mysql_fetch_fields", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o _pdo_mysql_stmt_execute in mysql_statement.o _pdo_mysql_stmt_next_rowset in mysql_statement.o "_mysql_get_host_info", referenced from: _pdo_mysql_get_attribute in mysql_driver.o "_mysql_get_server_version", referenced from: _mysql_handle_preparer in mysql_driver.o "_mysql_free_result", referenced from: _pdo_mysql_stmt_dtor in mysql_statement.o _pdo_mysql_stmt_dtor in mysql_statement.o _pdo_mysql_stmt_execute in mysql_statement.o _pdo_mysql_stmt_next_rowset in mysql_statement.o _pdo_mysql_stmt_cursor_closer in mysql_statement.o _pdo_mysql_stmt_cursor_closer in mysql_statement.o "_mysql_options", referenced from: _pdo_mysql_handle_factory in mysql_driver.o _pdo_mysql_handle_factory in mysql_driver.o _pdo_mysql_handle_factory in mysql_driver.o _pdo_mysql_handle_factory in mysql_driver.o _pdo_mysql_handle_factory in mysql_driver.o _pdo_mysql_handle_factory in mysql_driver.o "_mysql_close", referenced from: _mysql_handle_closer in mysql_driver.o "_mysql_stmt_affected_rows", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o "_mysql_fetch_row", referenced from: _pdo_mysql_stmt_fetch in mysql_statement.o "_mysql_num_fields", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o _pdo_mysql_stmt_execute in mysql_statement.o _pdo_mysql_stmt_next_rowset in mysql_statement.o "_mysql_store_result", referenced from: _pdo_mysql_stmt_dtor in mysql_statement.o _pdo_mysql_stmt_execute in mysql_statement.o _pdo_mysql_stmt_next_rowset in mysql_statement.o _pdo_mysql_stmt_cursor_closer in mysql_statement.o "_mysql_get_client_info", referenced from: _zm_info_pdo_mysql in pdo_mysql.o _pdo_mysql_get_attribute in mysql_driver.o "_mysql_real_query", referenced from: _mysql_handle_doer in mysql_driver.o _pdo_mysql_stmt_execute in mysql_statement.o "_mysql_errno", referenced from: __pdo_mysql_error in mysql_driver.o _mysql_handle_preparer in mysql_driver.o _pdo_mysql_stmt_fetch in mysql_statement.o "_mysql_error", referenced from: __pdo_mysql_error in mysql_driver.o __pdo_mysql_error in mysql_driver.o "_mysql_stmt_bind_result", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o "_mysql_sqlstate", referenced from: __pdo_mysql_error in mysql_driver.o "_mysql_stmt_result_metadata", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o "_mysql_insert_id", referenced from: _pdo_mysql_last_insert_id in mysql_driver.o "_mysql_use_result", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o _pdo_mysql_stmt_next_rowset in mysql_statement.o "_mysql_stmt_bind_param", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o "_mysql_stmt_attr_set", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o "_mysql_num_rows", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o "_mysql_init", referenced from: _pdo_mysql_handle_factory in mysql_driver.o "_mysql_real_connect", referenced from: _pdo_mysql_handle_factory in mysql_driver.o "_mysql_more_results", referenced from: _pdo_mysql_stmt_dtor in mysql_statement.o _pdo_mysql_stmt_dtor in mysql_statement.o _pdo_mysql_stmt_cursor_closer in mysql_statement.o _pdo_mysql_stmt_cursor_closer in mysql_statement.o "_mysql_stmt_execute", referenced from: _pdo_mysql_stmt_execute in mysql_statement.o "_mysql_ping", referenced from: _pdo_mysql_check_liveness in mysql_driver.o "_mysql_stmt_param_count", referenced from: _mysql_handle_preparer in mysql_driver.o "_mysql_stmt_free_result", referenced from: _pdo_mysql_stmt_next_rowset in mysql_statement.o _pdo_mysql_stmt_cursor_closer in mysql_statement.o "_mysql_stat", referenced from: _pdo_mysql_get_attribute in mysql_driver.o "_mysql_fetch_lengths", referenced from: _pdo_mysql_stmt_fetch in mysql_statement.o ld: symbol(s) not found collect2: ld returned 1 exit status make: *** [libs/libphp5.bundle] Error 1
なぞ
ちなみに、MySQL AB は
Mach-O 64-bit で x86_64 となってるのだが
> file /usr/local/mysql/lib/libmysqlclient.16.0.0.dylib /usr/local/mysql/lib/libmysqlclient.16.0.0.dylib: Mach-O 64-bit dynamically linked shared library x86_64 > file /usr/local/mysql/lib/libmysqlclient.dylib /usr/local/mysql/lib/libmysqlclient.dylib: Mach-O 64-bit dynamically linked shared library x86_64
/usr/local/mysql/bin/mysqlbug
>> CFLAGS='-g -Os -arch x86_64 -fno-common' CXX='gcc -static-libgcc' CXXFLAGS='-g -Os -arch x86_64 -felide-constructors -fno-common'
/usr/local/mysql/bin/mysql_config
Usage: /usr/local/mysql/bin/mysql_config [OPTIONS]
Options:
--cflags [-I/usr/local/mysql/include -g -Os -arch x86_64 -fno-common -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT -DDONT_DECLARE_CXA_PURE_VIRTUAL]
--include [-I/usr/local/mysql/include]
--libs [-L/usr/local/mysql/lib -lmysqlclient -lz -lm -lmygcc]
--libs_r [-L/usr/local/mysql/lib -lmysqlclient_r -lz -lm -lmygcc]
--plugindir [/usr/local/mysql/lib/plugin]
--socket [/tmp/mysql.sock]
--port [0]
--version [5.1.29-rc]
--libmysqld-libs [-L/usr/local/mysql/lib -lmysqld -lz -lm -lmygcc]
まとめ
無事、試行錯誤しながら何とかインストール出来た。もしかしたら wassr でその時の苦悩の様子がライブ中継してるかもしれないけど、何度、試行錯誤したことか...。
環境が違ったときにインストール方法とか微妙に違うのが面倒だねぇ
ま、インストールにハマると長引くので危険。誰かがやってた環境と同じのに合わせるのが一番早いのかもね
2006/03/24
pdo_pgsqlのprepare
またもやOSC2006のカンファレンスネタなんですが、質問にあった「PDO::prepare()ってどうなのよ?」って言われたので、かるーく眺めてみる。
まずは、pgsql.c で PHP_FUNCTION(pg_prepare) を探してみた。
#if HAVE_PQPREPARE
/* {{{ proto resource pg_prepare([resource connection,] string stmtname, string query)
Prepare a query for future execution */
PHP_FUNCTION(pg_prepare)
{
zval **query, **stmtname, **pgsql_link = NULL;
int id = -1;
int leftover = 0;
PGconn *pgsql;
PGresult *pgsql_result;
ExecStatusType status;
pgsql_result_handle *pg_result;
switch(ZEND_NUM_ARGS()) {
case 2:
if (zend_get_parameters_ex(2, &stmtname, &query)==FAILURE) {
RETURN_FALSE;
}
id = PGG(default_link);
CHECK_DEFAULT_LINK(id);
break;
case 3:
if (zend_get_parameters_ex(3, &pgsql_link, &stmtname, &query)==FAILURE) {
RETURN_FALSE;
}
break;
default:
WRONG_PARAM_COUNT;
break;
}
if (pgsql_link == NULL && id == -1) {
RETURN_FALSE;
}
ZEND_FETCH_RESOURCE2(pgsql, PGconn *, pgsql_link, id, "PostgreSQL link", le_link, le_plink);
convert_to_string_ex(stmtname);
convert_to_string_ex(query);
if (PQ_SETNONBLOCKING(pgsql, 0)) {
php_error_docref(NULL TSRMLS_CC, E_NOTICE,"Cannot set connection to blocking mode");
RETURN_FALSE;
}
while ((pgsql_result = PQgetResult(pgsql))) {
PQclear(pgsql_result);
leftover = 1;
}
if (leftover) {
php_error_docref(NULL TSRMLS_CC, E_NOTICE, "Found results on this connection. Use pg_get_result() to get these results first");
}
pgsql_result = PQprepare(pgsql, Z_STRVAL_PP(stmtname), Z_STRVAL_PP(query), 0, NULL);
if ((PGG(auto_reset_persistent) & 2) && PQstatus(pgsql) != CONNECTION_OK) {
PQclear(pgsql_result);
PQreset(pgsql);
pgsql_result = PQprepare(pgsql, Z_STRVAL_PP(stmtname), Z_STRVAL_PP(query), 0, NULL);
}
if (pgsql_result) {
status = PQresultStatus(pgsql_result);
} else {
status = (ExecStatusType) PQstatus(pgsql);
}
switch (status) {
case PGRES_EMPTY_QUERY:
case PGRES_BAD_RESPONSE:
case PGRES_NONFATAL_ERROR:
case PGRES_FATAL_ERROR:
PHP_PQ_ERROR("Query failed: %s", pgsql);
PQclear(pgsql_result);
RETURN_FALSE;
break;
case PGRES_COMMAND_OK: /* successful command that did not return rows */
default:
if (pgsql_result) {
pg_result = (pgsql_result_handle *) emalloc(sizeof(pgsql_result_handle));
pg_result->conn = pgsql;
pg_result->result = pgsql_result;
pg_result->row = 0;
ZEND_REGISTER_RESOURCE(return_value, pg_result, le_result);
} else {
PQclear(pgsql_result);
RETURN_FALSE;
}
break;
}
}
/* }}} */
#endif
ふむ。
PQprepareとか怪しいのがあるのを心にとめておきつつ、pdo_pgsql/pgsql_statement.cを眺める
#if HAVE_PQPREPARE
if (S->stmt_name) {
/* using a prepared statement */
if (!S->is_prepared) {
stmt_retry:
/* we deferred the prepare until now, because we didn't
* know anything about the parameter types; now we do */
S->result = PQprepare(H->server, S->stmt_name, S->query,
stmt->bound_params ? zend_hash_num_elements(stmt->bound_params) : 0,
S->param_types);
status = PQresultStatus(S->result);
switch (status) {
case PGRES_COMMAND_OK:
case PGRES_TUPLES_OK:
/* it worked */
S->is_prepared = 1;
PQclear(S->result);
break;
default: {
char *sqlstate = pdo_pgsql_sqlstate(S->result);
/* 42P05 means that the prepared statement already existed. this can happen if you use
* a connection pooling software line pgpool which doesn't close the db-connection once
* php disconnects. if php dies (no chanche to run RSHUTDOWN) during execution it has no
* chance to DEALLOCATE the prepared statements it has created. so, if we hit a 42P05 we
* deallocate it and retry ONCE (thies 2005.12.15)
*/
if (!strcmp(sqlstate, "42P05")) {
char buf[100]; /* stmt_name == "pdo_pgsql_cursor_%08x" */
PGresult *res;
snprintf(buf, sizeof(buf), "DEALLOCATE %s", S->stmt_name);
res = PQexec(H->server, buf);
if (res) {
PQclear(res);
}
goto stmt_retry;
} else {
pdo_pgsql_error_stmt(stmt, status, sqlstate);
return 0;
}
}
}
}
S->result = PQexecPrepared(H->server, S->stmt_name,
stmt->bound_params ?
zend_hash_num_elements(stmt->bound_params) :
0,
(const char**)S->param_values,
S->param_lengths,
S->param_formats,
0);
} else
#endif
ここにもPQprepareとか怪しいのが出てきたので、grepしてみると config.m4にこう書かれている
AC_CHECK_LIB(pq, PQprepare,AC_DEFINE(HAVE_PQPREPARE,1,[PostgreSQL 7.4 or later]))
AC_CHECK_LIB(pq, PQprepare,AC_DEFINE(HAVE_PQPREPARE,1,[prepared statements]))
ふむ。
とりあえず、PDOのpdo_pgsql::prepareに関してはpg_prepareと同じモノ使ってるっぽい。ということは分かった(テキトー
mysqlとsqliteなんかも後で調べてみます。後は任せました。
2005/12/04
プライマリーキーを調べる(PostgreSQL)
石田さんからプライマリキーを調べるSQLを教えていただいた。
ref - memo-space
s2con=> select a.attname
s2con-> from pg_attribute a, pg_constraint c, pg_class r
s2con-> where c.conrelid = r.oid
s2con-> and a.attrelid = r.oid
s2con-> and a.attnum = any (c.conkey)
s2con-> and r.relname = 'cd'
s2con-> and c.contype = 'p';
attname
---------
id
(1 row)
シンプルだ...
ちなみに PEAR::DB はこの辺SQL みたいですね。
うむ。石田さんのとそっくりな。
おいしく頂きました。
参考にさせていただきます。 m(_ _)m
2005/11/30
ポスグレが吐いてるクエリを知る
psql -E -U postgresのように -E で入る。
ref - [PHP-users 559] Re: PostgreSQLのテーブル一覧の取得
postgres@linux:~> psql -E test
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | cd | table | postgres
(1 row)
MySQLで。
nowel@linux:~/workspace/S2Dao.PHP5> mysql -u root -p -E
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.10a-Max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
*************************** 1. row ***************************
Database: hoge
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: pblog
*************************** 4. row ***************************
Database: s2con
*************************** 5. row ***************************
Database: test
5 rows in set (0.00 sec)
mysql> use s2con
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
*************************** 1. row ***************************
Tables_in_s2con: CD
*************************** 2. row ***************************
Tables_in_s2con: DEPT
*************************** 3. row ***************************
Tables_in_s2con: EMP
3 rows in set (0.02 sec)
mprofileをPHPに移植してみた。
ref - /platform/mysql/mprofile.php - CodeRepos::Share - Trac
ちょっとした filter を書きたいけど、ウチには perlmonger はいないのよねぇ。って人向け。
たぶん、一緒のことはできるはず。
via - Kazuho@Cybozu Labs: MySQL のボトルネックを統計的に監視・解析する方法
使い方はほとんど一緒で、、
でサンプリングしておいて
こんな感じ。
ちなみに、少し変更ポイントがあって、 mpdump において、SHOW FULL PROCESSLIST を拾っているコードのうち、
な部分は、なんか拾えるものが少ない(? というか Query && SHOW ... PROCESSLIST を外すだけ?)みたいなので、
で、やってます。
あと、まぁ細かなとこが違う(だろう)と思うけど、出力結果(dump)は基本一緒のはずです。
何かあったらコード直しちゃってください。
それでは、have fun!