Is tigase 7.1.x compatible with mysql 8? (#1492)

Unknown opened 2 years ago

We need to plan the migration from mysql 5.7 to 8, is tigase 7.1.7 compatible with Mysql 8?

There are any issues with stored procedure?

On a test env we had to add on the query string "&noAccessToProcedureBodies=true" otherwise the mysql connection fails with

ConfiguratorAbstract.setProperties()  SEVERE: Can't initialize auth default repository: 
tigase.db.DBInitException:

at tigase.server.XMPPServer.main(XMPPServer.java:112)
Caused by: java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.

is it safe to add this config to the jdbc query string?

noAccessToProcedureBodies=true

It seems that on mysql 8 there is no more mysql.proc so I cannot do:

GRANT SELECT ON mysql.proc TO 'tigaseUser'@'%';

Regards

Unknown commented 2 years ago

MySQL 8 is not officially supported by Tigase XMPP Server 7.1.x. Recommended MySQL versions for that Tigase version are 5.5 (minimum) and 5.6 (recommended).

For MySQL 8 it's recommended to update to the latest Tigase version, which supports latest MySQL version. What's more, it doesn't require manually granting rights for Tigase user nor relies on mysql.proc table.

Unknown commented 2 years ago

Ok, thanks

Unknown commented 2 years ago

I have the same problem also with tigase 8, still the same question: "is it safe to add this config to the jdbc query string?" (noAccessToProcedureBodies=true) here the exception if I do not use it:

Could not initialize bean default (class: class tigase.db.beans.AuthRepositoryMDPoolBean$AuthRepositoryConfigBean), skipping injection of this bean
RootCause:
   -> java.lang.RuntimeException: Could not initialize tigase.db.AuthRepository for name 'default'
      [tigase.db.beans.MDPoolConfigBean.beanConfigurationChanged(MDPoolConfigBean.java:109)]
      -> java.lang.reflect.InvocationTargetException
         [java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)]
         -> java.lang.RuntimeException: java.lang.RuntimeException: Could not initialize tigase.db.jdbc.TigaseCustomAuth for name 'default'
            [java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)]
            -> java.lang.RuntimeException: Could not initialize tigase.db.jdbc.TigaseCustomAuth for name 'default'
               [tigase.db.beans.MDPoolConfigBean.lambda$setInstances$0(MDPoolConfigBean.java:179)]
               -> tigase.db.DBInitException: Could not initialize TigaseCustomAuth instance
                  [tigase.db.jdbc.TigaseCustomAuth.setDataSource(TigaseCustomAuth.java:929)]
                  -> java.sql.SQLException: Could not initialize prepared statement
                     [tigase.db.DataRepositoryPool.executeForEachDataSource(DataRepositoryPool.java:250)]
                     -> java.lang.RuntimeException: java.lang.RuntimeException: Could not initialize prepared statement
                        [java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)]
                        -> java.lang.RuntimeException: Could not initialize prepared statement
                           [tigase.db.DataRepositoryPool.lambda$executeForEachDataSource$3(DataRepositoryPool.java:239)]
                           -> java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
                              [com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)]

Unknown commented 2 years ago

Could you check the rights of the Stored Procedures, i.e. if the definer is the Tigase user? (e.g. output of SHOW PROCEDURE STATUS;)?

If not, this should be adjusted.

Unknown commented 2 years ago

ok thanks, I have found that some are not, I will fix on that side

	Unknown opened 2 years ago We need to plan the migration from mysql 5.7 to 8, is tigase 7.1.7 compatible with Mysql 8? There are any issues with stored procedure? On a test env we had to add on the query string "&noAccessToProcedureBodies=true" otherwise the mysql connection fails with `ConfiguratorAbstract.setProperties() SEVERE: Can't initialize auth default repository: tigase.db.DBInitException:` `at tigase.server.XMPPServer.main(XMPPServer.java:112) Caused by: java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.` is it safe to add this config to the jdbc query string? `noAccessToProcedureBodies=true` It seems that on mysql 8 there is no more mysql.proc so I cannot do: `GRANT SELECT ON mysql.proc TO 'tigaseUser'@'%';` Regards
	Unknown commented 2 years ago MySQL 8 is not officially supported by Tigase XMPP Server 7.1.x. Recommended MySQL versions for that Tigase version are 5.5 (minimum) and 5.6 (recommended). For MySQL 8 it's recommended to update to the latest Tigase version, which supports latest MySQL version. What's more, it doesn't require manually granting rights for Tigase user nor relies on `mysql.proc` table.
	Unknown commented 2 years ago Ok, thanks
	Unknown commented 2 years ago I have the same problem also with tigase 8, still the same question: "is it safe to add this config to the jdbc query string?" (noAccessToProcedureBodies=true) here the exception if I do not use it: Could not initialize bean default (class: class tigase.db.beans.AuthRepositoryMDPoolBean$AuthRepositoryConfigBean), skipping injection of this bean RootCause: -> java.lang.RuntimeException: Could not initialize tigase.db.AuthRepository for name 'default' [tigase.db.beans.MDPoolConfigBean.beanConfigurationChanged(MDPoolConfigBean.java:109)] -> java.lang.reflect.InvocationTargetException [java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)] -> java.lang.RuntimeException: java.lang.RuntimeException: Could not initialize tigase.db.jdbc.TigaseCustomAuth for name 'default' [java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)] -> java.lang.RuntimeException: Could not initialize tigase.db.jdbc.TigaseCustomAuth for name 'default' [tigase.db.beans.MDPoolConfigBean.lambda$setInstances$0(MDPoolConfigBean.java:179)] -> tigase.db.DBInitException: Could not initialize TigaseCustomAuth instance [tigase.db.jdbc.TigaseCustomAuth.setDataSource(TigaseCustomAuth.java:929)] -> java.sql.SQLException: Could not initialize prepared statement [tigase.db.DataRepositoryPool.executeForEachDataSource(DataRepositoryPool.java:250)] -> java.lang.RuntimeException: java.lang.RuntimeException: Could not initialize prepared statement [java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)] -> java.lang.RuntimeException: Could not initialize prepared statement [tigase.db.DataRepositoryPool.lambda$executeForEachDataSource$3(DataRepositoryPool.java:239)] -> java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types. [com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)]
	Unknown commented 2 years ago Could you check the rights of the Stored Procedures, i.e. if the definer is the Tigase user? (e.g. output of `SHOW PROCEDURE STATUS;`)? If not, this should be adjusted.
	Unknown commented 2 years ago ok thanks, I have found that some are not, I will fix on that side