Now that I am about to start a new semester I finally tried to migrate from MyiSAM to InnoDB so I can upgrade from Moodle 2.8+ to Moodle 2.9+.
I think I have followed all the steps but I'm stuck on the last one. I am using LinkSky and the quick and easy script to do this isn't allowed.
Here are the steps I tried following the steps on Moodle Mayhem.
1. Manual export to a sql file
2. Substitute all occurrences of MyiSAM with InnoDB (this is where the problem is, I think)
3. Delete the database (this was worrisome since my system didn't match the video)
4. Manual import of the converted sql file
After doing the the database is blank. I can reload the backup of the previous database so that is not the problem.
I'm sure there's something basic I'm doing wrong, and I think it's in step 2. Could it be as stupid as that I changed the extension to .txt so I could edit using Mac TextEdit and then change it back to sql afterwards? If so, please refrain from using the word "stupid" in your reply, although I'll take any advice regardless.
If there are no easy solutions forthcoming, I will have to hire someone. Otherwise I'm stuck at 2.8. Any recommendations for finding someone to do what I think is a simple job?
Any and all useful advice appreciated.
If you have downloaded the .sql dump to your Mac, all one needs to do to open it is drag the icon for the .sql file atop the TextEdit App icon. TextEdit will open it - no need to change extension.
CMD f to do a find. Check the box for 'Replace' in the top bar.
Find line: ENGINE=MyISAM
Replace line: ENGINE=InnoDB
TextEdit will highlight the first instance it finds. Click the 'Replace' button and TextEdit will replace the instance it highlighed and jump to the next. Do that just a couple of times to assure yourself it is doing what it should.
Then, click the 'All' button. Wait! It might take some time depending upon size of sql file.
When it's done, scroll down in the file a little to see references.
Save the file: [CMD] s. [CMD] q to quit.
Upload the .sql file so you can import it into a new db via PHPMyAdmin.
The TextEdit part looks like image attached.
'spirit of sharing', Ken
Ken,
Thank you very much for the detailed reply. I tried following the steps, including adding the lines suggested at Moodle Mayhem
Place "SET AUTOCOMMIT = 0;" without quotes at the start of the SQL dump file
Place "COMMIT;" without quotes to the end of the SQL dump file
I may be doing something stupid, but I deleted the original database and then recreated it with the same name, then I go to phpmyadmin and import the compressed modified SQL file. After I do that, there is no change in the database.
I’m a little worried now, though, that I can’t seem to upload the original SQL file anymore.
Any suggestions regarding where I can look to see what is going on?
Sorry for all the basic questions. I appreciate anyone’s help and advice!
Thank you very much for the detailed reply. I tried following the steps, including adding the lines suggested at Moodle Mayhem
Place "SET AUTOCOMMIT = 0;" without quotes at the start of the SQL dump file
Place "COMMIT;" without quotes to the end of the SQL dump file
I may be doing something stupid, but I deleted the original database and then recreated it with the same name, then I go to phpmyadmin and import the compressed modified SQL file. After I do that, there is no change in the database.
I’m a little worried now, though, that I can’t seem to upload the original SQL file anymore.
Any suggestions regarding where I can look to see what is going on?
Sorry for all the basic questions. I appreciate anyone’s help and advice!
So there was something in the sql file when you finished the TextEdit search and replace. Right?
You should still have that file. Open with TextEdit again and remove the two lines you added. By default, MySQL runs with
autocommit mode enabled (https://dev.mysql.com/doc/refman/5.5/en/commit.html) If you notice the screen shot made above, there is no line for 'commit". Save the file. Have done this exact thing more times than I can remember and never added anything to the file.
Can you create a new DB using your PHPMyAdmin? If so, create a new one naming it moodle2 and give it character set utf8 collate utf8_general_ci. Then import the edited sql dump (the one without the commit lines added) into the moodle2 (the new one) DB.
Maybe it's time to remove the link to Moodle Mayhem's article.
'spirit of sharing', Ken
Ken,
Well, I tried following your suggestions. Still no joy. I even tried loading the SQL database for the premodified database and that still no longer works.
By the way, I tried it first without the additional lines and added them afterwards in hopes that that was what I missed.
I'm glad I tried it on moodle2 following your suggestion since I still have moodle 1 (now restored by the host from a backup 14 hours previous) to work with.
A colleague always used to say, "It must have been something you did" when things like this happened, and I'm sure he was right.
Any ideas? Thank you in advance if you'd be willing to stay with me on this. Failing that, I want to hire someone who knows what they are doing. I think this would take them about 5 minutes tops where it is already taking me a long time.
Steve
Well, I tried following your suggestions. Still no joy. I even tried loading the SQL database for the premodified database and that still no longer works.
By the way, I tried it first without the additional lines and added them afterwards in hopes that that was what I missed.
I'm glad I tried it on moodle2 following your suggestion since I still have moodle 1 (now restored by the host from a backup 14 hours previous) to work with.
A colleague always used to say, "It must have been something you did" when things like this happened, and I'm sure he was right.
Any ideas? Thank you in advance if you'd be willing to stay with me on this. Failing that, I want to hire someone who knows what they are doing. I think this would take them about 5 minutes tops where it is already taking me a long time.
Steve
Hi,
Do you know if InnoDB is supported on your host server? It's a compile-time option for MySQL and I've had hosts in the past that only supported MyISAM.
I guess you could find this out by clicking the option to create a new table in phpMyAdmin and seeing what's listed or (better) run the statement "SHOW ENGINES" using the SQL option in phpMyAdmin which lists what's available.
Assuming InnoDB is supported, you could change the 300 or so tables' engines one by one in phpMyAdmin, a laborious and potentially error-prone task (create a database backup first!).
As far as getting direct assistance is concerned hopefully there are Moodle Partners available in your location (https://moodle.com/partners/).
Leon