No wsrep and general variables after upgrade

soprano · September 19, 2023, 4:42pm

Hi all,

we upgraded our Percona Galera Xtradb Clusters (2 different cluster, one on CentOS and one on Ubuntu) to the version 5.7.43-31.65-1 and on both servers we got

performance scheme wrong structure

we fixed the error with mysql_upgrade (but i had to force it because we use InnoDB engine enforced) and we restarted all the cluster nodes

after that on both cluster we have no wsrep variables anymore

mysql> SHOW STATUS LIKE ‘%wsrep%’;
Empty set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE ‘%wsrep%’;
Empty set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE ‘%wsrep%’;
Empty set (0.00 sec)

in general also the

mysql> show variables;
Empty set (0.00 sec)

shows no variables on all nodes

our scripts based on them are not working, but the cluster seems to work

how i can fix the problem ?

We also tried to stop the cluster and bootstraping from the last node, but wsrep and general variables are not available anymore

any suggestions ?

Claudio

matthewb · September 19, 2023, 5:22pm

Either A) the wsrep plugin did not load; Check error logs, or B) the mysql_upgrade failed.

You should shut down MySQL, disable the wsrep_provider in my.cnf, start plain non-galera MySQL and run mysql_upgrade. Restart MySQL. Read the error log. Ensure no errors/warnings about the performance_schema.

Then, enable wsrep_provider and bootstrap.

soprano · September 20, 2023, 8:53am

My first question is,

the cluster is actually working fine ? is replicating datas ? because the tests i did seems all working good, but to be sure data was consistent my Load Balancer use only one node of the Cluster, so all the writes are going only on one node from the update.

For the steps indicated Will i have to do it for each node of the cluster ? or only on the actual working node ?

So the steps are:

shutdown the mysql on the actual working node with

systemctl stop mysql
disable wsrep_provider in my.cnf on that node in this way

#wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
start mysql normally

systemctl start mysql
run mysql_upgrade

mysql_upgrade --force -u root -p
search the logs for errors
if errors found report them here
if no errors found, enable wsrep_provider again in my.cnf and bootstrap the node

wsrep_provider = /usr/lib64/galera3/libgalera_smm.so

/etc/init.d/mysql bootstrap-pxc

For the rest of the nodes what i have to do ? Do i have to redo the same steps ? and then start mysql normally ?

To be sure they will resync from the one is working do i have to delete the grastate.dat file ?

Let me know.
Claudio

soprano · September 20, 2023, 1:19pm

I stopped one of nodes of first cluster (not the actually working)

commented the lines of wsrep_provider

started mysql

Sep 20 15:14:19 xxxx systemd: Starting Percona XtraDB Cluster…
Sep 20 15:14:20 xxxx mysqld_safe: mysqld_safe Adding ‘/usr/lib64/libjemalloc.so.1’ to LD_PRELOAD for mysqld
Sep 20 15:14:20 xxxx mysqld_safe: 2023-09-20T13:14:20.459530Z mysqld_safe Logging to syslog.
Sep 20 15:14:20 xxxx mysqld_safe: 2023-09-20T13:14:20.496414Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/db
Sep 20 15:14:20 xxxx mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql/db
Sep 20 15:14:20 xxxx mysqld_safe: 2023-09-20T13:14:20.509504Z mysqld_safe Skipping wsrep-recover for de5a461b-7059-11e7-9135-b6f59edaa63d:345591159 pair
Sep 20 15:14:20 xxxx mysqld_safe: Skipping wsrep-recover for de5a461b-7059-11e7-9135-b6f59edaa63d:345591159 pair
Sep 20 15:14:20 xxxx mysqld_safe: 2023-09-20T13:14:20.512466Z mysqld_safe Assigning de5a461b-7059-11e7-9135-b6f59edaa63d:345591159 to wsrep_start_position
Sep 20 15:14:20 xxxx mysqld_safe: Assigning de5a461b-7059-11e7-9135-b6f59edaa63d:345591159 to wsrep_start_position
Sep 20 15:14:21 xxxx mysql-systemd: SUCCESS!
Sep 20 15:14:21 xxxx systemd: Started Percona XtraDB Cluster.

when i run mysql_upgrade i get

[root@xxxx ~]# mysql_upgrade -u root
Checking if update is needed.
This installation of MySQL is already upgraded to 5.7.43-47, use --force if you still need to run mysql_upgrade

do i have to force it ?

The strange thing is that

[root@xxxx ~]# rpm -qa | grep -i percona
Percona-XtraDB-Cluster-client-57-5.7.43-31.65.1.el7.x86_64
Percona-XtraDB-Cluster-shared-57-5.7.43-31.65.1.el7.x86_64
percona-release-1.0-27.noarch
percona-xtrabackup-24-2.4.28-1.el7.x86_64
percona-toolkit-3.5.4-2.el7.x86_64
Percona-XtraDB-Cluster-server-57-5.7.43-31.65.1.el7.x86_64
Percona-XtraDB-Cluster-shared-compat-57-5.7.43-31.65.1.el7.x86_64
Percona-XtraDB-Cluster-57-5.7.43-31.65.1.el7.x86_64

version is 5.7.43-31 why it said 5.7.43-47 on mysql_upgrade ?

Claudio

matthewb · September 20, 2023, 5:21pm

Force it. Restart MySQL in NON-PXC mode. Log in. Check if you can SHOW GLOBAL VARIABLES and SHOW GLOBAL STATUS. Check mysql error log. If all is good, shut down. Enable PXC, start. Repeat verification. Check wsrep_ variables now visible.

soprano · September 21, 2023, 7:32am

Ok i did this

systemctl stop mysql

modified /etc/my.cnf

#wsrep_provider = …

systemctl start mysql

[root@XXXX ~]# mysql_upgrade -u root --force
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.db]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.user]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.func]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.tables_priv]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.columns_priv]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.proc]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.procs_priv]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.event]
mysql_upgrade: [ERROR] 1050: Table ‘cond_instances’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_waits_current’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_waits_history’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_waits_history_long’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_waits_summary_by_instance’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_waits_summary_by_host_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_waits_summary_by_user_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_waits_summary_by_account_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_waits_summary_by_thread_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_waits_summary_global_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘file_instances’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘file_summary_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘file_summary_by_instance’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘socket_instances’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘socket_summary_by_instance’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘socket_summary_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘host_cache’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘mutex_instances’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘objects_summary_global_by_type’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘performance_timers’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘rwlock_instances’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘setup_actors’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘setup_consumers’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘setup_instruments’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘setup_objects’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘setup_timers’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘table_io_waits_summary_by_index_usage’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘table_io_waits_summary_by_table’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘table_lock_waits_summary_by_table’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘threads’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘processlist’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_stages_current’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_stages_history’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_stages_history_long’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_stages_summary_by_thread_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_stages_summary_by_host_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_stages_summary_by_user_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_stages_summary_by_account_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_stages_summary_global_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_statements_current’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_statements_history’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_statements_history_long’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_statements_summary_by_thread_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_statements_summary_by_host_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_statements_summary_by_user_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_statements_summary_by_account_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_statements_summary_global_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_transactions_current’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_transactions_history’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_transactions_history_long’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_transactions_summary_by_thread_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_transactions_summary_by_host_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_transactions_summary_by_user_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_transactions_summary_by_account_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_transactions_summary_global_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘hosts’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘users’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘accounts’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘memory_summary_global_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘memory_summary_by_thread_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘memory_summary_by_account_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘memory_summary_by_host_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘memory_summary_by_user_by_event_name’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_statements_summary_by_digest’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘events_statements_summary_by_program’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘prepared_statements_instances’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘replication_connection_configuration’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘replication_group_member_stats’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘replication_group_members’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘replication_connection_status’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘replication_applier_configuration’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘replication_applier_status’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘replication_applier_status_by_coordinator’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘replication_applier_status_by_worker’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘pxc_cluster_view’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘session_connect_attrs’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘session_account_connect_attrs’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘table_handles’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘metadata_locks’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘user_variables_by_thread’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘variables_by_thread’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘global_variables’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘session_variables’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘status_by_thread’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘status_by_user’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘status_by_host’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘status_by_account’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘global_status’ already exists
mysql_upgrade: [ERROR] 1050: Table ‘session_status’ already exists
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.proxies_priv]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.tables_priv]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.columns_priv]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.user]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.db]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.func]
mysql_upgrade: [ERROR] 1726: Storage engine ‘InnoDB’ does not support system tables. [mysql.procs_priv]
mysql_upgrade: [ERROR] 1644: Unexpected content found in the performance_schema database.
Checking system database.
DB1 OK
…
Upgrade process completed successfully.
Checking if update is needed.

so performance schema was not correct

we enforce innodb engine

started again mysql was showing still

[root@XXXX ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 488
Server version: 5.7.43-47-57-log Percona XtraDB Cluster (GPL), Release rel47, Revision 69b5c96, WSREP version 31.65, wsrep_31.65

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show variables;
Empty set (0.00 sec)

mysql> show global variables;
Empty set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES;
Empty set (0.01 sec)

mysql> SHOW GLOBAL STATUS;
Empty set (0.00 sec)

mysql> quit

matthewb · September 21, 2023, 3:48pm

This is messed up. Are you somehow using MySQL 5.7 and MySQL 8 on the same data directory? The mysql.* tables should NOT be InnoDB tables in 5.7.

I would completely erase this server and let it SST from a good/existing node.

soprano · September 21, 2023, 4:50pm

I’m using the Percona XtraDB MYSQL Cluster 5.7 on 3 nodes, then the update comes out i updated all the 3 nodes and this is the result

anyway this is that is installed on the 3 ndoes

[root@node1 ~]# rpm -qa | grep -i percona
Percona-XtraDB-Cluster-client-57-5.7.43-31.65.1.el7.x86_64
Percona-XtraDB-Cluster-shared-57-5.7.43-31.65.1.el7.x86_64
percona-release-1.0-27.noarch
percona-toolkit-3.5.4-2.el7.x86_64
Percona-XtraDB-Cluster-server-57-5.7.43-31.65.1.el7.x86_64
Percona-XtraDB-Cluster-shared-compat-57-5.7.43-31.65.1.el7.x86_64
percona-xtrabackup-24-2.4.28-1.el7.x86_64
Percona-XtraDB-Cluster-57-5.7.43-31.65.1.el7.x86_64

[root@node2 ~]# rpm -qa | grep -i percona
Percona-XtraDB-Cluster-client-57-5.7.43-31.65.1.el7.x86_64
Percona-XtraDB-Cluster-shared-57-5.7.43-31.65.1.el7.x86_64
Percona-XtraDB-Cluster-server-57-5.7.43-31.65.1.el7.x86_64
percona-xtrabackup-24-2.4.28-1.el7.x86_64
percona-toolkit-3.5.4-2.el7.x86_64
Percona-XtraDB-Cluster-57-5.7.43-31.65.1.el7.x86_64
percona-release-1.0-27.noarch
Percona-XtraDB-Cluster-shared-compat-57-5.7.43-31.65.1.el7.x86_64

[root@node3 ~]# rpm -qa | grep -i percona
Percona-XtraDB-Cluster-client-57-5.7.43-31.65.1.el7.x86_64
Percona-XtraDB-Cluster-shared-57-5.7.43-31.65.1.el7.x86_64
percona-release-1.0-27.noarch
percona-xtrabackup-24-2.4.28-1.el7.x86_64
percona-toolkit-3.5.4-2.el7.x86_64
Percona-XtraDB-Cluster-server-57-5.7.43-31.65.1.el7.x86_64
Percona-XtraDB-Cluster-shared-compat-57-5.7.43-31.65.1.el7.x86_64
Percona-XtraDB-Cluster-57-5.7.43-31.65.1.el7.x86_64

the problem is that all 3 nodes are screwed so i can’t SST from none.

I would have a backup of the night before the yum update, what i have to recover from backup ?

I have to fix 2 clusters on both the same problem, this is on Centos 7.9, the other is on Ubuntu LTS 20.04 but both the same errors, same situation, something gone wrong on the update script for sure, all the servers started mysql with no problem, while the performance_schema had wrong structure error on the logs.

Repeat we have

enforce_storage_engine = InnoDB
sql_mode = ‘’
pxc_strict_mode = PERMISSIVE

in my.cnf of all nodes on both clusters.

I have to fix this cluster, there is a way to recreate the performance_schema DB ?

Else i can take one node down, reinstall it, then take mysqldumps from backup, bootstraping it and then reinstall the other nodes, let me know the fastest way, with minor downtime, i have a lot of stuffs on it that need to be on.

Claudio

matthewb · September 21, 2023, 7:12pm

You may have messed up everything:

If you’re using enforce_storage_engine, you must either disable it before doing mysql_upgrade or perform mysql_upgrade with server started with --skip-grants-tables.

I would not recommend the use of this parameter at all. Remove it, then restore your backup to restore the MYI-based system tables.

PXC doesn’t work with non-InnoDB engines anyway, so this variable isn’t really needed.

soprano · September 23, 2023, 10:34am

Anyway i succeded to recover one of two cluster that on Ubuntu 20.04, this is that i did

take one node down

systemctl stop mysql

commented in /etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf

#wsrep_provider = …

#engine_enforce_engine = InnoDB

#sql_mode = ‘’

started mysql

systemctl start mysql

dropped performance_schema database

mysql -u root -p

mysql> DROP DATABASE performance_schema;

run mysql_upgrade

mysql_upgrade -u root --force -p

restarted mysql

systemctl restart mysql

verified variables were ok

mysql -u root -p

mysql> SHOW VARIABLES;

and all variables finally were there

stopped mysql

systemctl stop mysql

enabled wsrep_provider in /etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf

wsrep_provider = …

started Percona Xtradb

systemctl start mysql

and all is ok on this node

Repeat all the steps on the second node and third node.

Now my main question is,

if someone create a table with MyIsam engine it will not be replicated, for this behaviour we enabled enforce_storage_engine and sql_mode, there is a way to deny creation of MyIsam DB or TABLES ?
And is it recommended ?

Another question is why mysql_upgrade don’t check that enforce_engine_storage is active and warn about it and don’t permit to make disasters when one upgrade the percona cluster ?

The other cluster is not working, we tried to make another mysql_upgrade (someone suggested it) and it screwed the nodes resetting mysql root password and deleting all the DB data files where we executed it, luckily we have a single node working in bootstrapping mode (no varables on it),
Next week we’ll make a down and we will repair it, then we will add the nodes again and they would SST all the DB datafiles.

Claudio

matthewb · September 23, 2023, 2:26pm

engine_enforce_engine only works if you also have NO_ENGINE_SUBSTUTION enabled for sql_mode, which you don’t above.

You can use this parameter after you have bootstrapped your cluster correctly. Ensure all the system tables are correct then you can implement this parameter.

soprano · September 25, 2023, 3:51pm

we have

default_storage_engine = InnoDB
enforce_storage_engine = InnoDB
sql_mode = ‘’

the last one would permit engine substution, we want all is InnoDB, no MyIsam or Memory engine

is that right ?

Claudio

Topic		Replies	Views
Mysql not starting, no wsrep Percona Server for MySQL 5.6	3	1865	October 27, 2021
"Unknow variable wsrep_cluster_address" (actually wsrep_*) after upgrade 5.5 -> 5.6 Percona XtraDB Cluster 5.x	2	1055	September 16, 2014
New cluster install, first cluster node doesn't show as Percona XtraDB Cluster 5.x	3	1836	August 2, 2017
Xtrabackup failing on SHOW GLOBAL VARIABLES Percona XtraDB Cluster 5.x	2	1272	September 18, 2023
Database not responding after adding a server to the cluster Percona XtraDB Cluster 8.x mysql , percona	11	869	November 16, 2022

No wsrep and general variables after upgrade

Related topics