[pgpool-general-jp: 131] Kesalahan Registrasi Gambar saat Menggunakan Mode Paralel pgpool-II

Kesalahan Registrasi Gambar saat Menggunakan Mode Paralel pgpool-II

Ditemukan masalah saat registrasi gambar menggunakan pgpool-II dalam mode paralel. Masalah terjadi pada aplikasi web PHP yang memproses unggahan gambar. Gambar yang diunggah akan dikompresi jika ukuran, lebar, atau tingginya melebihi batas tertentu (160px x 160px). Gambar disimpan ke basis data menggunakan fungsi pg_escape_bytea, bukan sebagai file. Saat mencoba registrasi beberapa gambar tertentu, muncul galat:

Warning: pg_query() [function-query]: Query failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. in /usr/local/apache2/htdocs/mypage/a_profile_image_comp.php on line 193

Galat terjadi pada baris pg_query($resource_id,"commit;"); saat transaksi berakhir. Tidak semua gambar gagal; hanya beberapa gambar tertentu, misalnya gambar berukuran 157x160 piksel (7 kB) dan 160x120 piksel (3,5 kB). Saat proses update (tulis ulang), galat tidak muncul. Masalah tidak terjadi jika pgpool-II tidak digunakan.

Lingkungan

• OS: Cent OS 4.4
• Apache: httpd-2.0.59
• PHP: 4.4.5
• PostgreSQL: 8.1.4

Opsi Instalasi PHP

'./configure' \'--with-apxs2=/usr/local/apache2/bin/apxs' \'--with-gd' \'--with-jpeg-dir=/usr' \'--with-zlib-dir=/usr' \'--with-png-dir=/usr' \'--with-openssl' \'--enable-calendar' \'--enable-mbstring' \'--enable-mbregex' \'--enable-zend-multibyte' \'--enable-ftp' \'--with-mcrypt' \

Konfigurasi Basis Data

Tiga instance PostgreSQL berjalan pada satu mesin dengan port berbeda:

PGPORT=5432 pg_ctl -D /usr/local/pgsql/data startPGPORT=5433 pg_ctl -D /usr/local/pgsql/data.1 startPGPORT=5434 pg_ctl -D /usr/local/pgsql/data.2 start

Konfigurasi pgpool-II (pgpool)

listen_addresses = 'localhost'port = 9999pcp_port = 9898socket_dir = '/tmp'pcp_socket_dir = '/tmp'backend_socket_dir = '/tmp'pcp_timeout = 10num_init_children = 32max_pool = 4child_life_time = 300connection_life_time = 0child_max_connections = 0logdir = '/tmp'replication_mode = falsereplication_strict = truereplication_timeout = 5000load_balance_mode = falsereplication_stop_on_mismatch = falsereset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'print_timestamp = truemaster_slave_mode = falseconnection_cache = truehealth_check_timeout = 20health_check_period = 0health_check_user = 'nobody'insert_lock = falseignore_leading_white_space = falselog_statement = falseparallel_mode = trueenable_query_cache = falsepgpool2_hostname = 'localhost'system_db_hostname = 'localhost'system_db_port = 5432system_db_dbname = 'pgpool'system_db_schema = 'pgpool_catalog'system_db_user = 'postgres'system_db_password = ''backend_hostname0 = ''backend_port0 = 5432backend_weight0 = 1backend_hostname1 = ''backend_port1 = 5433backend_weight1 = 1backend_hostname2 = ''backend_port2 = 5434backend_weight2 = 1

Definisi Tabel Distribusi

insert into pgpool_catalog.dist_def values('namadb','public','c_profile_image','c_image_id',ARRAY['c_image_id','filename','bin','r_datetime','type','c_member_id'],ARRAY['INTEGER','TEXT','BYTEA','TIMESTAMP','TEXT','INTEGER'],'pgpool_catalog.dist_def_c_profile_image')

Fungsi Aturan Distribusi

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_c_profile_image(val ANYELEMENT) RETURNS INTEGER AS $$SELECT CASE WHEN $1 > 0 then 1 END $$ LANGUAGE SQL;

Log Debug pgpool-II

Log sekitar galat:

2007-05-22 12:37:56 DEBUG: pid 13474: read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 32007-05-22 12:37:56 DEBUG: pid 13474: read_kind_from_backend: read kind from 1 th backend C NUM_BACKENDS: 32007-05-22 12:37:56 DEBUG: pid 13474: read_kind_from_backend: read kind from 2 th backend C NUM_BACKENDS: 32007-05-22 12:37:56 DEBUG: pid 13474: pool_process_query: kind from backend: C2007-05-22 12:37:56 DEBUG: pid 13474: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 32007-05-22 12:37:56 DEBUG: pid 13474: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 32007-05-22 12:37:56 DEBUG: pid 13474: read_kind_from_backend: read kind from 2 th backend Z NUM_BACKENDS: 32007-05-22 12:37:56 DEBUG: pid 13474: pool_process_query: kind from backend: Z2007-05-22 12:37:56 DEBUG: pid 13474: pool_read_message_length: slot: 0 length: 52007-05-22 12:37:56 DEBUG: pid 13474: pool_read_message_length: slot: 1 length: 52007-05-22 12:37:56 DEBUG: pid 13474: pool_read_message_length: slot: 2 length: 52007-05-22 12:37:56 DEBUG: pid 13474: ReadyForQuery: message length: 52007-05-22 12:37:56 DEBUG: pid 13474: ReadyForQuery: transaction state: I2007-05-22 12:37:56 DEBUG: pid 13474: pool_connection_pool_timer: set close time 11798050762007-05-22 12:37:56 DEBUG: pid 13471: pool_rewrite_stmt: XXX rule 701*** glibc detected *** free(): invalid next size (normal): 0x082a8108 ***2007-05-22 12:37:57 DEBUG: pid 13342: reap_handler called2007-05-22 12:37:57 DEBUG: pid 13342: reap_handler: call wait32007-05-22 12:37:57 DEBUG: pid 13342: child 13471 exits with status 6 by signal 62007-05-22 12:37:57 DEBUG: pid 13342: fork a new child pid 135102007-05-22 12:37:57 DEBUG: pid 13342: reap_handler: normally exited2007-05-22 12:37:57 DEBUG: pid 13510: I am 135102007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: auth kind: 02007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: parameter status data received2007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: parameter status data received2007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: parameter status data received2007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: parameter status data received2007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: parameter status data received2007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: parameter status data received2007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: parameter status data received2007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: parameter status data received2007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: parameter status data received2007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: backend key data received2007-05-22 12:37:57 DEBUG: pid 13510: s_do_auth: transaction state: I

Galat glibc (free(): invalid next size) muncul bersamaan dengan galat ini. Upaya upgrade glibc ke versi 2.3.4-2.36 tidak mengubah hasil.

Log PostgreSQL

2007-05-22 12:35:17 JST LOG: autovacuum: processing database "namadb"2007-05-22 12:36:17 JST LOG: autovacuum: processing database "namadb"2007-05-22 12:37:17 JST LOG: autovacuum: processing database "namadb"2007-05-22 12:37:56 JST idle in transactionLOG: unexpected EOF on client connection2007-05-22 12:38:49 JST LOG: autovacuum: processing database "namadb"2007-05-22 12:39:49 JST LOG: autovacuum: processing database "namadb"

Kode PHP untuk Registrasi Gambar

Alur program:

1. Menerima file dari form HTML.
2. Menyimpan sementara file ke direktori internal aplikasi.
3. Memeriksa ukuran, lebar, tinggi; kompresi jika melebihi batas.
4. Meng-escape data gambar dengan pg_escape_bytea dan mendaftarkan ke basis data.

<?php$date=date("YmdHis");$r_datetime=date("Y-m-d H:i:s");$time = time();// Menerima data gambar$profile_image_name1 = $_FILES["profile_image1"]["name"];$profile_image1 = $_FILES["profile_image1"]["tmp_name"];// Periksa tipe gambar (jpg)$imgInf1 = getimagesize("$profile_image1");$img_type1 = $imgInf1[2];if($img_type1 != 2){ $_FILES["profile_image1"]["tmp_name"] = "";}// Kompresi berdasarkan ukuran byte$i = 0;if($_FILES['profile_image1']["tmp_name"] != ""){ $imgFilesize1 = filesize("$profile_image1"); if($imgFilesize1 > 100000){ $im_inp = ImageCreateFromJPEG($_FILES['profile_image1']['tmp_name']); $outputJpgURL = "temporary_img/1_".$date."_".$c_member_id."_image"; while(true){ $i++; clearstatcache(); $assyuku = 95 - 10*($i - 1); $image = imagejpeg($im_inp,"$outputJpgURL",$assyuku); $outputFilesize = filesize("$outputJpgURL"); if($outputFilesize < 100000 || $i==8){break;} } }else{ move_uploaded_file($profile_image1, "temporary_img/1_".$date."_".$c_member_id."_image"); } $tmp1 = "temporary_img/1_".$date."_".$c_member_id."_image";}// Koneksi basis datarequire_once("../init/init_db.php");$resource_id = db_connectioni();// Daftar nama file sementaraif($tmp1 != ""){ $sql = "insert into c_profile_temp_image(filename,r_time)values('$tmp1','$time')"; $res_sql = pg_query($resource_id,"$sql");}// Filter file non-JPEG dan >100KB$imgInf1 = getimagesize("$tmp1");$imgfilesize1 = filesize("$tmp1");if($imgfilesize1 > 100000){ $tmp1 = "";}// Kompresi berdasarkan pikselif($tmp1 != ""){ $width1 = $imgInf1[0]; $height1 = $imgInf1[1]; if($width1 > 160 || $height1 > 160){ $w_loss1 = $width1 - 160; $h_loss1 = $height1 - 160; if($w_loss1 > $h_loss1){ $base_width1 = 160; $base_height1 = floor($height1/($width1/160)); }elseif($h_loss1 > $w_loss1){ $base_height1 = 160; $base_width1 = floor($width1/($height1/160)); }else{ $base_width1 = 160; $base_height1 = 160; } $im_inp1 = imagecreatefromjpeg($tmp1); $im_out1 = imagecreatetruecolor($base_width1, $base_height1); $recipe_image1 = imagecopyresampled($im_out1,$im_inp1,0,0,0,0,$base_width1,$base_height1,$width1,$height1); $image1 = imagejpeg($im_out1,$tmp1); move_uploaded_file($image1, $profile_image_name1); imagedestroy($im_inp1); imagedestroy($im_out1); $file_pass1=$tmp1; }else{ $file_pass1 = $tmp1; $base_width1 = $width1; $base_height1 = $height1; } $fp1=fopen($file_pass1,"rb"); $imgdata1=fread($fp1,filesize($file_pass1)); fclose($fp1); $imgdata1=pg_escape_bytea($imgdata1); $filename1 = "1_".$date."_".$c_member_id."_image";}@unlink($file_pass1);// Cari file dan ukuran yang sudah terdaftar$sql1 = "select * from c_member where c_member_id='$c_member_id'";$res_sql1 = pg_query($resource_id,"$sql1");$arycol1 = pg_fetch_array($res_sql1);$img1 = $arycol1['image_filename_1'];$image_width1 = $arycol1['image_width1'];$image_height1 = $arycol1['image_height1'];if($filename1 == ""){ $filename1 = $img1; $base_width1 = $image_width1; $base_height1 = $image_height1;}// Transaksipg_query($resource_id,"begin;");pg_query($resource_id,"lock table c_profile_image in share row exclusive mode");$sql = "select max(c_image_id) from c_profile_image";$res_sql = pg_query($resource_id,"$sql");$arycol = pg_fetch_array($res_sql);$max = $arycol['max'];if(!$res_sql){ exit;}if($tmp1 != ""){ if($img1 == ""){ $max += 1; $sql = "insert into c_profile_image(c_image_id,filename,bin,type,c_member_id,r_datetime)values('$max','$filename1','$imgdata1','$img_type1','$c_member_id','$r_datetime')"; $res_sql = pg_query($resource_id,"$sql"); if(!$res_sql || pg_affected_rows($res_sql) !=1){ pg_query($resource_id,"rollback;"); exit; } }else{ $sql = "update c_profile_image set filename='$filename1',bin='$imgdata1',type='$img_type1',r_datetime='$r_datetime' where filename='$img1'"; $res_sql = pg_query($resource_id,"$sql"); if(!$res_sql || pg_affected_rows($res_sql) !=1){ pg_query($resource_id,"rollback;"); exit; } }}pg_query($resource_id,"commit;");?>

Demikian laporan masalah. Jika ada yang mengetahui solusi, mohon informasinya. Terima kasih.