Monday, June 25, 2012

MYSQL ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (111)

So I was thrown this error recently, while setting up a testing mysql environment and attempting to access it from a remote host. Usually it's the simple things that are overlooked, so here's how to resolve the error:
ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (111)

The first thing we can check is to see if the user from the remote host is allowed.

1. Login as root on mysql server
mysql -u root -p

2. Select database and show users.
select * from mysql.user\G

**A vertical list will be displayed. Look at the first two fields for each entry. If the user is only allowed to connect from localhost, this may be the problem.
Example:

Host: localhost
User: mydbuser

A user will have to be defined with the same parameters as mydbuser for the remote host (or hosts)
Here's where your documentation will come in handy (or you can hope the old query exists in the mysql buffer!)

3. Allow remote hosts to connect
grant select,insert,update,delete,create,drop,index,alter on mydbname.* to mydbuser@'192.168.1.%' identified by 'mydbpassword' ;

flush privileges;

Note: if you only want to allow a certain host, specify the IP instead of the wildcard.


The second issue that may cause this error is a MySQL configuration.

1. Open MySQL config file
nano /etc/my.cnf

2. Ensure that the following are commented out.

#skip-external-locking
#skip-networking
#bind-address = xx.xx.xx.xx

Save and exit

3. Restart mysql service
service mysqld start

The third issue that may contribute to this error may be the security configuration rejecting incoming requests on the server.

1. Login as root on db server

2. Add rule to iptables
/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT

** this grants access to the entire subnet, use a specific IP where applicable.

service iptables save

3. Restart iptables service
service iptables restart


Test from remote host by using the following:
mysql -h 192.168.my.dbip -u mydbuser -p


-noveck



Monday, June 18, 2012

Moodle Issues: Backup and restore / course import fails

It was one of those strange situations that popped up after a disaster recovery scenario.

The database server suffered hardware failure, but that was quickly resolved and some tests performed on the database -
MYISAMCHK
and
MYSQLCHECK

Both tests indicated no issues.

Soon after I started getting reports of the course imports or (backup/restores) failing, and troubleshooting took a couple hours. There were a lot of leads on the moodle forums but this is the solution to my particular issue. I strongly recommend turning debugging on from the moodle interface, simply to get as much description on the error as possible.
Debugging revealed a cryptic message: Warning: fopen******* [function.fopen]: failed to open stream: No such file or directory in ******/backuplib.php on line ***followed by a bunch of fwrite problems.

PROBLEM MESSAGE: An error occurred while backing up course start

Solution:

0. Login as admin to the moodle interface.


1. Open the mysqladmin tool  (this can also be done from the MYSQL Server)
2. Truncate the following tables:
  • mdl_backup_courses
  • mdl_backup_files
  • mdl_backup_ids
TRUNCATE TABLE tablename;

3.  Run a mysqlcheck with the check and repair flags just in case.

In my case, this solved the problem. The important point to note, is that the debugging may lead you in the correct direction, so DO NOT USE THIS SOLUTION if you are not certain your problem is identical!


Cheers,
-noveck