Looking to leverage the conversion script provided at https://docs.moodle.org/28/en/Administration_via_command_line, specifically the script that converts InnoDB to Barracuda.
I run the script as follows:
php admin/cli/mysql_compressed_rows.php -f
PHP Warning: Creating default object from empty value in /var/www/moodle/config.php on line 5
Cannot enable GLOBAL innodb_file_per_table setting, use --showsql option and execute the statements manually.Default exception handler: Error writing to database Debug: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
SET GLOBAL innodb_file_per_table=1
Error code: dmlwriteexception
* line 446 of /lib/dml/moodle_database.php: dml_write_exception thrown
* line 974 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 140 of /admin/cli/mysql_compressed_rows.php: call to mysqli_native_moodle_database->execute()
!!! Error writing to database !!!
Any suggestions would help - I would like to make the conversion a success
All the scripts in admin/cli/ require parameters from config.php.
Think this is the culprit: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Some folks, when they configure their moodle set up a moodle DB user that doesn't have 'super privs'.
So in whatever app you have to get to the config of the MySQL, set the moodle user seen in config.php for the ability to 'do it all'. Then attempt to execute the command line script. OR, temporarily, make the DB user in the config.php file the 'super user' (also change the DB password). Then attempt to execute the command line script again. If using the the second suggestion above, you might want to change back those items you changed.
As far as making your Moodle a little easier to administer from the command line, updates, etc.. think the second suggestion above might need to be the setting ... or increase the abilities of the user set up for Moodle to do it all.
'spirit of sharing', Ken
Thank you for the expedient reply - I apologize for not showing you the courtesy of a more immediate response.
Your guidance is well received. I will action it tonight and let you know.
Like a charm Ken. As usual, excellent explanation.
You de man!
Shared hosting providers will more than likely not honor such request as it affects multiple customers whose applications may not require anything specific for DB. Your Moodle is using a shared DB server.
From what I read about MySQL barracuda vs antelope (a name not previously used in MySQL) barracuda seems to be the way most apps are leaning towards - ie the trend. Hosting providers are sometimes slow to make such jumps and while they might eventually get there, thresh holds have to reach 'critical mass' before that happens.
Note: the same thing happens with PHP versions.
Two things I'd suggest you'd ask your provider:
Do they provide alternate DB servers that meet the requirements of Moodle? Might be some extra fees for you, BTW.
Do they offer a level up from shared hosted that you can move to? (usually that also involves more $)
IF you share the providers name, maybe others in these forums can share their solutions with you.
As far as Moodle is concerned ... don't believe I've ever seen Moodle regress (ie, go backwards). Actually, not sure I've seen anything 'go backwards'.
That's tech for ya ... onwards and upwards!
'spirit of sharing', Ken
I am having the same problem with Siteground. They refused to update the database because it affects other users on the server. I asked them if they could have a server dedicated to users that needed the Barracuda database. No Answer. Of course, they said I could have my own server for a price 4x-10x what I am paying now. Since, I am running Moodle from my own pocket and for my own class I cannot afford to update to a dedicated server.
Do you have to update to Barracuda using 2.9?
Does anyone know of a provider that will update the database?
I don't have command line access. I do have access via myphpadmin. But, like I said the company refuses to update their database as other users are on the same server.
So, I have a choice: go below 2.9 and not be able to update until hosting companies get on board, find another hosting service or stop using Moodle that I have spent years learning.
$ php admin/cli/mysql_compressed_rows.php -s
Copy the following SQL statements and execute them using account with SUPER privilege:
SET SESSION sql_mode=STRICT_ALL_TABLES;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
ALTER TABLE mdl_data ROW_FORMAT=Compressed;
ALTER TABLE mdl_data_fields ROW_FORMAT=Compressed;
ALTER TABLE mdl_enrol_paypal ROW_FORMAT=Compressed;
ALTER TABLE mdl_lti ROW_FORMAT=Compressed;
ALTER TABLE mdl_user ROW_FORMAT=Compressed;
ALTER TABLE mdl_user_info_field ROW_FORMAT=Compressed;
After that you can enter those SQL commands in GUI tool like phpMyAdmin.This may be risky. May be somebody with the same Moodle version and commandline access would post the output of 'mysql_compressed_rows.php -s' here.
(Edited by Mary Cooch - original submission Wednesday, 20 May 2015, 3:45 PM)
I set innodb_file_per_table=1 in my "my.cnf" file on my server. It appears to me that some of these commands are directed to the mySQL database server itself (like indoor_file_per_table=1 and innodb_file_format=Barracuda), and some are directed to the specific moodle database (like all of the ALTERs). Is this correct?
If correct, then it seems logical to me that I should put innodb_file_format=Barracuda into my my.cnf file. Right?
How about that SET SESSION sql_mode=STRICT_ALL_TABLES; command, where should it logically go?
Somewhere, it seems like I saw more information about Barracuda and my.cnf. I am going to have to keep digging into this.
Yes, I see that in myPHPadmin, I should be able to issue all of the ALTERs, and I should only have to do this one time. All future upgrades will honor these alterations. Right?
Have you tried a fresh Softaculous Moodle install in a subdomain to test if fresh installations are created with the correct DB format?
I find it hard to believe that they'd create new instances with dud databases, it might just be that they can't(Don't want to) affect existing instances on the cheap accounts.
If a new install creates using the correct Db format you could try migration or MNET to that instance. Otherwise....
Good idea. I'll give it a try
Sorry, I forgot to email you back after I tried using Softaculous. So far, knock on wood, everything has been working fine.
I've done all those things. They wanted a lot more money for a dedicated server. I am just running my site for me and one other teacher.
Does anyone know of a site with shared servers that will run the newest Moodle?
I just did an update from 2.8.5 to 2.9 on a Siteground shared hosting site. Although they still use InnoDB, everything appears to be running fine. The Moodle checks during the upgrade didn't flag this as a blocker - just something to check. The only issues for me were a couple of Blocks where the write permissions needed to be changed.
As i said, I've only just done this (about an hour ago) but I really can't identify any issues at the moment. If anything appears, I'll update here.
All the best
I've just started going through the thread on migrating to 2.9 and found your post. I will keep looking, but you might be able to answer my question in a way that will let me avoid having to do that.
I do not have access to the command line, but I can apparently run the script ]
Is this how you upgraded? If yes, this would be the easiest since I am newbie enough that I don't know how to find the right place to alter the database using phpMyAdmin.
Finally, if you did upgrade using the script above, did you have to do anything with this addendum to the instructions in the Moodle documentation?
Add the following statement to /etc/my.cnf under the [mysqld] heading: "binlog_format=ROW" (without the quotes) and stop/restart MySQL. Without this statement you will encounter the dmlwriteexception error when moodle attempts to write to MySQL.
I have looked in my directory on my site (I can do that much!) but I don't see the my.cnf file. I'm wondering if that's only for people hosting on their own computer or using something like Linux (and my having to wonder shows my knowledge).
The helpline is very helpful, but recommendations that I ask the support person at my university unfortunately requires asking myself, and I don't know.
Thanks to anyone who can help!
I do my upgrades as follows - In Server | Update notifications, I have the option Enable updates deployment checked. Then I do the upgrade directly from Site administration |notifications
I *think* you're right about the addendum. I use hosted servers and have never had to do that (or that may be because of the above).
As a footnote, the system is still running fine!
All the best
I also finally looked at a video through a link on the Moodle documentation and see that it looks like I can also do a find and replace for MyISAM with InnoDB that should work if I need to. I was missing the step where you need to actually "drop" (delete?) the database you want to update.
I'm not sure whether I should try the script when the provider seemed to be so dead set against it.
The simple update you refer to for upgrading is the same one you can use to easily update plugins too, right? Unfortunately for me on my hosted server I get the friendly message...
You don't have permission to access /moodle/mdeploy.php on this server.
Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.
...and I don't know what or where to change things so I can access it. Is that something you worked out?
Anyway, thanks for the reply. I will get there eventually, but I don't to tinker with this when I have a class tomorrow morning my time.
Hi, Steve. I am hosting on WebhostingHub, which I find great. They have a softaculus tool for installing and upgrading Moodle, easy as pie (part of Cpanel).
But with Moodle 2.9, they required me to upgrade MyISAM to INNODB, just like you. I also tried running the script. No joy. (I do have shell access, but am finding it difficult to run SSH with a Key in Terminal, WebHosting wants me to use PuTTy.)
Anyway, I ended up doing a database dump, find and replace, and an upload. My DB was so large I had to get help from support to finish the upload, but it worked.
The only problem is that now I should convert from Antelope to Barracuda (file formats). (Recommended, but it still works.)
You probably finished this a long time ago, but thought it might help others.
I tried manual export, substitute, import with no success. I'm sure there's something basic I'm doing wrong. 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?
What happens is after I delete the database and then try to import the edited file the database remains empty. However, at that stage if I import the original backup that works fine, so the problem is with the edited file. Uh, right?
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 advice appreciated.