Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.11
-
MariaDB-10.2.11+maria~jessie-log
Description
When connecting with SSL from ODBC via WLAN , we can connect once and than closing ODBC , but after doing so reconnection get impossible.
We observe some pending connection in login state
847 | unauthenticated user | 192.168.100.50:58464 | NULL | Connect | 4388 | login | NULL | 0.000 |
|
| 878 | unauthenticated user | 192.168.100.50:58934 | NULL | Connect | 4334 | login | NULL | 0.000 |
|
| 914 | unauthenticated user | 192.168.100.50:59452 | NULL | Connect | 4277 | login | NULL | 0.000 |
|
| 920 | unauthenticated user | 192.168.100.50:59644 | NULL | Connect | 4253 | login | NULL | 0.000 |
|
| 925 | unauthenticated user | 192.168.100.50:60098 | NULL | Connect | 4204 | login | NULL | 0.000 |
|
| 926 | unauthenticated user | 192.168.100.50:60162 | NULL | Connect | 4196 | login | NULL | 0.000 |
|
| 945 | unauthenticated user | 192.168.100.50:33364 | NULL | Connect | 4035 | login | NULL | 0.000 |
|
| 967 | unauthenticated user | 192.168.100.50:35166 | NULL | Connect | 3835 | login | NULL | 0.000 |
|
| 972 | unauthenticated user | 192.168.100.50:35490 | NULL | Connect | 3799 | login
|
And database SQL become very slow for all operations, i guess that's an effect of thread pool.
Note VM has only one core, so thread_pool_size is 1
Killing those unauthenticated treads make the database performance back to normal .
So far downgrading to last 10.1 looks a valid workaround
Test was done using
Windows ODBC MySQL 3.51.30 32 bits
Windows ODBC MariaDB 3.0.2 32 bits
Connections to user that don't have USING_SSL never trigger this issue
custom variables
[mariadb]
|
|
|
max_connections = 500
|
max_user_connections = 450
|
thread_cache_size = 32
|
back_log = 128
|
skip_name_resolve = 1
|
max_allowed_packet = 512M
|
|
|
# Disabling symbolic-links is recommended to prevent assorted security risks
|
# symbolic-links = 0
|
sysdate_is_now = 1
|
connect_timeout = 10
|
bind-address = 192.168.100.71
|
|
|
local_infile = 0
|
max_connect_errors = 1000000
|
# pam_use_cleartext_plugin = ON
|
# secure_auth = 1
|
skip_show_database = 1
|
|
|
thread_handling = pool-of-threads
|
extra_port = 3307
|
extra_max_connections = 10
|
|
|
ssl
|
ssl-ca=/etc/mysql/ssl/ca-cert.pem
|
ssl-cert=/etc/mysql/ssl/server-cert.pem
|
ssl-key=/etc/mysql/ssl/server-key.pem
|
|
|
plugin_load_add ="semisync_master.so"
|
plugin_load_add ="semisync_slave.so"
|
|
|
loose_rpl_semi_sync_master = ON
|
loose_rpl_semi_sync_slave = ON
|
loose_rpl_semi_sync_master_enabled = ON
|
loose_rpl_semi_sync_slave_enabled = ON
|
|
|
sql_mode=''
|
|
|
open_files_limit = 65000
|
tmp_table_size = 256M
|
max_heap_table_size = 256M
|
key_buffer_size = 1000M
|
table_open_cache = 1024
|
table_definition_cache = 1024
|
max_allowed_packet = 512M
|
sort_buffer_size = 1M
|
read_buffer_size = 256K
|
join_buffer_size = 128M
|
|
|
innodb_buffer_pool_populate=0
|
innodb_force_primary_key = 1
|
innodb_default_row_format=dynamic
|
innodb_defragment = 1
|
|
|
|
|
transaction-isolation=READ-COMMITTED
|
default_storage_engine = InnoDB
|
innodb_adaptive_hash_index = 0
|
innodb_buffer_pool_size = 256M
|
innodb_buffer_pool_instances = 1
|
innodb_flush_log_at_trx_commit = 1
|
innodb_file_per_table=1
|
innodb_read_io_threads = 1
|
innodb_write_io_threads = 1
|
innodb_io_capacity = 150
|
innodb_io_capacity_max = 300
|
innodb_max_dirty_pages_pct = 10
|
innodb_flush_neighbors = 1
|
innodb_doublewrite = 1
|
innodb_log_file_size = 1024M
|
innodb_log_buffer_size = 256M
|
innodb_log_files_in_group = 4
|
innodb_thread_concurrency = 0
|
innodb_file_format = barracuda
|
innodb_flush_method = O_DIRECT
|
|
|
#innodb_undo_tablespaces = 4
|
innodb_autoinc_lock_mode = 2
|
innodb_stats_on_metadata = 0
|
innodb_buffer_pool_dump_at_shutdown = 1
|
innodb_buffer_pool_load_at_startup = 1
|
innodb_open_files = 16384
|
innodb_purge_threads = 8
|
innodb_change_buffering=none
|
innodb_concurrency_tickets =5000
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
When connecting with SSL from ODBC via WLAN , we can connect once and than closing ODBC , but after doing so reconnection get impossible.
We observe some pending connection in login state 847 | unauthenticated user | 192.168.100.50:58464 | NULL | Connect | 4388 | login | NULL | 0.000 | | 878 | unauthenticated user | 192.168.100.50:58934 | NULL | Connect | 4334 | login | NULL | 0.000 | | 914 | unauthenticated user | 192.168.100.50:59452 | NULL | Connect | 4277 | login | NULL | 0.000 | | 920 | unauthenticated user | 192.168.100.50:59644 | NULL | Connect | 4253 | login | NULL | 0.000 | | 925 | unauthenticated user | 192.168.100.50:60098 | NULL | Connect | 4204 | login | NULL | 0.000 | | 926 | unauthenticated user | 192.168.100.50:60162 | NULL | Connect | 4196 | login | NULL | 0.000 | | 945 | unauthenticated user | 192.168.100.50:33364 | NULL | Connect | 4035 | login | NULL | 0.000 | | 967 | unauthenticated user | 192.168.100.50:35166 | NULL | Connect | 3835 | login | NULL | 0.000 | | 972 | unauthenticated user | 192.168.100.50:35490 | NULL | Connect | 3799 | login And database SQL become very slow for all operations, i guess that's an effect of thread pool. Note VM has only one core, so thread_pool_size is 1 Killing those unauthenticated treads make the database performance back to normal . So far downgrading to last 10.1 looks a valid workaround Test was done using Windows ODBC MySQL 3.51.30 32 bits Windows ODBC MariaDB 3.0.2 32 bits Connections to user that don't have USING_SSL never trigger this issue custom variables [mariadb] max_connections = 500 max_user_connections = 450 thread_cache_size = 32 back_log = 128 skip_name_resolve = 1 max_allowed_packet = 512M # Disabling symbolic-links is recommended to prevent assorted security risks # symbolic-links = 0 sysdate_is_now = 1 connect_timeout = 10 bind-address = 192.168.100.71 local_infile = 0 max_connect_errors = 1000000 # pam_use_cleartext_plugin = ON # secure_auth = 1 skip_show_database = 1 thread_handling = pool-of-threads extra_port = 3307 extra_max_connections = 10 ssl ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem plugin_load_add ="semisync_master.so" plugin_load_add ="semisync_slave.so" loose_rpl_semi_sync_master = ON loose_rpl_semi_sync_slave = ON loose_rpl_semi_sync_master_enabled = ON loose_rpl_semi_sync_slave_enabled = ON sql_mode='' open_files_limit = 65000 tmp_table_size = 256M max_heap_table_size = 256M key_buffer_size = 1000M table_open_cache = 1024 table_definition_cache = 1024 max_allowed_packet = 512M sort_buffer_size = 1M read_buffer_size = 256K join_buffer_size = 128M innodb_buffer_pool_populate=0 innodb_force_primary_key = 1 innodb_default_row_format=dynamic innodb_defragment = 1 transaction-isolation=READ-COMMITTED default_storage_engine = InnoDB innodb_adaptive_hash_index = 0 innodb_buffer_pool_size = 256M innodb_buffer_pool_instances = 1 innodb_flush_log_at_trx_commit = 1 innodb_file_per_table=1 innodb_read_io_threads = 1 innodb_write_io_threads = 1 innodb_io_capacity = 150 innodb_io_capacity_max = 300 innodb_max_dirty_pages_pct = 10 innodb_flush_neighbors = 1 innodb_doublewrite = 1 innodb_log_file_size = 1024M innodb_log_buffer_size = 256M innodb_log_files_in_group = 4 innodb_thread_concurrency = 0 innodb_file_format = barracuda innodb_flush_method = O_DIRECT #innodb_undo_tablespaces = 4 innodb_autoinc_lock_mode = 2 innodb_stats_on_metadata = 0 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1 innodb_open_files = 16384 innodb_purge_threads = 8 innodb_change_buffering=none innodb_concurrency_tickets =5000 |
When connecting with SSL from ODBC via WLAN , we can connect once and than closing ODBC , but after doing so reconnection get impossible.
We observe some pending connection in login state {noformat} 847 | unauthenticated user | 192.168.100.50:58464 | NULL | Connect | 4388 | login | NULL | 0.000 | | 878 | unauthenticated user | 192.168.100.50:58934 | NULL | Connect | 4334 | login | NULL | 0.000 | | 914 | unauthenticated user | 192.168.100.50:59452 | NULL | Connect | 4277 | login | NULL | 0.000 | | 920 | unauthenticated user | 192.168.100.50:59644 | NULL | Connect | 4253 | login | NULL | 0.000 | | 925 | unauthenticated user | 192.168.100.50:60098 | NULL | Connect | 4204 | login | NULL | 0.000 | | 926 | unauthenticated user | 192.168.100.50:60162 | NULL | Connect | 4196 | login | NULL | 0.000 | | 945 | unauthenticated user | 192.168.100.50:33364 | NULL | Connect | 4035 | login | NULL | 0.000 | | 967 | unauthenticated user | 192.168.100.50:35166 | NULL | Connect | 3835 | login | NULL | 0.000 | | 972 | unauthenticated user | 192.168.100.50:35490 | NULL | Connect | 3799 | login {noformat} And database SQL become very slow for all operations, i guess that's an effect of thread pool. Note VM has only one core, so thread_pool_size is 1 Killing those unauthenticated treads make the database performance back to normal . So far downgrading to last 10.1 looks a valid workaround Test was done using Windows ODBC MySQL 3.51.30 32 bits Windows ODBC MariaDB 3.0.2 32 bits Connections to user that don't have USING_SSL never trigger this issue custom variables {noformat} [mariadb] max_connections = 500 max_user_connections = 450 thread_cache_size = 32 back_log = 128 skip_name_resolve = 1 max_allowed_packet = 512M # Disabling symbolic-links is recommended to prevent assorted security risks # symbolic-links = 0 sysdate_is_now = 1 connect_timeout = 10 bind-address = 192.168.100.71 local_infile = 0 max_connect_errors = 1000000 # pam_use_cleartext_plugin = ON # secure_auth = 1 skip_show_database = 1 thread_handling = pool-of-threads extra_port = 3307 extra_max_connections = 10 ssl ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem plugin_load_add ="semisync_master.so" plugin_load_add ="semisync_slave.so" loose_rpl_semi_sync_master = ON loose_rpl_semi_sync_slave = ON loose_rpl_semi_sync_master_enabled = ON loose_rpl_semi_sync_slave_enabled = ON sql_mode='' open_files_limit = 65000 tmp_table_size = 256M max_heap_table_size = 256M key_buffer_size = 1000M table_open_cache = 1024 table_definition_cache = 1024 max_allowed_packet = 512M sort_buffer_size = 1M read_buffer_size = 256K join_buffer_size = 128M innodb_buffer_pool_populate=0 innodb_force_primary_key = 1 innodb_default_row_format=dynamic innodb_defragment = 1 transaction-isolation=READ-COMMITTED default_storage_engine = InnoDB innodb_adaptive_hash_index = 0 innodb_buffer_pool_size = 256M innodb_buffer_pool_instances = 1 innodb_flush_log_at_trx_commit = 1 innodb_file_per_table=1 innodb_read_io_threads = 1 innodb_write_io_threads = 1 innodb_io_capacity = 150 innodb_io_capacity_max = 300 innodb_max_dirty_pages_pct = 10 innodb_flush_neighbors = 1 innodb_doublewrite = 1 innodb_log_file_size = 1024M innodb_log_buffer_size = 256M innodb_log_files_in_group = 4 innodb_thread_concurrency = 0 innodb_file_format = barracuda innodb_flush_method = O_DIRECT #innodb_undo_tablespaces = 4 innodb_autoinc_lock_mode = 2 innodb_stats_on_metadata = 0 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1 innodb_open_files = 16384 innodb_purge_threads = 8 innodb_change_buffering=none innodb_concurrency_tickets =5000 {noformat} |
| Fix Version/s | 10.2 [ 14601 ] | |
| Assignee | Vladislav Vaintroub [ wlad ] |
| issue.field.resolutiondate | 2018-11-07 01:13:10.0 | 2018-11-07 01:13:10.64 |
| Fix Version/s | 10.2.19 [ 23207 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 84616 ] | MariaDB v4 [ 153447 ] |