![\"\"](\"http:\/\/kedar.nitty-witty.com\/wp-content\/uploads\/2023\/06\/Screenshot-2023-06-25-at-5.54.46-PM-edited.png\")
<\/figure>\n<\/div><\/div>\n\n\n\nUnverified variables and experiment<\/h2>\n\n\n\n
I was reviewing the bug MDEV-31470, which focuses on the “wsrep_sst_method” variable accepting literally any value. This variable was supposed to accept only specified set of values but it doesn’t. It seems that there’s a lack of adequate value validation, enabling the assignment of potentially incorrect values.<\/p>\n\n\n\n
I was curious and decided to see what else is really going unchecked! Using a loop in the command line, I attempted to set each database variable to an unexpected value, “change_is_inevitable,” and observed the outcome. The experiment involved testing multiple versions of MariaDB (10.4, 10.6, 11.2), Percona Server 8, and MySQL Community 8.<\/p>\n\n\n\n
# Save commands for variable assignment to a wrong value in a file
mysql -BNe \"show variables\" | awk -F' ' '{print \"set global \" $1 \"=change_is_inevitable;\"}' > db.vars
# Execute the SQLs from file, one by one, verifying status
while read -r line; do echo \"trying \u2026 $line\"; echo $line | mysql; mysqladmin ping | grep -q \"mysqld is alive\" || (echo \" #### looks like mysqld crashed @ $line\" && sleep 5); echo; sleep 0.1; done < db.vars<\/code><\/pre>\n\n\n\nResults of the experiment <\/h2>\n\n\n\nMySQL Community 8.0.33<\/strong><\/h3>\n\n\n\nWe saw many variable getting the wrong value assigned. That said, MySQL was still running. It responded to queries, new connections etc. <\/p>\n\n\n\n
| admin_ssl_ca | change_is_inevitable |\n | admin_ssl_capath | change_is_inevitable |\n | admin_ssl_cert | change_is_inevitable |\n | admin_ssl_cipher | change_is_inevitable |\n | admin_ssl_crl | change_is_inevitable |\n | admin_ssl_crlpath | change_is_inevitable |\n | admin_ssl_key | change_is_inevitable |\n | admin_tls_ciphersuites | change_is_inevitable |\n | general_log_file | change_is_inevitable |\n | init_connect | change_is_inevitable |\n | init_replica | change_is_inevitable |\n | init_slave | change_is_inevitable |\n | innodb_buffer_pool_filename | change_is_inevitable |\n | mandatory_roles | change_is_inevitable |\n | session_track_system_variables | change_is_inevitable |\n | slow_query_log_file | change_is_inevitable |\n | ssl_ca | change_is_inevitable |\n | ssl_capath | change_is_inevitable |\n | ssl_cert | change_is_inevitable |\n | ssl_cipher | change_is_inevitable |\n | ssl_crl | change_is_inevitable |\n | ssl_crlpath | change_is_inevitable |\n | ssl_key | change_is_inevitable |\n | tls_ciphersuites | change_is_inevitable |<\/code><\/pre>\n\n\n\nPercona Server 8.0.33<\/strong><\/h3>\n\n\n\nFollowing variables accepted the wrong value. Also: TIL version = concat(version, version_comment).<\/p>\n\n\n\n
| admin_ssl_ca | change_is_inevitable |\n | admin_ssl_capath | change_is_inevitable |\n | admin_ssl_cert | change_is_inevitable |\n | admin_ssl_cipher | change_is_inevitable |\n | admin_ssl_crl | change_is_inevitable |\n | admin_ssl_crlpath | change_is_inevitable |\n | admin_ssl_key | change_is_inevitable |\n | admin_tls_ciphersuites | change_is_inevitable |\n | buffered_error_log_filename | change_is_inevitable |\n | general_log_file | change_is_inevitable |\n | init_connect | change_is_inevitable |\n | init_replica | change_is_inevitable |\n | init_slave | change_is_inevitable |\n | innodb_buffer_pool_filename | change_is_inevitable |\n | mandatory_roles | change_is_inevitable |\n | session_track_system_variables | change_is_inevitable |\n | slow_query_log_file | change_is_inevitable |\n | ssl_ca | change_is_inevitable |\n | ssl_capath | change_is_inevitable |\n | ssl_cert | change_is_inevitable |\n | ssl_cipher | change_is_inevitable |\n | ssl_crl | change_is_inevitable |\n | ssl_crlpath | change_is_inevitable |\n | ssl_key | change_is_inevitable |\n | tls_ciphersuites | change_is_inevitable |\n | validate_password.dictionary_file | change_is_inevitable |\n | version | 8.0.33-25change_is_inevitable |\n | version_comment | change_is_inevitable |\n | version_suffix | change_is_inevitable |<\/code><\/pre>\n\n\n\nIntroducing Signal 11 Crashes<\/h2>\n\n\n\nMariaDB 10.4.30<\/strong><\/h3>\n\n\n\nThis version causes MySQL to crash when we try setting a value to the variable. After my for-loop caused a crash I manually connected and ran this experiment. MySQL crashes after changing the variable session_track_system_variables<\/strong> while establishing a new connection!<\/p>\n\n\n\nMariaDB [(none)]> set global session_track_system_variables=change_is_inevitable;\nERROR 1193 (HY000): Unknown system variable 'change_is_inevitable'<\/strong>\nMariaDB [(none)]> \\s\n--------------\nmysql Ver 15.1 Distrib 10.4.30-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2\n\nConnection id:\t\t1437\nCurrent database:\nCurrent user:\t\troot@localhost\nSSL:\t\t\tNot in use\nCurrent pager:\t\tstdout\nUsing outfile:\t\t''\nUsing delimiter:\t;\nServer:\t\t\tMariaDB\nServer version:\t\t10.4.30-MariaDB-1:10.4.30+maria~ubu2004-log mariadb.org binary distribution\nProtocol version:\t10\nConnection:\t\tLocalhost via UNIX socket\nServer characterset:\tutf8mb4\nDb characterset:\tutf8mb4\nClient characterset:\tutf8mb4\nConn. characterset:\tutf8mb4\nUNIX socket:\t\t\/var\/run\/mysqld\/mysqld.sock\nUptime:\t\t\t20 hours 24 min 12 sec\n\nThreads: 7 Questions: 2178 Slow queries: 0 Opens: 31 Flush tables: 1 Open tables: 25 Queries per second avg: 0.029\n--------------\n\nMariaDB [(none)]> show global variables like 'session_track_system_variables';\n+--------------------------------+-------+\n| Variable_name | Value |\n+--------------------------------+-------+\n| session_track_system_variables | |\n+--------------------------------+-------+\n1 row in set (0.001 sec)\n\nMariaDB [(none)]> exit\nBye\nroot@ip-172-31-92-119:~# mysql\nERROR 2013 (HY000): Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 11<\/strong>\nroot@ip-172-31-92-119:~#<\/code><\/pre>\n\n\n\n# Stack trace<\/strong>\nThread pointer: 0x7f61ac00f4a8\nAttempting backtrace. You can use the following information to find out\nwhere mysqld died. If you see no messages after this, something went\nterribly wrong...\nstack_bottom = 0x7f61e8d21dd8 thread_stack 0x49000\n\/usr\/sbin\/mysqld(my_print_stacktrace+0x32)[0x555e3fe43252]\n\/usr\/sbin\/mysqld(handle_fatal_signal+0x55d)[0x555e3f8d676d]\n\/lib\/x86_64-linux-gnu\/libpthread.so.0(+0x14420)[0x7f61f514e420]\n\/lib\/x86_64-linux-gnu\/libc.so.6(+0x1886e5)[0x7f61f4d976e5]\n\/usr\/sbin\/mysqld(my_strdup+0x1d)[0x555e3fe3f72d]\n\/usr\/sbin\/mysqld(_ZN23Session_sysvars_tracker4initEP3THD+0x28)[0x555e3f60ec38]\n\/usr\/sbin\/mysqld(_Z18plugin_thdvar_initP3THD+0x1d2)[0x555e3f6da512]\n\/usr\/sbin\/mysqld(_ZN3THD4initEv+0x30)[0x555e3f67ac90]\n\/usr\/sbin\/mysqld(_ZN3THD11change_userEv+0x7c)[0x555e3f67b07c]\n\/usr\/sbin\/mysqld(_ZN3THD15reset_for_reuseEv+0x1e)[0x555e3f67b1fe]\n\/usr\/sbin\/mysqld(_ZN7CONNECT10create_thdEP3THD+0x28)[0x555e3f7cc6e8]\n\/usr\/sbin\/mysqld(_Z29one_thread_per_connection_endP3THDb+0x29d)[0x555e3f5f932d]\n\/usr\/sbin\/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x16b)[0x555e3f7cca4b]\n\/usr\/sbin\/mysqld(handle_one_connection+0x3f)[0x555e3f7ccc2f]\n\/lib\/x86_64-linux-gnu\/libpthread.so.0(+0x8609)[0x7f61f5142609]\n\/lib\/x86_64-linux-gnu\/libc.so.6(clone+0x43)[0x7f61f4d2e133]\n\nTrying to get some variables.\nSome pointers may be invalid and cause the dump to abort.\nQuery (0x0): (null)\nConnection ID (thread ID): 1437\nStatus: NOT_KILLED\n<\/code><\/pre>\n\n\n\nThis also failed in MariaDB 10.6 and latest MariaDB 11 and I have posted the details in the “post script” section.<\/p>\n\n\n\n
Here is the bug report<\/strong>: https:\/\/jira.mariadb.org\/browse\/MDEV-31534<\/p>\n\n\n\n\nTL;DR<\/h2>\n\n\n\n
Setting session_track_system_variables<\/strong> to an out of range values will crash your MariaDB.<\/p>\n\n\n\nDo NOT<\/strong> run on MariaDB:
SET GLOBAL session_track_system_variables<\/strong>=crash_is_inevitable;<\/p>\n<\/div><\/div>\n\n\n\nConclusion<\/h2>\n\n\n\n
The discovery of this bug highlights the importance of thorough validation and error handling within database management systems. Variables should be designed with stringent checks to prevent potential crashes and ensure system stability. By exposing this vulnerability, I have raised awareness of the issue and initiated the bug reporting process. <\/p>\n\n\n\n
Robust variable validation mechanisms must be implemented to protect against similar vulnerabilities in the future. As administrators and developers, it is crucial for us to remain vigilant and actively contribute to improving the reliability and security of our database systems.<\/p>\n\n\n\n
PS<\/h2>\n\n\n\n
MariaDB 10.6.5<\/strong><\/p>\n\n\n\n[root@centos_2 ~]# mysql\nWelcome to the MariaDB monitor. Commands end with ; or \\g.\nYour MariaDB connection id is 7\nServer version: 10.6.5-MariaDB-log MariaDB Server\n\nCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nMariaDB [(none)]> set global session_track_system_variables=change_is_inevitable;\nERROR 1193 (HY000): Unknown system variable 'change_is_inevitable'\nMariaDB [(none)]> exit\nBye\n[root@centos_2 ~]# mysql\nERROR 2013 (HY000): Lost connection to server at 'handshake: reading initial communication packet', system error: 11\n[root@centos_2 ~]#\n\n# Stack trace from error log\nThread pointer: 0x7f178c000a98\nAttempting backtrace. You can use the following information to find out\nwhere mysqld died. If you see no messages after this, something went\nterribly wrong...\nstack_bottom = 0x7f17ac173cc0 thread_stack 0x49000\n??:0(my_print_stacktrace)[0x561e31791a4e]\n??:0(handle_fatal_signal)[0x561e311e1e27]\nsigaction.c:0(__restore_rt)[0x7f17c48c5630]\n:0(__strlen_sse2_pminub)[0x7f17c3e498c1]\n??:0(my_strdup)[0x561e3178e07c]\n??:0(Session_sysvars_tracker::init(THD*))[0x561e30f05f66]\n??:0(THD::init())[0x561e30f6ee9d]\n??:0(THD::change_user())[0x561e30f6f34e]\n??:0(THD::reset_for_reuse())[0x561e30f6f569]\n??:0(CONNECT::create_thd(THD*))[0x561e310bf026]\n??:0(do_handle_one_connection(CONNECT*, bool))[0x561e310bf425]\n??:0(handle_one_connection)[0x561e310bf614]\n??:0(MyCTX_nopad::finish(unsigned char*, unsigned int*))[0x561e31421bb2]\npthread_create.c:0(start_thread)[0x7f17c48bdea5]\n??:0(__clone)[0x7f17c3dd8b0d]\n\nTrying to get some variables.\nSome pointers may be invalid and cause the dump to abort.\nQuery (0x0): (null)\nConnection ID (thread ID): 7\nStatus: NOT_KILLED<\/code><\/pre>\n\n\n\nMariaDB 11<\/strong><\/p>\n\n\n\n\nMariaDB [(none)]> set global session_track_system_variables=change_is_inevitable;\nERROR 1193 (HY000): Unknown system variable 'change_is_inevitable'\nMariaDB [(none)]> exit;\n...\nroot@ip-172-31-92-119:~# mysql\nmysql: Deprecated program name. It will be removed in a future release, use '\/usr\/bin\/mariadb' instead\nERROR 2013 (HY000): Lost connection to server at 'handshake: reading initial communication packet', system error: 11\n\n\n# stack trace from error log\nThread pointer: 0x7f923c000fb8\nAttempting backtrace. You can use the following information to find out\nwhere mysqld died. If you see no messages after this, something went\nterribly wrong...\nstack_bottom = 0x7f92404f7dd8 thread_stack 0x49000\n\/usr\/sbin\/mysqld(my_print_stacktrace+0x32)[0x556cf07df252]\n\/usr\/sbin\/mysqld(handle_fatal_signal+0x55d)[0x556cf027276d]\n\/lib\/x86_64-linux-gnu\/libpthread.so.0(+0x14420)[0x7f924c924420]\n\/lib\/x86_64-linux-gnu\/libc.so.6(+0x1886e5)[0x7f924c56d6e5]\n\/usr\/sbin\/mysqld(my_strdup+0x1d)[0x556cf07db72d]\n\/usr\/sbin\/mysqld(_ZN23Session_sysvars_tracker4initEP3THD+0x28)[0x556ceffaac38]\n\/usr\/sbin\/mysqld(_Z18plugin_thdvar_initP3THD+0x1d2)[0x556cf0076512]\n\/usr\/sbin\/mysqld(_ZN3THD4initEv+0x30)[0x556cf0016c90]\n\/usr\/sbin\/mysqld(_ZN3THD11change_userEv+0x7c)[0x556cf001707c]\n\/usr\/sbin\/mysqld(_ZN3THD15reset_for_reuseEv+0x1e)[0x556cf00171fe]\n\/usr\/sbin\/mysqld(_ZN7CONNECT10create_thdEP3THD+0x28)[0x556cf01686e8]\n\/usr\/sbin\/mysqld(_Z29one_thread_per_connection_endP3THDb+0x29d)[0x556ceff9532d]\n\/usr\/sbin\/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x16b)[0x556cf0168a4b]\n\/usr\/sbin\/mysqld(handle_one_connection+0x3f)[0x556cf0168c2f]\n\/lib\/x86_64-linux-gnu\/libpthread.so.0(+0x8609)[0x7f924c918609]\n\/lib\/x86_64-linux-gnu\/libc.so.6(clone+0x43)[0x7f924c504133]\n\nTrying to get some variables.\nSome pointers may be invalid and cause the dump to abort.\nQuery (0x0): (null)\nConnection ID (thread ID): 1274\nStatus: NOT_KILLED<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"Bugs in database management systems can be a nightmare for administrators and developers. In my recent experiments, I discovered a Signal 11 bug in multiple versions of MariaDB, resulting in…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[869,8,377],"tags":[889,623,890,624,891,621,622],"class_list":{"0":"post-2937","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mariadb","7":"category-mysql","8":"category-mysql-articles","9":"tag-how-to-crash-mysql","10":"tag-mariadb-bug","11":"tag-mariadb-crash","12":"tag-mariadb-signal-11","13":"tag-mysql-crash","14":"tag-mysql-signal-11","15":"tag-signal-11"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2937","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2937"}],"version-history":[{"count":6,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2937\/revisions"}],"predecessor-version":[{"id":2945,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2937\/revisions\/2945"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2937"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2937"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2937"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}