Database error after openfire upgrade from 3.10.2 to 4.5.1-1

Openfire
1

Hi

I have juste upgraded from 3.10.2 to 4.5.1-1 (on Linux Centos 7) and I have this error :
Loading class com.mysql.jdbc.Driver'. This is deprecated. The new driver class is com.mysql.cj.jdbc.Driver’. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Database setup or configuration error: Please verify your database settings and check the logs/error.log file for detailed error messages.
java.lang.IllegalArgumentException: java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation; OFPRESENCE_PK table: OFPRESENCE
at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:786)
at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:499)
at org.jivesoftware.openfire.XMPPServer.(XMPPServer.java:216)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at java.lang.Class.newInstance(Class.java:442)
at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:92)
at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:56)
Caused by: java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation; OFPRESENCE_PK table: OFPRESENCE
at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:152)
at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:776)
… 9 more
Erreur au démarrage du serveur. Veuillez vérifier les journaux pour plus d’information.
Server halted

Thanks for any advice

Karim

2

You appear to have a corrupted database. The corruption, however, was already guarded for in Openfire 3.10.2 as far as I can tell. Do you have custom integrations that work on that database / did someone modify the constraints on that database?

What is happening is that the table OFPRESENCE in your database has rows with duplicate values for the username column.

My advice is to first carefully backup everything, then try and remove the conflicting rows from the database.

You can mostly ignore this bit. Openfire is running with an older MySQL driver.

3

Thanks for your help
I have tried to remove all the lines with “INSERT INTO OFPRESENCE…”

cat openfire.script | grep -v “INSERT INTO OFPRESENCE” > new
cp new openfire.script

But I still have the error

4

I have reinstalled from scratch :slight_smile: