Finding & Resolving Mysql Connection Leaks

As the title imply; we need to know why Mysql runs out of connections even if it may be configured to handle much more connections. First, most leaks come from the code, check for unclosed connections before anything. If you are using JPA/Hibernate - which is my case - you use a data source either managed by the container as a JNDI resource or a simple component used by the application.

In my application I had a lot of time debugging why mysql runs out of connections and finally I did the following to know the cause:

1- Limit the running features of your app: for example; I ran only the scheduled jobs of the system to maintain a clear log and also monitor the DBMS health slowly. Let your app run for a while till the problem happen or a suitable time has passed.
2- Mysql Admin: see the health of the DBMS from the "mysql admin" tool, if you find the connection usage rising up then we have a connection leak problem for sure.
3- show processlist: in the mysql command line tool, type "show processlist;". Notice the "Command" column of the results in front of you if you find a lot of connections marked as "Sleep" then we are on the right track for the disaster as sooner or later the connection pool will run out of connections.
4- wait_timeout: The "wait_timeout" variable tells Mysql when to consider an unused connection available for termination. for me, that was the problem, a default setting for the DB server not suitable for production or heavily loaded environments. Open the system variables tab and find the "wait_timeout" variable and check its value - default is 28880 seconds -, change this to a much lower value say 15 for example.

What caused the sleeping connections?
A data source may not close the connection after communicating with the DBMS for future use purposes, so, it puts the connection in a sleeping mode and it is the responsibility of the DBMS to terminate it as needed. What we have done in the last step is making sure the DBMS does its part in the play.

.. more.

Enabling remote access to Mysql

A very common problem when using Mysql is when you try to remotely connect to the server, the solution is very simple and direct.

Configuration:
1- find a file named "my.cnf", this file contains the server configurations.
2- if you find a line with this syntax "skip-networking", comment it by putting a "#" in the begining of the line.
3- if you find a line with this syntax: bind-address = 127.0.0.1, comment it as well, this line tells mysql to accept connections only from localhost.

Persmissions:
now, you have to tell mysql who is to access the server with what permissions
1- login to mysql(example using root user name) : mysql -uroot -p
2- suppose that the data base we need to make available is named koko, so to make koko available to root with password 'password' at the machine with address 192.168.1.5 we write:
GRANT ALL on koko.* TO 'root'@'192.168.1.5' IDENTIFIED BY 'password';
3- then write : FLUSH PRIVILEGES;

Note: do not forget to restart mysql to reload configurations in the my.cnf file.

.. more.

MySQL .Net Connector Error 2869 on Windows VISTA

I've solved an error I had while installing the .net connector of MySQL on windows VISTA.
I had an error number 2869 and the setup rolled back.
The solution that I found on the MySQL website was to run the command prompt in administrator mode and run the setup from there.
it worked with me, & I hope it does the same for u

.. more.