Everyone has a piece of the puzzle. No one has the answer. We have to figure out our piece and share it with everyone. This is why the guru thing doesn’t work anymore.

–Abdi Assadi
Source: Accidental Chic, http://goo.gl/nJRxv




The first time I couldn’t login to my Moodle, I almost panicked. Before I pulled the plug and reinstalled everything, conceding defeat–a temptation when something like this happens–I decided on a simple trick. Login to the MySQL database, copy the encrypted password (that’s how they are stored in the MySQL database) of a user I knew and then paste it into the admin’s password. After that, login as admin using the other user’s password. Did it work? Yes.


But what happens when you can’t even login to MySQL? What do you do? This is the exact problem that faced Austin when he wanted his students to login to Moodle recently. This is what Austin wrote–via the MoodleMayhem.org email list–to describe the problem:

My students went to log on to our Moodle this morning and they all got an error.

Error: Database connection failed
It is possible that the database is overloaded or otherwise not running properly.
The site administrator should also check that the database details have been correctly specified in config.php

I remotely logged in to the computer we use as our Moodle server to see if I could diagnose the problem. I tried accessing the database using MySQLYog and MySQL Workbench. Both programs gave me errors when trying to access the database. I then restarted the server just to see if that would correct the problem. It didn’t.

I then tried to access the database via the command line client. Again I received an error. The error I get from the command line client is the same as the error I got in the other programs I tried. The error is, “Error 1045 <28000>: Access denied for user ‘root@localhost’ ”

The server is running Zend CE 5.0.4 and MySQL 5.1. No one else has access to the server, and no changes/updates have been made since I successfully used the Moodle yesterday around 4pm. I do have a program that makes automatic backups of the database nightly.

Any ideas on the issue? Do I need to try reinstalling MySQL?

 The localhost Moodle user account–the one account that Moodle uses to access the MySQL database–wasn’t working. In fact, the root user–the most powerful one–couldn’t login. Something had gone wrong, but how to fix it?

One of the powerful aspects of a community of users is that someone knows the answer, or can provide a hint to the solution. While there are a million answers to questions like these on the web, every problem is subtly different. Here’s my response to Austin’s question, which gave him enough information to solve the issue himself with the right customization:

I’ve only run into this problem once, and not on a server, thank goodness. I ended up having to wipe out my installation and then reload everything from scratch, then restore backups.
If I understand your email right, the user/pwd that connects Moodle to MySQL is NOT working. Worse, your ROOT username and password that you login to make create new databases/users, etc. is NOT working. As I recall, you can’t backup the MySQL user table
So the question is, how do I reset my MySQL root user password? 


If that is the question you are trying to answer, would the following how to work? http://www.nixexp.com/index.php/2006/12/16/how-can-i-reset-mysql-root-password-root-password-recovery/
If that process is successful, you will have regained access to MySQL databases and can then recreate users, etc.
Another question to ask is, how often have you been optimizing your MySQL tables? Sometimes corruption occurs. I don’t know if that is the problem, just something to check afterwards.
So, I can’t offer you a solid solution at the moment. Please share back if you try the solution above.

Austin took the response above and customized it for his own server:

Thanks for the suggestion! That link was for a Linux system and my server is on a Windows, but I was able to use your idea to find another walkthrough on how to reset the root password. This link is from the actual MySQL documentation. It was relatively simple to follow. I just had to tweak the path to the installation path for my installation of MySQL.

One part that was a bit confusing was that in the command prompt window when I entered the command there was never any feedback telling me the process had run successfully. I just decided to try to log in with the new password I set and it worked.

I then used phpMyAdmin to change the password to what I wanted it to be. I could’ve done that when I reset the password using the walkthrough, but I was so focused on following the directions exactly that I forgot to change the password at that step.

Here is the link: http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting-permissions-windows
All of my data is there and I’ve checked my Moodle: it’s running fine.

A quick follow-up question: You mentioned “optimizing” tables in my database. How does one go about doing that? (Optimizing Your MySQL Tables  – just a walkthrough with screenshots. Very easy using PHPMyAdmin.)

Powerful conversations that lead to solutions…that’s what folks are able to accomplish when they are willing to share their failures and challenges.

On another note, Austin made another suggestion that I wasn’t aware of when I asked him about how he had setup his Windows server:

I’m running MySQL/PHP/Apache. I used a package from Zend called Zend CE. It has MySQL, Apache server, PHP included. It was a VERY easy install. It walks you through and allows you to set everything up how you like as you install. Also, it had all the necessary php extensions to run Moodle included. It includes an easy way to access the php portion of the server to restart php or add other extensions, edit behaviors, etc. I would say give that a shot to see if it will make getting it all set up easier. http://www.zend.com/en/products/server-ce/downloads I think I installed the newest version of MySQL since the Zend package contained an older version, but other than that I just used that Zend package.

I’ll be trying out this package to see how it works.


Get Blog Updates via Email!

Enter your email address:

Delivered by FeedBurner

PingIt! pingthis();

Everything posted on Miguel Guhlin’s blogs/wikis are his personal opinion and do not necessarily represent the views of his employer(s) or its clients. Read Full Disclosure
Advertisements