Troubleshooting MySQL

Owned by Former user (Deleted)
Last updated: Aug 29, 2011 by Michael DowneyVersion comment
3 min read

On this page:

Configure Instance hangs on starting the service

Problem

In windows during the last step of the installation of MySQL, the box hangs before the "starting the service" box has been checked.

Solution

Delete the service using these instructions and try the installation again.

Error 1045

Problem

Error Nr. 1045 appears using the configuration instance.

Solution

A. Another program has installed a MySQL instance on your computer.

B. A previous installation has left files.
The data directory (defaulted in windows 7 to c:\ProgramData\MySQL\MySQL Server x.x) may need to be deleted as the previous installation may have left residual data. To delete the files successfully in windows 7 you will likely need either to use an unlocking program like Unlocker or reboot and delete the directory. Failure to do so will result in the reinstallation of MySQL using the same root password even if you've changed the directory location.

MySQL has gone away (Error 2006)

Problem

Or maybe you saw an error about packet length? This can be caused by the client and/or server trying to handle data larger than the maximum packet length. The default is 1 MB. Some of the larger records (e.g., form data) can easily exceed this maximum when multiple records are placed into the same SQL statement (e.g., when performing a SQL dump or loading scripts).

Solution

Increase the max_allowed_packet setting for MySQL

  • Edit your INI file (e.g. for Windows, C:\Program Files\MySQL\MySQL Server x.x\my.ini).
    • You must edit the actual INI file in use. MySQL comes with several alternative configurations (INI configurations). If you use the administration program, you should be able to find the max_allowed_packet setting — I found it using MySQL Administrator under the "Health" section's "System Variables" tab (Connections ? Networking ? max_allowed_packet in the hierarchy). [This is with MySQL 4.1.9 and MySQL Administrator version 1.0.19, the details may differ between versions).
  • On Linux, edit the /etc/my.cnf file
  • Under the [archive:mysqld] section, add the line: max_allowed_packet=64M
  • You can safely increase this setting up to 128M or higher (max for 5.0 is 1GB, I believe) as MySQL only uses the memory as needed.

Restart MySQL

  • Your client may need to be adjusted as well. For mysql.exe (the MySQL command line), you can use the command: mysql --max_allowed_packet=64M
  • other clients may have a different method for changing this value

I would suggest googling on something like "MySQL has gone away" or mysql max_allowed_packet for up-to-date references on this topic.

In my case, I was trying to load a 32 megabyte SQL script and kept getting the not-so-helpful "MySQL has gone away" error, indicating the the MySQL server was having trouble and disconnecting in the middle of the query. I set the max_allowed_packet to 64M and my query ran successfully. Hopefully this tip will save you some time. -Burke

Connecting with multiple MySQL instances on a single system

Problem

  • When attempting to run a second database instance for OpenMRS on a system where MySQL is already running, the OpenMRS initial setup is unable to create the OpenMRS user and database, or
  • OpenMRS is unable to connect to its database instance while another MySQL instance is running on a separate port.

Solution

On UNIX-based systems, MySQL clients always use UNIX sockets to connect to MySQL when "localhost" is specified in the connection URL. This is a known issue/limitation/bug in MySQL and is documented in more detail at http://bugs.mysql.com/bug.php?id=31577. As a result, if you want to run OpenMRS in a separate database instance than one already existing on a given system (for example, if you want to run OpenMRS Standalone on a system where MySQL is already installed) you will need to do two things:

  1. Ensure the new database instance is running on a different port.
  2. Ensure you are connecting via TCP/IP instead of the same UNIX socket as the existing instance.

The easiest way to do this is to use "127.0.0.1" instead of "localhost" in the connection string. An alternative is to add &server.port=XXXX to the value of connection.url in the openmrs-runtime.properties file, where "XXXX" is the port used by the second MySQL instance to be used for OpenMRS.

Examples: (assuming the MySQL instance used by OpenMRS is running on port 4242)

openmrs-runtime.properties
...
connection.url=jdbc:mysql://127.0.0.1:4242/openmrs?autoReconnect=true&sessionVariables=storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8
...

or

openmrs-runtime.properties
...
connection.url=jdbc:mysql://localhost:4242/openmrs?autoReconnect=true&sessionVariables=storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8&server.port=4242
...