Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Michael Otto -
Number of replies: 25

Hey all

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

[array (

)]

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

Thanks


Average of ratings: -
In reply to Michael Otto

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Ken Task -
Picture of Particularly helpful Moodlers

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.

This also assumes the DB host is localhost or a dedicated DB server (NOT shared).

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

Average of ratings: Useful (5)
In reply to Ken Task

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Michael Otto -

Hey 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.

Mike

Average of ratings: -
In reply to Michael Otto

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Michael Otto -

Like a charm Ken. As usual, excellent explanation. 

You de man! wink

Mike

Average of ratings: Useful (1)
In reply to Michael Otto

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Ken Task -
Picture of Particularly helpful Moodlers

<begin_red_in_face>Awww ... geee! </end_red_in_face>

However, credit where credit is due ...  * @author of the script:    Petr Skoda.

but thanks for the +1.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Guido Hornig -
Picture of Plugin developers

I talked to my hosting service to do the changes in the mysql setup. But they denied.

Saying it is not an issue. And they do not reconfigure their whole mySql server.

If the barracuda filesystem becomes necessary for every moodle installation, I have find a new hosting service?

Average of ratings: -
In reply to Guido Hornig

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

Are you having restore issues?  Have you tried changing the ROW format?  You might not need to convert...

Average of ratings: -
In reply to Guido Hornig

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Ken Task -
Picture of Particularly helpful Moodlers

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


Average of ratings: -
In reply to Ken Task

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Mel Ausman -

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?


Average of ratings: -
In reply to Mel Ausman

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
The kind of database updates we are talking about are done through SQL commands. If you have command line access you can follow this post: "Re: Converting InnoDB tables to Barracuda" https://moodle.org/mod/forum/discuss.php?d=312863#p1255850.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Mel Ausman -

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.


Average of ratings: -
In reply to Mel Ausman

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Your problem is to get the output of the following command in your Moodle version (the example below is for 2.7.7+): $ php admin/cli/mysql_compressed_rows.php -s 

Copy the following SQL statements and execute them using account with SUPER privilege:

;USE dbname; 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)

Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Visvanath, I am on vacation but still trying to work through some of the "Barracuda" stuff.  Your posts are helpful.  I actually like the way you present this information in your post above, but I have a few questions.

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?

Average of ratings: -
In reply to Rick Jerz

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi Rick,
that command is directed to the session within the other commands will run, regardless what configured at global i.e. server level, to enforce data integrity.

HTH,
Matteo

Average of ratings: -
In reply to Mel Ausman

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Vernon Spain -
Picture of Plugin developers Picture of Testers

Hi Mel,

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....


Regards,

Vern

Average of ratings: -
In reply to Vernon Spain

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Mel Ausman -

Good idea.  I'll give it a try  

Average of ratings: -
In reply to Vernon Spain

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Mel Ausman -

Sorry, I forgot to email you back after I tried using Softaculous.  So far, knock on wood, everything has been working fine.


Average of ratings: Useful (2)
In reply to Ken Task

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Mel Ausman -

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?  

Average of ratings: -
In reply to Mel Ausman

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Andy Chaplin -

Hi Mel

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



Andy


Average of ratings: -
In reply to Andy Chaplin

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Steve McGuire -
Andy,

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 ]

http://www.YOUR_MOODLE_SITE/admin/tool/innodb/index.php

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!
Average of ratings: -
In reply to Steve McGuire

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Andy Chaplin -

Hi Steve,

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


Andy

Average of ratings: -
In reply to Andy Chaplin

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Steve McGuire -
Andy,

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...

***
Forbidden

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.

Steve
Average of ratings: -
In reply to Steve McGuire

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Kevin Ryan -

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. 

This is the method I used, (4th in the list of the main Document on conversion. 

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. 

Average of ratings: -
In reply to Kevin Ryan

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Steve McGuire -
Now that I am about to start a new semester I finally tried to migrate from MySAM to InnoDB so I can upgrade to 2.9.

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.
Average of ratings: -
In reply to Steve McGuire

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
This is the most chaotic thread I've seen for a long time. Here a couple of people are discussing at least two topics, all in one!

P.S. Converting MyISAM tables to InnoDB is one thing. Converting InnoDB tables (from Antelope) to Barracuda is something else.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Error Converting InnoDB tables to Barracuda - Moodle 2.8.5 - 2.8.5+ (Build: 20150501)

by Steve McGuire -
My apologies for posting in the wrong thread. I should have read more carefully. I looked to see if I could delete my post but couldn't.
Average of ratings: -