22 April 2017, 3:28

We've been asked to take over an existing Mahara installation.

As part of that process I've been trying to convert the db from mysql to postgres. I used a great tool called pgloader to do the conversion and thought things were good, but when I run the application I get errors of the form shown below. It complains about a type mismatch of boolean to 0, which postgres does not like.

The thing is, when I first encountered this error I thought it was my conversion. I had initially created a fresh mahara install against a pg db and thought things were working. I then tried converting my mysql backup to postgres and running that but ran into these errors. So to try and  eliminate the converted db structure as a possibility I created a fresh mahara install, initialized the db and then ran the conversion telling it to truncate existing data before inserting, i.e. copy the mysql data into a pg db that had its structure created by mahara, no data. The errors below still appeared. What I find confusing is I would have expected mahara to know at this point that it is using pg and have correct syntax, i.e. 'i.suspended = false' because it appears that it is a malformed query that is in error and not the data. But then it should not have worked with my initial clean install, if I did everything correctly (which is an open question).

Another possibility is the adodb library bundled with Mahara, maybe?

In any event, before I go down this rabbit hole, I wanted to post this to see if anyone is familiar with this type of issue and can advise.

Note that this is working with:
Mahara version 15.04.9 (pulled from mahara.org)
Postgres 9.6 (and tried9.4 on another server)

I'm trying to get things converted, using my local pc as a dev env and upgrade to the latest Mahara release when things are working.

Suggestions?

cheers,

john

Example of errors:
------------------

 Call stack (most recent first):
   * log_message(string(size 1054), integer, true, true) at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\lib\\errors.php:97
   * log_warn(string(size 1054)) at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\lib\\errors.php:822
   * SQLException->__construct(string(size 1054)) at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\lib\\dml.php:880
   * get_column_sql(string(size 367)) at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\auth\\lib.php:1393
   * auth_get_enabled_auth_plugins() at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\auth\\lib.php:1291
   * auth_get_login_form_elements() at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\auth\\lib.php:1157
   * auth_get_login_form() at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\auth\\lib.php:504
   * auth_setup() at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\init.php:398
   * require("C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\init....") at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\index.php:16
 [WAR] b8 (lib\\dml.php:880) Failed to get a recordset: postgres8 error: [-1: ERROR:  operator does not exist: boolean = integer
 [WAR] b8 (lib\\dml.php:880) LINE 10:                 i.suspended = 0 AND
 [WAR] b8 (lib\\dml.php:880)                                      ^
 [WAR] b8 (lib\\dml.php:880) HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.] in EXECUTE("SELECT
 [WAR] b8 (lib\\dml.php:880)                 DISTINCT(authname)
 [WAR] b8 (lib\\dml.php:880)             FROM
 [WAR] b8 (lib\\dml.php:880)                 "auth_instance" ai
 [WAR] b8 (lib\\dml.php:880)             JOIN
 [WAR] b8 (lib\\dml.php:880)                 "institution" i ON ai.institution = i.name
 [WAR] b8 (lib\\dml.php:880)             JOIN
 [WAR] b8 (lib\\dml.php:880)                 "auth_installed" inst ON inst.name = ai.authname
 [WAR] b8 (lib\\dml.php:880)             WHERE
 [WAR] b8 (lib\\dml.php:880)                 i.suspended = 0 AND
 [WAR] b8 (lib\\dml.php:880)                 inst.active = 1
 [WAR] b8 (lib\\dml.php:880)             ORDER BY authname")
 [WAR] b8 (lib\\dml.php:880) Command was: SELECT
 [WAR] b8 (lib\\dml.php:880)                 DISTINCT(authname)
 [WAR] b8 (lib\\dml.php:880)             FROM
 [WAR] b8 (lib\\dml.php:880)                 "auth_instance" ai
 [WAR] b8 (lib\\dml.php:880)             JOIN
 [WAR] b8 (lib\\dml.php:880)                 "institution" i ON ai.institution = i.name
 [WAR] b8 (lib\\dml.php:880)             JOIN
 [WAR] b8 (lib\\dml.php:880)                 "auth_installed" inst ON inst.name = ai.authname
 [WAR] b8 (lib\\dml.php:880)             WHERE
 [WAR] b8 (lib\\dml.php:880)                 i.suspended = 0 AND
 [WAR] b8 (lib\\dml.php:880)                 inst.active = 1
 [WAR] b8 (lib\\dml.php:880)             ORDER BY authname
 Call stack (most recent first):
   * get_column_sql(string(size 367)) at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\auth\\lib.php:1393
   * auth_get_enabled_auth_plugins() at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\auth\\lib.php:1291
   * auth_get_login_form_elements() at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\auth\\lib.php:1157
   * auth_get_login_form() at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\auth\\lib.php:504
   * auth_setup() at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\init.php:398
   * require("C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\init....") at C:\\Bitnami\\wappstack-5.6.30-2\\apache2\\htdocs\\index.php:16
 

02 May 2017, 9:11

Hi John,

It sounds like the way mysql saves some data is not quite the same as postgres, eg string 0 vs integer 0

One thing you could try with migrating data from one db to another is writing a script that uses two db connections. One to mysql and one to postgres. That way you can read the data into php with the mysql connection and save it again with the postgres connection.

I helped out with a script that did this for a migrating data from a v1.9 Mahara using mysql into an institution on another 1.9 Mahara using Postgres.

dbmigrate script

You should be able to extract it into the htdocs/ of the site you are moving the db from and then go into the htdocs/local/dbmigrate/ directory and run

sudo php cli.php

It will prompt you for the db info for the destination database

The cli.php script is the 'recipe' of what will be copied and how/where it will end up. Please check it/tweak it as you need.

To do a migration the 'from' and 'to' sites need to be on the same versions for it to work.

By the sounds of things you won't need something as complicated as the current recipe as it was designed to add an existing mahara site into another existing mahara site as an institution.

But hopefully it gives you some idea of how one can use the two db connections to pass data from one to Mahara to another

Cheers

Robert

03 May 2017, 6:27

Hi Robert,

Thanks for the reply.

I eventually used FromMySqlToPostgreSql. Not as feature rich, but pretty specific to my use case and not too hard to tweak. I'm still evaluating the results but it looks good so far. Issues that aren't handled by the script I can handle in the db.

Of course the major issue, PEBCAC, I still have to deal with.

I may take a look at your script for inspiration!

Thanks again, John