MySQL server has gone away

Software Bahmni
1

We are getting following error mysql> show tables; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect… Connection id: 12 Current database: openmrs

ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect… ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (111) ERROR: Can’t connect to the server

Restarting mysql or the server itself hasn’t helped. mysql starts in force mode but as readonly.

Any ideas?

2

@ramashish It usually does that when you are providing the wrong connect information.look at bahmni connection credentials and retry.

3

How do I check bahmni connection credentials? Bahmni does not run at all. OpenMRS able to login but Administration->Location Hierarchy gives JDBConnection error.

This error is seen even from mysql prompt

4

It usually does that when you are providing the wrong connect information.

5

Any specific file where I should be checking?

6

I suppose he desires your guidance on how to check the bahmni connection credentials

7

@ramashish can you look at this thread error in uploading address-hierarchy

and https://bahmni.atlassian.net/wiki/spaces/BAH/pages/53837974/List+Of+Configurable+Installation+Variables1 they may be of help.

FYI @darius

8

This will not help as this is not related to the user id pwd issue. I am able to login to mysql. It throws error after login to mysql from command prompt.

9

@ramashish have you read through the post and the Bhamni documentation?And assuming you have done so which credentials are you using?

10

Anyways

The MySQL server has gone away (error 2006) has two main causes and solutions:

Server timed out and closed the connection. To fix, check that wait_timeout mysql variable in your my.cnf configuration file is large enough.

Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection.

To fix, you can increase the maximal packet size limit max_allowed_packet in my.cnf file, eg. set max_allowed_packet = 128M, then restart your MySQL server: sudo /etc/init.d/mysql restart

11

To know the “max_allowed_packet” variable size you can drop this query in your MySQL client,

{ SHOW VARIABLES like ‘max_allowed_packet’ } ,it will show you the current value ,then rectify it to a bigger value…

Hope this is helpful

12

I had tried this too. I think the value 128M has already been set in auto.cnf

13

@ramashish what have you tried?

14

Things we tried following and their results

  1. Checked both log files - /var/log/mysqld.log and var/log/openmrs/openmrs.log did not have any relevant information. Ensured that the last successful backup gets restored on another machine.

  2. Forced restart of mySQL - Restarted mySQL using

innodb_force_recovery = 4.

This restarted the mySQL server in Read Only mode which only confirmed that there is no other issue (uid, pwd, bahmni, openmrs etc.) other than some problem with the database itself.

  1. mySQL check From CentOS command Prompt as mentioned here:

mysqlcheck -uuid -ppwd openmrs

This showed OK for most tables till location_tag where connection got terminated indicating that this table has some issue. When tried mysqlcheck on location_tag_map it showed Ok. So used

mysqlcheck --repair -uuid -ppwd openmrs location_tag

with no success ie connection got terminated. Tried

Alter table location_tag ENGINE = InnoDB;

which did not help either ie connection got terminated.

  1. Restore specific table - Due to lack of repair from the last successful backup which was restored on another machine extracted location_tag table using

mysqldump -uuid -ppwd openmrs location_tag > location_tag.sql

Restored it using mysql -uuid -ppwd openmrs < location_tag.sql

This also failed due to connection terminated.

  1. Restore last successful backup - Using innodb_force_recovery = 4 started mysql and dropped openmrs database. Removed innodb_force_recovery = 4 and restarted mysql. Then restored the last successful backup.

This got successfully restored but need further investigation on why the repair options failed.