Ludovic, Because you noticed the .cnf situation, I changed the .pm files back to the original configuration and started the synchronization process again. The synchronization is successful this time.
I have the master cluster member and two other nodes. Only one of the other nodes had the .pm files modified and at some point during this exercise, neither one of the other nodes could synchronize the database. But, now they both can. My attempts today have been using only the node with the modified .pm files. It is possible changing the configuration to be mariadb.cnf instead of mariadb.conf had an impact. You would know better than I. I modified generatemariadb.pm to create both mariadb.conf and mariadb.cnf I modified pf-mariadb to specify mariadb.cnf when calling mysqld_safe This was an attempt to correct an earlier issue I was dialing with. It did not help and I just hadn’t changed the configuration back to original. I solved the earlier issue another way. The database would start in standalone mode successfully with the modifications in place I’m inclined to believe I missed a step the last 15 times I tried to synchronize and have finally gotten it right. This latest attempt required the effort of manually stopping each individual PacketFence service. The command ‘pfcmd service pf stop’ would not do it. It was different than usual, but effective. Either way, thank you for seeing the mariadb.cnf difference. It led me to a successful synchronization. Now, on to the next step. Jeff From: Jeff Linden via PacketFence-users <[email protected]> Sent: Friday, October 16, 2020 2:15 PM To: Ludovic Zammit <[email protected]>; [email protected] Cc: Jeff Linden <[email protected]> Subject: Re: [PacketFence-users] Cannot Synchronize MariaDB in Distributed Cluster Ludovic, Yes, sorry, I did that along the way as a troubleshooting measure. I came across the idea that MariaDB or MySQL once had a bug that caused a bad behavior when loading a defaults file with an extension different than .cnf. The .cnf file is a duplicate of .conf. I modified the Perl programs to generate and call the .cnf instead of the .conf. It made no difference. I did not mean to leave that in there when sharing my troubles with you. I will take it out. You should be able to safely ignore the filename difference in your consideration of what might be going wrong. Thanks, Jeff From: Ludovic Zammit <[email protected]<mailto:[email protected]>> Sent: Friday, October 16, 2020 1:51 PM To: [email protected]<mailto:[email protected]> Cc: Jeff Linden <[email protected]<mailto:[email protected]>> Subject: Re: [PacketFence-users] Cannot Synchronize MariaDB in Distributed Cluster Hello Jeff, Did you alter the mariadb configuration ? Because I don’t know why it’s loading the wrong config My status: [root@pf pf]# systemctl status packetfence-mariadb ● packetfence-mariadb.service - PacketFence MariaDB instance Loaded: loaded (/usr/lib/systemd/system/packetfence-mariadb.service; enabled; vendor preset: disabled) Active: active (running) since Tue 2020-09-15 10:59:02 EDT; 1 months 0 days ago Main PID: 3999 (pf-mariadb) Status: "Taking your SQL requests now..." CGroup: /packetfence.slice/packetfence-base.slice/packetfence-mariadb.service ├─3999 pf-mariadb ├─6871 /bin/sh /usr/bin/mysqld_safe --defaults-file=/usr/local/pf/var/conf/mariadb.conf ├─7073 /usr/sbin/mysqld --defaults-file=/usr/local/pf/var/conf/mariadb.conf --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --pid-... └─7074 logger -t mysqld -p daemon.error Yours: ● packetfence-mariadb.service - PacketFence MariaDB instance Loaded: loaded (/lib/systemd/system/packetfence-mariadb.service; enabled; vendor preset: enabled) Active: activating (start) since Fri 2020-10-16 12:26:23 EDT; 34s ago Process: 215522 ExecStartPre=/usr/local/pf/bin/pfcmd generatemariadbconfig (code=exited, status=0/SUCCESS) Main PID: 215534 (pf-mariadb) Status: "Free innodb buffer pool" Tasks: 4 (limit: 36864) CGroup: /packetfence.slice/packetfence-base.slice/packetfence-mariadb.service ├─215534 pf-mariadb ├─218833 /bin/bash /usr/bin/mysqld_safe --defaults-file=/usr/local/pf/var/conf/mariadb.cnf ├─218981 /usr/sbin/mysqld --defaults-file=/usr/local/pf/var/conf/mariadb.cnf --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/x86_64-linux-gnu/mari └─218982 logger -t mysqld -p daemon error [root@pf pf]# yum list intalled mariadb\* Loaded plugins: fastestmirror Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast Loading mirror speeds from cached hostfile * base: centos.mirror.globo.tech<http://centos.mirror.globo.tech> * extras: centos.mirror.globo.tech<http://centos.mirror.globo.tech> * updates: centos.mirror.netelligent.ca<http://centos.mirror.netelligent.ca> Installed Packages MariaDB-client.x86_64 10.1.21-1.el7.centos @packetfence MariaDB-common.x86_64 10.1.21-1.el7.centos @packetfence MariaDB-server.x86_64 10.1.21-1.el7.centos @packetfence MariaDB-shared.x86_64 10.1.21-1.el7.centos @packetfence Available Packages mariadb.x86_64 1:5.5.65-1.el7 base mariadb-bench.x86_64 1:5.5.65-1.el7 base mariadb-devel.i686 1:5.5.65-1.el7 base mariadb-devel.x86_64 1:5.5.65-1.el7 base mariadb-embedded.i686 1:5.5.65-1.el7 base mariadb-embedded.x86_64 1:5.5.65-1.el7 base mariadb-embedded-devel.i686 1:5.5.65-1.el7 base mariadb-embedded-devel.x86_64 1:5.5.65-1.el7 base mariadb-libs.i686 1:5.5.65-1.el7 base mariadb-libs.x86_64 1:5.5.65-1.el7 base mariadb-server.x86_64 1:5.5.65-1.el7 base mariadb-test.x86_64 1:5.5.65-1.el7 base Could you run: yum list intalled mariadb\* Thanks, Ludovic Zammit [email protected]<mailto:[email protected]> :: +1.514.447.4918 (x145) :: www.inverse.ca<http://www.inverse.ca> Inverse inc. :: Leaders behind SOGo (http://www.sogo.nu) and PacketFence (http://packetfence.org) On Oct 16, 2020, at 12:35 PM, Jeff Linden via PacketFence-users <[email protected]<mailto:[email protected]>> wrote: Hello, I have now moved on to creating a cluster. I’m following the instructions here:https://packetfence.org/doc/PacketFence_Clustering_Guide.html The cluster is distributed; each of the 3 nodes are in different layer 3 networks. I’ve reached section 3.5.3 MariaDB sync in the instructions and it is not succeeding. On the master node, I have executed /usr/local/pf/sbin/pf-mariadb –force-new-cluster. The mysqld process is running and the following errors occur in /usr/local/pf/logs/packetfence.log: Oct 16 12:12:27 nadc1-pfence-01 packetfence: -e(23457) FATAL: unable to connect to database: Can't connect to MySQL server on '127.0.0.1' (111 "Connection refused") at /usr/local/pf/lib/pf/dal.pm line 70. I find the database is running on the master node. I can successfully connect using the command ‘mysql -u root -p’. I can also connect using the command ‘mysql -h localhost -u root -p’. And, from the secondary node, I can connect to the database on the master node using the command ‘mysql -h <remotehostIP> -u root -p’. I’m confident the database is running and listening. On the other node, the configuration is synced, haproxy-db and httpd.webservices are running. On all servers, iptables is stopped. The MariaDB data is flushed using ‘rm -fr /var/lib/mysql/*’ and I can see the folder is empty. I execute the command ‘systemctl restart packetfence-mariadb’ and see the following message repeating in /usr/local/pf/logs/packetfence.log: Oct 16 12:01:46 novi-pfence-01 packetfence: pf-mariadb(59988) INFO: There is a peer with an alive DB. Will attempt to connect to the cluster (main::startup_clean_shutdown) Using the command ‘systemctl status packetfence-mariadb’ a couple times in a row, the following errors occur showing that the process is crashing and restarting. ● packetfence-mariadb.service - PacketFence MariaDB instance Loaded: loaded (/lib/systemd/system/packetfence-mariadb.service; enabled; vendor preset: enabled) Active: activating (start) since Fri 2020-10-16 12:26:23 EDT; 34s ago Process: 215522 ExecStartPre=/usr/local/pf/bin/pfcmd generatemariadbconfig (code=exited, status=0/SUCCESS) Main PID: 215534 (pf-mariadb) Status: "Free innodb buffer pool" Tasks: 4 (limit: 36864) CGroup: /packetfence.slice/packetfence-base.slice/packetfence-mariadb.service ├─215534 pf-mariadb ├─218833 /bin/bash /usr/bin/mysqld_safe --defaults-file=/usr/local/pf/var/conf/mariadb.cnf ├─218981 /usr/sbin/mysqld --defaults-file=/usr/local/pf/var/conf/mariadb.cnf --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/x86_64-linux-gnu/mari └─218982 logger -t mysqld -p daemon error Oct 16 12:26:54 novi-pfence-01 mysqld[218781]: 2020-10-16 12:26:54 139773373455744 [Note] Recovering after a crash using tc.log Oct 16 12:26:54 novi-pfence-01 mysqld[218781]: 2020-10-16 12:26:54 139773373455744 [Note] Starting crash recovery... Oct 16 12:26:54 novi-pfence-01 mysqld[218781]: 2020-10-16 12:26:54 139773373455744 [Note] Crash recovery finished. Oct 16 12:26:54 novi-pfence-01 mysqld[218781]: 2020-10-16 12:26:54 139773373455744 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist Oct 16 12:26:54 novi-pfence-01 mysqld[218781]: 2020-10-16 12:26:54 139773373455744 [Note] Server socket created on IP: '10.22.0.82'. Oct 16 12:26:54 novi-pfence-01 mysqld[218781]: 2020-10-16 12:26:54 139773373455744 [Note] WSREP: disabled, skipping position recovery Oct 16 12:26:57 novi-pfence-01 packetfence[215534]: pf-mariadb(215534) INFO: There is a peer with an alive DB. Will attempt to connect to the cluster (main::startup_clean_ Oct 16 12:26:57 novi-pfence-01 pf-mariadb[215534]: Starting MySQL with command: mysqld_safe --defaults-file=/usr/local/pf/var/conf/mariadb.cnf Oct 16 12:26:57 novi-pfence-01 pf-mariadb[215534]: 201016 12:26:57 mysqld_safe Logging to syslog. Oct 16 12:26:57 novi-pfence-01 pf-mariadb[215534]: 201016 12:26:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql root@novi-pfence-01:/var/lib/mysql# systemctl -l --wait status packetfence-mariadb ● packetfence-mariadb.service - PacketFence MariaDB instance Loaded: loaded (/lib/systemd/system/packetfence-mariadb.service; enabled; vendor preset: enabled) Active: activating (start) since Fri 2020-10-16 12:26:23 EDT; 35s ago Process: 215522 ExecStartPre=/usr/local/pf/bin/pfcmd generatemariadbconfig (code=exited, status=0/SUCCESS) Main PID: 215534 (pf-mariadb) Status: "Starting Innodb crash recovery" Tasks: 23 (limit: 36864) CGroup: /packetfence.slice/packetfence-base.slice/packetfence-mariadb.service ├─215534 pf-mariadb ├─219013 /bin/bash /usr/bin/mysqld_safe --defaults-file=/usr/local/pf/var/conf/mariadb.cnf --wsrep-recover ├─219167 /usr/sbin/mysqld --defaults-file=/usr/local/pf/var/conf/mariadb.cnf --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/x86_64-linux-gnu/mari └─219168 logger -t mysqld -p daemon error Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: Compressed tables use zlib 1.2.8 Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: Using Linux native AIO Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: Using SSE crc32 instructions Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: Initializing buffer pool, size = 500.0M Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: Completed initialization of buffer pool Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: Highest supported file format is Barracuda. Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: The log sequence number 1607435 in ibdata file do not match the log seque Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer... Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: 128 rollback segment(s) are active. Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: Waiting for purge to start Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: Percona XtraDB (http://www.percona.com<http://www.percona.com/>) 5.6.47-87.0 started; log sequenc root@novi-pfence-01:/var/lib/mysql# systemctl -l --wait status packetfence-mariadb ● packetfence-mariadb.service - PacketFence MariaDB instance Loaded: loaded (/lib/systemd/system/packetfence-mariadb.service; enabled; vendor preset: enabled) Active: activating (start) since Fri 2020-10-16 12:26:23 EDT; 35s ago Process: 215522 ExecStartPre=/usr/local/pf/bin/pfcmd generatemariadbconfig (code=exited, status=0/SUCCESS) Main PID: 215534 (pf-mariadb) Status: "Waiting for master thread to exit" Tasks: 20 (limit: 36864) CGroup: /packetfence.slice/packetfence-base.slice/packetfence-mariadb.service ├─215534 pf-mariadb ├─219013 /bin/bash /usr/bin/mysqld_safe --defaults-file=/usr/local/pf/var/conf/mariadb.cnf --wsrep-recover ├─219167 /usr/sbin/mysqld --defaults-file=/usr/local/pf/var/conf/mariadb.cnf --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/x86_64-linux-gnu/mari └─219168 logger -t mysqld -p daemon error Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] InnoDB: Percona XtraDB (http://www.percona.com<http://www.percona.com/>) 5.6.47-87.0 started; log sequenc Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Warning] InnoDB: Skipping buffer pool dump/restore during wsrep recovery. Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] Plugin 'FEEDBACK' is disabled. Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] Recovering after a crash using tc.log Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] Starting crash recovery... Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] Crash recovery finished. Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] Server socket created on IP: '10.22.0.82'. Oct 16 12:26:58 novi-pfence-01 mysqld[219168]: 2020-10-16 12:26:58 139864779369856 [Note] WSREP: disabled, skipping position recovery The most significant error appears to be that about table ‘mysql.servers’ doesn’t exist, but I don’t know what to do about it. I can see that after running the command to restart packetfence-mariadb on the other node, the folder /var/lib/mysql has the following files in it. I think this means it has contacted the remote server and started the sync, but cannot be certain. -rw-rw---- 1 mysql mysql 16384 Oct 16 12:33 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Oct 16 12:33 aria_log_control -rw-rw---- 1 mysql mysql 12582912 Oct 16 12:33 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Oct 16 12:33 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Oct 16 12:28 ib_logfile1 -rw-rw---- 1 mysql mysql 24576 Oct 16 12:33 tc.log I look forward to any advice available on how to proceed from here. Thank you, Jeff PRIVACY NOTICE: The information contained in this e-mail, including any attachments, is confidential and intended only for the named recipient(s). Unauthorized use, disclosure, forwarding, or copying is strictly prohibited and may be unlawful. If you are not the intended recipient, please delete the e-mail and any attachments and notify us immediately by return e-mail. _______________________________________________ PacketFence-users mailing list [email protected]<mailto:[email protected]> https://lists.sourceforge.net/lists/listinfo/packetfence-users
_______________________________________________ PacketFence-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/packetfence-users
