View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000038 | Pgpool-II | Bug | public | 2012-11-21 01:29 | 2012-11-23 20:12 |
| Reporter | awalland | Assigned To | t-ishii | ||
| Priority | normal | Severity | major | Reproducibility | always |
| Status | assigned | Resolution | open | ||
| Platform | x86_64 | OS | linux 2.6.32-279.14.1.el6.x86_64 | OS Version | centos 6 |
| Summary | 0000038: pgpool causes erroneous commit when DROP FUNCTION is sent | ||||
| Description | we have a pgpool 3.2.0 cluster in replication & load balance mode with 3 nodes. we execute sql scripts with psql against the pgpool node and we have a problem as soon as a "DROP FUNCTION" statement appears in the script, because what happens is, that 1 of the three nodes continues the open transaction, whereas the other 2 nodes are issued a commit which leads to inconsistent databases. | ||||
| Steps To Reproduce | psql -h pgpoolserver BEGIN; drop function if exists a(a text); statement log of the nodes: node1: LOG: statement: begin; LOG: statement: drop function if exists a(a text); node2: LOG: statement: begin; LOG: statement: drop function if exists a(a text); LOG: statement: COMMIT node3: LOG: statement: begin; LOG: statement: drop function if exists a(a text); LOG: statement: COMMIT | ||||
| Additional Information | this does for example not happen with drop table. for us this is quite a big problem, since it leads to inconsistent databases. | ||||
| Tags | No tags attached. | ||||
|
|
Couldn't reproduce here(pgpool-II 3.2.0 on Linux x86_64). Can you show pgpool.conf? |
|
|
hi! thank you very much for your fast reply! here is my pgpool.conf [root@dbbalancer ~]# egrep -v '^\s*#' /etc/pgpool-II/pgpool.conf |sed '/^$/d' listen_addresses = '*' port = 5432 socket_dir = '/tmp' pcp_port = 9898 pcp_socket_dir = '/tmp' backend_hostname0 = 'db01' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/pgsql/9.2/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = 'db02' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/pgsql/9.2/data' backend_flag1 = 'ALLOW_TO_FAILOVER' backend_hostname2 = 'db03' backend_port2 = 5432 backend_weight2 = 1 backend_data_directory2 = '/var/lib/pgsql/9.2/data' backend_flag2 = 'ALLOW_TO_FAILOVER' enable_pool_hba = off authentication_timeout = 60 ssl = off num_init_children = 32 max_pool = 400 child_life_time = 300 child_max_connections = 0 connection_life_time = 0 client_idle_limit = 0 log_destination = 'stderr' print_timestamp = on log_connections = off log_hostname = off log_statement = off log_per_node_statement = off log_standby_delay = 'none' syslog_facility = 'LOCAL0' syslog_ident = 'pgpool' debug_level = 0 pid_file_name = '/var/run/pgpool/pgpool.pid' logdir = '/tmp' connection_cache = on reset_query_list = 'ABORT; DISCARD ALL' replication_mode = on replicate_select = off insert_lock = on lobj_lock_table = '' replication_stop_on_mismatch = off failover_if_affected_tuples_mismatch = off load_balance_mode = on ignore_leading_white_space = on white_function_list = '' black_function_list = 'currval,lastval,nextval,setval' master_slave_mode = off master_slave_sub_mode = 'slony' sr_check_period = 0 sr_check_user = 'nobody' sr_check_password = '' delay_threshold = 0 follow_master_command = '' parallel_mode = off pgpool2_hostname = '' health_check_period = 0 health_check_timeout = 20 health_check_user = 'nobody' health_check_password = '' health_check_max_retries = 0 health_check_retry_delay = 1 failover_command = '' failback_command = '' fail_over_on_backend_error = on recovery_user = 'nobody' recovery_password = '' recovery_1st_stage_command = '' recovery_2nd_stage_command = '' recovery_timeout = 90 client_idle_limit_in_recovery = 0 use_watchdog = off trusted_servers = '' delegate_IP = '' wd_hostname = '' wd_port = 9000 wd_interval = 10 ping_path = '/bin' ifconfig_path = '/sbin' if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0' if_down_cmd = 'ifconfig eth0:0 down' arping_path = '/usr/sbin' # arping command path arping_cmd = 'arping -U $_IP_$ -w 1' wd_life_point = 3 wd_lifecheck_query = 'SELECT 1' relcache_expire = 0 relcache_size = 256 check_temp_table = on memory_cache_enabled = on memqcache_method = 'shmem' memqcache_memcached_host = 'localhost' memqcache_memcached_port = 11211 memqcache_total_size = 67108864 memqcache_max_num_cache = 10000000 memqcache_expire = 28800 ## 8h memqcache_auto_cache_invalidation = on memqcache_maxcache = 409600 memqcache_cache_block_size = 1048576 memqcache_oiddir = '/var/log/pgpool/oiddir' white_memqcache_table_list = '' black_memqcache_table_list = '' did i overlook some configuration issue? |
|
|
I recreated pgpool.conf based on yours (actual pgpool.conf attached), but still not reproduced. Can you start pgpool with debug option (-d) enabled and show us log? |
|
|
|
|
|
|
|
|
hi! i added the debug.log information to the timestamps in the log: 2012-11-21 10:30:33 <- hit enter after 'begin;' 2012-11-21 10:30:42 <- hit enter after 'drop function if exists a(a text);' 2012-11-21 10:30:55 <- hit ctrl+d to log out |
|
|
just some additional information: i just cloned the git repo and used a freshly compiled pgpool to solve the jdbc connection issue and the erroneous commit issue is still there. currently we are working around it by using "create or replace function" instead of "drop function" and "create function". |
|
|
Still cannot reproduce the problem. Can you attach gdb to pgpool right after you hit "drop function if exists..." and take backtrace? |
|
|
hi! i am not 100% sure if i did it right, but here's what i did: 1.) psql 2.) begin; 3.) drop function if exists a(a text); 4.) find pgpool child pid that i am connected to 5.) gdb /usr/bin/pgpool <pid> 6.) bt see attached bt.txt for the backtrace. |
|
|
|
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2012-11-21 01:29 | awalland | New Issue | |
| 2012-11-21 08:16 | t-ishii | Note Added: 0000160 | |
| 2012-11-21 08:16 | t-ishii | Assigned To | => t-ishii |
| 2012-11-21 08:16 | t-ishii | Status | new => assigned |
| 2012-11-21 08:16 | t-ishii | Steps to Reproduce Updated | |
| 2012-11-21 08:29 | awalland | Note Added: 0000161 | |
| 2012-11-21 11:15 | t-ishii | Note Added: 0000163 | |
| 2012-11-21 11:16 | t-ishii | File Added: pgpool.conf | |
| 2012-11-21 18:32 | awalland | File Added: debug.log | |
| 2012-11-21 18:34 | awalland | Note Added: 0000164 | |
| 2012-11-22 19:07 | awalland | Note Added: 0000165 | |
| 2012-11-23 01:38 | awalland | Note Edited: 0000165 | |
| 2012-11-23 12:19 | t-ishii | Note Added: 0000166 | |
| 2012-11-23 20:11 | awalland | Note Added: 0000167 | |
| 2012-11-23 20:12 | awalland | File Added: bt.txt | |
| 2012-11-23 20:13 | awalland | Note Edited: 0000167 |
