Converting InnoDB tables to Barracuda

Converting InnoDB tables to Barracuda

by DeWayne Demland -
Number of replies: 49

now that I have corrected my mistake, I'm now getting the message to convert the table. On this page it states:


The error is due to the fact that the MySQL default InnoDB file format (Antelope) cannot handle more than 10 text columns. Thus you are recommended to change the InnoDB file format to Barracuda using the following command: 

 php admin/cli/mysql_compressed_rows.php
 

I opened a terminal and typed that in. It states:

dewayne@APTServer:~$ php admin/cli/mysql_compressed_rows.php
Could not open input file: admin/cli/mysql_compressed_rows.php


I'm not very experienced with this. Can anyone help enlighten me as to how I am doing this incorrectly.

Average of ratings: -
In reply to DeWayne Demland

Re: Converting InnoDB tables to Barracuda

by Simey Lameze -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Peer reviewers Picture of Plugin developers Picture of Testers

Hi, are you in the correct path? This command must be executed on your moodle www root  directory. Ex. /var/www/htdocs/moodle/moodle

Average of ratings: -
In reply to Simey Lameze

Re: Converting InnoDB tables to Barracuda

by Enrique Robredo -

Hi

I have this message:

Cannot enable GLOBAL innodb_file_per_table setting, use --showsql option and execute the statements manually.!!!

Thanks

Average of ratings: -
In reply to Enrique Robredo

Re: Converting InnoDB tables to Barracuda

by erotavlas erotavlas -

Hi,

I do not know if you already solved your problem. However, just in case. You have to tyoe:

sudo php admin/cli/mysql_compressed_rows.php --showsql

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

USE Sql790278_1;
SET SESSION sql_mode=STRICT_ALL_TABLES;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
ALTER TABLE mdl_hotpot_cache 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;

Then you have to open mysql cli and type each row one by one and at the end you solved.


Average of ratings: Useful (1)
In reply to erotavlas erotavlas

Re: Converting InnoDB tables to Barracuda

by Kevin Bruton -

This worked well for me.

Although it has to be noted that after entering the command

sudo php admin/cli/mysql_compressed_rows.php --showsql

The commands that you have to enter one by one, are shown in a customized way, according to your specific Moodle installation, ie. Database name etc. so they are the commands to have to enter, and not the literal example above.

Cheers!

Average of ratings: -
In reply to DeWayne Demland

Re: Converting InnoDB tables to Barracuda

by Juan Pablo Miranda Aguayo -
Hi,

Hope this could help someone...

I had the same after installing 2.8.5+ version into dedicated server.

I tried with "php admin/cli/mysql_compressed_rows.php" and the answer was "Could not open input file: admin/cli/mysql_compressed_rows.php"

Well, in the moodle server environment page, at the right-top margin, there was a button with something like 'component update'

I clicked down this button opening a new page telling that the update was completed and a button to continue, backing again to the server enviroment page,  the advice was disappeared.

It looks all is working fine by the moment.

I don't know why the warning disappeared but it did.

Good luck.

JP
Average of ratings: -
In reply to Juan Pablo Miranda Aguayo

Re: Converting InnoDB tables to Barracuda

by Ken Task -
Picture of Particularly helpful Moodlers

For others who might read this ...

The script is found in /path_to_moodle_code/admin/cli/ directory

The directions given to execute the script via command line as root user said to change into /path_to_moodle_code/ then issue the command.

So, for example, path to moodle code on a 'typical' RedHat flavored server:

cd /var/www/html/moodle

Then one issues

php admin/cli/mysql_compressed_rows.php

executes without the error ... 'Could not open input file ...'.

Would have also worked if one cd /var/www/html/moodle/admin/cli/ and then issued:

php mysql_compressed_rows.php [ENTER]

Why it happens?  PHP scripts are not generally in the root users environmental path.   PHP itself looks into only a few directories for things it needs to function.   So when running a php script from the command line, one needs to use the full path to the file or change into the directory where the php script resides before executing.    That's good!  Last thing one wants to do is something like:

C:\> erase *.*

in the WRONG location! ;)

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Converting InnoDB tables to Barracuda

by DeWayne Demland -

Ok, I had great success on my test server in upgrading to moodle 2.9; how ever on my hosted server I have not had the same experience. I cannot get get a SUDO access to run the script to modify the database. My hosting service is telling my I need to upgrade to a "dedicated" server to accomplish this. Is there any other way to modify the database so that I can use moodle 2.9 on this shared server?

Average of ratings: -
In reply to DeWayne Demland

Re: Converting InnoDB tables to Barracuda

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Here is a Unix "screen-shot":

$ cd /path/to/your/moodle
$ php admin/cli/mysql_compressed_rows.php
Script for detection of row size problems in MySQL InnoDB tables.

By default InnoDB storage table is using legacy Antelope file format
which has major restriction on database row size.
Use this script to detect and fix database tables with potential data
overflow problems.

Options:
-i, --info Show database information
-l, --list List problematic tables
-f, --fix Attempt to fix all tables (requires SUPER privilege)
-s, --showsql Print SQL statements for fixing of tables
-h, --help Print out this help

Example:
$ sudo -u www-data /usr/bin/php admin/cli/mysql_compressed_rows.php -l

$ php admin/cli/mysql_compressed_rows.php -l
mdl_data Compact (needs fixing)
mdl_data_fields Compact (needs fixing)
mdl_enrol_paypal Compact (needs fixing)
mdl_lti Compact (needs fixing)
mdl_user Compact (needs fixing)
mdl_user_info_field Compact (needs fixing)

$ php admin/cli/mysql_compressed_rows.php -f
Cannot enable GLOBAL innodb_file_per_table setting, use --showsql option and execute the statements manually.!!! Error writing to database !!!

$ php admin/cli/mysql_compressed_rows.php -s
Copy the following SQL statements and execute them using account with SUPER privilege:

USE moodle27;
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;

$ mysql -u root -p
Enter password: [invisible]
Welcome to the MySQL monitor.
[...]
mysql> use dbname;
Database changed
mysql> SET SESSION sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE mdl_data ROW_FORMAT=Compressed;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 2

[...]

Yes, the "sudo -u www-data" part in the example line:
$ sudo -u www-data /usr/bin/php admin/cli/mysql_compressed_rows.php -l
is unnecessary! (The php script ultimately sends SQL commands to the database which is controlled by the user management of the database, not the Unix shell.)

Note on type setting:
normal: what programs print on the screen
bold: what you type
bold slanted: you type them adjusting to your setup.


Average of ratings: Useful (6)
In reply to Visvanath Ratnaweera

Re: Converting InnoDB tables to Barracuda

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers

Very nice details post.

I tried your first two lines, and did not succeed on my MAMP install. Any suggestions? (Should I begin after your first "Example"?)

Here's the error message:

Ricks-MBP-2:moodle28exp rjerz$ php admin/cli/mysql_compressed_rows.php

!!! <p>Error: Database connection failed</p>

<p>It is possible that the database is overloaded or otherwise not running properly.</p>

<p>The site administrator should also check that the database details have been correctly specified in config.php</p> !!!

Average of ratings: -
In reply to Rick Jerz

Re: Converting InnoDB tables to Barracuda

by Usman Asar -

you can try this on a test environment first.

mysql> select version();
        +------------+
        | version()  |
        +------------+
        | 5.5.24-cll |
        +------------+
        1 row in set (0.00 sec)


mysql> show variables like "%innodb_file%";
 +--------------------------+----------+
   Variable_name            | Value    |
 +--------------------------+----------+
 | innodb_file_format       | Antelope |
 | innodb_file_format_check | ON       |
 | innodb_file_format_max   | Antelope |
 | innodb_file_per_table    | ON       |
 +--------------------------+----------+
 4 rows in set (0.00 sec)

mysql> SET GLOBAL innodb_file_format = barracuda;
     Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%innodb_file%";
   +--------------------------+-----------+
   | Variable_name            | Value     |
   +--------------------------+-----------+
   | innodb_file_format       | Barracuda |
   | innodb_file_format_check | ON        |
   | innodb_file_format_max   | Antelope  |
   | innodb_file_per_table    | ON        |
   +--------------------------+-----------+
   4 rows in set (0.00 sec)

mysql> SET GLOBAL innodb_file_format_max = barracuda;
          Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%innodb_file%";
       +--------------------------+-----------+
       | Variable_name            | Value     |
       +--------------------------+-----------+
       | innodb_file_format       | Barracuda |
       | innodb_file_format_check | ON        |
       | innodb_file_format_max   | Barracuda |
       | innodb_file_per_table    | ON        |
       +--------------------------+-----------+
        4 rows in set (0.00 sec)


Database is as well accessible after this, but again, recommended to test on a test environment first.

Average of ratings: -
In reply to Rick Jerz

Re: Converting InnoDB tables to Barracuda

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Rick

Is that Moodle operational? The error says $CFG->dbuser with dbpass has no access to dbname.

Try:
$ mysql -u dbuser -p
Enter password: [dbpass]
Welcome to the MySQL monitor. Commands end with ; or \g.
[...]
mysql> use dbname;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
[...]
315 rows in set (0.01 sec)
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Converting InnoDB tables to Barracuda

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
In the above post I assumed $CFG->dbhost = 'localhost'.

If not,
$ mysql -h dbhost -u dbuser -p
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Dear Visvanath,

I've done everything as described above.  I don't have Super privileges on shared hosting so I downloaded the DB and worked on it locally, imported it back up into an empty DB and got the same results like Usman (above)

mysql> show variables like "%innodb_file%";
       +--------------------------+-----------+
       | Variable_name            | Value     |
       +--------------------------+-----------+
       | innodb_file_format       | Barracuda |
       | innodb_file_format_check | ON        |
       | innodb_file_format_max   | Barracuda |
       | innodb_file_per_table    | ON        |
       +--------------------------+-----------+
        4 rows in set (0.00 sec)

However, Moodle Environment is still showing Antelope and my CLI query : mysql_compressed_rows.php -l is still showing the same problem rows.

What have I missed ?? !!

Please note that I'm only just about able to use phpMyAdmin - still learning.

Many thanks

Ray

Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi Ray

What does "everything as described above" mean to you? There are very many different things describled in this long thread?

Do you mean, you ran the full set of SQL statements the 'mysql_compressed_rows.php -s' told you to run, as in the example below?

$ 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;

All of them ran and gave you OK?

If yes, and Moodle environment test still fails, then there is a bug in Moodle.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Dear Visvanath,

Yes, I ran all the statements as indicated by compressed_rows.php -l and as shown by you in your reply.  Everything came back "green", no errors. After that I ran "show variables like "%innodb_file%";" and it returned Barracuda.

I then DROPped all the rows in the online DB and imported the fixed one.  When I ran 2.9+ it still showed the Antelope warning in Environment.

I then exported the DB again and ran phpMyAdim locally.  This time "show variables like "%innodb_file%" showed

+ Options
Variable_name Value
innodb_file_format Antelope
innodb_file_format_check ON
innodb_file_format_max Barracuda
innodb_file_per_table OFF

Many thanks

Ray

Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Matteo Scaramuccia -

Hi Ray,
you should run the CLI command explained above: configuring the DB server works for the brand new tables on your local environment but the imported one could be still Antelope based.

Since you're on a shared hosting ask your Provider to change the global configuration - not sure if they'll agree with such a change - and after that change you'll run the output of the command with the -s argument, removing just the two lines below:

SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;

Alternately, provide the full SQL output to your Provider: they can run it on behalf of you but as super user.

HTH,
Matteo

Average of ratings: -
In reply to Matteo Scaramuccia

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Hi Matteo,

Many thanks for your reply. I've tried this, my provider will not run the statements, and I can't run the CLi as I don't have Super privileges. BUT .....

I've installed PHP and MySQL on my own PC and run the statements and appear to have altered the rows to Barracuda.

Now my problem is I can't import the altered DB back to up the server as it's large (c.200mb) !

I've asked the provider to up my post_max_size and memory_limit in the site's php.ini.  When that's done we'll know if it worked, or not !

Nightmare city !

Best regards

Ray

Average of ratings: -
In reply to Matteo Scaramuccia

Re: Converting InnoDB tables to Barracuda

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers

Do you recommend adding these two lines to the mySQL configuration file, my.cnf, so that they always happen even on a reboot?  If one SETs these as global, won't they disappear on a server reboot?  (I am not a mySQL expert, but I have learned that mySQL should be tweaked in it's my.cnf configuration file.)

Average of ratings: -
In reply to Rick Jerz

Re: Converting InnoDB tables to Barracuda

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Rick

If you can edit my.cnf then you have super user access. As a result many alternatives. The problem is that right now we are discussion the situation of Ray, who is on a shared server, i.e. without super user access.

This place is getting crowded. What I know is:
- The OP DeWayne solved half of his problem
- Juan Pablo solved his problem completely
- Don't know what happened to Enrique
- Ray has almost given up, then Matteo joined

It would make our life simpler if each discuss his problem in his own thread, or at least in own sub-thread. Then moderators can split them. But not when they alternate!

The worst of it: I haven't encountered this problem yet. I am a slow adopter. Trying to learn by solving other people's problems before it happens to me. ;-D
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Dear Visvanath,

Update on my situation :  I'm still waiting on my provider to sort out my upload restrictions in CPanel.

Regarding the Antelope - Barracuda conversion, I must be missing something.  I've downloaded a DB to convert locally using phpMyAdmin.  I have all privileges (on my PC). I run all the statements as discussed above and run

show variables like "%innodb_file%"; I get :

Variable_name

Value


innodb_file_format

Barracuda

innodb_file_format_check

ON

innodb_file_format_max

Barracuda

innodb_file_per_table

ON


I then export the DB to desktop.  If I then exit and restart phpMyAdmin and import the DB fresh again, I get

Variable_name

Value


innodb_file_format

Antelope

innodb_file_format_check

ON

innodb_file_format_max

Barracuda

innodb_file_per_table

OFF


What have I missed please ?

I do appreciate everyone's help.

Many thanks

Ray

Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Ken Task -
Picture of Particularly helpful Moodlers

Please see:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_file_format

from link above:

"The file format to use for new InnoDB tables. Currently, Antelope and Barracuda are supported. This applies only for tables that have their own tablespace, so for it to have an effect, innodb_file_per_table must be enabled. The Barracuda file format is required for certain InnoDB features such as table compression.

Be aware that ALTER TABLE operations that recreate InnoDB tables (ALGORITHM=COPY) will use the current innodb_file_format setting (the conditions outlined above still apply)."

So it looks like innodb_file_per_table must be set in MySQL server config (phpmyadmin is only a set of scripts to interact with DB's in MySQL so 'restarting' it isn't really restarting MySQL Server daemon).

IF remote host doesn't support/won't support, either upgrade package ... VPS (something that give you total control of the DB/systems settings) OR find a provider that will.

'spirit of sharing', Ken

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

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Dear Ken,

Thanks for that, but it's gone way over my head.  What it means for a small outfit like ours is that shared hosting limits us to v 2.8.  Up to this, upgrading was a relatively simple affair but now this is a quantum leap for me.

There's little more I can do at this time, but who knows what's around the corner.

I won't add further to this thread but thank you all for your input and help.

Regards

Ray

Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Ken Task -
Picture of Particularly helpful Moodlers

Sorry!  Yes, it appears to be a mountain, but with time, and some effort, it's really a somewhat steep hill. ;)   IMHO, worth the journey.

One cannot stay at 2.8 forever.   Start working on your provider - it's ok to pester them from time to time.   After all, they are taking your $$$'s are they not?   And who knows, you may not be alone.   If that provider has a customer forum, go participate and describe the problem there.   Yes, I know you don't have support there yet, but other customers will see and just might say "me too'!   Now how many "me 2's" would be required to reach critical mass with the provider ... who knows.   But worth a shot.

Encourage you to continue to tinker with the local copy you have.

I speak from experience ... started the 'journey' as classroom teacher (k12), then an Instructional Technologist, then a  ... Heck, IF I can do it, anyone can!

'spirit of sharing', Ken
Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Jeff Shrek -

Hi Ray .... maybe this will help you:

I am on shared hosting, and I was not able to run the commands from CLI and was also not able to run them from within phpMyAdmin. I checked with my webhost and they confirmed I do not have root access and do not have SUPER privileges for mysql database.

I asked my webhost if they could run the commands for me and they said no problem, so they ran the commands and all is converted to Barracuda now and solved for me!

This is the email I sent my host (note that you need to change my_database_name to the correct name of your moodle database):


I need to change our Moodle database from InnoDB Antelope to Barracuda.

When I run the commands listed below in phpMyAdmin, I get the following error:
SQL query:
SET GLOBAL innodb_file_per_table =1;
MySQL said: Documentation
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Since I do not have SUPER privileges, can you run the following commands for me?

USE my_database_name;
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;

Average of ratings: Useful (1)
In reply to Jeff Shrek

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Dear Jeff,

Many thanks for that.  You're right, it is my only option other than to change host providers.  I will ask them again but they were reluctant to run the commands for me when I last asked them.

If I succeed, I'll let you know.

Best regards,

Ray

Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Sam Mudle -

My hosting provider says the same thing.  As usual, their response is to upgrade to the really expensive $$$ VPS hosting.

I think you have to remember that the Barracuda is optional for Moodle 2.9/2.8 -- it only affects the ability to restore a site.

Quoting the Moodle online manual:

(https://docs.moodle.org/29/en/Upgrading#MySQL_dmlwriteexception_error_when_restoring_a_course)

If you obtain a dmlwriteexception error when restoring a course, it is recommended that InnoDB tables are converted to the Barracuda file format.

(https://docs.moodle.org/29/en/Administration_via_command_line#Converting_InnoDB_tables_to_Barracuda)

This may cause a problem on larger sites when restoring a course... However, converting tables to Barracuda is only recommended, and not required, since not all MySQL users are affected. (It may only be a problem for larger sites.)

I've not personally seen a problem when restoring a course under 2.9, and I have really small number of users. YMMV
Average of ratings: -
In reply to Jeff Shrek

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Hi again Jeff,

My hosting people said they would run the queries for me, but have not done so (since 30 June).  Getting a bit browned off with them as they have not replied to a couple of tickets I've sent them, and I don't create tickets often.

Dare I ask who you are using ?  If you don't want to print that here,, please feel free to email me at raydimascio@gmail.com.

If they have done the routine for you, then they are familiar with what is needed.

I know I can upgrade to 2.9.1 regardless, but not knowing when the problems may kick in is a little disconcerting. The reference (https://docs.moodle.org/29/en/Administration_via_command_line#Converting_InnoDB_tables_to_Barracuda) refers to "larger sites".  Any idea what defines a large site ?  My db is almost 250Mb, maybe that's relatively small !

Best regards,

Ray


Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Colin Fraser -

mmm I actually ignored the warning, and my v2.9 works... so far. thoughtful

Average of ratings: -
In reply to Colin Fraser

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Hi Colin,

A warning is a warning and I really don't know what the worst case scenario could be.  I used to be an airline pilot.  A few times in my career we got the warning "there may be a bomb on your aircraft".  When nothing happened, nothing happened.  But if it had gone wrong, there was no coming back from the consequences.

Now maybe these Antelopes are not as hostile as I imagine, but what if ?

Please forgive my paranoia, but I don't know what I'm dealing with.

Best regards to all.

Ray

Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Colin Fraser -

Hi Ray, I accept what you are getting at, but I am using MariaDB as a database, a MySQL drop-in replacement I believe, on my test Moodle. As I understand it, MySQL has only just dropped support for innodb_* options, which is where the push to use Barracuda is likely coming from. (As I am reading it, this has been forced on MySQL by Percona omitting, [ie dropping] the now quite old innodb code from their XtraDB, one of the storage engines used by MySQL and MariaDB. Its complicated.) I have had a number of warnings about potential issues with it, but have not had any issues, and not expecting any. Admittedly I don't have 500 courses and 2000 students, or such, but it still gets a hiding occasionally, and MariaDB has never missed a beat. 

I have not decided to change the MySQL database to MariaDB on my production site, yet. I am thinking that if there is going to be a change, then it is likely to be MariaDB on the next major upgrade, at the end of the year. The speed enhancements alone show promise, and MariaDB has not supported innodb_* options for the last few versions. I doubt that Barracuda is a standard adopted by MariaDB, or they are likely to have their own version of it, similar to, but not the same as. It may be that MariaDB presents a more optimistic future for Moodle than MySQL anyway, might want to give it a try on your test Moodle.      

BTW, I once had a retired airline pilot tell me that he had years of genteel boredom flying followed by a few seconds of sheer terror, which put a whole new gloss on the years of genteel boredom after that. He was just glad to be an old pilot, I think. 

Average of ratings: -
In reply to Colin Fraser

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Hi Coin,

Many thanks for that, very informative.  How did you get your providers to use Maria or are you using a VPS or indeed running your own server ?  For the moment, due to costs, I have to use shared hosting.

I agree with your friend - the moment of terror usually came while trying to eat !

Regards

Ray

Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Colin Fraser -

Hi Ray, I run it on my own server, my test Moodle, where I try anything. I have an older box I use as a headless server, plus a Synology NAS which has downloadable set of packages, Moodle being one of them. The NAS introduced me to the use of MariaDB, I had never heard of it, so did a few things, and found it was quite forgiving. Admittedly, performance enhancements of a database mean little in a test bed, but it "felt" a better fit, if that makes sense. I went here to the MariaDB Knowledge Base,, and finished up here to download the installer for Windows. Went in relatively easily as I didn't have MySQL on that box, installed a new Moodle pointing it at MariaDB, and had no problems.

For the moment, I am one admin at one production site, a small not-for-profit organization, that is used as training for social services and consumer rights for socially disadvantaged-basically kiosk stuff, for clientele with training for their own staff. All I do really is upgrade once a year, and they pretty much run it themselves now and they leave it alone. Essentially standard, out the box, which works for them. I may talk to them about making the change, but while I am happy with MariaDB, making such a change in a production site may bring a few radical issues to the fore, which I really want to avoid...smile But I am playing with courses and such trying it out, so far, so good....     

Average of ratings: -
In reply to Colin Fraser

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Hi Colin,

Thanks for that.  Sounds like a nice relaxed setup. I'm stuck with Shared Hosting with all it's limitations.

I've upgraded all my dbs to 2.9.1+ without any real hitches.  I did find that I had to install it bare, without any add ons, and then add them in after installation.

I had a quick look at the Maria download page and it does look quite user friendly.  I'll play with ti when I have more time.  Thanks for the link.

I hope the swap to Maria really works well for you.  After all, "How do you solve a problem like Maria" ? !!

All the best

Ray

Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Ray Di Mascio -

Hi Folks,

I'm back !

It took me all this time to find a friendly host who was really helpful.  I'm still on shared hosting, but using MariaDB.  I managed to change the fields from Antelope to Barracuda.  Everything reoprts "Barracuda" and php mysql_compressed_rows -i -s -f all show nothing needs to be changed and everything compressed.

But Server Environment still shows an Antelope warning.

Any ideas please ?

Thanks

Ray

Average of ratings: -
In reply to Ray Di Mascio

Re: Converting InnoDB tables to Barracuda

by Kingsley Kcay -

Don't know if this might help. At first it always switched back to Antelope from Barracuda then I discovers a table was missing in the command. Kindly find below. This fixed all:


USE YOUR_DB_NAME;

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_questionnaire_survey ROW_FORMAT=Compressed;

ALTER TABLE mdl_user ROW_FORMAT=Compressed;

ALTER TABLE mdl_user_info_field ROW_FORMAT=Compressed;


It appeared this command (ALTER TABLE mdl_questionnaire_survey ROW_FORMAT=Compressed;) wasn't included so it kept switching back to antelope.


Regards. smile



Average of ratings: -
In reply to Matteo Scaramuccia

Re: Converting InnoDB tables to Barracuda

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers

I finally got back to this issue of upgrading both my MAMP and my production moodle to Barracuda.  I gained confidence in what I was doing by talking with "Dave" from mySQL at MoodleMoot US 2015.

I wanted to be able to make these Barracuda changes within my my.cnf file. (Dave even thought that it was best to put the Barracuda line in my.cnf.)  I have one of these on my production server, but not on my MAMP server.  What I did was to copy the my.cnf file from my production server, make a few changes, and add the line innodb_file_format=Barracuda; after the innodb_file_per_table=1; line, which I already had in my.cnf.  I put this my.cnf file into the MAMP/conf folder.  I stopped and then started the server in order to initiate the change.  No problem.  Then, in MAMP, I went to phpMyAdmin, picked my moodle 2.9 database, and then ran the six ALTER commands verifying that each one worked.  Well, that was it!  It worked.  I proved that this worked by upgrading my moodle 2.9 to the latest version.  During this upgrade, I did not receive any Barracuda warnings.

Next, I did the same thing on my production Moodle.  I modified my my.cnf file with the one Barracuda line, stopped and started the mysql server, went to phpMyAdmin and issues the ALTER commands, and upgraded my production moodle to the latest 2.9.1+.  Again, no problems whatsoever.

For you experienced folks, I am sure that you are saying "of course no problems."  I like to take things a little slower, and I had the problem that my production moodle was really "in production" serving two online courses, so I didn't want to do any damage.

I thought I would share what I did so that this might help others.

I only have one question (buried deep into this post).  Can one issue the "SET GLOBAL innodb_file_format=Barracuda;" from within myPHPAdmin as a SQL command, just as I issued all of the ALTER commands?  I ask this because many hosted servers provide this myPHPAdmin tool. 




Average of ratings: Useful (1)
In reply to Visvanath Ratnaweera

Re: Converting InnoDB tables to Barracuda

by Juan Pablo Miranda Aguayo -

Thank you Visvanath

The details of your post gave me enough clues to carry out the work.

Finally, I got it!

Regards

JP

Average of ratings: -
In reply to Juan Pablo Miranda Aguayo

Re: Converting InnoDB tables to Barracuda

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi Juan Pablo

I'm glad that it helped.

Yes, a "screen-shot" tells more than words!
smile
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Converting InnoDB tables to Barracuda

by Gabriel Pass -

I also am not able to get these scripts to run properly.

Below is my exact output from putty when I tried to run “mysql_compressed_rows.php”

For me the script failed to run with a “500 Internal Server Error”.  As you can see from the output below, my moodle install is at the following directory:  ~/public_html/gapmedia-apps/courses.  I verified that the file directory was correct and again received the same error.  I tried the command numerous times with variations of sudo -u apache /usr/bin/php... all to no avail. 

user@site.org [~/public_html/gapmedia-apps/courses]# php admin/cli/mysql_compressed_rows.php -i

Status: 500 Internal Server Error

Content-type: text/html

This is clearly a tedious process.  I would really great if someone could post a clear step by step process with everything spelled out (including access control and parameters that can vary per site).  What interface should we be using (a terminal, can they run from phpMyAdmin (if so how).  Are there precursors to running this script?


Thanks for any help you can give.

Average of ratings: -
In reply to Gabriel Pass

Re: Converting InnoDB tables to Barracuda

by Ken Task -
Picture of Particularly helpful Moodlers

@Gabriel ...

From the 'clues shared' ... ~/public_html/... it appears it's Linux and on a shared system.  Thus some php commands might be in a 'jail' or behind some other php protections on the server so that customers who share the machine can't harm/do stuff to other customers sites/databases.

The script reads the config.php file of the site for the DB host, the  DB name, and the DB user.  That DB user in config.php might not have access rights enough to change configuration of tables.  The fact you get an error 500 from Apache means the apache server is called (it's not supposed to be ... just php talking to the DB host) and not allowing command line php.

You can test this a little further ... the following commands do work on a NON-shared server and as root user (in your case, the DB user seen in the config.php file of your site).  I actually expect they will fail on your system.

mysql -u [DBusernamefoundinMdlConfig] -p[DBPasswordfoundinMdlConfig]

Do you get a mysql> prompt?

If so, at that mysql> prompt type: show databases;

Be sure to include the ';' at the end.

See your DB name as it is in config.php of your site?

At the mysql> prompt type:

mysql> show grants for '[yourDBuser]'@'[yourDBhost]';

Include the single quotes as in the command above but substitute your info and replace the []'d items - including the ['s.

Does your shared system provide any control panel for customer use that can interact with DB's (not phpmyadmin)?

Remotely hosted shared systems normally have a client/customer FAQ of some sort.   You might need to seek that out or even contact your providers helpdesk to inquire.

As far as documenting this for all possible providers, that's pretty much 'mission impossible', am afraid.   But, that's what community sharing is about ... folks who run on Bluehost, GoDaddy, etc. come back to these forums and share back what they had to do to get xyz to work for others who might follow.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Converting InnoDB tables to Barracuda

by Gabriel Pass -

Thanks for the suggestions!  You guessed correctly I have a “virtual” dedicated server with Just Host.  I have never run into the bounds of that "virtual" part until now.

Here’s what I run in to when I try that:

user@site.org [~]# mysql -u root -p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib                                                       /mysql/mysql.sock' (2)

 

user@site.org [~]# mysql -u dbusername -p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib                                                       /mysql/mysql.sock' (2)

So shall I assume that the only way I can get this done with my current access is to follow Ray Di Mascio’s plan and see if I can get my web host to run the commands?

Average of ratings: -
In reply to Gabriel Pass

Re: Converting InnoDB tables to Barracuda

by Ken Task -
Picture of Particularly helpful Moodlers

One more thing to do ... check the DB host in the config.php file of the moodle code.

Then use that info in the mysql command:

mysql -h [hostseeninconfig] -u dbusername -p

Can you connect?   Should be able to cause your Moodle wouldn't run otherwise.

The issue IS, I think, the DB user doesn't have permissions to change tables/engine, config of MySQL overall etc..   So, yes, you will have to contact your provider help desk and ask for assistance.

Might pass along to them this ... if they run the script with proper credentials and use the following command it will show them the SQL they need to run.

Example:

[root@sos cli]# php mysql_compressed_rows.php -s
Copy the following SQL statements and execute them using account with SUPER privilege:

USE moodle29;
SET SESSION sql_mode=STRICT_ALL_TABLES;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;

The above is a REAL example.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Converting InnoDB tables to Barracuda

by Gabriel Pass -
Great!!!   I got access to MySQL through my ssh terminal.  I found out there was an extra layer of security preventing MySQL from communicating through ssh.

But I still can't run the php scripts to tell me which tables need fixing...  So, Can I do this blind?   Will the tables that need to be updated be the same?  Or at least can I update them all without causing any problems?

Essentially, should I be able to just run these commands and be all set?  : 
USE moodle-da-name;
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;
Average of ratings: -
In reply to Gabriel Pass

Re: Converting InnoDB tables to Barracuda

by Gabriel Pass -
Also, these commands require SUPER USER access. 
Is there any way to set these locally just for the moodle db ???

SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
Average of ratings: -
In reply to Gabriel Pass

Re: Converting InnoDB tables to Barracuda

by Ken Task -
Picture of Particularly helpful Moodlers

You are correct - those commands for superuser.

The other 'tidbit' not shared via Moodle docs:

"If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change."

Ref: 

https://dev.mysql.com/doc/refman/5.5/en/set-statement.html

You need to inquire with provider to see if they can do this for you.   If not, afraid they don't provide what's needed and you'll need to seek another provider.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Converting InnoDB tables to Barracuda

by Gabriel Pass -

Thanks!   I am working with my hosting provider on that now.  It looks like they'll be ok with making the change.  Clearly, allowing Barracuda tables is a matter of having an up-to-date server.   I did a lot of reading and I understand that it's exactly that, the setting must be global because it tells the server to simply allow the file format.


THANK YOU for the info about the option file!   That's a crucial piece of information!

So two more questions then:

FIRST: If the change is not made permanently (via a settings file) would it damage the converted tables, when the server eventually reverts to Antelope?  Either way, Barracuda tables would probably be un-readable by a server not allowing barracuda and cause Moodle to fail to open?


SECOND: My server also lists the following variable: innodb_file_format_max=Antelope.

It appears from what I have read that if innodb_file_format_check=1 and innodb_file_format_max=Antelope ,  as long as the server is SQL 5.1 or higher, the XtraDB/InnoDB service will start and and simply change innodb_file_format_max, to =Barracuda, each session.  Is that correct?  Is this setting truly irrelevant???

OR must innodb_file_format_max=Antelope  also be changed to  =Barracuda ?


Again, thank you so much for answering my questions.  I would be completely stuck without the benevolence of such well informed individuals as yourself!

Average of ratings: -
In reply to Gabriel Pass

Re: Converting InnoDB tables to Barracuda

by Ken Task -
Picture of Particularly helpful Moodlers

Well, now we're at the level of in-experience/competency for me ... I 'dig' a lot and not a certified MySQL DB admin person.   The servers I do help manage are not multi-customers/shared systems but rather dedicated machines for an entity.   Thus am able to make the changes to my.cnf.

Since I've not had backend config responsibility for multi-customers/shared system I'm really not sure how to respond to the questions.  And I don't have a tinker system that I'd care to wreck finding out, either! ;)

Will venture a guess that once a table is made Barracuda it's that.  As long as the MySQL server supports it.   However, what I have seen after installing an add-on, some tables created using Antelope.   That has not bitten when upgrading Moodles via git, however.   I've had to re-run the script via command line but no issues appeared to rear their ugly head.

If we are unsure about hosting provider's ability to set this up, suggest when they do, make a full site backup of what you have (code/sql dump/data directory) and download it/them.

Invite anyone who can answer those questions to do so! ;)

'spirit of sharing', Ken


Average of ratings: Useful (1)
In reply to Visvanath Ratnaweera

Re: Converting InnoDB tables to Barracuda

by mantiak mantiak -

Thank you very much Viswanath Ranaweera, finally checks the server status is OK ...smile

I use Moodle BitNami Lamp Stack  apps.

Average of ratings: -