[MDEV-32676] mariadb won't start wsrep anymore Created: 2023-11-04  Updated: 2023-11-07  Resolved: 2023-11-07

Status: Closed
Project: MariaDB Server
Component/s: Galera, Server, wsrep
Affects Version/s: 10.5.21
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Czadek Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: galera, mariadb, server
Environment:

debian 11.8
ii libmariadb3:amd64 1:10.5.21-0+deb11u1 amd64 MariaDB database client library
ii mariadb-backup 1:10.5.21-0+deb11u1 amd64 Backup tool for MariaDB server
ii mariadb-client-10.5 1:10.5.21-0+deb11u1 amd64 MariaDB database client binaries
ii mariadb-client-core-10.5 1:10.5.21-0+deb11u1 amd64 MariaDB database core client binaries
ii mariadb-common 1:10.5.21-0+deb11u1 all MariaDB common configuration files
hi mariadb-server 1:10.5.21-0+deb11u1 all MariaDB database server (metapackage depending on the latest version)
ii mariadb-server-10.5 1:10.5.21-0+deb11u1 amd64 MariaDB database server binaries
ii mariadb-server-core-10.5 1:10.5.21-0+deb11u1 amd64 MariaDB database core server files
hi galera-4 26.4.11-0+deb11u1 amd64 Replication framework for transactional applications


 Description   

Since one node of the three node cluster was restartet it cannot join back the cluster because wsrep is not loaded anymore.
Now trying to bootstrap the cluster another time also doesn't work because the other to nodes after shuting down also can't load theyre wsrep.
All nodes are configured the same except the special options.
What could be here the problem?

MariaDB [(none)]> show status like '%wsrep%';
 
+-------------------------------+----------------------+
 
| Variable_name                 | Value                |
 
+-------------------------------+----------------------+
 
| wsrep                         | 0                    |
 
| wsrep_applier_thread_count    | 0                    |
 
| wsrep_cluster_capabilities    |                      |
 
| wsrep_cluster_conf_id         | 18446744073709551615 |
 
| wsrep_cluster_size            | 0                    |
 
| wsrep_cluster_state_uuid      |                      |
 
| wsrep_cluster_status          | Disconnected         |
 
| wsrep_connected               | OFF                  |
 
| wsrep_local_bf_aborts         | 0                    |
 
| wsrep_local_index             | 18446744073709551615 |
 
| wsrep_provider_capabilities   |                      |
 
| wsrep_provider_name           |                      |
 
| wsrep_provider_vendor         |                      |
 
| wsrep_provider_version        |                      |
 
| wsrep_ready                   | OFF                  |
 
| wsrep_rollbacker_thread_count | 0                    |
 
| wsrep_thread_count            | 0                    |
 
+-------------------------------+----------------------+
 
 
 
MariaDB [(none)]> show variables like 'wsrep%';
 
+---------------------------------+-----------------------------------------------+
 
| Variable_name                   | Value                                         |
 
+---------------------------------+-----------------------------------------------+
 
| wsrep_osu_method                | TOI                                           |
 
| wsrep_sr_store                  | table                                         |
 
| wsrep_auto_increment_control    | ON                                            |
 
| wsrep_causal_reads              | OFF                                           |
 
| wsrep_certification_rules       | strict                                        |
 
| wsrep_certify_nonpk             | ON                                            |
 
| wsrep_cluster_address           |                                               |
 
| wsrep_cluster_name              | my_wsrep_cluster                              |
 
| wsrep_convert_lock_to_trx       | OFF                                           |
 
| wsrep_data_home_dir             | /data/pr-be-mdb-galera02-02/                  |
 
| wsrep_dbug_option               |                                               |
 
| wsrep_debug                     | NONE                                          |
 
| wsrep_desync                    | OFF                                           |
 
| wsrep_dirty_reads               | OFF                                           |
 
| wsrep_drupal_282555_workaround  | OFF                                           |
 
| wsrep_forced_binlog_format      | NONE                                          |
 
| wsrep_gtid_domain_id            | 0                                             |
 
| wsrep_gtid_mode                 | OFF                                           |
 
| wsrep_gtid_seq_no               | 0                                             |
 
| wsrep_ignore_apply_errors       | 7                                             |
 
| wsrep_load_data_splitting       | OFF                                           |
 
| wsrep_log_conflicts             | OFF                                           |
 
| wsrep_max_ws_rows               | 0                                             |
 
| wsrep_max_ws_size               | 2147483647                                    |
 
| wsrep_mysql_replication_bundle  | 0                                             |
 
| wsrep_node_address              |                                               |
 
| wsrep_node_incoming_address     | AUTO                                          |
 
| wsrep_node_name                 | pr-be-mdb-galera02-02                         |
 
| wsrep_notify_cmd                |                                               |
 
| wsrep_on                        | OFF                                           |
 
| wsrep_patch_version             | wsrep_26.22                                   |
 
| wsrep_provider                  | none                                          |
 
| wsrep_provider_options          |                                               |
 
| wsrep_recover                   | OFF                                           |
 
| wsrep_reject_queries            | NONE                                          |
 
| wsrep_replicate_myisam          | OFF                                           |
 
| wsrep_restart_slave             | OFF                                           |
 
| wsrep_retry_autocommit          | 1                                             |
 
| wsrep_slave_fk_checks           | ON                                            |
 
| wsrep_slave_uk_checks           | OFF                                           |
 
| wsrep_slave_threads             | 1                                             |
 
| wsrep_sst_auth                  |                                               |
 
| wsrep_sst_donor                 |                                               |
 
| wsrep_sst_donor_rejects_queries | OFF                                           |
 
| wsrep_sst_method                | rsync                                         |
 
| wsrep_sst_receive_address       | AUTO                                          |
 
| wsrep_start_position            | d72345ad-798f-11ee-a87d-57fff0a6653e:26499564 |
 
| wsrep_strict_ddl                | OFF                                           |
 
| wsrep_sync_wait                 | 0                                             |
 
| wsrep_trx_fragment_size         | 0                                             |
 
| wsrep_trx_fragment_unit         | bytes                                         |
 
+---------------------------------+-----------------------------------------------+
 
 
 
[mysqld]
 
####################################
 
# mysql/mariadb settings
 
 
 
binlog_format            = ROW
 
default-storage-engine   = innodb
 
innodb_autoinc_lock_mode = 2
 
innodb_file_per_table    = on
 
binlog-row-image         = minimal
 
 
 
innodb_doublewrite       = 1
 
 
 
####################################
 
# galera settings
 
 
 
wsrep_on              = ON
 
wsrep_provider        = /usr/lib/galera/libgalera_smm.so
 
wsrep_cluster_name    = "sql-gc02"
 
wsrep_cluster_address = "gcomm://10.49.65.120,10.49.65.121,10.49.65.122"
 
wsrep_node_address    = "10.49.65.121"
 
wsrep_node_name       = "pr-be-mdb-galera02-02"
 
wsrep_sst_method      = "mariabackup"
 
wsrep_sst_auth        = xxxxxxxxxx:xxxxxxxxxxxxxxxxxxxx
 
ignore_db_dirs        = .snapshot
 
wsrep_slave_threads   = 8
 
/etc/mysql/mariadb.conf.d/60-galera.cnf (END)
 
 
 
2023-11-04  8:33:01 0 [Note] Starting MariaDB 10.5.21-MariaDB-0+deb11u1-log source revision bed70468ea08c2820647f5e3ac006a9ff88144ac as process 9674
 
2023-11-04  8:33:01 0 [Warning] The parameter innodb_buffer_pool_instances is deprecated and has no effect.
 
2023-11-04  8:33:01 0 [Warning] The parameter innodb_thread_concurrency is deprecated and has no effect.
 
2023-11-04  8:33:01 0 [Note] InnoDB: Uses event mutexes
 
2023-11-04  8:33:01 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
2023-11-04  8:33:01 0 [Note] InnoDB: Number of pools: 1
 
2023-11-04  8:33:01 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
 
2023-11-04  8:33:01 0 [Note] InnoDB: Using Linux native AIO
 
2023-11-04  8:33:01 0 [Note] InnoDB: Initializing buffer pool, total size = 17179869184, chunk size = 134217728
 
2023-11-04  8:33:02 0 [Note] InnoDB: Completed initialization of buffer pool
 
2023-11-04  8:33:02 0 [Note] InnoDB: 128 rollback segments are active.
 
2023-11-04  8:33:03 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2023-11-04  8:33:03 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2023-11-04  8:33:03 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2023-11-04  8:33:03 0 [Note] InnoDB: 10.5.21 started; log sequence number 1768408011526; transaction id 78141750
 
2023-11-04  8:33:03 0 [Note] InnoDB: Loading buffer pool(s) from /data/pr-be-mdb-galera02-02/ib_buffer_pool
 
2023-11-04  8:33:03 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2023-11-04  8:33:03 0 [Note] Server socket created on IP: '0.0.0.0'.
 
2023-11-04  8:33:03 0 [Warning] 'proxies_priv' entry '@% root@pr-be-mdb-galera02-01' ignored in --skip-name-resolve mode.
 
2023-11-04  8:33:03 0 [Note] Reading of all Master_info entries succeeded
 
2023-11-04  8:33:03 0 [Note] Added new Master_info '' to hash table
 
2023-11-04  8:33:03 5 [Note] Slave I/O thread: Start asynchronous replication to master 'replicator@10.49.65.59:3306' in log 'mysql-bin.086975' at position 4156292
 
2023-11-04  8:33:03 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
Version: '10.5.21-MariaDB-0+deb11u1-log'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Debian 11
 
2023-11-04  8:33:03 5 [Note] Slave I/O thread: connected to master 'replicator@10.49.65.59:3306',replication started in log 'mysql-bin.086975' at position 4156292
 
2023-11-04  8:33:03 6 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.086975' at position 4156292, relay log './pr-be-mdb-galera02-02-relay-bin.001382' position: 3804910
 
2023-11-04  8:33:03 7 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
 
2023-11-04  8:33:03 8 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
 
2023-11-04  8:34:29 0 [Note] InnoDB: Buffer pool(s) load completed at 231104  8:34:29



 Comments   
Comment by Sergei Golubchik [ 2023-11-04 ]

what was changed since it worked last time? did you upgrade anything? any configuration changes?

Comment by Daniel Czadek [ 2023-11-05 ]

hi sergei,

thanks for your reply.
there wasn't any change on the system with the hardware or software packages.
the only thing that was changed because of a failed backup was the innodb_log_file_size on the second server which is used to make the backup.

[00] FATAL ERROR: 2023-11-03 00:17:35 xtrabackup_copy_logfile() failed: redo log block is overwritten, please increase redo log size with innodb_log_file_size parameter.

the other two nodes weren't changed in their configuration.
but why can't any node now start the wsrep?

greetings
daniel

Comment by Daniel Czadek [ 2023-11-07 ]

well now i found it finally after a long search...
it was my fault for higher security because of the password in the config file and the file permissions then:

pr-be-mdb-galera02-02:~$ sudo ls -la /etc/mysql/mariadb.conf.d/
 
total 36
 
drwxr-xr-x 2 root root 4096 Nov  6 11:21 .
 
drwxr-xr-x 4 root root 4096 Oct  9 11:17 ..
 
-rw-r--r-- 1 root root  575 Feb 10  2023 50-client.cnf
 
-rw-r--r-- 1 root root  231 Feb 10  2023 50-mysql-clients.cnf
 
-rw-r--r-- 1 root root  927 Feb 10  2023 50-mysqld_safe.cnf
 
-rw-r--r-- 1 root root 4709 Nov  3 15:15 50-server.cnf
 
-rw-r----- 1 root root  740 Nov  6 11:21 60-galera.cnf
 
-rw-r--r-- 1 root root  125 Oct 31 15:09 70-replication.cnf

now its working normally and ticket can be closed.

Comment by Sergei Golubchik [ 2023-11-07 ]

Thanks for the info! Closed.

Generated at Thu Feb 08 10:33:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.