10.1.44-MariaDB-0ubuntu0.18.04.1 Have a view **volv_action** which does a few joins which has been working for years and was not changed. But last night Mariadb started giving erroneous results if its datetime column is included in the where clause. This started happening in more than one database with this particular view on the same instance each with their own data in the tables (details on that part at bottom). Data that's there: SELECT scene_id, action_date_created FROM volv_action WHERE scene_id=11020; scene_id action_date_created 11020 2020-02-08 07:10:16 11020 2020-02-08 07:14:28 11020 2020-02-08 07:25:04 11020 2020-02-08 07:30:05 11020 2020-02-08 07:39:49 11020 2020-02-08 07:44:10 11020 2020-02-08 07:49:38 11020 2020-02-08 07:53:32 11020 2020-02-08 07:56:49 11020 2020-02-08 08:00:28 11020 2020-02-08 08:05:15 11020 2020-02-08 08:11:19 11020 2020-02-08 08:14:22 With action_date_created doing a comparison in where clause like this gets no rows returned: SELECT scene_id, action_date_created FROM volv_action WHERE scene_id=11020 and action_date_created > '2020-01-01' Change ">" to " '2020-01-01' order by 1 scene_id action_date_created 11020 2020-02-08 07:10:16 11020 2020-02-08 07:14:28 11020 2020-02-08 07:25:04 11020 2020-02-08 07:30:05 11020 2020-02-08 07:39:49 11020 2020-02-08 07:44:10 11020 2020-02-08 07:49:38 11020 2020-02-08 07:53:32 11020 2020-02-08 07:56:49 11020 2020-02-08 08:00:28 11020 2020-02-08 08:05:15 11020 2020-02-08 08:11:19 11020 2020-02-08 08:14:22 A query where "action_date_created is not null" without other clauses on that column gives correct results, but not if there is one of the ">" clauses that causes failure. Querying the underlying table with action_date_created works fine. Spot-checking other views that have similar datetime columns does not reproduce the problem. Analyzed all tables in the database. Have recreated the view, problem persists. Later found it is not just this database on the instance with the problem for this specific view. There is a separate dev database on the same Mariadb instance with an identical data model but different data in it. Checked and it has started showing the same exact problem with its data as well. The queries had worked for years. I know it worked successfully at 11pm last night, but was broken when someone ran one around 2am. We've done a service mysql restart, but same problem persists. I'm perplexed. Any help would be appreciated. Edit to give link to schema code: https://raw.githubusercontent.com/volundmush/mushcode/master/Schema.sql

I have 2 tables with below structures. question table id, audio, s_id 1, ad1, 500 2, ad2, 500 3, ad3, 500 answers table where q_id is FK of question table. id, s_id, q_id, number, answer 1, 500, 1, 088888801, 1 1, 500, 3, 088888801, 3 1, 500, 1, 090078601, 2 1, 500, 2, 090078601, 4 1, 500, 3, 090078601, 2 Lets take this example for better understanding of above structure. I have generated phone calls on 2 numbers (090078601, 088888801), They both have picked up the call. On call, There will be total of 3 questions that will play. As you can see the answer table, Number 088888801 has answered question 1 and 3, he has skip question number 2, if user skip a question, there will be no entry in answer table. But number 090078601 has answered all 3 questions. I want to fetch all record but if a user has skipped the answer, I want to show that this number has skipped that question, Here is my query SELECT r.number, GROUP_CONCAT(COALESCE(r.answer, 'skip')) as answer FROM questions LEFT JOIN answers ON r.q_id= q.id WHERE q.s_id= 500 group by r.number order by q.id ASC This query returns me below | number | answer | | -------- | ----- | | 088888801 | 1,3 | | NULL | NULL | | 090078601 | 2,4,2 | What I actually want is | number | answer | | -------- | ----- | | 088888801 | 1,'skip', 3 | | 090078601 | 2,4,2 | NOTE: I want an optimized query because There can be around 24 questions and more than 200000 numbers, If each number answers 24 questions there will be 4.8M records in answer table. This is just for one S_ID, There will be more records for other s_id, But I want query only on s_id because s_id is unique for each survey that runs.

While replicating from Mariadb 10.1 to MySQL (5.0, 5.1, 5.5) or Mariadb (5.2, 5.5) lower versions, if master's binlog_format is set to row, the replication failure occurs with the following message at slave (show slave status \G;): > Last_Error: Table definition on master and slave does not match: Column 18 type mismatch - received type 19, rtmariadb10.empdetails > has type 11 Here Master: Mariadb 10.1,binlog_format: row ; Slave : Mysql 5.1, binlog_format=statement/row/mixed(any one of these) Can someone please help to solve this issue?

I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is returned. I need the SELECT statement to calculate a column that means "This is the customer's Nth purchase for the day." The data is sorted by customer name and date already, so when the customer name or date changes, I want the counter variable to reset to 1. ---------------------------------------------------------------- | Customer Name | Product | PurchaseDate | PurchaseNumberForDate | ---------------------------------------------------------------- | Customer A | ... | 2019-04-01 | 1 | | Customer A | ... | 2019-04-02 | 1 | | Customer A | ... | 2019-04-03 | 1 | | Customer A | ... | 2019-04-03 | 2 | | Customer A | ... | 2019-04-03 | 3 | | Customer B | ... | 2019-04-03 | 1 | | Customer B | ... | 2019-04-03 | 2 | | Customer B | ... | 2019-04-04 | 1 | ---------------------------------------------------------------- I have tried using MySQL variables, but I cannot figure out how to reset the counter conditionally when the customer name or purchase date change. If I could get the PurchaseNumberForDate correctly calculated, I will use this as a subquery with another query that will select WHERE PurchaseNumberForDate = 1. I have found plenty of examples using COUNT() and @var := @var+1, but I haven't found one based on multiple conditions. Is this possible with MySQL?

I have been reading too many information about InnoDB parameters, but I still have a doubt. Should "innodb_log_buffer_size" be larger than "innodb_buffer_pool_size"?

While Partitioning large tables in Mariadb we want some partitions to be placed in another storage device with different speed and capacity. That is our requirement. For that we are trying to perform some testing in our local set up,where we have placed some partitions of a table(Actually partitions of the innodb tables files, the .ibd ones) outside of the data directory by means of alter table statements with data directory clause. ***But the problem is that whenever we alter the table after partitioning ,the partitioned .ibd files placed outside of datadir comes back to the datadir which is really unexpected.*** So can anyone please help in this context to clarify whether this is a issue with mariadb or with the concept itself described as above.

When I run following command mysqld_safe --skip-grant-tables & I get message `myuser@myvm:~$ 200523 08:24:41 mysqld_safe Logging to syslog. 200523 08:24:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql` After that when I type mysql then I get following error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory") How do I restart mariadb in safe mode to reset root password?

I can't get a simple update or insert to save special äöü characters. Instead they are being stored as äöü. The website displays äöü characters correctly if they are already stored as äöü in the database using phpMyAdmin (or some other tool like HeidiSQL), but for some reason I can't use my code/connection to the database to store them properly. This is where I stand: I added charset=utf8 to my connection string: MM_Connection_STRING = "PROVIDER=MSDASQL;DRIVER={MySQL ODBC 5.1 Driver};port=3306;SERVER=localhost;UID=xxx;PWD=xxx;database=xxx;Option=3;charset=utf8" I added accept-charset="UTF-8" to all my forms. I am running MariaDB and IIS 10. Server version: 10.1.13-MariaDB mariadb.org binary distribution Protocol version: 10 The page gets proxied using Apache 2.4. I am running asp (classic) code. Calling the MySQL server for character and collation, I get this: MariaDB> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+--------------------+ | Variable_name | Value | +--------------------------+--------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | +--------------------------+--------------------+ The code worked when I was using iso-8859-1 as charset. Any help will be very much appropriated. Thank you.

Edit: filed a bug report at https://jira.mariadb.org/browse/MDEV-14493 Mariadb nodes freeze occasionally with the following error in the logs, any clues? (drupal app, db freezes on clearing application cache) [Warning] WSREP: Failed to apply app buffer: seqno: 903792, status: 1 Nov 23 21:42:55 websrv4 mysqld: #011 at galera/src/trx_handle.cpp:apply():351 Nov 23 21:42:55 websrv4 mysqld: Retrying 2th time Nov 23 21:42:55 websrv4 mysqld: 2017-11-23 21:42:55 140081879742208 [Warning] WSREP: BF applier failed to open_and_lock_tables: 1927, fatal: 0 wsrep = (exec_mode: 1 conflict_state: 5 seqno: 903792) Nov 23 21:42:55 websrv4 mysqld: 2017-11-23 21:42:55 140081879742208 [ERROR] Slave SQL: Error executing row event: 'Connection was killed', Internal MariaDB error code: 1927

I have a table called "sessions" with say 4 columns like below

id  name s_time f_time
01  abc  10.15  10.45
02  abc  11.05  11.55
03  abc  12.18  13.46
04  abc  15.12  16.53
05  def  10.01  12.58
06  def  14.06  16.51
07  def  17.43  18.54
08  xyz  09.45  12.36
09  xyz  14.51  15.57
10  xyz  16.23  18.01

How can I get the last f_time for each name? What I need is:

name f_time
abc  16.53
def  18.54
xyz  18.01

What am trying is this: select name,f_time from sessions where name in ('abc','def','xyz') order by id DESC LIMIT 1; but am only getting the finish time for the first name. MariaDB 10.1.37

I have a table with two foreign key constraints as below: mysql> SHOW CREATE TABLE user; CREATE TABLE user ( id int(11) unsigned NOT NULL AUTO_INCREMENT, region_id int(11) unsigned DEFAULT NULL, town_id int(11) unsigned DEFAULT NULL, fullname varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, username varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, email varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, password varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, active tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (id), KEY FK_G38T6P7EKUXYWH1 (region_id), KEY FK_J8VWK0ZN7FD2QX4 (town_id), CONSTRAINT FK_G38T6P7EKUXYWH1 FOREIGN KEY (region_id) REFERENCES region (id) ON UPDATE NO ACTION, CONSTRAINT FK_J8VWK0ZN7FD2QX4 FOREIGN KEY (town_id) REFERENCES town (id) ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I can't drop a foreign key column although I disable FOREIGN_KEY_CHECKS. mysql> ALTER TABLE user DROP COLUMN region_id; 1553 - Cannot drop index 'FK_G38T6P7EKUXYWH1': needed in a foreign key constraint mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | foreign_key_checks | ON | +--------------------+-------+ 1 row in set mysql> SET FOREIGN_KEY_CHECKS = 0; Query OK, 0 rows affected mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | foreign_key_checks | OFF | +--------------------+-------+ 1 row in set mysql> ALTER TABLE user DROP COLUMN region_id; 1828 - Cannot drop column 'region_id': needed in a foreign key constraint 'FK_G38T6P7EKUXYWH1'

MariaDB server experiences some sort of freezes happening sporadically with no visible reason or connection to outside factors (load, etc). After a thorough investigation the common picture for those freezes is the following: 1. There are many different queries (all of them are lightweight select using an index or plain inserts) that got frozen waiting for something. 2. After this *something* is over, queries are processed pretty fast. 3. Freeze lasts for about 5-10 seconds. *** ---TRANSACTION 35712946030, not started ... --TRANSACTION 35712946031, not started flushing log ... ---TRANSACTION 35712945941, not started ... ---TRANSACTION 35712945772, not started flushing log mysql tables in use 1, locked 1 MySQL thread id 1904927, OS thread handle 0x7f437b04b700, query id 88121305 localhost dbu_user query end INSERT INTO t_log ... ---TRANSACTION 35712945932, not started flushing log mysql tables in use 1, locked 1 MySQL thread id 1904895, OS thread handle 0x7f4371568700, query id 88121792 localhost dbu_user query end INSERT INTO t_log ... ---TRANSACTION 35712945902, not started flushing log mysql tables in use 1, locked 1 MySQL thread id 1904887, OS thread handle 0x7f3dace37700, query id 88121710 localhost dbu_user query end INSERT INTO t_log ... ---TRANSACTION 35712946032, not started MySQL thread id 1904891, OS thread handle 0x7f3de3d9d700, query id 88122203 localhost dbu_user ---TRANSACTION 35712946047, not started MySQL thread id 1904899, OS thread handle 0x7f437da37700, query id 88122191 localhost dbu_user ---TRANSACTION 35712945339, not started MySQL thread id 1904898, OS thread handle 0x7f3de3f14700, query id 88122121 localhost dbu_user ... So every INSERT transaction says: not started flushing log Other says: not started More of *SHOW ENGINE INNODB STATUS*: MySQL thread id 1, OS thread handle 0x7f437dacd700, query id 0 Waiting for requests -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 1; buffer pool: 1 6994083 OS file reads, 2285417 OS file writes, 234347 OS fsyncs 11.60 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB 0 RW transactions active inside InnoDB 0 RO transactions active inside InnoDB 0 out of 1000 descriptors used Main thread process no. 1493, id 139904004646656, state: sleeping Number of rows inserted 5295384, updated 429036, deleted 692576, read 1748285626 12.60 inserts/s, 1.55 updates/s, 0.00 deletes/s, 27032.25 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT Most of the times there is the following row for *main process*: Main thread process no. 1493, id 139904004646656, state: flushing log Header says: ===================================== 2021-05-21 17:39:59 7f3df0fff700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 17 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 50917 srv_active, 0 srv_shutdown, 253 srv_idle srv_master_thread log flush and writes: 51168 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 96271 OS WAIT ARRAY INFO: signal count 282014 Mutex spin waits 1951655, rounds 3496412, OS waits 64297 RW-shared spins 238185, rounds 1674512, OS waits 21055 RW-excl spins 81399, rounds 1006087, OS waits 10118 Spin rounds per wait: 1.79 mutex, 7.03 RW-shared, 12.36 RW-excl **SELECT VERSION():** VERSION() 10.1.48-MariaDB-0+deb9u2 **SHOW GLOBAL STATUS:** Variable_name Value *** Aria_pagecache_blocks_not_flushed 0 Aria_pagecache_blocks_unused 15706 Aria_pagecache_blocks_used 15706 Aria_pagecache_read_requests 338868768 Aria_pagecache_reads 30678281 Aria_pagecache_write_requests 69095480 Aria_pagecache_writes 370410 Aria_transaction_log_syncs 13 Binlog_commits 0 Binlog_group_commits 0 Binlog_group_commit_trigger_count 0 Binlog_group_commit_trigger_lock_wait 0 Binlog_group_commit_trigger_timeout 0 Binlog_snapshot_file Binlog_snapshot_position 0 Binlog_bytes_written 0 Binlog_cache_disk_use 0 Binlog_cache_use 0 Binlog_stmt_cache_disk_use 0 Binlog_stmt_cache_use 0 Busy_time 0.000000 Bytes_received 18590540106 Bytes_sent 181560472399 Com_admin_commands 3 Com_alter_db 0 Com_alter_db_upgrade 0 Com_alter_event 0 Com_alter_function 0 Com_alter_procedure 0 Com_alter_server 0 Com_alter_table 59 Com_alter_tablespace 0 Com_analyze 0 Com_assign_to_keycache 0 Com_begin 15 Com_binlog 0 Com_call_procedure 0 Com_change_db 6264 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 14 Com_compound_sql 0 Com_create_db 0 Com_create_event 0 Com_create_function 0 Com_create_index 0 Com_create_procedure 0 Com_create_role 0 Com_create_server 0 Com_create_table 1 Com_create_temporary_table 0 Com_create_trigger 0 Com_create_udf 0 Com_create_user 0 Com_create_view 0 Com_dealloc_sql 0 Com_delete 3 Com_delete_multi 4 Com_do 0 Com_drop_db 0 Com_drop_event 0 Com_drop_function 0 Com_drop_index 0 Com_drop_procedure 0 Com_drop_role 0 Com_drop_server 0 Com_drop_table 1 Com_drop_temporary_table 0 Com_drop_trigger 0 Com_drop_user 0 Com_drop_view 0 Com_empty_query 2 Com_execute_sql 0 Com_flush 0 Com_get_diagnostics 0 Com_grant 0 Com_grant_role 0 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_help 0 Com_insert 1967555 Com_insert_select 847 Com_install_plugin 0 Com_kill 3 Com_load 0 Com_lock_tables 0 Com_optimize 0 Com_preload_keys 0 Com_prepare_sql 0 Com_purge 0 Com_purge_before_date 0 Com_release_savepoint 1 Com_rename_table 0 Com_rename_user 0 Com_repair 0 Com_replace 1 Com_replace_select 0 Com_reset 0 Com_resignal 0 Com_revoke 0 Com_revoke_all 0 Com_revoke_role 0 Com_rollback 0 Com_rollback_to_savepoint 2 Com_savepoint 1 Com_select 29384364 Com_set_option 4278953 Com_show_authors 0 Com_show_binlog_events 0 Com_show_binlogs 0 Com_show_charsets 0 Com_show_collations 50 Com_show_contributors 0 Com_show_create_db 131 Com_show_create_event 0 Com_show_create_func 0 Com_show_create_proc 0 Com_show_create_table 30281755 Com_show_create_trigger 0 Com_show_databases 6 Com_show_engine_logs 0 Com_show_engine_mutex 0 Com_show_engine_status 20 Com_show_errors 0 Com_show_events 30 Com_show_explain 0 Com_show_fields 30282518 Com_show_function_status 1 Com_show_generic 0 Com_show_grants 0 Com_show_keys 119 Com_show_master_status 0 Com_show_open_tables 0 Com_show_plugins 0 Com_show_privileges 0 Com_show_procedure_status 1 Com_show_processlist 116 Com_show_profile 0 Com_show_profiles 0 Com_show_relaylog_events 0 Com_show_slave_hosts 3 Com_show_slave_status 3 Com_show_status 16 Com_show_storage_engines 6 Com_show_table_status 104 Com_show_tables 0 Com_show_triggers 22 Com_show_variables 26 Com_show_warnings 141 Com_shutdown 0 Com_signal 0 Com_start_all_slaves 0 Com_start_slave 0 Com_stmt_close 0 Com_stmt_execute 0 Com_stmt_fetch 0 Com_stmt_prepare 0 Com_stmt_reprepare 0 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_stop_all_slaves 0 Com_stop_slave 0 Com_truncate 6 Com_uninstall_plugin 0 Com_unlock_tables 1 Com_update 319512 Com_update_multi 788 Com_xa_commit 0 Com_xa_end 0 Com_xa_prepare 0 Com_xa_recover 0 Com_xa_rollback 0 Com_xa_start 0 Compression OFF Connection_errors_accept 0 Connection_errors_internal 0 Connection_errors_max_connections 0 Connection_errors_peer_address 0 Connection_errors_select 0 Connection_errors_tcpwrap 0 Connections 2141938 Cpu_time 0.000000 Created_tmp_disk_tables 30318872 Created_tmp_files 38088 Created_tmp_tables 36812016 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Delete_scan 1 Empty_queries 6291088 Executed_events 0 Executed_triggers 0 Feature_delay_key_write 0 Feature_dynamic_columns 0 Feature_fulltext 0 Feature_gis 0 Feature_locale 0 Feature_subquery 2011 Feature_timezone 3 Feature_trigger 1 Feature_xml 0 Flush_commands 1 Handler_commit 31637906 Handler_delete 692578 Handler_discover 52 Handler_external_lock 0 Handler_icp_attempts 19307656516 Handler_icp_match 62206365 Handler_mrr_init 0 Handler_mrr_key_refills 0 Handler_mrr_rowid_refills 0 Handler_prepare 0 Handler_read_first 3284385 Handler_read_key 258101790 Handler_read_last 4146611 Handler_read_next 1298969205 Handler_read_prev 38190 Handler_read_retry 0 Handler_read_rnd 482143308 Handler_read_rnd_deleted 16887 Handler_read_rnd_next 446790716 Handler_rollback 4 Handler_savepoint 1 Handler_savepoint_rollback 2 Handler_tmp_update 29778550 Handler_tmp_write 359275356 Handler_update 440384 Handler_write 23298394 Innodb_available_undo_logs 128 Innodb_background_log_sync 57342 Innodb_buffer_pool_bytes_data 21139406848 Innodb_buffer_pool_bytes_dirty 204390400 Innodb_buffer_pool_dump_status Dumping buffer pool(s) not yet started Innodb_buffer_pool_load_status Loading buffer pool(s) not yet started Innodb_buffer_pool_pages_data 1290247 Innodb_buffer_pool_pages_dirty 12475 Innodb_buffer_pool_pages_flushed 275633 Innodb_buffer_pool_pages_free 20453 Innodb_buffer_pool_pages_lru_flushed 0 Innodb_buffer_pool_pages_made_not_young 133262961 Innodb_buffer_pool_pages_made_young 6416964 Innodb_buffer_pool_pages_misc 0 Innodb_buffer_pool_pages_old 475882 Innodb_buffer_pool_pages_total 1310700 Innodb_buffer_pool_read_ahead 635595 Innodb_buffer_pool_read_ahead_evicted 0 Innodb_buffer_pool_read_ahead_rnd 0 Innodb_buffer_pool_read_requests 3823266111 Innodb_buffer_pool_reads 6800967 Innodb_buffer_pool_wait_free 99906 Innodb_buffer_pool_write_requests 41795423 Innodb_checkpoint_age 198686598 Innodb_checkpoint_max_age 3478212404 Innodb_data_fsyncs 253776 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 0 Innodb_data_pending_writes 0 Innodb_data_read 122639356416 Innodb_data_reads 7485133 Innodb_data_writes 2394663 Innodb_data_written 12082536448 Innodb_dblwr_pages_written 277234 Innodb_dblwr_writes 77724 Innodb_deadlocks 0 Innodb_have_atomic_builtins ON Innodb_history_list_length 29 Innodb_ibuf_discarded_delete_marks 0 Innodb_ibuf_discarded_deletes 0 Innodb_ibuf_discarded_inserts 0 Innodb_ibuf_free_list 232719 Innodb_ibuf_merged_delete_marks 1242585 Innodb_ibuf_merged_deletes 126524 Innodb_ibuf_merged_inserts 63001 Innodb_ibuf_merges 52159 Innodb_ibuf_segment_size 232721 Innodb_ibuf_size 1 Innodb_log_waits 1 Innodb_log_write_requests 4463179 Innodb_log_writes 2031869 Innodb_lsn_current 2668534004820 Innodb_lsn_flushed 2668534003645 Innodb_lsn_last_checkpoint 2668335318222 Innodb_master_thread_active_loops 57090 Innodb_master_thread_idle_loops 253 Innodb_max_trx_id 35716233897 Innodb_mem_adaptive_hash 339996064 Innodb_mem_dictionary 85658368 Innodb_mem_total 22481469440 Innodb_mutex_os_waits 71063 Innodb_mutex_spin_rounds 3743636 Innodb_mutex_spin_waits 2067078 Innodb_oldest_view_low_limit_trx_id 0 Innodb_os_log_fsyncs 64283 Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Innodb_os_log_written 2992949248 Innodb_page_size 16384 Innodb_pages_created 62985 Innodb_pages_read 7484347 Innodb_pages0_read 425 Innodb_pages_written 277234 Innodb_purge_trx_id 35716233820 Innodb_purge_undo_no 0 Innodb_read_views_memory 984 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 526 Innodb_row_lock_time_avg 37 Innodb_row_lock_time_max 282 Innodb_row_lock_waits 14 Innodb_rows_deleted 692576 Innodb_rows_inserted 5357728 Innodb_rows_read 1901348483 Innodb_rows_updated 440384 Innodb_system_rows_deleted 0 Innodb_system_rows_inserted 0 Innodb_system_rows_read 0 Innodb_system_rows_updated 0 Innodb_s_lock_os_waits 21170 Innodb_s_lock_spin_rounds 1678379 Innodb_s_lock_spin_waits 238517 Innodb_truncated_status_writes 0 Innodb_x_lock_os_waits 10269 Innodb_x_lock_spin_rounds 1011254 Innodb_x_lock_spin_waits 81605 Innodb_page_compression_saved 0 Innodb_page_compression_trim_sect512 0 Innodb_page_compression_trim_sect1024 0 Innodb_page_compression_trim_sect2048 0 Innodb_page_compression_trim_sect4096 0 Innodb_page_compression_trim_sect8192 0 Innodb_page_compression_trim_sect16384 0 Innodb_page_compression_trim_sect32768 0 Innodb_num_index_pages_written 323915 Innodb_num_non_index_pages_written 2069541 Innodb_num_pages_page_compressed 0 Innodb_num_page_compressed_trim_op 0 Innodb_num_page_compressed_trim_op_saved 0 Innodb_num_pages_page_decompressed 0 Innodb_num_pages_page_compression_error 0 Innodb_num_pages_encrypted 0 Innodb_num_pages_decrypted 0 Innodb_have_lz4 OFF Innodb_have_lzo OFF Innodb_have_lzma OFF Innodb_have_bzip2 OFF Innodb_have_snappy OFF Innodb_defragment_compression_failures 0 Innodb_defragment_failures 0 Innodb_defragment_count 0 Innodb_onlineddl_rowlog_rows 0 Innodb_onlineddl_rowlog_pct_used 0 Innodb_onlineddl_pct_progress 0 Innodb_secondary_index_triggered_cluster_reads 232038557 Innodb_secondary_index_triggered_cluster_reads_avoided 0 Innodb_buffered_aio_submitted 635595 Innodb_encryption_rotation_pages_read_from_cache 0 Innodb_encryption_rotation_pages_read_from_disk 0 Innodb_encryption_rotation_pages_modified 0 Innodb_encryption_rotation_pages_flushed 0 Innodb_encryption_rotation_estimated_iops 0 Innodb_encryption_key_rotation_list_length 0 Innodb_encryption_n_merge_blocks_encrypted 0 Innodb_encryption_n_merge_blocks_decrypted 0 Innodb_encryption_n_rowlog_blocks_encrypted 0 Innodb_encryption_n_rowlog_blocks_decrypted 0 Innodb_scrub_background_page_reorganizations 0 Innodb_scrub_background_page_splits 0 Innodb_scrub_background_page_split_failures_underflow 0 Innodb_scrub_background_page_split_failures_out_of_filespace 0 Innodb_scrub_background_page_split_failures_missing_index 0 Innodb_scrub_background_page_split_failures_unknown 0 Innodb_encryption_num_key_requests 0 Key_blocks_not_flushed 0 Key_blocks_unused 53569 Key_blocks_used 9 Key_blocks_warm 0 Key_read_requests 85795 Key_reads 9 Key_write_requests 0 Key_writes 0 Last_query_cost 0.000000 Master_gtid_wait_count 0 Master_gtid_wait_time 0 Master_gtid_wait_timeouts 0 Max_statement_time_exceeded 0 Max_used_connections 149 Memory_used 244375568 Not_flushed_delayed_rows 0 Open_files 132 Open_streams 0 Open_table_definitions 413 Open_tables 867 Opened_files 121314326 Opened_plugin_libraries 0 Opened_table_definitions 452 Opened_tables 1031 Opened_views 1695 *** Prepared_stmt_count 0 Qcache_free_blocks 1 Qcache_free_memory 1031336 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 0 Qcache_queries_in_cache 0 Qcache_total_blocks 1 Queries 98665350 Questions 98665350 Rows_read 1901287392 Rows_sent 732824967 Rows_tmp_read 499084097 Rpl_status AUTH_MASTER Select_full_join 0 Select_full_range_join 17548 Select_range 9003432 Select_range_check 0 Select_scan 30293630 *** Slow_launch_threads 0 Slow_queries 11828 Sort_merge_passes 19804 Sort_priority_queue_sorts 869093 Sort_range 5142865 Sort_rows 485949805 Sort_scan 6526377 *** Subquery_cache_hit 8 Subquery_cache_miss 5517 Syncs 36 Table_locks_immediate 55939785 Table_locks_waited 0 Tc_log_max_pages_used 0 Tc_log_page_size 4096 Tc_log_page_waits 0 Threadpool_idle_threads 21 Threadpool_threads 24 Threads_cached 0 Threads_connected 11 Threads_created 2664 Threads_running 3 Update_scan 48 Uptime 61512 Uptime_since_flush_status 61512 *** my.cnf is pretty standard, InnoDB settings: innodb_file_per_table innodb_buffer_pool_size = 20G innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 2 innodb_buffer_pool_instances = 20 innodb_log_file_size = 2G innodb_log_buffer_size = 8M Some output from *mysqltuner.pl*: -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 17h 14m 39s (99M q [1K qps], 2M conn, TX: 170G, RX: 17G) [--] Reads / Writes: 92% / 8% [--] Binary logging is disabled [--] Physical Memory : 62.8G [--] Max MySQL memory : 29.3G [--] Other process memory: 0B [--] Total buffers: 20.5G global + 34.9M per thread (256 max threads) [--] P_S Max memory usage: 105M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 25.6G (40.82% of installed RAM) [OK] Maximum possible memory usage: 29.3G (46.63% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (11K/99M) [OK] Highest usage of available connections: 58% (149/256) [OK] Aborted connections: 0.00% (16/2163778) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (19K temp sorts / 11M sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 82% (30M on disk / 37M total) [--] Thread cache not used with thread_handling=pool-of-threads [OK] Table cache hit rate: 84% (868 open / 1K opened) [OK] table_definition_cache(800) is upper than number of tables(491) [OK] Open file limit used: 5% (132/2K) [OK] Table locks acquired immediately: 100% (56M immediate / 56M locks) **InnoDB Metrics:** -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 20.0G/193.6G [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 2/20.0G should be equal to 25% [OK] InnoDB buffer pool instances: 20 [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [OK] InnoDB Read buffer efficiency: 99.82% (3853979630 hits/ 3860826332 total) [!!] InnoDB Write Log efficiency: 54.38% (2430712 hits/ 4470120 total) [!!] InnoDB log waits: 0.00% (1 waits / 2039408 writes) Everything seems fine, so I must be missing something out. Asking for advice. **UPDATE.** Some extra weird data and mutex stats: ===================================== 2021-05-22 21:17:29 7fb9de7ff700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 46933 srv_active, 0 srv_shutdown, 26232 srv_idle srv_master_thread log flush and writes: 73161 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 83146 --Thread 140435913168640 has waited at trx0sys.ic line 103 for 4.0000 seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x7fbf117fe968 '&block->lock' a writer (thread id 140435913168640) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file buf0flu.cc line 1126 Last time write locked in file trx0sys.ic line 103 Holder thread 0 file not yet reserved line 0 --Thread 140435783866112 has waited at trx0trx.cc line 1057 for 4.0000 seconds the semaphore: Mutex at 0x7fbf66bbc068 '&trx_sys->mutex', lock var 1 Last time reserved by thread 140435913168640 in file not yet reserved line 0, waiters flag 1 --Thread 140459985676032 has waited at trx0trx.cc line 1057 for 4.0000 seconds the semaphore: Mutex at 0x7fbf66bbc068 '&trx_sys->mutex', lock var 1 Last time reserved by thread 140435913168640 in file not yet reserved line 0, waiters flag 1 --Thread 140435788166912 has waited at trx0trx.cc line 1057 for 4.0000 seconds the semaphore: Mutex at 0x7fbf66bbc068 '&trx_sys->mutex', lock var 1 Last time reserved by thread 140435913168640 in file not yet reserved line 0, waiters flag 1 --Thread 140435799533312 has waited at trx0trx.cc line 1057 for 4.0000 seconds the semaphore: ... (post length limits) OS WAIT ARRAY INFO: signal count 171303 Mutex spin waits 566839, rounds 1843729, OS waits 48137 RW-shared spins 954015, rounds 4867601, OS waits 18698 RW-excl spins 247858, rounds 2764788, OS waits 14386 Spin rounds per wait: 3.25 mutex, 5.10 RW-shared, 11.15 RW-excl **Mutex stats:** show engine innodb mutex Type Name Status ... InnoDB &ibuf_mutex os_waits=8 InnoDB &dict_sys->mutex os_waits=152 InnoDB &trx_sys->mutex os_waits=3802 InnoDB &lock_sys->mutex os_waits=95 InnoDB &log_sys->mutex os_waits=11308 InnoDB &buf_pool->flush_state_mutex os_waits=1613 InnoDB &buf_pool->LRU_list_mutex os_waits=7 InnoDB &buf_pool->flush_state_mutex os_waits=2599 InnoDB &buf_pool->LRU_list_mutex os_waits=22 InnoDB &buf_pool->flush_state_mutex os_waits=1406 InnoDB &buf_pool->LRU_list_mutex os_waits=7 InnoDB &buf_pool->flush_state_mutex os_waits=8609 InnoDB &buf_pool->LRU_list_mutex os_waits=63 InnoDB &buf_pool->flush_state_mutex os_waits=428 InnoDB &buf_pool->LRU_list_mutex os_waits=3 InnoDB &buf_pool->flush_state_mutex os_waits=1317 InnoDB &buf_pool->flush_state_mutex os_waits=445 InnoDB &buf_pool->LRU_list_mutex os_waits=1 InnoDB &buf_pool->flush_state_mutex os_waits=3218 InnoDB &buf_pool->LRU_list_mutex os_waits=3 InnoDB &buf_pool->flush_state_mutex os_waits=519 InnoDB &buf_pool->LRU_list_mutex os_waits=3 InnoDB &buf_pool->flush_state_mutex os_waits=628 InnoDB &buf_pool->LRU_list_mutex os_waits=15 InnoDB &buf_pool->flush_state_mutex os_waits=1738 InnoDB &buf_pool->LRU_list_mutex os_waits=4 InnoDB &buf_pool->flush_state_mutex os_waits=4458 InnoDB &buf_pool->LRU_list_mutex os_waits=18 InnoDB &buf_pool->flush_state_mutex os_waits=362 InnoDB &buf_pool->LRU_list_mutex os_waits=4 InnoDB &buf_pool->flush_state_mutex os_waits=214 InnoDB &buf_pool->LRU_list_mutex os_waits=2 InnoDB &buf_pool->flush_state_mutex os_waits=175 InnoDB &buf_pool->LRU_list_mutex os_waits=1 InnoDB &buf_pool->flush_state_mutex os_waits=1310 InnoDB &buf_pool->LRU_list_mutex os_waits=5 InnoDB &buf_pool->flush_state_mutex os_waits=1268 InnoDB &buf_pool->LRU_list_mutex os_waits=15 InnoDB &buf_pool->flush_state_mutex os_waits=110 InnoDB &buf_pool->LRU_list_mutex os_waits=1 InnoDB &buf_pool->flush_state_mutex os_waits=80 InnoDB &buf_pool->flush_state_mutex os_waits=497 InnoDB &buf_pool->LRU_list_mutex os_waits=4 InnoDB &fil_system->mutex os_waits=157 InnoDB &srv_sys.mutex os_waits=59 InnoDB combined &block->mutex os_waits=129 InnoDB &new_index->lock os_waits=2 InnoDB &new_index->lock os_waits=274 InnoDB &new_index->lock os_waits=332 InnoDB &new_index->lock os_waits=480 InnoDB &new_index->lock os_waits=4 InnoDB &new_index->lock os_waits=2 InnoDB &new_index->lock os_waits=11 InnoDB &new_index->lock os_waits=3 InnoDB &new_index->lock os_waits=3 InnoDB &new_index->lock os_waits=484 InnoDB &new_index->lock os_waits=1 InnoDB &new_index->lock os_waits=1 InnoDB &space->latch os_waits=17 InnoDB &space->latch os_waits=2 InnoDB &space->latch os_waits=1 InnoDB &new_index->lock os_waits=28 InnoDB &dict_operation_lock os_waits=78 InnoDB &log_sys->checkpoint_lock os_waits=5379 InnoDB &btr_search_latch_arr[i] os_waits=16938 InnoDB table->sync_obj.rw_locks + i os_waits=13 InnoDB table->sync_obj.rw_locks + i os_waits=6 InnoDB table->sync_obj.rw_locks + i os_waits=1 InnoDB table->sync_obj.rw_locks + i os_waits=8 InnoDB table->sync_obj.rw_locks + i os_waits=5 InnoDB table->sync_obj.rw_locks + i os_waits=1 InnoDB table->sync_obj.rw_locks + i os_waits=23 InnoDB table->sync_obj.rw_locks + i os_waits=5 InnoDB table->sync_obj.rw_locks + i os_waits=10 InnoDB table->sync_obj.rw_locks + i os_waits=6 InnoDB combined &block->lock os_waits=2609 **UPDATE (following *Rick James* answer).** Thanks for the directions on tackling the problem, has already switched long_query_time to 1sec. The weirdest thing about the case is that there is no visible link to any activity or parameter external to the database. It happens under different amounts of load, both low and medium. The following queries take more than a second to complete (according to slow log): SHOW FULL COLUMNS FROM tbl_xyz; SELECT * FROM tbl_index t WHERE t.term='weird'; // term is a PK I know my system inside out, each and every query was thoroughly designed, tested, stress-tested, and watched closely after being deployed to production. The system handles pretty heavy tasks during nightly jobs just fine. These freezes/stalls are happening unpredictably and they don't seem to be connected with queries being executed on the database. Sounds crazy? That's exactly how it is. I was thinking of a bug, but I don't have any idea of how to prove it. The database is updated to the latest available for Debian Stretch: 10.1.48-MariaDB-0+deb9u2. Hardware fault was an option too, but monitoring shows no signs of it. My backup plan is to upgrade to 10.3.27-0+deb10u1, which requires some heavy maintenance activity. Keeping it as a last resort. **P.S.** There was a hardware issue (SSD) about a month ago. Has done *mysqlanalyze/mysqlcheck*, recovered one of the tables (by redeploying it from scratch), everything is in OK status since then. Not sure if it has any effect of happening now, but putting it there just in case.

How to restart MariaDB Galera cluster? After all node have been crashed I try to recover the cluster but without success. I have only 2 nodes. As documentation says I set a parameter on one of the node: set global wsrep_provider_options="pc.bootstrap=true"; And then try to start first node: systemctl start mariadb After that I get an error: Oct 11 16:11:12 proxy1 sh: 2016-10-11 16:11:12 140291677038720 [Note] /usr/sbin/mysqld (mysqld 10.1.18-MariaDB) starting as process 2402 ... Oct 11 16:11:15 proxy1 sh: WSREP: Recovered position b6c1dc93-8fa7-11e6-933e-e64cd44e3be0:141 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] /usr/sbin/mysqld (mysqld 10.1.18-MariaDB) starting as process 2434 ... Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Read nil XID from storage engines, skipping position init Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera/libgalera_smm.so' Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: wsrep_load(): Galera 25.3.18(r3632) by Codership Oy loaded successfully. Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: CRC-32C: using hardware acceleration. Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Found saved state: b6c1dc93-8fa7-11e6-933e-e64cd44e3be0:-1 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 192.168.0.41; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140046790919936 [Note] WSREP: Service thread queue flushed. Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Assign initial position for certification: 141, protocol version: -1 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: wsrep_sst_grab() Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Start replication Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Setting initial position to b6c1dc93-8fa7-11e6-933e-e64cd44e3be0:141 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: protonet asio version 0 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Using CRC-32C for message checksums. Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: backend: asio Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: gcomm thread scheduling priority set to other:0 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Warning] WSREP: access file(/var/lib/mysql//gvwstate.dat) failed(No such file or directory) Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: restore pc from disk failed Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: GMCast version 0 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') multicast: , ttl: 1 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: EVS version 0 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: gcomm: connecting to group 'test_cluster', peer '192.168.0.41:,192.168.0.42:' Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') connection established to 30a7b2e6 tcp://192.168.0.41:4567 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Warning] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') address 'tcp://192.168.0.41:4567' points to own listening address, blacklisting Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') connection established to 30a7b2e6 tcp://192.168.0.41:4567 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') connection established to 1ef15511 tcp://192.168.0.42:4567 Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: declaring 1ef15511 at tcp://192.168.0.42:4567 stable Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Warning] WSREP: no nodes coming from prim view, prim not possible Oct 11 16:11:15 proxy1 mysqld: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: view(view_id(NON_PRIM,1ef15511,2) memb { Oct 11 16:11:15 proxy1 mysqld: 1ef15511,0 Oct 11 16:11:15 proxy1 mysqld: 30a7b2e6,0 Oct 11 16:11:15 proxy1 mysqld: } joined { Oct 11 16:11:15 proxy1 mysqld: } left { Oct 11 16:11:15 proxy1 mysqld: } partitioned { Oct 11 16:11:15 proxy1 mysqld: }) Oct 11 16:11:18 proxy1 mysqld: 2016-10-11 16:11:18 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') turning message relay requesting off Oct 11 16:11:19 proxy1 mysqld: 2016-10-11 16:11:19 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.0.42:4567 Oct 11 16:11:20 proxy1 mysqld: 2016-10-11 16:11:20 140047023368320 [Note] WSREP: forgetting 1ef15511 (tcp://192.168.0.42:4567) Oct 11 16:11:20 proxy1 mysqld: 2016-10-11 16:11:20 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') turning message relay requesting off Oct 11 16:11:20 proxy1 mysqld: 2016-10-11 16:11:20 140047023368320 [Warning] WSREP: no nodes coming from prim view, prim not possible Oct 11 16:11:20 proxy1 mysqld: 2016-10-11 16:11:20 140047023368320 [Note] WSREP: view(view_id(NON_PRIM,30a7b2e6,3) memb { Oct 11 16:11:20 proxy1 mysqld: 30a7b2e6,0 Oct 11 16:11:20 proxy1 mysqld: } joined { Oct 11 16:11:20 proxy1 mysqld: } left { Oct 11 16:11:20 proxy1 mysqld: } partitioned { Oct 11 16:11:20 proxy1 mysqld: 1ef15511,0 Oct 11 16:11:20 proxy1 mysqld: }) Oct 11 16:11:25 proxy1 mysqld: 2016-10-11 16:11:25 140047023368320 [Note] WSREP: cleaning up 1ef15511 (tcp://192.168.0.42:4567) Oct 11 16:11:46 proxy1 mysqld: 2016-10-11 16:11:46 140047023368320 [Note] WSREP: view((empty)) Oct 11 16:11:46 proxy1 mysqld: 2016-10-11 16:11:46 140047023368320 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out) Oct 11 16:11:46 proxy1 mysqld: at gcomm/src/pc.cpp:connect():162 Oct 11 16:11:46 proxy1 mysqld: 2016-10-11 16:11:46 140047023368320 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out) Oct 11 16:11:46 proxy1 mysqld: 2016-10-11 16:11:46 140047023368320 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1380: Failed to open channel 'test_cluster' at 'gcomm://192.168.0.41,192.168.0.42': -110 (Connection timed out) Oct 11 16:11:46 proxy1 mysqld: 2016-10-11 16:11:46 140047023368320 [ERROR] WSREP: gcs connect failed: Connection timed out Oct 11 16:11:46 proxy1 mysqld: 2016-10-11 16:11:46 140047023368320 [ERROR] WSREP: wsrep::connect(gcomm://192.168.0.41,192.168.0.42) failed: 7 Oct 11 16:11:46 proxy1 mysqld: 2016-10-11 16:11:46 140047023368320 [ERROR] Aborting Oct 11 16:11:47 proxy1 systemd: mariadb.service: main process exited, code=exited, status=1/FAILURE Oct 11 16:11:47 proxy1 systemd: Failed to start MariaDB database server. -- Subject: Unit mariadb.service has failed -- Defined-By: systemd -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel -- -- Unit mariadb.service has failed. -- -- The result is failed. Oct 11 16:11:47 proxy1 systemd: Unit mariadb.service entered failed state. Oct 11 16:11:47 proxy1 systemd: mariadb.service failed. Oct 11 16:11:47 proxy1 polkitd: Unregistered Authentication Agent for unix-process:2360:148848 (system bus name :1.15, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US.UTF-8) (disconnected from bus) How to recover a cluster?

When I create a new user in MariaDB, I cannot login as that user. First I login to the database as root doing mariadb in a bash terminal. I then run the following commands to create a database and a user:

CREATE DATABASE mydb;
grant all privileges on mydb.* TO 'myuser'@'localhost' identified by 'password';
FLUSH PRIVILEGES;

When I then run SELECT user,host,plugin FROM mysql.user;, it prints the following:

+-----------+-----------+-------------+
| user      | host      | plugin      |
+-----------+-----------+-------------+
| root      | localhost | unix_socket |
| myuser    | localhost |             |
+-----------+-----------+-------------+

However, when I then try to login as that user doing mariadb -u myuser mydb -p, I have no luck.

ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)

I have no idea what is happening. I am running the following MariaDB version:

mysqld  Ver 10.1.44-MariaDB-0ubuntu0.18.04.1 for debian-linux-gnu on x86_64 (Ubuntu 18.04)

I'm using 10.1.25-MariaDB-1~xenial, but engine is innodb though. I'm performing an alter by default it does ONLINE alter. When I add column it takes at least 50 mins for 50G table in our infrastructure, but when I performed removing and adding a composite index of 3 columns it took 8 mins. Any idea internals of what it is doing? Also does the duration changes if I'm adding an PK index/UK index.

I have read many different questions about slow MariaDB/MySQL shutdown, but I have not found an answer which solves my problem yet. On one of our servers with the highest load, when I issue a stop command for MariaDB, in the logfile I see: 2020-05-26 13:26:02 140304972457728 [Note] /opt/lampp/sbin/mysqld: Normal shutdown 2020-05-26 13:26:02 140304972457728 [Note] Event Scheduler: Purging the queue. 0 events From there, it won't move, at least not in the 15-20 minutes I waited, and I usually have to kill the mysqld daemon. By following other questions and answers found online, the shutdown command is given through the mysqladmin command via TCP (instead of socket), and also I check that the dirty pages are at or close to zero. mysqladmin --user=shutdown --password=xxx --port=3306 --host=127.0.0.1 --protocol=tcp shutdown The log does not show any specific error or warning message which might explain such a slow shutdown, and when monitoring CPU/RAM usage during the command, the server seems just idle. I also checked the OS logs, but found nothing unusual. Finally, the server uses MariaDB 10.1.36 and hosts 10 databases, each a clone of the other, which are made of ~2000 tables each, all InnoDB; it's a dual core server with 8 GB RAM and CentOS 7.8 as its OS. innodb_fast_shutdown is set to 1. What can I do to lower the database shutdown time to a more acceptable 1-2 minutes? Here is the database configuration from my.cnf: innodb_data_home_dir = /opt/agews64/data/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /opt/agews64/data/mysql/ innodb_lock_wait_timeout = 60 innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_large_prefix = 1 innodb_fast_shutdown = 1 default_storage_engine = InnoDB innodb_flush_log_at_trx_commit = 1 sync_binlog = 0 innodb_flush_method = O_DIRECT #innodb_thread_concurrency = 8 innodb_io_capacity = 1000 innodb_io_capacity_max = 2000 innodb_stats_on_metadata = 0 innodb_buffer_pool_dump_at_shutdown = 0 innodb_buffer_pool_load_at_startup = 0 innodb_buffer_pool_dump_pct = 100 innodb_adaptive_hash_index_partitions = 8 innodb_checksum_algorithm = crc32 innodb_log_checksum_algorithm = crc32 #table_open_cache_instances = 16 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_max_dirty_pages_pct = 90 innodb_max_dirty_pages_pct_lwm = 10 max_connections = 20 sort_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 4M join_buffer_size = 4M thread_stack = 512K binlog_cache_size = 32K myisam_sort_buffer_size = 1M net_buffer_length = 16K wait_timeout = 3600 interactive_timeout = 3600 # BUFFER GLOBALI key_buffer_size = 64M query_cache_type = 0 query_cache_size = 0 tmp_table_size = 64M max_heap_table_size = 64M aria_pagecache_buffer_size = 128M innodb_buffer_pool_size = 3072M innodb_buffer_pool_instances = 3 innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_log_buffer_size = 64M table_open_cache = 30000 table_definition_cache = 15400 open_files_limit = 90000 innodb_open_files = 90000 thread_cache_size = 8 thread_handling = one-thread-per-connection max_sp_recursion_depth = 16 log_output = NONE general_log = 0 slow_query_log = 0 #slow_query_log_file = /opt/agews64/logs/slow_queries.log long_query_time = 3 min_examined_row_limit = 0 log_queries_not_using_indexes = 0 log_slow_admin_statements = 0 plugin_load_add = query_response_time query_response_time_stats = 0 userstat = 0 #innodb_monitor_enable = all performance_schema = 1 performance_schema_instrument = "%=off" performance_schema_consumer_global_instrumentation = 0 performance_schema_consumer_thread_instrumentation = 0 performance_schema_consumer_events_stages_current = 0 performance_schema_consumer_events_stages_history = 0 performance_schema_consumer_events_stages_history_long = 0 performance_schema_consumer_events_statements_current = 0 performance_schema_consumer_events_statements_history = 0 performance_schema_consumer_events_statements_history_long = 0 performance_schema_consumer_events_waits_current = 0 performance_schema_consumer_events_waits_history = 0 performance_schema_consumer_events_waits_history_long = 0 performance_schema_consumer_statements_digest = 0 performance_schema_events_stages_history_size = 100 performance_schema_events_stages_history_long_size = 10000 performance_schema_events_statements_history_size = 100 performance_schema_events_statements_history_long_size = 10000 performance_schema_events_waits_history_size = 100 performance_schema_events_waits_history_long_size = 10000 performance_schema_digests_size = -1 plugin_load_add = "federated=ha_federatedx.so" # gestione della criptazione dei dati plugin_load_add = file_key_management file_key_management_filename = /opt/agews64/data/mysql/.keys file_key_management_filekey = FILE:/opt/agews64/etc/mysql/.key file_key_management_encryption_algorithm = aes_ctr innodb_encrypt_tables = OFF innodb_encrypt_log = 0 encrypt_tmp_disk_tables = 0 encrypt_tmp_files = 0 encrypt_binlog = 0

**Question:** Does anyone have advice on what I can do to successfully get this new replica set up and replicating from our production server just like the old replica? ---------- **Details:** I am trying to set up a new MariaDB replica from an existing replica so that we do not have to take the production server down to create the new replica. Here is the procedure I go through to import the DB to the new host. 1. On the current replica: MariaDB> STOP SLAVE; 2. On the current replica: $ /usr/bin/mysql -u $USER -p $PASS -e 'SHOW SLAVE STATUS \G' > slave_status.txt 3. On the new replica: $ /usr/bin/mysqldump --add-drop-database --all-databases -u $USER -p $PASS -h $CURRENT_REPLICA --flush-privileges | /usr/bin/mysql -u root 4. On the current replica: Make sure that the MariaDB> SHOW SLAVE STATUS \G output is still the same as it was in slave_status.txt 5. Grab the Relay_Master_Log_File, Master_Host, Master_User, and Exec_Master_Log_Pos values from slave_status.txt 6. On the new replica: MariaDB> CHANGE MASTER TO MASTER_HOST='$Master_Host', MASTER_USER='$Master_User', MASTER_PASSWORD='$Master_Password', MASTER_LOG_FILE='$Relay_Master_Log_File', MASTER_LOG_POS=$Exec_Master_Log_Pos; 7. On the new replica: MariaDB> SHOW SLAVE STATUS \G and compare to slave_status.txt... All looks good 8. On the new replica: MariaDB> START SLAVE; Once the replication has been running for a little bit, usually a few minutes, I get error #1451 below > Error 'Cannot delete or update a parent row: a foreign key constraint fails ('$DATABASE'.'$TABLE', CONSTRAINT '$TABLE_ibfk_3' FOREIGN KEY ('$KEY') REFERENCES '$OTHER_TABLE' ('$OTHER_TABLE_KEY') ON UPDATE CASCADE)' on query. Default database: '$DATABASE'. Query: 'DELETE FROM $OTHER_TABLE WHERE $OTHER_TABLE_KEY in (#########, #########, #########, #########, #########, ... There are multiple databases hosted in this MariaDB instance and every time I have done this process it is the same database that runs into this foreign key dependency error. The current replica has been running for years without a problem. I have tried the same MariaDB version (10.1.12) on the new host as the old host, as well as a newer version (10.4.17) on the new host. The database which results in this error is using InnoDB. I have verified that /etc/my.cnf and /etc/my.cnf.d/* are the same except for the server ID for the two replicas. One of the first times I ran through this process I copy-pasted the failed command from the error log and it ran without a problem, I then set the Skip_Counter to 1 and it continued for a while and got the same error. I did this 5 times before giving up on that as a method to proceed. If there is any other useful information I can provide, please let me know. I tried to include everything I could think of but I wouldn't be surprised if I missed something useful.

Slow query is enabled but only logs few queries when you retrieve the slow log. It’s been working ok all last week, suddenly this week, it only logs just 2 or 3 queries. Long query time = 0.01 and I expect to see anything above 1sec but this is not the case. I had to query performance schema table to view long running queries instead of the slow log. What could have changed?

I am facing an issue with MariaDB 10.1 on a log table with the following (simplified) structure: CREATE TABLE logs ( log_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, entity_type VARCHAR(10), entity_id INT UNSIGNED, field VARCHAR(30), old_value VARCHAR(255), new_value VARCHAR(255), log_message TEXT DEFAULT NULL, user_id INT UNSIGNED, INDEX (entity_type, entity_id), INDEX (entity_type, field), INDEX (log_date), INDEX (user_id) ) ENGINE=InnoDB On one of our customers' database, where the table as almost 50 million rows, I am seeing a strange result, for which a query which there are no rows takes a much longer time than one where there are rows. For example: SELECT * FROM logs WHERE entity_type = 'employee' AND entity_id = ID_FOR_WHICH_LOGS_EXIST ORDER BY log_date DESC, log_id DESC LIMIT 10 gives a result in about 2-3 seconds, here its EXPLAIN output: +------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | logs | index | entity_type,entity_type_2 | log_date | 21 | NULL | 2094 | 100.00 | Using where | +------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+ While another query SELECT * FROM logs WHERE entity_type = 'employee' AND entity_id = INVALID_ID_OR_WITHOUT_LOGS ORDER BY log_date DESC, log_id DESC LIMIT 10 takes almost 5 minutes to complete, here is the EXPLAIN output, identical to the previous query: +------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | logs | index | entity_type,entity_type_2 | log_date | 21 | NULL | 2094 | 100.00 | Using where | +------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+ I have noticed that, in the second case, removing the LIMIT clause brings the execution time to a few milliseconds; the same happens if I change the LIMIT to have an offset, for example LIMIT 10, 10. **Is there anything wrong with my queries or table structure (and, if so, how could I fix it?), or is it an issue with the database itself?** EDIT: I have just noticed that also the first query, if used with a LIMIT clause without offset, is slower than the same query with an offset, the execution time goes from 2-3 seconds to about 0.01 seconds.

When I execute the following: mysqldump -u root -n mydatabase > mydatabase.sql The tables are all dumped without the database name, but the views oddly get the database name prepended: /*!50001 DROP TABLE IF EXISTS vfiscalperiodcombobox*/; /*!50001 DROP VIEW IF EXISTS vfiscalperiodcombobox*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8 */; /*!50001 SET character_set_results = utf8 */; /*!50001 SET collation_connection = utf8_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=root@localhost SQL SECURITY DEFINER */ /*!50001 VIEW mydatabase.vfiscalperiodcombobox AS select mydatabase.fiscalperiod.name AS text,mydatabase.fiscalperiod.id AS value from mydatabase.fiscalperiod order by mydatabase.fiscalperiod.start */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; Because of this I cannot import this file into another database (without manually removing the database names from the file). This happens with the following MariaDB version on Windows: mysql Ver 15.1 Distrib 10.1.9-MariaDB, for Win32 (AMD64) It does NOT happen with the following version on Debian: mysql Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Is this simply a bug? Or am I doing something wrong? Or is there a flag with which I can force mysqldump to never prepend the database name?