Connection Error after upgrading to mariadb

Connection Error after upgrading to mariadb

由Christe McMenomy發表於
Number of replies: 12

I am running a test version of my Moodle 3.5  system on  my Mac using Apache 2.4 37 and  PHP 7.2.  I upgraded the database from mySQL 5.6.36 to mariadb 10.3.11.  Everything appeared to work perfectly, but running the Administration > server >  environment check to see whether we were ready for a Moodle upgrade to 3.6 complained that my CFG->dbtype should be changed from mysqli to mariadb in my moodle/config.php file.

I made the change, but now when I try to log in, I get an error:

Exception -- Call to a member function IsConneceted() on boolean.

and the theme displays only the header box.

Reverting to CFG->dbtype="mysqli" appears to fix the problem  -- if I reload the page three times,  it finally displays correctly and I am logged in.  All of the functions then work correctly as far as I can tell (i.e., I can take a quiz with CLOZE answers and it scores properly; I can access random course pages).

I have tried purging caches after setting CFG->dbtype = "mariadb", but the error persists if CFG->dbtype is set to "mariadb".

I use an external database (which is part of the same database instance) for authentication.  There is no "mariadb" option to set for auth_db type on the Plugins > authentication > external database page, so it is still set to mysqli and there appears to be no way to change this.

Setting debug on gives the following error stack:

Debug info: 

Error code: generalexceptionmessage

Stack trace:

  • line 462 of /lib/adodb/drivers/adodb-mysql.inc.php: Error thrown
  • line 493 of /lib/adodb/drivers/adodb-mysql.inc.php: call to ADODB_mysql->_connect()
  • line 667 of /lib/adodb/adodb.inc.php: call to ADODB_mysql->_nconnect()
  • line 869 of /enrol/database/lib.php: call to ADOConnection->Connect()
  • line 144 of /enrol/database/lib.php: call to enrol_database_plugin->db_init()
  • line 245 of /lib/enrollib.php: call to enrol_database_plugin->sync_user_enrolments()
  • line 477 of /lib/classes/session/manager.php: call to enrol_check_plugins()
  • line 4414 of /lib/moodlelib.php: call to core\session\manager::login_user()
  • line 181 of /login/index.php: call to complete_user_login()
Any insight would be helpful.  While we appear to be running if everything is set to "mysqli", this is obviously not optimal for the long run or the proposed upgrade to Moodle 3.6.






評比平均分數： -
In reply to Christe McMenomy

Re: Connection Error after upgrading to mariadb

由Howard Miller發表於
Core developers的相片 Documentation writers的相片 Particularly helpful Moodlers的相片 Peer reviewers的相片 Plugin developers的相片

The original versions of Moodle 3.5 did not work with MariaDB 10.3.  Make sure you have the latest version of Moodle 3.5 before you do anything else. 

評比平均分數： -
In reply to Howard Miller

Re: Connection Error after upgrading to mariadb

由Christe McMenomy發表於

I am having a similar problem with Moodle 3.5.1 on Linux with Mariadb 10.1.31.  Moreover, with this environment, I can't even write to the database from the Moodle; I get a dmlwriteexception error.


Here is the stack trace:

Debug info: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
INSERT INTO mdl_question_usages (contextid,component,preferredbehaviour) VALUES(?,?,?)
[array (
0 => 45669,
1 => 'mod_quiz',
2 => 'interactive',
)]
Error code: dmlwriteexception
Stack trace:
  • line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
  • line 1329 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1375 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
  • line 92 of /question/engine/datalib.php: call to mysqli_native_moodle_database->insert_record()
  • line 100 of /question/engine/lib.php: call to question_engine_data_mapper->insert_questions_usage_by_activity()
  • line 351 of /mod/quiz/locallib.php: call to question_engine::save_questions_usage_by_activity()
  • line 2422 of /mod/quiz/locallib.php: call to quiz_attempt_save_started()
  • line 111 of /mod/quiz/startattempt.php: call to quiz_prepare_and_start_new_attempt()
Is there a page listing which versions of Moodle work with which versions of Mariadb?  I had done some serious research and all I could find was that Moodle 3.5 ran with Mariadb as a dropin and should work as long as I reset the mariadb flag -- now it appears that isn't really the case. 

Please note that the only thing I have done in both cases is swap out the database -- I have not reinistalled the Moodle.





評比平均分數： -
In reply to Christe McMenomy

Re: Connection Error after upgrading to mariadb

由Christe McMenomy發表於

The write issue on the Linux system seems to have been resolved by changing BINLOG_FORMAT to MIXED and increasing max_allowed_packet to 16M.

However, shifting config.php CFG->dbtype to "mariadb" creates a disconnect issue on both systems. Do I need to reinstall/upgrade the Moodle with config set to mariadb?  There appears to be no driver under lib/adodb for mariadb.

評比平均分數： -
In reply to Christe McMenomy

Re: Connection Error after upgrading to mariadb

由Matteo Scaramuccia發表於

Hi Christe,
you have three different issues here in this thread:

  1. wrong binary log format - fixed
  2. wrong DB Type, as configured in the config.php file - Moodle is suggesting the right type for you (MDL-51080) and you should change it in your config.php file
  3. an issue with enrollments driven by 3rd party DB integration (external database enrolment plugin)

(3) is unrelated w/ (2); I mean: you can select anything for enrol_database | dbtype (Site administration > Plugins > Enrolments > External database, /admin/settings.php?section=enrolsettingsdatabase), based just on the vendor of the targeted database.
There, mariadb is not an option so you still need to use mysqli, when your "external database" is MySQL or MariaDB.

They are two different settings: (2) is for the database where Moodle data is hosted while (3) is for the database where Moodle should read the enrollments data from.

Are we missing some more details about your overall system setup?

HTH,
Matteo

評比平均分數：Useful (1)
In reply to Matteo Scaramuccia

Re: Connection Error after upgrading to mariadb

由Christe McMenomy發表於
We can eliminate issues 1 (fixed) and 3 (mysqli appears to be the only and also correct designation for the external database). I have never had a problem with connecting to the external database for login control.  The reason I reported that was because I was not sure if there should be a mariadb setting.


This leaves the issue with having upgraded the database to mariadb without having reinstalled the Moodle into the database.  I understood this was possible BUT if after the database upgrade I change the my.cnf CFG->dbtype variable from mysqli to mariadb, I cannot complete login.

I have two environments, and the #2 issue occurs in both the same way.

TEST environment: Mac OS Sierra running Apache 2.4.37and PHP 7.2.13 and Moodle 3.5.1 (Build 2018051701) with mariadb 10.3.11 (homebrew).  If config.php CFG->dbtype = "mysqli" then everything works.  If set to "mariadb", connection to the database fails.

I get the following error trace when the Moodle debug is set to maximum:

Debug info: 

Error code: generalexceptionmessage

Stack trace:

  • line 462 of /lib/adodb/drivers/adodb-mysql.inc.php: Error thrown
  • line 493 of /lib/adodb/drivers/adodb-mysql.inc.php: call to ADODB_mysql->_connect()
  • line 667 of /lib/adodb/adodb.inc.php: call to ADODB_mysql->_nconnect()
  • line 869 of /enrol/database/lib.php: call to ADOConnection->Connect()
  • line 144 of /enrol/database/lib.php: call to enrol_database_plugin->db_init()
  • line 245 of /lib/enrollib.php: call to enrol_database_plugin->sync_user_enrolments()
  • line 477 of /lib/classes/session/manager.php: call to enrol_check_plugins()
  • line 4414 of /lib/moodlelib.php: call to core\session\manager::login_user()
  • line 181 of /login/index.php: call to complete_user_login()


PROD environment: Linux (Gentoo) running Apache 2 (system does not show release detail), PHP 7.2,10, Moodle 3.5.1, and Mariadb 10.1.37.  This environement has exactly the same behavior after converting the MySQL db to mariaDB without reinstalling the Moodle. It runs fine if the config.php CFG->dbtype = "mysqli" but produces an error on login if the dbtype is set to mariadb: 

Debug info: 

Error code: generalexceptionmessage

Stack trace:

  • line 462 of /lib/adodb/drivers/adodb-mysql.inc.php: Error thrown
  • line 493 of /lib/adodb/drivers/adodb-mysql.inc.php: call to ADODB_mysql->_connect()
  • line 667 of /lib/adodb/adodb.inc.php: call to ADODB_mysql->_nconnect()
  • line 869 of /enrol/database/lib.php: call to ADOConnection->Connect()
  • line 144 of /enrol/database/lib.php: call to enrol_database_plugin->db_init()
  • line 245 of /lib/enrollib.php: call to enrol_database_plugin->sync_user_enrolments()
  • line 477 of /lib/classes/session/manager.php: call to enrol_check_plugins()
  • line 4414 of /lib/moodlelib.php: call to core\session\manager::login_user()
  • line 181 of /login/index.php: call to complete_user_login()

So my key question now is why is these systems failing if I set dbtype to mariadb?  Am I missing some library or executable? Is some other part of the Moodle 3.5.1 install (originally done with mySQL) keyed still to mySQL even if the config.inc variable is changed? I thought mariadb was supposed to be a drop in replacement that would not require further tweaking in the moodle environment, but obviously that's not the case.




評比平均分數： -
In reply to Christe McMenomy

Re: Connection Error after upgrading to mariadb

由Ken Task發表於
Particularly helpful Moodlers的相片

When you first converted from MySQL to MariaDB, did you run 'mysql_upgrade'?

While it should do that 'automagically', have always run that afterwards to check/make sure.   By now, you might need to execute that with --force option.  Forget to run it since I don't do many conversions ... weirdness was result ... --force fixed, however.

Ref:

https://mariadb.com/resources/blog/how-to-migrate-from-mysql-to-mariadb-on-linux-in-five-steps/

'spirit of sharing', Ken



評比平均分數： -
In reply to Christe McMenomy

Re: Connection Error after upgrading to mariadb

由Matteo Scaramuccia發表於

Hi Christe,
apologies if I do repeat myself but I'm not native english 眨眼.
If you look at your stack trace the error is thrown by the AdoDB library which is used in Moodle to talk w/ external DBs e.g. to manage enrolments and adodb-mysql.inc.php shows that you are using mysql and not mysqli as the dbtype configured in Site administration > Plugins > Enrolments > External database.
Have you double checked that setting in the administrative page /admin/settings.php?section=enrolsettingsdatabase?

When you successfully log into the system, the code then tries to sync the enrollments for that user and then it fails in accessing the external database: could you give me some more details about how your system has been configured to let me try to replicate your supposed bug in my dev env?

HTH,
Matteo

評比平均分數： -
In reply to Matteo Scaramuccia

Re: Connection Error after upgrading to mariadb

由Christe McMenomy發表於

My external database and its tables are in the same database system as the Moodle database tables, so both databases are now actually mariadb.

If both the config.php CFG->dbtype is set to mysqli and the Plugins > Enrolments > External database are set to mysql (mysqli is not an option), then everything works.

If both the config.php CFG->dbtype is set to mariadb and the Plugins > Enrolments > External database are set to mysql (mariadb is not an option), then the connection to the database fails. It is not clear which database cannot be accessed at this point -- the Moodle's db or the external enrollment db.  However, the ONLY thing I have changed is the CFG type for the Moodle db; I have not changed the otherwise working enrollment db type.


評比平均分數： -
In reply to Christe McMenomy

Re: Connection Error after upgrading to mariadb

由Matteo Scaramuccia發表於

Hi Christe,
I'll give it a try (need spare time to create a test bed for db enrol): Moodle 3.5.1 has msqli as an actual option for AdoDB DB type, https://github.com/moodle/moodle/blob/5b78584fdbda65ac516f39f9e66f9376a793de92/enrol/database/settings.php#L34.

In the mean time, I'll better follow your stack trace for a static analysis of your issue - still convinced that the issue is when talking w/ 3rd party db enrol via AdoDB.

HTH,
Matteo

評比平均分數： -
In reply to Matteo Scaramuccia

Re: Connection Error after upgrading to mariadb

由Christe McMenomy發表於

My external enrollment database type is set to mysqli because mariadb is not an option in the dropdown. It works if the CGF->dbtype is set to mysqli also, but NOT if the dbtype is set to mariadb.  I don't understand why changing the dbtype for the MOODLE should affect login for the external database, which was otherwise working.

評比平均分數： -
In reply to Christe McMenomy

Re: Connection Error after upgrading to mariadb

由Matteo Scaramuccia發表於

Hi Christe,
that's my thought too, even after having looked at the core code: you stack trace tells that the external DB type is set to mysql, w/o the trailing i.

You could change the code in your test env to look for the actual setting e.g.:

$ git diff enrol/database/lib.php
diff --git a/enrol/database/lib.php b/enrol/database/lib.php
index 941fb99b7f1..9e62efc29a7 100644
--- a/enrol/database/lib.php
+++ b/enrol/database/lib.php
@@ -857,6 +857,7 @@ class enrol_database_plugin extends enrol_plugin {

         // Connect to the external database (forcing new connection).
         $extdb = ADONewConnection($this->get_config('dbtype'));
+        echo $this->get_config('dbtype');
         if ($this->get_config('debugdb')) {
             $extdb->debug = true;
             ob_start(); // Start output buffer to allow later use of the page headers.

HTH,
Matteo

評比平均分數： -