Error when upgrading percona xtradb cluster from 56 to 57

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi,

I am trying to upgrade Ubuntu Server 18.04 from percona xtradb cluster from 56 to 57.

I have followed the upgrade steps available here. Upgrading Percona XtraDB Cluster - Percona XtraDB Cluster.

In Step 3: I have got the following error:

$ apt-get install percona-xtradb-cluster-57


percona-xtradb-cluster-57 percona-xtradb-cluster-client-5.7 percona-xtradb-cluster-common-5.7 percona-xtradb-cluster-server-5.7



dpkg-query: no path found matching pattern /etc/mysql/my.cnf

-------------

* The suggested mysql options and settings are in /etc/mysql/percona-server.conf.d/mysqld.cnf

* If you want to use mysqld.cnf as default configuration file please make backup of /etc/my.cnf

* Once it is done please execute the following commands:

rm -rf /etc/mysql/my.cnf

update-alternatives --install /etc/mysql/my.cnf my.cnf "/etc/mysql/percona-xtradb-cluster.cnf" 200

-------------

Setting up libcurl4-openssl-dev:amd64 (7.58.0-2ubuntu3.13) …

Setting up percona-xtrabackup-24 (2.4.22-1.bionic) …

Setting up percona-xtradb-cluster-client-5.7 (5.7.33-31.49-1.bionic) …

Setting up percona-xtradb-cluster-server-5.7 (5.7.33-31.49-1.bionic) …

Installing new version of config file /etc/default/mysql …

Installing new version of config file /etc/init.d/mysql …

  • Percona XtraDB Cluster is distributed with several useful UDF (User Defined Function) from Percona Toolkit.

  • Run the following commands to create these functions:

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"

mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"

mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

===============================
I have removed the file

rm -rf /etc/mysql/my.cnf

run this command

update-alternatives --install /etc/mysql/my.cnf my.cnf "/etc/mysql/percona-xtradb-cluster.cnf" 200

Added my config from /etc/mysql/my.cnf to
about wsrep → /etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf. (Based on the suggestions from Configuring Nodes for Write-Set Replication - Percona XtraDB Cluster
)

rest of the things to /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf

Even after doing so, mysql failled to start. As in the next step I need to stop even if the mysql gets started. SO i proceeded to next steps.

=====================================
When I run the step 6 as discussed in the upgrade guide (mysqld --skip-grant-tables --user=mysql --wsrep-provider=‘none’), it exits without waiting for step 7 ( mysql_upgrade command).

Is this really a problem or not?
I could run step 6 with root user, Should I use root?

2 Likes
2

Hi @jay welcome to the Percona forums!
It would be helpful to see the error log contents, as it is probably a mis-configured entry in one of the configuration files

1 Like
3

Hi @Michael_Coburn ,

It says new users are not allowed to upload files. so pasting the complete config here.

$ cat /etc/mysql/percona-xtradb-cluster.conf.d/client.cnf

[client]

host = localhost

user = debian-sys-maint

password = *************

socket = /var/run/mysqld/mysqld.sock

port = 3306

[mysql_upgrade]

host = localhost

user = debian-sys-maint

password = maintenance_cips!d00l

socket = /var/run/mysqld/mysqld.sock

basedir = /usr

$ cat /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf

[mysqld]

datadir=/var/lib/mysql

explicit_defaults_for_timestamp = 1

bind-address=10.1.12.104

port=3306

max_connections=20000

default-storage-engine=innodb

binlog_format=ROW

collation-server=utf8_general_ci

init-connect='SET NAMES utf8'

character-set-server=utf8

default-storage-engine=innodb

log_error=/var/log/mysql/error.log

skip-external-locking

skip-name-resolve

myisam_sort_buffer_size=64M

wait_timeout=1800

open_files_limit=102400

table_open_cache=10000

key_buffer_size=64M

max_allowed_packet=256M

query_cache_size=0

query_cache_type=0

innodb_file_format=Barracuda

innodb_file_per_table=1

#innodb_data_file_path = ibdata1:10M:autoextend

innodb_doublewrite=1

#innodb_thread_concurrency=64

innodb_buffer_pool_size=3138M

innodb_log_file_size=627M

innodb_read_io_threads=8

innodb_write_io_threads=8

innodb_io_capacity=500

innodb_flush_log_at_trx_commit=2

innodb_flush_method=O_DIRECT

innodb_autoinc_lock_mode=2

innodb_locks_unsafe_for_binlog=1

wsrep_cluster_address="gcomm://10.1.12.102:4567,10.1.12.103:4567,10.1.12.104:4567/?pc.wait_prim=no"

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

wsrep_cluster_name=galeracluster

wsrep_slave_threads=8

wsrep_sst_method=xtrabackup-v2

wsrep_sst_auth=root:root_cips!d00l

wsrep_node_address=10.1.12.104

wsrep_provider_options="gcache.size = 256M"

wsrep_provider_options="gmcast.listen_addr = tcp://10.1.12.104:4567"

[xtrabackup]

parallel=4

[sst]

streamfmt=xbstream

transferfmt=socat

sockopt=,nodelay,sndbuf=1048576,rcvbuf=1048576

$ cat /etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf

[mysqld]

# Path to Galera library

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

# Cluster connection URL contains IPs of nodes

#If no IP is found, this implies that a new cluster needs to be created,

#in order to do that you need to bootstrap this node

wsrep_cluster_address=gcomm://10.1.12.102:4567,10.1.12.103:4567,10.1.12.104:4567/?pc.wait_prim=no

# In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW

# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

# Slave thread to use

wsrep_slave_threads= 8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

# Node IP address

wsrep_node_address=10.1.12.104

# Cluster name

wsrep_cluster_name=galeracluster

#If wsrep_node_name is not specified, then system hostname will be used

wsrep_node_name=h004

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER

pxc_strict_mode=ENFORCING

# SST method

wsrep_sst_method=xtrabackup-v2

#Authentication for SST method

#wsrep_sst_auth="sstuser:s3cretPass"

wsrep_sst_auth=root:********

$ cat /etc/mysql/percona-xtradb-cluster.cnf

#

# The Percona XtraDB Cluster 5.7 configuration file.

#

#

# * IMPORTANT: Additional settings that can override those from this file!

# The files must end with '.cnf', otherwise they'll be ignored.

# Please make any edits and changes to the appropriate sectional files

# included below.

#

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/percona-xtradb-cluster.conf.d/

$ cat /etc/mysql/my.cnf | less

#

# The Percona XtraDB Cluster 5.7 configuration file.

#

#

# * IMPORTANT: Additional settings that can override those from this file!

# The files must end with '.cnf', otherwise they'll be ignored.

# Please make any edits and changes to the appropriate sectional files

# included below.

#

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/percona-xtradb-cluster.conf.d/
1 Like
4

Hi @jay
I tried to reproduce your issue using your config files but without success.
Could you please mysql error log and from syslog.

1 Like
5

Hi @Evgeniy_Patlan ,

mysql error logs have these:

2021-05-11T19:40:34.930362Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.33-36-57) starting as process 72389 …
2021-05-11T19:40:34.933382Z 0 [Note] WSREP: Setting wsrep_ready to false
2021-05-11T19:40:34.933395Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
2021-05-11T19:40:34.933399Z 0 [Note] WSREP: wsrep_load(): loading provider library ‘/usr/lib/galera3/libgalera_smm.so’
2021-05-11T19:40:34.933938Z 0 [Note] WSREP: wsrep_load(): Galera 3.49(r0ef0d79) by Codership Oy info@codership.com loaded successfully.
2021-05-11T19:40:34.933980Z 0 [Note] WSREP: CRC-32C: using 64-bit x86 acceleration.
2021-05-11T19:40:34.934219Z 0 [Note] WSREP: Found saved state: fb17ff94-5ca5-11e7-b264-cf0729785edd:1128187973, safe_to_bootstrap: 1
2021-05-11T19:40:34.934997Z 0 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 10.1.12.104; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; 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 = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; 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.listen_addr = tcp://10.1.12.104:4567; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 9; socket.checksum = 2; socket.recv_buf_size = auto; socket.send_buf_size = auto;
2021-05-11T19:40:34.947524Z 0 [Note] WSREP: GCache history reset: fb17ff94-5ca5-11e7-b264-cf0729785edd:0 → fb17ff94-5ca5-11e7-b264-cf0729785edd:1128187973
2021-05-11T19:40:34.947651Z 0 [Note] WSREP: Assign initial position for certification: 1128187973, protocol version: -1
2021-05-11T19:40:34.947660Z 0 [Note] WSREP: Preparing to initiate SST/IST
2021-05-11T19:40:34.947664Z 0 [Note] WSREP: Starting replication
2021-05-11T19:40:34.947670Z 0 [Note] WSREP: Setting initial position to fb17ff94-5ca5-11e7-b264-cf0729785edd:1128187973
2021-05-11T19:40:34.947822Z 0 [Note] WSREP: Using CRC-32C for message checksums.
2021-05-11T19:40:34.947890Z 0 [Note] WSREP: gcomm thread scheduling priority set to other:0
2021-05-11T19:40:34.947946Z 0 [Warning] WSREP: Fail to access the file (/var/lib/mysql//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
2021-05-11T19:40:34.947952Z 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
2021-05-11T19:40:34.948079Z 0 [Note] WSREP: GMCast version 0
2021-05-11T19:40:34.948192Z 0 [Note] WSREP: (c1170afa, ‘tcp://10.1.12.104:4567’) listening at tcp://10.1.12.104:4567
2021-05-11T19:40:34.948197Z 0 [Note] WSREP: (c1170afa, ‘tcp://10.1.12.104:4567’) multicast: , ttl: 1
2021-05-11T19:40:34.948386Z 0 [Note] WSREP: EVS version 0
2021-05-11T19:40:34.948436Z 0 [Note] WSREP: gcomm: connecting to group ‘galeracluster’, peer ‘10.1.12.102:4567,10.1.12.103:4567,10.1.12.104:4567’
2021-05-11T19:40:34.948516Z 0 [Warning] WSREP: error while trying to listen ‘tcp://10.1.12.104:4567?socket.non_blocking=1’, asio error ‘bind: Address already in use’
2021-05-11T19:40:34.948544Z 0 [ERROR] WSREP: failed to open gcomm backend connection: 98: error while trying to listen ‘tcp://10.1.12.104:4567?socket.non_blocking=1’, asio error ‘bind: Address already in use’: 98 (Address already in use)
at gcomm/src/asio_tcp.cpp:listen():937
2021-05-11T19:40:34.948549Z 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():209: Failed to open backend connection: -98 (Address already in use)
2021-05-11T19:40:34.948589Z 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1527: Failed to open channel ‘galeracluster’ at ‘gcomm://10.1.12.102:4567,10.1.12.103:4567,10.1.12.104:4567/?pc.wait_prim=no’: -98 (Address already in use)
2021-05-11T19:40:34.948595Z 0 [ERROR] WSREP: gcs connect failed: Address already in use
2021-05-11T19:40:34.948602Z 0 [ERROR] WSREP: Provider/Node (gcomm://10.1.12.102:4567,10.1.12.103:4567,10.1.12.104:4567/?pc.wait_prim=no) failed to establish connection with cluster (reason: 7)
2021-05-11T19:40:34.948606Z 0 [ERROR] Aborting
2021-05-11T19:40:34.948608Z 0 [Note] Giving 0 client threads a chance to die gracefully
2021-05-11T19:40:34.948612Z 0 [Note] WSREP: Waiting for active wsrep applier to exit
2021-05-11T19:40:34.948615Z 0 [Note] WSREP: Service disconnected.
2021-05-11T19:40:34.948617Z 0 [Note] WSREP: Waiting to close threads…
2021-05-11T19:40:39.948727Z 0 [Note] WSREP: Some threads may fail to exit.
2021-05-11T19:40:39.962312Z 0 [Note] WSREP: Service thread queue flushed.
2021-05-11T19:40:39.962455Z 0 [Note] WSREP: MemPool(SlaveTrxHandle): hit ratio: 0, misses: 0, in use: 0, in pool: 0
2021-05-11T19:40:39.962538Z 0 [Note] WSREP: Shifting CLOSED → DESTROYED (TO: 0)
2021-05-11T19:40:39.963159Z 0 [Note] Binlog end
2021-05-11T19:40:39.963325Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
2021-05-11T19:41:40.919282Z 0 [Warning] WSREP: Node is not a cluster node. Disabling pxc_strict_mode
2021-05-11T19:41:40.919336Z 0 [Note] mysqld (mysqld 5.7.33-36-57) starting as process 72521 …
2021-05-11T19:41:40.922270Z 0 [Warning] You need to use --log-bin to make --binlog-format work.
2021-05-11T19:41:40.928910Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See <>
2021-05-11T19:41:40.928928Z 0 [Warning] InnoDB: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead; Please refer to <>
2021-05-11T19:41:40.928967Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-05-11T19:41:40.928975Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-05-11T19:41:40.928979Z 0 [Note] InnoDB: Uses event mutexes
2021-05-11T19:41:40.928982Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-05-11T19:41:40.928985Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-05-11T19:41:40.928989Z 0 [Note] InnoDB: Using Linux native AIO
2021-05-11T19:41:40.937338Z 0 [Note] InnoDB: Number of pools: 1
2021-05-11T19:41:40.937444Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-05-11T19:41:40.939844Z 0 [Note] InnoDB: Initializing buffer pool, total size = 4G, instances = 8, chunk size = 128M
2021-05-11T19:41:41.105611Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-05-11T19:41:41.160125Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-05-11T19:41:41.173790Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2021-05-11T19:41:41.173803Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2021-05-11T19:41:41.173808Z 0 [ERROR] InnoDB: Failed to open the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2021-05-11T19:41:41.173815Z 0 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2021-05-11T19:41:41.173820Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Cannot open a file
2021-05-11T19:41:42.274840Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.
2021-05-11T19:41:42.274866Z 0 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
2021-05-11T19:41:42.274877Z 0 [ERROR] Failed to initialize builtin plugins.
2021-05-11T19:41:42.274884Z 0 [ERROR] Aborting
2021-05-11T19:41:42.274904Z 0 [Note] Binlog end
2021-05-11T19:41:42.274953Z 0 [Note] Shutting down plugin ‘CSV’
2021-05-11T19:41:42.276445Z 0 [Note] mysqld: Shutdown complete
2021-05-12T06:57:25.451515Z 0 [Note] mysqld (mysqld 5.7.33-36-57) starting as process 169862 …
2021-05-12T06:57:25.454502Z 0 [Note] WSREP: Setting wsrep_ready to false
2021-05-12T06:57:25.454512Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
2021-05-12T06:57:25.454516Z 0 [Note] WSREP: wsrep_load(): loading provider library ‘‘none’’
2021-05-12T06:57:25.454660Z 0 [ERROR] WSREP: wsrep_load(): dlopen(): ‘none’: cannot open shared object file: No such file or directory
2021-05-12T06:57:25.454667Z 0 [ERROR] WSREP: wsrep_load() failed to load the provider(‘‘none’’): Invalid argument (22). Need to abort.
2021-05-12T06:57:25.454676Z 0 [ERROR] Aborting
2021-05-12T06:57:25.454680Z 0 [Note] Binlog end
2021-05-12T06:57:25.454797Z 0 [Note] mysqld: Shutdown complete

1 Like
6

Hi @Michael_Coburn and @Evgeniy_Patlan ,

Problem was might be because of two things.

  1. In my /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf , i needed to add the following line [mysqld]. As this was the pid-file default mentioned in /etc/mysql/percona-xtradb-cluster.conf.d/mysqld_safe.cnf (might) need to match. When pid-file was not added, it was creating the pid file with host name (h004.pid , for example). It was failing.

[mysqld]
pid-file=/var/run/mysqld/mysqld.pid

  1. For some reason some of the files group was changed to “root” and for some files user:group changed to “root:root” in following paths.

$ ls -l /var/run/ | grep mysql
drwxr-xr-x 2 mysql root 40 May 11 16:26 mysqld

$ chown -R mysql:mysql /var/run/mysqld
$ chown -R mysql:mysql /var/lib/mysql

Now, I am able to start the cluster and all three nodes now back in the cluster. Thanks

1 Like