Moodle with clustered DB replication issues

Moodle with clustered DB replication issues

by Rubén Romero -
Number of replies: 2

Hi all,

It's my first time with Moodle, PHP and Database clustering, so understand any lack of knowledge.

We need to install a clustered platform for high availability and reading some recommendations (https://moodle.org/mod/forum/discuss.php?d=57202) we decided to proceed as follows:

  • 1 Hardware load balancer for frontends
  • 2 NGINX frontends with PHP-FPM
  • 1 NFS shared folder for html and moodledata
  • 2 MariaDB nodes performing a Multimaster Cluster

For database load balancing we used mysqlnd_ms with the following configuration

mysqlnd_ms.enable = On
mysqlnd_ms.config_file = /etc/mysqlnd_ms_plugin.ini
mysqlnd_ms.multi_master = On
mysqlnd_ms.collect_statistics = On
mysqlnd_ms.force_config_usage = On

And 

{
"localhost": {
"master": {
"master_1": {
"host": "IP_NODE_1",
"port": "3306"
},
"master_2": {
"host": "IP_NODE_2",
"port": "3306"
}
},
"slave": {
},
"filters": {
"roundrobin": {
}
},
"failover": {
"strategy": "loop_before_master",
"remember_failed": true
}
}
}

With this we can configure moodle to use localhost and mysqlnd_ms can balance over the cluster.

MariaDB cluster is configured this way:

[mysqld]
bind_address=IP_NODE_1
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_flush_log_at_trx_commit=2
# wsrep provider configuration: basic wsrep options
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://IP_NODE_2?gmcast.listen_addr=IP_NODE_1
wsrep_cluster_name='galera_cluster'
wsrep_node_address=IP_NODE_1
wsrep_node_name='node1'
wsrep_sst_method=rsync

And similar on the other node.

The problem comes after installing moodle and start using it. Everything seems to work as expected until creating a course, a student, a quiz and attempting the quiz with this user. The following error happens:

Debug info: SELECT * FROM {quiz_attempts} WHERE id = ?
[array (
0 => 12,
)]
Error code: invalidrecord
Stack trace:
  • line 1372 of /lib/dml/moodle_database.php: dml_missing_record_exception thrown
  • line 1348 of /lib/dml/moodle_database.php: call to moodle_database->get_record_select()
  • line 490 of /mod/quiz/attemptlib.php: call to moodle_database->get_record()
  • line 508 of /mod/quiz/attemptlib.php: call to quiz_attempt::create_helper()
  • line 43 of /mod/quiz/attempt.php: call to quiz_attempt::create()

 

Any suggestion, advice, blame? :P

Thanks in advance and kind regards.

Average of ratings: -
In reply to Rubén Romero

Re: Moodle with clustered DB replication issues

by jason everling -

What is this for "?gmcast.listen_addr=IP_NODE_1" ? at the end of your wsrep_cluster_address . Are you trying to multicast? If so it doesnt look right, check here  .

If not, then you need to remove that line so it reads;  wsrep_cluster_address=gcomm://IP_NODE_2 .

Also it could be the extension you are using to load balance, mysqlnd_ms . We currently have a clustered db setup using Galera, which is the underlining libraries you are using in MariaDB and do not have any issues.

You are going to have issues using the driver also, from Galera themselves, "However, for Galera, the JDBC and PHP drivers are not aware of internal Galera state information. For instance, a Galera donor node is read-only while it is helping another node resynchronize."

Try to switch out you load balancer and use HAPROXY here which is how we load balance mysql,

You can also use GaleraLB here;

You can also try to bypass the Load balancer all together, to test it out by connecting your moodle config.php to the one of the node IPs directly and see what happens, if success then you know it is the load balancer PHP extension you are using. I would switch load balancers anyway because that is going to cause issues during a failover or loss, can also lead to issues when a node is trying to catch up in read-only mode and your lb is directing traffic its way.

Jason

Average of ratings: Useful (1)
In reply to jason everling

Re: Moodle with clustered DB replication issues

by Rubén Romero -

Hi Jason,

I removed the multicast configuration by now, I'll try to configure it properly later on. Following your recommendations, I decided to use Galera Load Balancer in each frontend and now everything works fine.

Thanks for your help and kind regards,

Rubén.

Average of ratings: -