Need help with changing mysql configuration

Need help with changing mysql configuration

by Aki Tsirigotis -
Number of replies: 12

Hi everyone,

I seem to be getting a bit frustrated with upgrading my Moodle site, so I hope someone can help me. Please note, I am not a MySQL expert (or on databases). I manually upgraded my Moodle site from 3.4.8 to 3.6.6+ and I am receiving some messages from Moodle before the installation can go through:


What I am struggling with is how the heck do I change these settings in MySQL? I followed the moodle directions and i have no clue. My hosting provider is Godaddy and I am trying to access MySQL via my CPanel. So, do I change these settings through myPHP? If so, how I do I find the my.cnf file? I am completely lost. I cannot find anything on the web that can show me this step-by-step. I always see something if you are using Windows or Linux, but it does not apply in my situation.

Thanks!

Average of ratings: -
In reply to Aki Tsirigotis

Re: Need help with changing mysql configuration

by Ken Task -
Picture of Particularly helpful Moodlers

Resident expert on GoDaddy hosted Moodle - Rick Jerz - should be along any minute now.   Where are ya, Rick? smile

Just for some prelim info ... Rick will probably ask any way ...

Is your account with GoDaddy on a shared system or do you have a VPS (virtual private system)?

And just so you know ... on shared system some of the things you've mentioned/shown can't be changed ... shared system DB changes for example can't be made for one customer cause the other X number of customers could be affected adversely by the change.

And ... cPanel doesn't run on Windows, so you are using an interface into a Linux system.

And, just in case ... cause I don't host with GoDaddy ... in the cPanel screen, is there a 'Terminal' icon?    If so, there is hope that some fixes can be applied ... only through terminal and running a php script found in your moodlecode/admin/cli/ that were designed to fix the issues.  No cPanel tool will fix some things and GoDaddy helpdesk draws the line at running them for customers.

'spirit of sharing', Ken

In reply to Ken Task

Re: Need help with changing mysql configuration

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
As Ken has asked, "Is your account with GoDaddy on a shared system or do you have a VPS (virtual private system)?"

If VPS, we can go on. If "hosted" you are probably stuck, unless your hosted plan provides MariaDB. It's that "Baracuda" thing that is the problem. As far as I know, a GoDaddy hosted server plan does not use MariaDB, but I am not an expert on all the ways a person might pick, pay for, and request the variety of features that GoDaddy offers.

I own a GoDaddy VPS, and my Moodle runs great on it.
In reply to Rick Jerz

Re: Need help with changing mysql configuration

by Aki Tsirigotis -
Hi Ken and Rick!

Thanks so much for your response! Unfortunately, I run on a shared system, *sigh*. Crap. This seems to be a the issue I guess. I was able to update Moodle through Installatron but this Baracuda format was causing issues. Where would I find this terminal icon? I will do some digging to see if I can find it. So essentially, I won't be able to run any scripts to change everything because of MariaDB?

One final, which hosting site would you recommend for Moodle and us basic-knowledge admins to use? Godaddy is such a headache.

**UPDATE - There is no terminal icon in cpanel anymore. Just checked.
In reply to Aki Tsirigotis

Re: Need help with changing mysql configuration

by Aki Tsirigotis -
Additional update: MariaDB is not supported in shared plans. Dedicated servers and VPS is too expensive for me to run, Any suggestions of other web hosts that are Moodle friendly and not expensive?
In reply to Aki Tsirigotis

Re: Need help with changing mysql configuration

by Usman Asar -
Picture of Plugin developers Picture of Testers

How big is your moodle? will you be able to re-install from start? 

you can though try changing the current one (after backing up everything), go to config.php and change the following

dbcollation' => 'utf8_unicode_ci'

I haven't tried this on already installed moodle, but for new moodle installs it works - means does let you install moodle, so it's a work around, but IDEALLY you'll be needing to change your table format to Barracuda from Antelope (which is currently running on EVERY shared hosting and no one is willing to change it as well) - except from ONE shared hosting that I recently found (Planet Hoster), for the price it's giving a lot lot more than any other shared hosting service will do on top of all green ticks with moodle server checks (https protocol, Barracuda tables, unicode character set and opcache), making it my next hosting provider for moodle testings, you can visit them HERE.  (Please note, this is my personal reference to get 10% discount ), their site is in french, but you'll be able to change to English as well, and you can choose between French and Canadian data centers (which ever is closest to your location).  If you choose to go with them, for additional $2.40, you'll find option of VIP that comes with LiteSpeed server which I'll highly recommend for speed and performance. 

In reply to Usman Asar

Re: Need help with changing mysql configuration

by Morin Mote -

Hey Usman,

I am not a professional in it but https://www.inmotionhosting.com/support/website/general-server-setup/edit-mysql-my-cnf or 

 can solve your problem. smile

--

Warm Regards

Editor at UK Web Hosting Reviews X

In reply to Morin Mote

Re: Need help with changing mysql configuration

by Usman Asar -
Picture of Plugin developers Picture of Testers

Morin, 

If you had read the article from inmotion hosting, it says on top VPS and Dedicated customers can change the MySQL settings, through my.cnf, every hosting provider with VPS and Dedicated allows that to customers, therefore it does solve the problem but for VPS and Dedicated server customers only, but how many of shared hosts provide the shell access to change the configuration of MySQL shared server? NONE! 

PHPMyAdmin is given by every shared hosting, one can access DB server variables through PHpMyAdmin as well, have you even tried changing the variables? Not a single hosting provider allows that on Shared Host.

Aki had clearly mentioned, VPS and Dedicated were too expensive for her, and it's not just her but plenty other who start with moodle, therefore on a shared host, it hasn't solved my problem sad

btw, Ken has these commands on finger tips all the time, he would have written them already, but he does reads the posts before replying wink 

In reply to Aki Tsirigotis

Re: Need help with changing mysql configuration

by Ken Task -
Picture of Particularly helpful Moodlers

Since I don't host on GoDaddy will defer to any advice Rick might provide you with GoDaddy hosting.

Will say this about the Terminal icon ... if present, once clicked, opens in a terminal in the browser and one finds themselves located in their account.  Web based apps are normally in public_html so a command like 'cd public_html' will change into that directory.  If Moodle code is there, you will see a config.php file ... ls config.php will show if it's there.   If there, good. 

cd admin/cli/

ls

In there one will find the cli php scripts that fixes things/displays information etc.  Those scripts use the DB user, DB password, and DB name from config.php.   IF the DB user in config.php isn't the super user of the DB server, can't do global things to DB server, but could fix some things with the DB for moodle.

A VPS with Terminal icon in cPanel is what one needs ... IMHO!

Ok ... will leave this to Rick now! smile

'SoS', Ken

In reply to Ken Task

Re: Need help with changing mysql configuration

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
"A VPS with Terminal icon in cPanel is what one needs ... IMHO!" Yep, maybe so. The last time that I was having students learn to use SSH some mentioned to me that they found a Terminal application at their GoDaddy login web page, outside of cPanel. When I look now, I can't find it. But things are always changing.  

I actually have two accounts with GoDaddy, one for my VPS, and one for my experimental hosted server.  On my VPS account, GoDaddy points users to using Putty or Terminal.  Right now on my hosted server account, I can't find what some of my students were finding last summer.
In reply to Rick Jerz

Re: Need help with changing mysql configuration

by Ken Task -
Picture of Particularly helpful Moodlers

Have been into other systems (not GoDaddy and don't re-call which ones now) to assist folks via their cPanel (one was on a shared host!!!!).  If terminal icon present, clicking opens in browser ... black square on a new cpanel generated page ... with a shell prompt showing location ... customers home directory).  Doesn't launch a terminal application on local machine.  Don't have to mess with setting up putty if on PC nor termenal if on a Mac ... no keys to exchange/setup ... simple ... no fuss ... no muss!

cd public_html/admin/cli/

and one can run almost all php scripts in that location.   Since user in config.php isn't superuser of DB server can't use the options to some scripts that would change 'global' settings of the DB server, but can do things like converting all tables and columns in those tables to collation that matches what's in config.php.  The database user in config.php should allow all permissions on the DB being used for the moodle.

Yes, InnoDB with Barracuda would be best, but one could limp along for a while, me thinks.

'SoS', Ken


In reply to Aki Tsirigotis

Re: Need help with changing mysql configuration

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
It's a bit more complex. One needs to be able to switch from Antelop to Barracuda. This is somewhat of a back-end database issue, and on many of these hosted servers, but not all, this is not allowed. Some later versions of MariaDB don't need this change, which is why I mentioned MariaDB. I think (maybe Usman mentioned in another discussion) that some companies like Bluehost are now using MariaDB as their default instead of MySQL. This situation has caught a lot of folks who want to run Moodle on a low-cost hosted server. Many years ago, I ran my Moodle on a GoDaddy hosted server, but then I upgraded to a VPS before this Barracuda stuff happened.

To minimize your headache, search around a little because Usman had offered some ideas (maybe solutions) to a few other folks discussing the same problem. (Usman, hope that I am not misrepresenting you.)

My guess is that this problem might go away with time. But until then...

"Terminal" is a way of saying "SSH" access. Yes, GoDaddy supports SSH and FTP access to these hosted servers. In cPanel, search for either "SSH" and "FTP." Sometimes you must "enable" these. Then, one needs client (meaning on your local computer) means of doing SSH and FTP. There are many FTP products that one can pick, and some are free (and quite good.) For SSH, if you are using a Mac, you actually already have an SSH product called "Terminal" so nothing else is really required. On a PC, one very popular SSH client is "Putty." And there are others. Knowing how to SSH and FTP into your server, and use these means of access are a bit technical, nothing too hard, and are skills that are recommended for working with bigger servers, such as a VPS. If you don't know, or learn how to use these, it becomes much more difficult to manage a server. There are folks here, like Usman and Ken, who are really good at helping people with commands. I am not a server-admin, but know enough to be dangerous.

GoDaddy will answer questions like "How do I enable SSH?" or "How do I enable FTP?" But GoDaddy is a server company, and they don't support the many open-source products directly, so you can't ask them "How do I install Moodle?" If you are looking for this kind of support, you have to search around, or get a Moodle partner involved.

Many of these fast one-click installer programs, like Installatron, are third-party products. They are not GoDaddy software products, they are only provided to try to help people less familiar with servers get something up and going. Even cPanel is third-party product, not GoDaddy. There have been times when I phone GoDaddy for cPanel help and they say "that's not our product, contact cPanel." (Actually, it might be cPanel that gives you the Installatron product, but I am not sure if Installatron is produced by cPanel.)

Some folks like Ken, Usman, and Howard I really respect because they can manage their servers without any of these helper utilities... they know Linux. I am amazed with what they can do.

Having said all of this, I have learned enough so that I can run my Moodle on a GoDaddy VPS, at a reasonable cost, and the server is rock-solid. I have been using a GoDaddy VPS for around 6-7 years. Someday I plan to make a video of how I do that, but I have been too busy teaching for my school.
In reply to Rick Jerz

Re: Need help with changing mysql configuration

by Usman Asar -
Picture of Plugin developers Picture of Testers

Thank you for the kind words Rick, but it's always Ken and Visvanath I looked up for commands, I am definitely not a Linux person, but more of a GUI/Windows. 

anyway, most of the web hosting companies now offer MariaDB as replacement for MySQL, but then they are using the same fork of MariaDB as MySQL, therefore still required to change table format to Barracuda, and bluehost is using the same, so required to change to Barracuda, but then there is work around, enforcing unicode in config.php file, that does though initiate the installation, but still recommend to change to Barracuda, PlanetHoster (to my knowledge) however is only shared host so far using MariaDB 10. Below is Environment check of Bluehost Cloud Sites (on moodle 2.7x) still asking for table format change.


and then we have from Planet Hoster