Mysql error followed by Openfire XMPP server halt

I faced an issue as my IM messenger suddenly got disconnected from the openfire xmpp server. I checked the internet connection to my computer and it was still looking fine. So I hop into openfire server and tried to manually start it. It showed as openfire service starting, but it went off suddenly. I checked some of its logs and could see the issue was with connectivity to mysql service running in same box. Following was the step by step methods I tried to pin point the issue and to sort it out.

First I did ssh the server and got into bash shell.

Tried to manually start openfire service
host # /etc/init.d/openfire start
Starting openfire:

It started running
host # /etc/init.d/openfire status
openfire is running

Suddenly went off
host # /etc/init.d/openfire status
openfire is not running

Showed a pid already exists for openfire when I tried to start openfire again
host # /etc/init.d/openfire start
Openfire is already running. Remove /var/run/openfire.pid if you know this to be untrue.

Removed existing pid
host # rm /var/run/openfire.pid

Starting service
host # /etc/init.d/openfire start
Starting openfire:

It started once again
host # /etc/init.d/openfire status
openfire is running

Went off again
host # /etc/init.d/openfire status
openfire is not running

Checked one of openfire’s log. After referencing some threads, I could see this is due to connectivity issues with the database.
host # tail -f /opt/openfire/logs/info.log
at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:216)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at java.lang.Class.newInstance0(Unknown Source)
at java.lang.Class.newInstance(Unknown Source)
at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:113)
at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:58)
2015.08.31 11:49:23 org.jivesoftware.openfire.XMPPServer – Server halted

I checked status of mysql and found it was down
host # /etc/init.d/mysqld status
mysqld is stopped

Failed over and over while attempting on starting it.
host # /etc/init.d/mysqld start
MySQL Daemon failed to start.
Starting mysqld: [FAILED]

Found the issue with broken binary index file
host # tail -f /var/log/mysqld.log
150831 11:42:10 [ERROR] Failed to open log (file ‘/var/lib/mysql/mysql-bin.000078’, errno 2)
150831 11:42:10 [ERROR] Could not open log file
150831 11:42:10 [ERROR] Can’t init tc log
150831 11:42:10 [ERROR] Aborting

150831 11:42:10 InnoDB: Starting shutdown…
150831 11:42:11 InnoDB: Shutdown completed; log sequence number 97468154
150831 11:42:11 [Note] /usr/libexec/mysqld: Shutdown complete

150831 11:42:11 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Removed current index file
host # rm /var/lib/mysql/mysql-bin.index

Started mysql server. Fresh Index file will automatically recreate.
host # /etc/init.d/mysqld start
Starting mysqld: [ OK ]

Found it was still running fine
host # /etc/init.d/mysqld status
mysqld (pid 10838) is running…

Finally fixed openfire issue as well.
host # /etc/init.d/openfire start
Starting openfire:

host # /etc/init.d/openfire status
openfire is running

Proper Method To Change Default Mysql Engine Permanently

Here the mysql engine is set to InnoDB by default and I want to change it to MyISAM for long run. It was the old way setting parameter “skip-innodb” and commenting “innodb_file_per_table=1” in my.cnf file. Additionally we used to execute mysql query “SET storage_engine=MYISAM;”. Now a days, the above changes wont work and perhaps the mysql server doesn’t come back after initiating a restart since the changes made. The proper method is set the variable “default-storage-engine = MyISAM” like my mysql configuration as follows:


<> ~> cat /etc/my.cnf
[mysqld]
skip-innodb
#innodb_file_per_table=1
default-storage-engine = MyISAM
#innodb_force_recovery = 4

Nothing else you need to do 🙂
Now restart mysql server and check the default engine is set to what


<> ~> /etc/init.d/mysql restart
Shutting down MySQL. [ OK ]
Starting MySQL. [ OK ]


mysql> show engines;
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| InnoDB | NO | Supports transactions, row-level locking, and foreign keys | NULL | NULL | NULL |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.00 sec)

If you want to enable InnoDB as well along with default MyISAM engine, then comment out the variable skip-innodb and remove comment before variable innodb_file_per_table=1