Antelope Barracuda - it's a jungle out there

Antelope Barracuda - it's a jungle out there

از Frankie Kam در
Number of replies: 15
عکس Plugin developers

Hi there, intrepid Moodle 3.3x user!

My server supports three Moodle installations. They are in versions 2.7, 2.8 and 2.9. Now when I try to install the latest Moodle 3.3.1, it is asking me to convert my databases from a deer into a predatory fish. The terms remind of a National Geography documentary. 

Anyway, my dumbie newbie question is this:
Can I convert just the Moodle 3.3.1 installation's database from Antelope to Barracuda, and leave my other Moodle 2.x installations' databases as Antelope? Or does it mean that if I need to install Moodle 3.3.1, then I need to convert ALL moodle installation databases into Baraccuda?

The reason I am asking is that I would rather not touch my earlier Moodle installation databases, and would rather just leaving them as they are - as Antelope deer roaming free on the savanna. IF it ain't broke, so why fix it Sparky? But I still want to install Moodle 3.3.1which means I have to (no choice) convert the Moodle 3.3.1 installation's database. So can I have my cake and eat it too?

Regards
Frankie Kam


میانگین امتیازات:  -
In reply to Frankie Kam

Re: Antelope Barracuda - it's a jungle out there

از Ken Task در
عکس Particularly helpful Moodlers

I too, like you, have multiple versions including 'long term support' ... now no longer long term support ... 2.7 ... or I had a 2.7.

Check the configuration file - config-dist.php for the database setup section:

there is a collation setting that reads thus (emphasis added):

'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
                                // support. If you wish to use partial UTF-8
                                // (three bytes) then set this option to
                                // 'utf8_unicode_ci', otherwise this option
                                // can be removed for MySQL (by default it will
                                // use 'utf8mb4_unicode_ci'. This option should
                                // be removed for all other databases.

So if one changed what Moodle will attempt to use to utf8_unicode_ci *before* attempting install?

Means copying the config-dist.php to config.php and editing in advance of pulling the trigger.

Not actually tried that myself ... yet.   Gonna see this AM however ... when I have some time.

'spirit of sharing', Ken


میانگین امتیازات:  -
In reply to Ken Task

Re: Antelope Barracuda - it's a jungle out there

از Ken Task در
عکس Particularly helpful Moodlers

Well ... dang!   Was going to try to install a 2.7.highest using utf8_unicode_ci but now re-call 2.7 cannot run under php 7.0.x.    Seems like the typical 'catch 22' now-a-days.

Don't use the web based installer and am lazy ... when installing via web and arriving at the section on DB, is there an option for the installer to use utf8_unicode_ci or utf8mb4_unicode_ci.   That sure would make it easier for those remotely hosted that might not have superuser priv's to make global changes.  Of course, that is really just delaying what will eventually not be an option, me thinks.

When initially installing the 3.3.x had used utf8_unicode_ci and install via command line had no issues.   It was only when upgrading ... minor upgrade ... that ran into environment check after successfully upgrading via command line. 

which prompted this 'shared experience' posting:

https://moodle.org/mod/forum/discuss.php?d=355139

Oh ... well ...

'spirit of sharing', Ken


میانگین امتیازات: Useful (1)
In reply to Ken Task

Re: Antelope Barracuda - it's a jungle out there

از Ken Task در
عکس Particularly helpful Moodlers

And a follow up, for Frankie/others ...

https://dev.mysql.com/doc/refman/5.7/en/charset-database.html

Shows how to alter the character set/collation of a database
thus avoiding the global settings a little while longer.

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

 To see the default character set and collation for a given database, use these statements:

USE db_name;
SELECT @@character_set_database, @@collation_database;

Just check my tinker box with mutiples ...

the older versions running collation utf8_general_ci and are running just fine.

The 3.2 running utf8_unicode_ci and no issues.

The 3.3 is where I had to use mysql global commands to get by the environment checks after success upgrade to 3.3.1 (last week)  is using utf8mb4_unicode_ci

So if your PHP version isn't too high, one should be able to run 2.7 -> 3.3.highest on same box.

The 'dust will settle' sooner or later. ;)

Never thought, when first beginning to use Moodle around version 1.4 or 1.5, that I'd ever have to learn how to be a DB admin.   But, that's 'progress' for ya! ;)

'spirit of sharing', Ken


میانگین امتیازات:  -
In reply to Ken Task

Re: Antelope Barracuda - it's a jungle out there

از Frankie Kam در
عکس Plugin developers

Hi Ken

Thank you for sharing the information. I just can't seem to see the forest for the trees. I see the trees alright, those in front of me. It's the trees I can't see in advance that scare me. 

So for now I am reluctant to make any changes to my databases lest I screw up big time and have a horde of administrators, lecturers and students with pitchforks and torches storming up the pathway to my house (if they knew where I stayed that is). I'd prefer not to make changes to the production sites. After all, I am not going to upgrade them to the latest Moodle version. 

To many 'dances' to carry out, all fraught with peril at every step for an inexperienced admin user like me. I will continue to compile the information and the links that you have provided. Maybe one day I have a server that allows me to install Moodle 4.0 without any hassle or without having to make prepatory conversions and what have you. Looks like I'm taking the chicken way out. What would be prudent and practical for me to do is to setup a local XAMPP environment where I can practice shooting myself in the foot without inflicting any real or lasting pain. That way I can gain more confidence before taking the plunge. 

Again many thanks for taking me down this rabbit hole of installing Moodle 3.x with Barracuda database . I'm still falling down it and there seems no sight of the bottom --- yet. Cheers!


regards
Frankie Kam

میانگین امتیازات:  -
In reply to Ken Task

Re: Antelope Barracuda - it's a jungle out there

از Frankie Kam در
عکس Plugin developers

Whoa..I just found https://moodle.org/mod/forum/discuss.php?d=312136

Marcos Oviedo says he can instal Moodle 3.2.2 without converting the database:

>Hey Miguel
>
>I had the same problem and I`ve found a way around. The problem is not converting
>from antelope to barracuda but instead is setting your database in utf8_(whatever
>language)_ci (It looks like the default language is latin1_swedish_ci)
>
>So, to fix this:
>1- Go to your bluehost cPanel / Database Tools / phpMyAdmin
>2- Select your database in the left panel.
>3- Go to Operations menu (the one with the wrench icon). and set the correct database
>setting utf8_(whatever language)_ci. I`ve tried with English and Spanish and works fine
>anyway.
>
>Good luck.
>

I think this is worth trying. Since I have to select a database, I can select the one that I have created to install Moodle 3.2.2. So the other databases will be unaffected. Then I will see if Moodle allows me to complete the intallation. YES!! *fist pumping moment* .  But I will do a complete backup via CPanel first. Just in case. I will let you know how it goes.

Regards
Frankie Kam 


میانگین امتیازات:  -
In reply to Frankie Kam

Re: Antelope Barracuda - it's a jungle out there

از Frankie Kam در
عکس Plugin developers

Well, well, well. All's well that ends well. I used the phpmyadmin method suggested by Marcos Oviedo. I chose ut8_general_ci for the Collation setting and clicked "Go". Then I managed to install Moodle 3.3.1 via the index.php web browser method. Works! Yahoo! Since I doubt my new courses are going to be larger than 500Mb, this will do just fine. Yes!!! *double fist pump moment* 

What Frankie wants, Frankie gets.

Cheers
Frankie Kam

میانگین امتیازات:  -
In reply to Frankie Kam

Re: Antelope Barracuda - it's a jungle out there

از Ken Task در
عکس Particularly helpful Moodlers

First, thanks for your kind comments ... Moodle is a 'journey' ... and if one runs a Moodle, one is always an omicron tester (nature of open source).

Congrats!    Don't mean to burst your bubble, but .... let's hope it sticks when you attempt an update.

I had installed using collation utf8_unicode_ci and the installation had no issues.   Site been running just fine.    But, when updating to the next highest version up via git and command line, for the first time ever, an environment check warning appeared.    It still upgraded, but when logging onto the site as thrown into the environment check with now way to escape.   After figuring out a way to get by that, I see, in in the GUI, there were 3 tables that had not be upgraded.   Have never seen that behavior before either.

Did I botch the update?   Yes .... should have changed the variable that defined collation in config.php to how it was installed.    So I've made a note in config.php now to remind myself when next updating ... and there will be an update forthcoming for sure.

Ok, my 2 cents on running a locathost copy to learn ... IF server is Linux, think one is much better off running something like VirtualBox and in VB run the OS of your server.   One will learn the difference in hosting plans for sure.   Besides that, what one learns like that, should be exactly like the production server ... the exception being how providers build your server for you.

Yes, doing something on a production server might bring the wrath of teachers/students ... that's why the sandbox tinkering is sooo important.   No one likes a hickup or to make a mistake.   Again, that's why a testing environment is important.   That testing environment needs to begin as a clone of what one has in production ... different OR no FQDN ... nor https ... those not really that important to test what one needs test.

So ... best of luck on the next update! ;)   Maybe by then, Moodle HQ will have sorted this out.

'spirit of sharing', Ken

میانگین امتیازات:  -
In reply to Ken Task

Re: Antelope Barracuda - it's a jungle out there

از Frankie Kam در
عکس Plugin developers

Hi Ken

Thanks for the warning and heads up. Yes, the right thing to do is to bite the bullet and to do a proper conversion...after sandboxing and simulating the real thing in a safe environment. Just that I can't spare the time for now. Shortcut hacks may work for the day, but the night's coming! I also wanted to be able to install Moodle 3.3.1 fast so that I could experiment with some of the newer themes. 

As for upgrading, yeah, the thing is that my strength is also my weakness. I enjoy tinkering and implementing hacks on the current sites to take advantage of custom code, to the extent that I can't keep track of the hacks and mods enough to unhack the code so that an upgrade can take place. In the long run, I really need to up my game on server-side knowledge. Thanks for providing the impetus.

Regards
Frankie Kam 

میانگین امتیازات:  -
In reply to Frankie Kam

Re: Antelope Barracuda - it's a jungle out there

از Maria Holloway در

Frankie and Ken,

GREAT THREAD!!!!  I am on the march from 1.9.11 to 3.x (however far I can go).  Looks like 3.3.x (Thanks, Frankie)

But, right now, I am using the "plain ole, out of the box" version of Moodle - no extra plugins right now. I also have small courses. But, I will have to add a theme which I hope will work fine.

My v2.7.20 Moodle says:

Other checks

Information Report Plugin Status
unsupported_db_table_row_format if this test fails, it indicates a potential problem

Your database has tables using Antelope as the file format. You are recommended to convert the tables to the Barracuda file format. See the documentation Administration via command line for details of a tool for converting InnoDB tables to Barracuda.
Check


My message is - It is recommended to convert the tables.  BUT, I am on a cloud service and the vendor says that they do not have any MariaDBs in Barracuda.  I am hoping that they can convert JUST ONE server to Barracuda but that is to be seen.  The recent budget cut means that we won't be purchasing a separate server just for our Training Portal.

SOOOOO, it looks like I too will make the switch to ut8_general_ci and add it to the config.php.  Keep on hoping for the best and look out for Ken's warnings.

Thanks for the info and I will be watching this thread.


میانگین امتیازات:  -
In reply to Maria Holloway

Re: Antelope Barracuda - it's a jungle out there

از Ken Task در
عکس Particularly helpful Moodlers

@Robert ... so you are 'on the march' and have a 2.7.x site now marched from a 1.9.x.   Is that right so far?

The check you are seeing right now at 2.7.x can be overcome by the scripts in moodlecode/admin/cli/

mysql_compressed_rows.php

now if you don't have ssh access then guess you are gonna have to ask helpdesk where you are hosted to assist.

cd /path/to/moodle/code/admin/cli/

php mysql_compressed_rows.php by itself will beget the help response which shows the options to the script usage.

Inside that script:

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

Note what is says about super privs.   The tech, could change the user/password for DB in config.php file, then execute the commands, then change user/password back to whatever was there.

OR, if you have phpmyadmin or some other tool, one could show the sql and use the sql against the DB.

As far as watching this thread ... me thinks you are driving now ... so post away!

Uhhhh ... it really doesn't hurt to mention where one is hosted.

'spirit of sharing', Ken

میانگین امتیازات: Useful (2)
In reply to Ken Task

Re: Antelope Barracuda - it's a jungle out there

از Maria Holloway در

Thanks, Ken,

Started with 1.9.11 yesterday.  Made it to 2.2.10 by the end of the day.  Pushed it to 2.7.20 overnight.  Now reaching for 3.3.1.  Backing up EVERYTHING along the way (which takes longer than installation).

The courses we have are basically a small intro text, questionnaire, link to course, and then print your certificate (now will be badges).  My big thing is that we have 28,304 user accounts from the past 8 years and my bosses do not want any of them deleted.  So as long as the users and courses are intact at 3.3.1 then I will be crazy happy.

From my reading, the mysql_compress_rows.php is the file to run.  What I did was step through the code with the help of the MariaDB documentation then created and ran the SQL statements myself. The code reflects exactly what the MariaDB documentation states you should do.

But, I should try it through SSH....just to be sure.

BUT, being on a cloud service I shared the database server with others so I can't change anything at the file level.

The host is LiquidWeb.  They purchased Rackspace's Cloud Sites recently.  They have been HIGHLY RESPONSIVE to my calls and very helpful. Hopefully, they can create a Barracuda-based MariaDB as a special favor to me.

میانگین امتیازات: Useful (1)
In reply to Ken Task

Re: Antelope Barracuda - it's a jungle out there

از Maria Holloway در

OK, this is interesting.....

LiquidWeb Support researched my MariaDB Barracuda situation.

They sent me this article saying that this will force the table to use Barracuda.  https://mariadb.com/kb/en/mariadb/how-to-change-innodb-fileformat-to-barracuda/  But, I am not allowed to set the global DB setting to Barracuda.  The article shows how to set your tables to ROW_FORMAT=DYNAMIC.

But, Moodle is saying ROW_FORMAT=COMPRESSED. 

Reading further I found this article: https://mariadb.com/kb/en/mariadb/xtradbinnodb-storage-formats/

This explains that ROW_FORMAT=COMPRESSED is for the newer XtraDB Barracuda file format.

Combining all of the reading together I ran this query in the information_schema database.

SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=COMPRESSED;") AS MySQLCMD
FROM TABLES
WHERE TABLE_SCHEMA = <database_name>;

Ran the query results which is a series of ALTER TABLE commands just like mysql_compressed_rows.php.  It took a minute to complete.

SO NOW...when I look at the table structure in phpMyAdmin I see this...

FormatCompact
Optionsrow_format=COMPRESSED
Collationutf8_general_ci

When I ran the 3.3.1 update - I still received the warning about the unsupported_db_table_row_format but I believe the script is checking the global variables.  It also warned me about the utf8_general_ci collation but I will try to change that after I backup the database.

Installation Complete...Everything looks good...so far

میانگین امتیازات: Useful (1)
In reply to Maria Holloway

Re: Antelope Barracuda - it's a jungle out there

از Ken Task در
عکس Particularly helpful Moodlers

If I could award a Moodle badge ... I would!!!!

Now we just need someone else in community, in same boat/situation as you, to replicate your success .... and after that add it to the Docs.

Hmmmm you say LiquidWeb bought Rackspace Cloud sites?

Don't work for them ... but do help a 'customer' that has Rackspace boxen - cloud servers ...

Hmmmm ...

https://www.liquidweb.com/blog/press-release-liquid-web-acquires-cloud-sites-rackspace/

About Liquid Web
Liquid Web delivers reliable, highly-available, secure and hassle-free hosting fueled by our Heroic Support. ® The company empowers its employees to go above and beyond to make life easier for professionals who create the content and commerce on the ever-changing web – so they can focus on the work they love. Liquid Web offers a broad portfolio designed so customers can choose their own adventure whether it is hands-on or hands-off or a hybrid of the two.

Now if they just hired some well versed Moodle admins .... ;)

'spirit of sharing', Ken


میانگین امتیازات: Useful (1)
In reply to Ken Task

Re: Antelope Barracuda - it's a jungle out there

از Frankie Kam در
عکس Plugin developers

FYI.

https://moodle.org/mod/forum/discuss.php?d=349351#p1434745

میانگین امتیازات:  -
In reply to Frankie Kam

Re: Antelope Barracuda - it's a jungle out there

از Maria Holloway در

Thanks, Frankie!!!

I would do that EXACT same operation IF I owned the database.

Since I am using Cloud Services, I share the database server with many others so I can't change nor configure the database server to my liking.  Same holds true for the Apache and PHP configurations.

I am always looking for workarounds.  In this case, it looks like the tables are configured for Barracuda file storage even though the database variables are not set for defaulting to Barracuda.

Time will tell if this is successful but so far it seems to be working.



میانگین امتیازات:  -