Joomla giving intermittent "Database connection error (2): Could not connect to MySQL" errors

We have Joomla, PHP and MySQL installed on a Windows 8 server.

Recently, we seem to get an error message intermittently when trying to access pages on the site:

Database connection error (2): Could not connect to MySQL

It's intermittent, so it's not an access issue. Could it a performance setting and, if so, what are the settings I should check in mySQL?

Thanks,

Steve
skbohlerAsked:
Who is Participating?
 
Steve BinkCommented:
Try setting your maxInstances to limit the number of processes IIS will start.  See here for instructions: http://www.iis.net/configreference/system.webserver/fastcgi/application
0
 
Steve BinkCommented:
Some questions:

What versions of PHP, MySQL, and Joomla are you using?  
What database library are you using from within Joomla?  
How much traffic does your site get?
Please post your my.cnf or the results of "SHOW VARIABLES;".

In the large view, this could be from a variety of causes...anything that causes MySQL to not receive or accept a connection.  In Joomla 3.4 code, the condition is checked only at a boolean level.  However, you could easily put some trace code in to examine the error returned from the MySQL server.

Since it is intermittent, my first guess is something to do with available threads on the database, or maximum open connections.
0
 
skbohlerAuthor Commented:
Joomla version is 2.6+
How do I determine the version of MySQL? Workbench version is 5.2.39

There are suddenly a lot of php-cgi.exe processes running now as well. Site is very slow.

Variables from workbench are:

auto_increment_increment.............................. 1
auto_increment_offset................................. 1
autocommit............................................ ON
automatic_sp_privileges............................... ON
back_log.............................................. 50
basedir............................................... D:\Program Files\MySQL\MySQL Server 5.5\
big_tables............................................ OFF
binlog_cache_size..................................... 32768
binlog_direct_non_transactional_updates............... OFF
binlog_format......................................... STATEMENT
binlog_stmt_cache_size................................ 32768
bulk_insert_buffer_size............................... 8388608
character_set_client.................................. utf8
character_set_connection.............................. utf8
character_set_database................................ utf8
character_set_filesystem.............................. binary
character_set_results................................. utf8
character_set_server.................................. utf8
character_set_system.................................. utf8
character_sets_dir.................................... D:\Program Files\MySQL\MySQL Server 5.5\share\charsets\
collation_connection.................................. utf8_general_ci
collation_database.................................... utf8_general_ci
collation_server...................................... utf8_general_ci
completion_type....................................... NO_CHAIN
concurrent_insert..................................... AUTO
connect_timeout....................................... 10
datadir............................................... D:\ProgramData\MySQL\MySQL Server 5.5\data\
date_format........................................... %Y-%m-%d
datetime_format....................................... %Y-%m-%d %H:%i:%s
default_storage_engine................................ InnoDB
default_week_format................................... 0
delay_key_write....................................... ON
delayed_insert_limit.................................. 100
delayed_insert_timeout................................ 300
delayed_queue_size.................................... 1000
div_precision_increment............................... 4
engine_condition_pushdown............................. ON
event_scheduler....................................... OFF
expire_logs_days...................................... 0
flush................................................. OFF
flush_time............................................ 1800
foreign_key_checks.................................... ON
ft_boolean_syntax..................................... + -><()~*:""&|
ft_max_word_len....................................... 84
ft_min_word_len....................................... 4
ft_query_expansion_limit.............................. 20
ft_stopword_file...................................... (built-in)
general_log........................................... OFF
general_log_file...................................... D:\ProgramData\MySQL\MySQL Server 5.5\data\p2742624.log
group_concat_max_len.................................. 1024
have_compress......................................... YES
have_crypt............................................ NO
have_csv.............................................. YES
have_dynamic_loading.................................. YES
have_geometry......................................... YES
have_innodb........................................... YES
have_ndbcluster....................................... NO
have_openssl.......................................... DISABLED
have_partitioning..................................... YES
have_profiling........................................ YES
have_query_cache...................................... YES
have_rtree_keys....................................... YES
have_ssl.............................................. DISABLED
have_symlink.......................................... YES
hostname.............................................. p2742624
ignore_builtin_innodb................................. OFF
init_connect..........................................
init_file.............................................
init_slave............................................
innodb_adaptive_flushing.............................. ON
innodb_adaptive_hash_index............................ ON
innodb_additional_mem_pool_size....................... 18874368
innodb_autoextend_increment........................... 8
innodb_autoinc_lock_mode.............................. 1
innodb_buffer_pool_instances.......................... 1
innodb_buffer_pool_size............................... 877658112
innodb_change_buffering............................... all
innodb_checksums...................................... ON
innodb_commit_concurrency............................. 0
innodb_concurrency_tickets............................ 500
innodb_data_file_path................................. ibdata1:10M:autoextend
innodb_data_home_dir..................................
innodb_doublewrite.................................... ON
innodb_fast_shutdown.................................. 1
innodb_file_format.................................... Antelope
innodb_file_format_check.............................. ON
innodb_file_format_max................................ Antelope
innodb_file_per_table................................. OFF
innodb_flush_log_at_trx_commit........................ 1
innodb_flush_method...................................
innodb_force_load_corrupted........................... OFF
innodb_force_recovery................................. 0
innodb_io_capacity.................................... 200
innodb_large_prefix................................... OFF
innodb_lock_wait_timeout.............................. 50
innodb_locks_unsafe_for_binlog........................ OFF
innodb_log_buffer_size................................ 9437184
innodb_log_file_size.................................. 439353344
innodb_log_files_in_group............................. 2
innodb_log_group_home_dir............................. .\
innodb_max_dirty_pages_pct............................ 75
innodb_max_purge_lag.................................. 0
innodb_mirrored_log_groups............................ 1
innodb_old_blocks_pct................................. 37
innodb_old_blocks_time................................ 0
innodb_open_files..................................... 300
innodb_purge_batch_size............................... 20
innodb_purge_threads.................................. 0
innodb_random_read_ahead.............................. OFF
innodb_read_ahead_threshold........................... 56
innodb_read_io_threads................................ 4
innodb_replication_delay.............................. 0
innodb_rollback_on_timeout............................ OFF
innodb_rollback_segments.............................. 128
innodb_spin_wait_delay................................ 6
innodb_stats_method................................... nulls_equal
innodb_stats_on_metadata.............................. ON
innodb_stats_sample_pages............................. 8
innodb_strict_mode.................................... OFF
innodb_support_xa..................................... ON
innodb_sync_spin_loops................................ 30
innodb_table_locks.................................... ON
innodb_thread_concurrency............................. 17
innodb_thread_sleep_delay............................. 10000
innodb_use_native_aio................................. ON
innodb_use_sys_malloc................................. ON
innodb_version........................................ 1.1.8
innodb_write_io_threads............................... 4
interactive_timeout................................... 2880
join_buffer_size...................................... 131072
keep_files_on_create.................................. OFF
key_buffer_size....................................... 8388608
key_cache_age_threshold............................... 300
key_cache_block_size.................................. 1024
key_cache_division_limit.............................. 100
large_files_support................................... ON
large_page_size....................................... 0
large_pages........................................... OFF
lc_messages........................................... en_US
lc_messages_dir....................................... D:\Program Files\MySQL\MySQL Server 5.5\share\
lc_time_names......................................... en_US
license............................................... GPL
local_infile.......................................... ON
lock_wait_timeout..................................... 31536000
log................................................... OFF
log_bin............................................... OFF
log_bin_trust_function_creators....................... OFF
log_error............................................. D:\ProgramData\MySQL\MySQL Server 5.5\data\p2742624.err
log_output............................................ FILE
log_queries_not_using_indexes......................... OFF
log_slave_updates..................................... OFF
log_slow_queries...................................... OFF
log_warnings.......................................... 1
long_query_time....................................... 10.000000
low_priority_updates.................................. OFF
lower_case_file_system................................ ON
lower_case_table_names................................ 1
max_allowed_packet.................................... 1048576
max_binlog_cache_size................................. 18446744073709547520
max_binlog_size....................................... 1073741824
max_binlog_stmt_cache_size............................ 18446744073709547520
max_connect_errors.................................... 10
max_connections....................................... 151
max_delayed_threads................................... 20
max_error_count....................................... 64
max_heap_table_size................................... 16777216
max_insert_delayed_threads............................ 20
max_join_size......................................... 18446744073709551615
max_length_for_sort_data.............................. 1024
max_long_data_size.................................... 1048576
max_prepared_stmt_count............................... 16382
max_relay_log_size.................................... 0
max_seeks_for_key..................................... 4294967295
max_sort_length....................................... 1024
max_sp_recursion_depth................................ 0
max_tmp_tables........................................ 32
max_user_connections.................................. 0
max_write_lock_count.................................. 4294967295
metadata_locks_cache_size............................. 1024
min_examined_row_limit................................ 0
multi_range_count..................................... 256
myisam_data_pointer_size.............................. 6
myisam_max_sort_file_size............................. 107374182400
myisam_mmap_size...................................... 18446744073709551615
myisam_recover_options................................ OFF
myisam_repair_threads................................. 1
myisam_sort_buffer_size............................... 265289728
myisam_stats_method................................... nulls_unequal
myisam_use_mmap....................................... OFF
named_pipe............................................ OFF
net_buffer_length..................................... 16384
net_read_timeout...................................... 30
net_retry_count....................................... 10
net_write_timeout..................................... 60
new................................................... OFF
old................................................... OFF
old_alter_table....................................... OFF
old_passwords......................................... OFF
open_files_limit...................................... 2803
optimizer_prune_level................................. 1
optimizer_search_depth................................ 62
optimizer_switch...................................... index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
performance_schema.................................... OFF
performance_schema_events_waits_history_long_size..... 10000
performance_schema_events_waits_history_size.......... 10
performance_schema_max_cond_classes................... 80
performance_schema_max_cond_instances................. 1000
performance_schema_max_file_classes................... 50
performance_schema_max_file_handles................... 32768
performance_schema_max_file_instances................. 10000
performance_schema_max_mutex_classes.................. 200
performance_schema_max_mutex_instances................ 1000000
performance_schema_max_rwlock_classes................. 30
performance_schema_max_rwlock_instances............... 1000000
performance_schema_max_table_handles.................. 100000
performance_schema_max_table_instances................ 50000
performance_schema_max_thread_classes................. 50
performance_schema_max_thread_instances............... 1000
pid_file.............................................. D:\ProgramData\MySQL\MySQL Server 5.5\data\p2742624.pid
plugin_dir............................................ D:\Program Files\MySQL\MySQL Server 5.5\lib\plugin\
port.................................................. 3306
preload_buffer_size................................... 32768
profiling............................................. OFF
profiling_history_size................................ 15
protocol_version...................................... 10
query_alloc_block_size................................ 8192
query_cache_limit..................................... 2097152
query_cache_min_res_unit.............................. 4096
query_cache_size...................................... 67108864
query_cache_type...................................... ON
query_cache_wlock_invalidate.......................... OFF
query_prealloc_size................................... 8192
range_alloc_block_size................................ 4096
read_buffer_size...................................... 1048576
read_only............................................. OFF
read_rnd_buffer_size.................................. 524288
relay_log.............................................
relay_log_index.......................................
relay_log_info_file................................... relay-log.info
relay_log_purge....................................... ON
relay_log_recovery.................................... OFF
relay_log_space_limit................................. 0
report_host...........................................
report_password.......................................
report_port........................................... 3306
report_user...........................................
rpl_recovery_rank..................................... 0
secure_auth........................................... OFF
secure_file_priv......................................
server_id............................................. 0
shared_memory......................................... OFF
shared_memory_base_name............................... MYSQL
skip_external_locking................................. ON
skip_name_resolve..................................... OFF
skip_networking....................................... OFF
skip_show_database.................................... OFF
slave_compressed_protocol............................. OFF
slave_exec_mode....................................... STRICT
slave_load_tmpdir..................................... C:\Windows\TEMP
slave_net_timeout..................................... 3600
slave_skip_errors..................................... OFF
slave_transaction_retries............................. 10
slave_type_conversions................................
slow_launch_time...................................... 2
slow_query_log........................................ OFF
slow_query_log_file................................... D:\ProgramData\MySQL\MySQL Server 5.5\data\p2742624-slow.log
socket................................................ MySQL
sort_buffer_size...................................... 524288
sql_auto_is_null...................................... OFF
sql_big_selects....................................... ON
sql_big_tables........................................ OFF
sql_buffer_result..................................... OFF
sql_log_bin........................................... ON
sql_log_off........................................... OFF
sql_low_priority_updates.............................. OFF
sql_max_join_size..................................... 18446744073709551615
sql_mode.............................................. STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_notes............................................. ON
sql_quote_show_create................................. ON
sql_safe_updates...................................... OFF
sql_select_limit...................................... 18446744073709551615
sql_slave_skip_counter................................ 0
sql_warnings.......................................... OFF
ssl_ca................................................
ssl_capath............................................
ssl_cert..............................................
ssl_cipher............................................
ssl_key...............................................
storage_engine........................................ InnoDB
stored_program_cache.................................. 256
sync_binlog........................................... 0
sync_frm.............................................. ON
sync_master_info...................................... 0
sync_relay_log........................................ 0
sync_relay_log_info................................... 0
system_time_zone...................................... Eastern Daylight Time
table_definition_cache................................ 400
table_open_cache...................................... 256
thread_cache_size..................................... 8
thread_concurrency.................................... 10
thread_handling....................................... one-thread-per-connection
thread_stack.......................................... 262144
time_format........................................... %H:%i:%s
time_zone............................................. SYSTEM
timed_mutexes......................................... OFF
tmp_table_size........................................ 133169152
tmpdir................................................ C:\Windows\TEMP
transaction_alloc_block_size.......................... 8192
transaction_prealloc_size............................. 4096
tx_isolation.......................................... REPEATABLE-READ
unique_checks......................................... ON
updatable_views_with_limit............................ YES
version............................................... 5.5.23
version_comment....................................... MySQL Community Server (GPL)
version_compile_machine............................... x86
version_compile_os.................................... Win64
wait_timeout.......................................... 28800
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Steve BinkCommented:
I assume you mean Joomla 2.5.x..?  Since you're running PHP in CGI mode, that means it does not necessarily exit when the script comes to an end.  In turn, that means any MySQL connections being generated may not be properly closed as they should be.  So, MySQL should be stacked with connections, and return a specific error to Joomla.

You can check for this issue in two ways:
1) Check the results of "SHOW PROCESSLIST;" when your site is exhibiting the connection issue.  If you see a large number of connections, there is a good chance this is your problem, or
2) Use trace code inside Joomla to determine the exact reason the connection fails.  That means editing a Joomla core file (either in mysql.php or mysqli.php) to analyze connection_error or connection_errno.  

I prefer method 2, mostly because if this isn't the issue, you'll at least know what MySQL is returning.
0
 
Peter HartCommented:
I dislike installing Joomla on Windows, always problematic.
this link has a list of checks and things you should do on a Windows - Joomla install.
good luck!
http://forum.joomla.org/viewtopic.php?t=52721
0
 
Steve BinkCommented:
@chilternPC: The problem isn't with Joomla, but rather PHP on IIS.  Of all the instructions in the two long posts at your link, the Joomla instructions consist of "unzip and install", while the special considerations for PHP go on and on...  I also definitely prefer a nix platform for my installs.
0
 
skbohlerAuthor Commented:
Isn't this a MySQL issue?
0
 
Steve BinkCommented:
Maybe, or maybe not.  It could be that MySQL is doing exactly what it is supposed to, and that PHP is not closing connections as it should be.  When PHP runs in CGI mode, that means the web server is maintaining a single copy of PHP in memory.  It is bootstrapped on the first request, and when that request ends, PHP just goes into a "waiting" mode.  The next request coming in, regardless of process or thread, is sent to the same running copy of PHP.

This is normally done for performance reasons - it can take awhile for PHP to start up.  But, it does have consequences, such as shared global scopes, etc.  PHP will automatically clean up resources when it exits, but since it never exits, it doesn't have to clean up, which means connections not explicitly closed can remain open.  If the code being run continues to open MySQL connections, it doesn't take long before you've reached your maximum connection limit.  The reason this would manifest as an intermittent problem is that connections can and do time out after a period of time...  essentially, MySQL says, "OK, you're done".

Still, this is just a guess based on your reported symptoms and environment.  The best next step is to observe the *actual* reason MySQL is refusing a connection.
0
 
skbohlerAuthor Commented:
Steve Bink: That does make a lot of sense.

I did notice a hundred or two hundred php-cgi.exe processes running, and we don't get  much traffic at all. Would that be another sign of the problem you highlighted?
0
 
Steve BinkCommented:
>>> I did notice a hundred or two hundred php-cgi.exe processes running

That is another issue, though related.  Each of those processes could be holding an open connection to MySQL, blocking it much the same way.   What web server are you using?
0
 
skbohlerAuthor Commented:
IIS
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.