Tables of the Moodle DB should be InnoDB.
If your system is on linux and you have ssh access there is a php script that will convert all tables of the database to innodb located in moodle/admin/cli/
[root@moodle cli]# php -f mysql_engine.php
MySQL engine conversions script.
It is recommended to stop the web server before the conversion.
Do not use MyISAM if possible, because it is not ACID compliant
and does not support transactions.
Options:
--engine=ENGINE Convert MySQL tables to different engine
-l, --list Show table information
-h, --help Print out this help
Example:
$sudo -u www-data /usr/bin/php admin/cli/mysql_engine.php --engine=InnoDB
IF you don't have ssh access, one will have to use some other MySQL DB tool to convert all tables. PhpMyAdmin is one that could do.
If you are remotely hosted, there could be some control panel/tool for interacting with MySQL. More than likely, should be able to accomplish the same there.
'spirit of sharing', Ken
Hi Ken,
Thanks for the response. Really appreciated. I have made a backup of my database before starting.
I have found a file in docs http://docs.moodle.org/21/en/Migration_from_MyISAM_to_InnoDB but I do not know how to run a command in PHPMyAdmin.
Can you help please as I can't find more detailed instructions anywhere.
Well, let me retract that phpmyadmin could do ... from what I've read, it can't do entire DB. :\ Sorry bout that.
According to the page you've provided:
1st and foremost ... make a backup of the DB - an sql dump. Download that sql dump. Make a copy of that download sql file and work with the copy.
The last item mentioned uses a combination of editing a SQL dump of the database (searching for old engine and replacing with new engine) then re-importing the DB (replacing existing) using PHPMyAdmin.
Even has movies. Please see that one.
'spirit of sharing', Ken
Hi Ken,
Thanks for the response - now I feel like a rabbit in headlights!
As it's gone 1am here I think I'm gonna leave this till the morning when I'm hopefully more able to understand what to do.
Pray for me lol
Well, let's start at the beginning then ... with a question ... do you want a fish or do you want to learn to fish?
If the later, first step is learning how to use the tool to which you have access to backup the database ... ie, make an .sql dump of the database.
Sounds like your Moodle is remotely hosted. If that's true, who is provider and do they have an FAQ/Tutorial/Forum for usage of their tools, like phpmyadmin.
If they don't, Google for: phpmyadmin backup database
You might find hits that don't mention Moodle at all ... that's ok, we're learning how to backup ANY database using PhpMyAdmin so the application doesn't really matter.
That link that went to Google Groups has something you could follow, I think.
Have to ask ... did your provider have a 'one button' click install of Moodle? Hmmmm .... sounds like they set the hook if they don't provide information for how to maintain what they installed for you AND if they don't provide at least information on how to upgrade.
That's my 2 cents, of course.
'spirit of sharing', Ken
Hi Ken,
I have been running a live moodle course since 1.2 and up to now have never had a problem with upgrading or managing my installation.
To date I have never had to do anything to the database. The change to version 2 is the only thing that has given me problems and of course I would love to learn how to manage the database but have so far never had the need to.
I have tried to innodb.php but the actual file appears to be missing from /admin/
I found this http://virtualizesharepoint.com/2011/05/11/convert-myisam-to-innodb-using-phpmyadmin-for-moodle/ which gives step by step instructions for doing it manually in phpmyadmin.
I am off to do this and see what happens.
Thanks again
Hi All,
I am sorry I did not see this sooner, because in my opinion, innodb.php is the easiest method to convert all the tables, but Moodle 2.2 DOES have innodb.php...it has just been moved from its old location. To run it, log in as admin, then go to:
http://yourdomain/yourmoodle/admin/tool/innodb/index.php
then hit Enter.
Moodle will ask if your sure you want to convert from MYISAM to InnoDB, so click Yes.
Don't worry, if you have already converted, it will tell you so, and just give you a continue button. When You have a number of Moodle versions like I do, it really makes it convenient to be able to check so easily.
Anyway, after you click yes, sit back and wait for it to finish, then click Continue.
HTH,
AL
Hi Al,
Thanks for that (if it was a little late for me it will help someone else).
Perhaps the docs file should be changed to reflect where it is in different versions of moodle.
And talking of docs - can this be implemented on our own sites and how?
Annie,
I don't see why not...I do it on my Moodle but usually I only make entries about things that have directlly affected me. I have a Miscellanious category with various courses in it that are available to only me. Some of the courses are experiments but one is "Installing Moodle" in which I have a number of wiki's. (Truthfully, though, since Moodle 2 came out and there seems to be no upgrade path for dfwiki, I have been converting them all to Books.) This course is the first thing I transfer from an old Moodle to a new one when I do a from scratch install.
I have various wiki's/books in which I keep all kinds of notes such as how to set up my Linux server, create multiple versions of Moodle on the same server without them interfering with each other, converting database tables from MYISAM to InnoDB, etc.
Sometimes when I am digging through the online documentation at the Main Moodle SIte, I will occasionally run across something good to know and will quickly create a version of it in my stuff...I got less than they do which makes it easier for me to find again, later on.
I also sometimes just make a hot link to sections of the Moodle documentation when I know it is undergoing updates.
One thing that helps too, is I always have a small paper journal in which I make notes as I work and make modifications on my server. Later, time permitting, I transcribe the notes into the appropriate book.
HTH,
AL
I am running 2.2.4+ and when I run the command in the URL it seems to work but the database dose not change I still get the notification stating I need to convert my DB inInnoDB. Any ideas?
tom
Tom,
I had that happen last week and I am not sure which thing "fixed" it as I was working on a bunch of things on two different servers and I know that I restarted Apache and eventually rebooted the server which of course restarted MySQL...anyway the next time I checked, (just now) everything was Innodb.
Maybe that will work for you,
AL
PS: OOOH!!! After posting I just remembered before my edit time ran out...I used phpMyAdmin, selected the database, clicked on Operations, then copied the database to a new name. Once it was done making the copy, I renamed the original to backup. Then I Renamed the new database to the right name I needed. The innodb.php file then worked on the new copy of the database and changed all the tables of the new database to type of InnoDB. Once I used the new database for a day I deleted the backup...must have been something wrong with it.
HTH,
AL
That's great ... so you've had some experience with older versions but around 1.6 to 1.7/8 one did have to convert the DB to UTF8. Maybe you forgot. ;)
Version 2 is a complete re-write of code and there is much more emphasis on the database than before (document management). So think you'll be learning more DB administration than before. Of course that depends upon the size of the Moodle implementation.
The reference you found showing conversion of the engine shows how to convert one table. The engine has to be changed for ALL tables - ie, the DB.
This one shows that:
http://virtualizesharepoint.com/2011/05/13/convert-mysql-database-from-myisam-to-innodb/
At one time there was a page on Moodle.org which showed how to do a search and replace in an editor - can't find it now! Found that much easier to do than the number of steps shown in the two phpmyadmin links.
Basically: export DB to sql, edit sql, drop original database, re-create new moodle db using UTF8 and collate utf8_general_ci, then import edited sql into the new DB. Done.
Best of luck!
'spirit of sharing', Ken
Hiya,
You are right - I had forgotten about the conversion to UTF8!
And I have now manually altered every single table by hand - sigh!
The only problem I seem to have now is backups. I got this email ...
Summary
==================================================
Courses: 32
OK: 0
Skipped: 0
Error: 32
Unfinished: 0
Some of your courses weren't saved!!
When you click on the link there is an error next to each of the 32 courses but doesn't say what the error is.
Should I start a seperate forum message for this?
Moodle 2 backups are now .mbz's (really zips). And the database stores them all ... unless using automated backups and one has selected an alternative folder in addition to the 'files area' (highly recommended, IMHO!).
Check in moodledata/temp/backup/ folder. Successful backups will show a 'hashname'.log file of 0 byte size.
-rw-rw-rw- 1 root root 0 Jul 7 17:05 106903480690e7d928e496e40c303cf2.log
Failed backups will have a 'hashname'.log file that is larger than 0 bytes and logs the problems. There is no administrators link to that folder nor the .log files - have to be viewed by some other means.
Failed backups also have a folder by hashname. Something like:
44037826f622a68227a61dadf9e9a2f8
Changing into those directories and viewing the contents, one will see folders/files that were to be used to construct the .mbz files. Inspection of the moodle.xml file (text file which could be opened by any text editor) might provide some clues as to reason for failure.
Possible reason for failures *could be many* ... like lack of drive space or allocation if remotely hosted for the DB.
This response is by no means definititive ... others feel free to jump in and share your experiences with failed backups.
'spirit of sharing', Ken
There is no administrators link to that folder nor the .log files - have to be viewed by some other means.
Doesn't .../report/backups/index.php effectivly provide that access? Or are we thinking of different things?
While that does show a chart of courses and if they completed ok, it doesn't show the actual .log file itself. In the case of failed restores, the .log file does (or did in the past) show items it could not find a handler for and/or other errors. In version 2.3, doing automated backups, I now see:
-rw-rw-rw- 1 root root 1323 Jul 9 08:00 730848f4c2001bb794ce3a82342fe879.log
-rw-rw-rw- 1 root root 1323 Jul 9 08:01 79c07573af4c8c9c5559f0146e31d7fd.log
-rw-rw-rw- 1 root root 1323 Jul 9 08:01 9ac5b63e81d737bfccba5d6806709d43.log
Those were *not* present in 2.2.x. Matter of fact, one would see a 0 byte .log file if the backup were successful.
An example of one such file as listed above:
[Mon 09 Jul 2012 08:00:37 CDT] [debug] setting controller status to 100
[Mon 09 Jul 2012 08:00:37 CDT] [debug] loading controller plan
[Mon 09 Jul 2012 08:00:38 CDT] [debug] setting controller status to 300
[Mon 09 Jul 2012 08:00:38 CDT] [debug] applying plan defaults
[Mon 09 Jul 2012 08:00:38 CDT] [debug] setting controller status to 400
[Mon 09 Jul 2012 08:00:38 CDT] [info] checking plan security
[Mon 09 Jul 2012 08:00:38 CDT] [debug] setting controller status to 700
[Mon 09 Jul 2012 08:00:38 CDT] [debug] saving controller to db
Now I realize not all Admins would want to see that 'stuff', but would something different show on a failed backup now in 2.3?
Am curious is there any documentation concerning the 'controller status' number. And, if that is something an admin could use or not.
'spirit of sharing', Ken
Thanks for that Ken,
I have just logged into my control panel to see what the files say and have discovered that a lot of the folders are blocked by the hosting company because they are 777 or 666. Could this be what is causing the problem? And if so, what should I set them to?
Also, the cron this morning says ....
The cron for the backup this morning says ....
backup_auto_failed_on_course MYSITE Home
Exception: ddlexecuteerror
backup_auto_failed_on_course COURSE1
Exception: ddltablealreadyexists backup_ids_temp
Hi Visvanath,
Thanks for that - I remembered that you had answered that question about file permissions when I tried to do an install of 2.2 and import 1.9 courses and users. (didn't know at the time that you couldn't import 1.9 user data). The problem was that I couldn't find the answer again.
I am now in the process of CHMOD all the files for 2.2 - having to do it individually as there seems no easy way of doing it in bulk.
I intend moving to 2.3 as soon as all these problems are cleared. In the interests of the cleanest site possible should I
(a) upgrade from 2.2 to 2.3 or
(b) do a fresh install of 2.3 and import the courses and user data from 2.2 (I've never done this before and have meta-courses)
Hi Visvanath,
Good idea to split the topic and have done as you suggested and messaged the moderator.
Found a way to CHMOD with dreamweaver. Do I need to CHOWN?
