Announcement

Collapse
No announcement yet.

Problem after upgrade to php5

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem after upgrade to php5

    Hello,

    I'm hoping someone out there might be able to help us with some insights. We upgraded our front-end web servers on Friday with the following:

    php 5.2.3
    eaccelerator 0.9.5.1
    Zend Optimizer 3.2.8
    memcache 2.1.2

    After the upgrade we started getting automated errors from vbulletin reporting a problem with the database.

    We upgraded 4 webservers. We upgraded one server first and that one started sending emails, then the second was upgraded and it too started sending emails. So now I have 4 webservers upgraded and all 4 send out the same emails.

    Here is the odd part, there have be no complaints from surfers and we can 't make the problem manifest in the web browser. You don't get the typical "there was a problem with the database" splash screens. We've had those before and our visitors aren't afraid to let us know when they crop up. So, it really appears that the database error isn't causing any visual problems when someone is surfing.

    We get an error email about every 60 seconds since Friday afternoon. So I've got several thousand sitting here now. The curious part to me is that every error is reporting a problem with a specific kind of action it is trying to do. As you know with vbulletin, there are lots of querries that take place. Pulling in avatars, checking if a private message is available, editing a post, pulling in a smilee, etc. None of those kinds of tasks are generating any kind of vbulletin emails. The only errors are coming from one of two types of calls (not sure of the correct terminology):

    Invalid SQL: UPDATE session
    and
    Invalid SQL: INSERT IGNORE INTO session

    If there were actual connectivity issues with MYSQL because either MYSQL was overwhelmed or because there was a problem with the backend network, then I'd think we'd get all kinds of random errors for all the many different kinds of querries. But we have a few thousand now and they are all either "UPDATE session" or "INSERT IGNORE INTO session" which leads me to believe there must be some kind of compatibility issue. Maybe in PHP5 some kind of query changed. We were running a late version of php4 before. I can't find any information on this error though, so I could be crazy. I'm just haphazarding guesses at this point I suppose.

    I'll copy two of the errors below in case they shed any light to anyone ou there. What we know is that the errors are only generated from pages where users can login. We even get the errors from pages that aren't part of the vbulletin core, but pages that we have included a login box outside of vbulletin like on our homepage Our homepage isn't part of vbulletin, but we allow users to login on our index page and that page will also generate these session errors that show up in the emails vbulletin sends out.

    Here are two examples of each kind of error email:

    #1:
    Database error in vBulletin 3.6.5:

    Invalid SQL:

    UPDATE session
    SET lastactivity = 1182185146, inthread = 0, incalendar = 0, badlocation = 0
    WHERE sessionhash = '2d637fb1cb06d659bc0a7688362ba85f';

    MySQL Error : Lost connection to MySQL server during query
    Error Number : 2013
    Date : Monday, June 18th 2007 @ 01:03:07 PM
    Script : /forum/forumdisplay.php?f=35&page=2&order=desc
    Referrer : /forum/forumdisplay.php?f=35&page=2&order=desc
    IP Address : 217.207.170.138
    Username : EuroboyFilms
    Classname : vB_Database

    #2:

    Database error in vBulletin 3.6.5:

    Invalid SQL:

    INSERT IGNORE INTO session
    (sessionhash, userid, host, idhash, lastactivity, location, styleid, languageid, loggedin, inforum, inthread, incalendar, badlocation, useragent, bypass, profileupdate)
    VALUES
    ('3bedc92c81423ff82c2003292ab008c1', 177976, '210.5.85.226', '4693691b7fac6718c1b18495953d45ce', 1182182742, '/forum/showthread.php?t=21187', 0, 0, 1, 33, 21187, 0, 0, 'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1)', 0, 0);

    MySQL Error : Lost connection to MySQL server during query
    Error Number : 2013
    Date : Monday, June 18th 2007 @ 12:08:58 PM
    Script : /forum/showthread.php?t=21187
    Referrer : /forum/forumdisplay.php?f=33
    IP Address : 210.5.85.226
    Username : goodie1416
    Classname : vB_Database

    We've battled this for several days now and our web host has updated the kernel on our mysql box that is dedicated to vbulletin, but mysql looks fine. It's a box with 4GB of memory and shows it has a lot available. It's doing about 180 queries per second but the memory shows as 3 gb free our host says.

    I appreciate any and all help and suggestions!

    Thanks,
    Bryce

  • #2
    This is a server issue. See this post for the possible causes of a lost connection error:

    http://www.vbulletin.com/forum/showt...265#post310265
    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
    Change CKEditor Colors to Match Style (for 4.1.4 and above)

    Steve Machol Photography


    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


    Comment


    • #3
      Do you see anything here that looks off?

      Server version 4.1.18-standard-log
      Protocol version 10
      Connection 10.10.40.4 via TCP/IP
      TCP port 3306
      Uptime: 56 min 21 sec

      Threads: 7 Questions: 529888 Slow queries: 0 Opens: 364 Flush tables: 1 Open tables: 356 Queries per second avg: 156.725

      Variables:
      +---------------------------------+------------------------------------------+
      | Variable_name | Value |
      +---------------------------------+------------------------------------------+
      | back_log | 50 |
      | basedir | /usr/ |
      | binlog_cache_size | 32768 |
      | bulk_insert_buffer_size | 8388608 |
      | character_set_client | latin1 |
      | character_set_connection | latin1 |
      | character_set_database | latin1 |
      | character_set_results | latin1 |
      | character_set_server | latin1 |
      | character_set_system | utf8 |
      | character_sets_dir | /usr/share/mysql/charsets/ |
      | collation_connection | latin1_swedish_ci |
      | collation_database | latin1_swedish_ci |
      | collation_server | latin1_swedish_ci |
      | concurrent_insert | ON |
      | connect_timeout | 5 |
      | datadir | /web/sites/data/ |
      | date_format | %Y-%m-%d |
      | datetime_format | %Y-%m-%d %H:%i:%s |
      | default_week_format | 0 |
      | delay_key_write | ON |
      | delayed_insert_limit | 100 |
      | delayed_insert_timeout | 300 |
      | delayed_queue_size | 1000 |
      | expire_logs_days | 0 |
      | flush | OFF |
      | flush_time | 0 |
      | ft_boolean_syntax | + -><()~*:""&| |
      | ft_max_word_len | 84 |
      | ft_min_word_len | 4 |
      | ft_query_expansion_limit | 20 |
      | ft_stopword_file | (built-in) |
      | group_concat_max_len | 1024 |
      | have_archive | YES |
      | have_bdb | NO |
      | have_blackhole_engine | NO |
      | have_compress | YES |
      | have_crypt | YES |
      | have_csv | NO |
      | have_example_engine | NO |
      | have_geometry | YES |
      | have_innodb | YES |
      | have_isam | NO |
      | have_ndbcluster | NO |
      | have_openssl | NO |
      | have_query_cache | YES |
      | have_raid | YES |
      | have_rtree_keys | YES |
      | have_symlink | YES |
      | init_connect | |
      | init_file | |
      | init_slave | |
      | innodb_additional_mem_pool_size | 1048576 |
      | innodb_autoextend_increment | 8 |
      | innodb_buffer_pool_awe_mem_mb | 0 |
      | innodb_buffer_pool_size | 8388608 |
      | innodb_data_file_path | ibdata1:10M:autoextend |
      | innodb_data_home_dir | |
      | innodb_fast_shutdown | ON |
      | innodb_file_io_threads | 4 |
      | innodb_file_per_table | OFF |
      | innodb_flush_log_at_trx_commit | 1 |
      | innodb_flush_method | |
      | innodb_force_recovery | 0 |
      | innodb_lock_wait_timeout | 50 |
      | innodb_locks_unsafe_for_binlog | OFF |
      | innodb_log_arch_dir | |
      | innodb_log_archive | OFF |
      | innodb_log_buffer_size | 1048576 |
      | innodb_log_file_size | 5242880 |
      | innodb_log_files_in_group | 2 |
      | innodb_log_group_home_dir | ./ |
      | innodb_max_dirty_pages_pct | 90 |
      | innodb_max_purge_lag | 0 |
      | innodb_mirrored_log_groups | 1 |
      | innodb_open_files | 300 |
      | innodb_table_locks | ON |
      | innodb_thread_concurrency | 8 |
      | interactive_timeout | 60 |
      | join_buffer_size | 131072 |
      | key_buffer_size | 8 |
      | key_cache_age_threshold | 300 |
      | key_cache_block_size | 1024 |
      | key_cache_division_limit | 100 |
      | language | /usr/share/mysql/english/ |
      | large_files_support | ON |
      | license | GPL |
      | local_infile | ON |
      | locked_in_memory | OFF |
      | log | ON |
      | log_bin | OFF |
      | log_error | /web/sites/data/mysql.err |
      | log_slave_updates | OFF |
      | log_slow_queries | ON |
      | log_update | OFF |
      | log_warnings | 1 |
      | long_query_time | 5 |
      | low_priority_updates | OFF |
      | lower_case_file_system | OFF |
      | lower_case_table_names | 0 |
      | max_allowed_packet | 15727616 |
      | max_binlog_cache_size | 4294967295 |
      | max_binlog_size | 1073741824 |
      | max_connect_errors | 999999 |
      | max_connections | 16384 |
      | max_delayed_threads | 20 |
      | max_error_count | 64 |
      | max_heap_table_size | 16777216 |
      | max_insert_delayed_threads | 20 |
      | max_join_size | 4294967295 |
      | max_length_for_sort_data | 1024 |
      | max_relay_log_size | 0 |
      | max_seeks_for_key | 4294967295 |
      | max_sort_length | 1024 |
      | max_tmp_tables | 32 |
      | max_user_connections | 0 |
      | max_write_lock_count | 4294967295 |
      | myisam_data_pointer_size | 4 |
      | myisam_max_extra_sort_file_size | 2147483648 |
      | myisam_max_sort_file_size | 2147483647 |
      | myisam_recover_options | OFF |
      | myisam_repair_threads | 1 |
      | myisam_sort_buffer_size | 134217728 |
      | myisam_stats_method | nulls_unequal |
      | net_buffer_length | 16384 |
      | net_read_timeout | 30 |
      | net_retry_count | 10 |
      | net_write_timeout | 60 |
      | new | OFF |
      | old_passwords | ON |
      | open_files_limit | 261424 |
      | pid_file | /web/sites/data/mysqld.pid |
      | port | 3306 |
      | preload_buffer_size | 32768 |
      | protocol_version | 10 |
      | query_alloc_block_size | 8192 |
      | query_cache_limit | 1048576 |
      | query_cache_min_res_unit | 4096 |
      | query_cache_size | 268435456 |
      | query_cache_type | ON |
      | query_cache_wlock_invalidate | OFF |
      | query_prealloc_size | 8192 |
      | range_alloc_block_size | 2048 |
      | read_buffer_size | 3067904 |
      | read_only | OFF |
      | read_rnd_buffer_size | 16773120 |
      | relay_log_purge | ON |
      | relay_log_space_limit | 0 |
      | rpl_recovery_rank | 0 |
      | secure_auth | OFF |
      | server_id | 0 |
      | skip_external_locking | ON |
      | skip_networking | OFF |
      | skip_show_database | OFF |
      | slave_net_timeout | 3600 |
      | slave_transaction_retries | 0 |
      | slow_launch_time | 2 |
      | socket | /var/run/mysqld/mysqld.sock |
      | sort_buffer_size | 3071992 |
      | sql_mode | |
      | sql_notes | OFF |
      | sql_warnings | OFF |
      | storage_engine | MyISAM |
      | sync_binlog | 0 |
      | sync_frm | ON |
      | sync_replication | 0 |
      | sync_replication_slave_id | 0 |
      | sync_replication_timeout | 0 |
      | system_time_zone | EDT |
      | table_cache | 1024 |
      | table_type | MyISAM |
      | thread_cache_size | 32 |
      | thread_stack | 126976 |
      | time_format | %H:%i:%s |
      | time_zone | SYSTEM |
      | tmp_table_size | 33554432 |
      | tmpdir | /tmp |
      | transaction_alloc_block_size | 8192 |
      | transaction_prealloc_size | 4096 |
      | tx_isolation | REPEATABLE-READ |
      | version | 4.1.18-standard-log |
      | version_comment | MySQL Community Edition - Standard (GPL) |
      | version_compile_machine | i686 |
      | version_compile_os | pc-linux-gnu |
      | wait_timeout | 120 |
      +---------------------------------+------------------------------------------+

      Extended-Status:


      +----------------------------+------------+
      | Variable_name | Value |
      +----------------------------+------------+
      | Aborted_clients | 62 |
      | Aborted_connects | 8 |
      | Binlog_cache_disk_use | 0 |
      | Binlog_cache_use | 0 |
      | Bytes_received | 188150677 |
      | Bytes_sent | 2819083060 |
      | Com_admin_commands | 0 |
      | Com_alter_db | 0 |
      | Com_alter_table | 0 |
      | Com_analyze | 0 |
      | Com_backup_table | 0 |
      | Com_begin | 0 |
      | Com_change_db | 64223 |
      | Com_change_master | 0 |
      | Com_check | 0 |
      | Com_checksum | 0 |
      | Com_commit | 0 |
      | Com_create_db | 0 |
      | Com_create_function | 0 |
      | Com_create_index | 0 |
      | Com_create_table | 0 |
      | Com_dealloc_sql | 0 |
      | Com_delete | 1508 |
      | Com_delete_multi | 0 |
      | Com_do | 0 |
      | Com_drop_db | 0 |
      | Com_drop_function | 0 |
      | Com_drop_index | 0 |
      | Com_drop_table | 0 |
      | Com_drop_user | 0 |
      | Com_execute_sql | 0 |
      | Com_flush | 0 |
      | Com_grant | 0 |
      | Com_ha_close | 0 |
      | Com_ha_open | 0 |
      | Com_ha_read | 0 |
      | Com_help | 0 |
      | Com_insert | 19099 |
      | Com_insert_select | 0 |
      | Com_kill | 0 |
      | Com_load | 0 |
      | Com_load_master_data | 0 |
      | Com_load_master_table | 0 |
      | Com_lock_tables | 331 |
      | Com_optimize | 2 |
      | Com_preload_keys | 0 |
      | Com_prepare_sql | 0 |
      | Com_purge | 0 |
      | Com_purge_before_date | 0 |
      | Com_rename_table | 0 |
      | Com_repair | 0 |
      | Com_replace | 2041 |
      | Com_replace_select | 0 |
      | Com_reset | 0 |
      | Com_restore_table | 0 |
      | Com_revoke | 0 |
      | Com_revoke_all | 0 |
      | Com_rollback | 0 |
      | Com_savepoint | 0 |
      | Com_select | 189574 |
      | Com_set_option | 78 |
      | Com_show_binlog_events | 0 |
      | Com_show_binlogs | 4 |
      | Com_show_charsets | 10 |
      | Com_show_collations | 10 |
      | Com_show_column_types | 0 |
      | Com_show_create_db | 0 |
      | Com_show_create_table | 0 |
      | Com_show_databases | 6 |
      | Com_show_errors | 0 |
      | Com_show_fields | 0 |
      | Com_show_grants | 2 |
      | Com_show_innodb_status | 0 |
      | Com_show_keys | 0 |
      | Com_show_logs | 0 |
      | Com_show_master_status | 0 |
      | Com_show_ndb_status | 0 |
      | Com_show_new_master | 0 |
      | Com_show_open_tables | 0 |
      | Com_show_privileges | 0 |
      | Com_show_processlist | 0 |
      | Com_show_slave_hosts | 0 |
      | Com_show_slave_status | 0 |
      | Com_show_status | 3 |
      | Com_show_storage_engines | 0 |
      | Com_show_tables | 48 |
      | Com_show_variables | 22 |
      | Com_show_warnings | 0 |
      | Com_slave_start | 0 |
      | Com_slave_stop | 0 |
      | Com_stmt_close | 0 |
      | Com_stmt_execute | 0 |
      | Com_stmt_prepare | 0 |
      | Com_stmt_reset | 0 |
      | Com_stmt_send_long_data | 0 |
      | Com_truncate | 0 |
      | Com_unlock_tables | 331 |
      | Com_update | 44595 |
      | Com_update_multi | 0 |
      | Connections | 64335 |
      | Created_tmp_disk_tables | 534 |
      | Created_tmp_files | 72 |
      | Created_tmp_tables | 9137 |
      | Delayed_errors | 0 |
      | Delayed_insert_threads | 0 |
      | Delayed_writes | 0 |
      | Flush_commands | 1 |
      | Handler_commit | 0 |
      | Handler_delete | 2405 |
      | Handler_discover | 0 |
      | Handler_read_first | 24707 |
      | Handler_read_key | 14009145 |
      | Handler_read_next | 50245366 |
      | Handler_read_prev | 4312143 |
      | Handler_read_rnd | 1854494 |
      | Handler_read_rnd_next | 201599752 |
      | Handler_rollback | 0 |
      | Handler_update | 703727 |
      | Handler_write | 257730 |
      | Key_blocks_not_flushed | 0 |
      | Key_blocks_unused | 0 |
      | Key_blocks_used | 0 |
      | Key_read_requests | 49390613 |
      | Key_reads | 49393272 |
      | Key_write_requests | 149262 |
      | Key_writes | 149262 |
      | Max_used_connections | 59 |
      | Not_flushed_delayed_rows | 0 |
      | Open_files | 464 |
      | Open_streams | 0 |
      | Open_tables | 369 |
      | Opened_tables | 377 |
      | Qcache_free_blocks | 4921 |
      | Qcache_free_memory | 232750056 |
      | Qcache_hits | 236393 |
      | Qcache_inserts | 185538 |
      | Qcache_lowmem_prunes | 0 |
      | Qcache_not_cached | 3703 |
      | Qcache_queries_in_cache | 12683 |
      | Qcache_total_blocks | 30814 |
      | Questions | 622565 |
      | Rpl_status | NULL |
      | Select_full_join | 938 |
      | Select_full_range_join | 0 |
      | Select_range | 27392 |
      | Select_range_check | 0 |
      | Select_scan | 27907 |
      | Slave_open_temp_tables | 0 |
      | Slave_retried_transactions | 0 |
      | Slave_running | OFF |
      | Slow_launch_threads | 0 |
      | Slow_queries | 0 |
      | Sort_merge_passes | 34 |
      | Sort_range | 39202 |
      | Sort_rows | 6366445 |
      | Sort_scan | 4905 |
      | Table_locks_immediate | 492387 |
      | Table_locks_waited | 13145 |
      | Threads_cached | 24 |
      | Threads_connected | 9 |
      | Threads_created | 112 |
      | Threads_running | 7 |
      | Uptime | 3979 |
      +----------------------------+------------+


      Thanks again!

      Comment


      • #4
        Originally posted by jubmedia View Post
        MySQL Error : Lost connection to MySQL server during query
        How's your file system? Any partition full?

        What's in the MySQL server's log file when such a disconnect happens?
        "The lurking suspicion that something could be simplified is the world's richest source of rewarding challenges"
        - Edsger Dijkstra

        Comment


        • #5
          Hi,

          The file systems on the webservers and the mysql boxes all have lots of free space.

          We can't find anything at all in the mysql server log

          I've experimented with the wait_timeout and have it now set to 14440 and that has really slowed down the emails. Instead of getting 1 per second we are getting 1 every half hour.

          Did I fix the problem though, or just cover it up? Are there any ill effects that may hit me by increasing that number from 120 all the way up to 14440?

          Thanks!

          Comment


          • #6
            Could you type

            df

            on the command line and output the results here?

            Comment


            • #7
              I just wanted to let everyone know that our problem appears to be solved. I wanted to post the answer in case it helps someone else down the road. The answer to my problem was actually found in a different thread, but this is the magic command for us:

              SET GLOBAL wait_timeout=14400;

              The original setting was =120


              Thanks everyone.

              Comment

              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
              Working...
              X