Forums | Mahara Community
Tool for database verification
09 January 2014, 3:58 AM
I believe in our Mahara update to version 1.6.6 something went wrong
and the database does not have the status it should have.
The tables artefact_access_role, artefact_access_usr and grouptype_roles are
still of type MyISAM.
The default engine is MyISAM also registered with the server.
We noticed that when many users started to have difficulties
adding a text box to a porfolio. That takes very long if it works at all.
In slow-query.log then is, for example, the following:
# Time: 140108 9:47:32
# User@Host: mahara[mahara] @ xxx.xxx.uni-kassel.de [xxx.xxx.xxx.xxx]
# Query_time: 158.764502 Lock_time: 0.000345 Rows_sent: 5 Rows_examined: 64850
SELECT a.*, CAST(a.owner IS NOT NULL AND a.owner = '287' AS UNSIGNED) AS editable FROM "artefact" a
LEFT OUTER JOIN "artefact_parent_cache" apc ON (a.id = apc.artefact AND a.institution = 'mahara' AND apc.parent = 85507) WHERE (
a.owner = '287'
OR a.id IN (
FROM "group_member" m
JOIN "artefact" aa ON m.group = aa.group
JOIN "artefact_access_role" aar ON aar.role = m.role AND aar.artefact = aa.id
WHERE m.member = '287' AND aar.can_republish = 1
OR a.id IN (SELECT artefact FROM "artefact_access_usr" WHERE usr = '287' AND can_republish = 1)
OR (apc.parent IS NOT NULL)
OR a.institution IN ('unikassel')
) AND artefacttype IN('html')ORDER BY title ASC LIMIT 5;
In Moodle there is the tool "XMLDB-Editor" with which you check the consistency of the
database, is there something like that for Mahara?
09 January 2014, 10:09 AM
It has come to our attention that sites that use mysql and have been upgraded from version 1.0 have some tables where the foreign keys and indexes are not fully correct, and this can cause slowness on big sites.
A patch for this has been applied to master branch. https://reviews.mahara.org/#/c/2744/11/htdocs/lib/db/upgrade.php
The tables that need fixing are:
You should change your tables to InnoDB (http://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html) and add the missing keys.
That should sort out the slowness problems.
A good way to check what the db structure should be is to install a clean 1.6.6 Mahara and compare the db structure between it and the one you upgraded.
10 January 2014, 3:10 AM
thank you very much!
Mahara now works fine again ;-)
Greetings from Kassel