Cannot run dataInit on new uPortal-start (with mySQL)

[Tom Reijnders]

L.S.,

I've just installed a new uPortal-start and configured it for mysql support.

It fails in dataInit (in the Announcement portlet). So some table are initialised as they should.

The error from the build is:

> Task :overlays:Announcements:dataInit FAILED

The log in catalina_base_IS_UNDEFINED/logs/AnnouncementsPortlet.log:

INFO  [main] o.h.tool.hbm2ddl.SchemaUpdate 2022-06-13 12:58:15,819 - fetching database metadata
WARN  [main] o.h.util.JDBCExceptionReporter 2022-06-13 12:58:15,830 - SQL Error: 0, SQLState: null
ERROR [main] o.h.util.JDBCExceptionReporter 2022-06-13 12:58:15,830 - Cannot create PoolableConnectionFactory (SELECT comm
and denied to user 'uportal'@'localhost' for table 'user')
ERROR [main] o.h.tool.hbm2ddl.SchemaUpdate 2022-06-13 12:58:15,830 - could not get database metadata
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (SELECT command denied to user 'uporta
l'@'localhost' for table 'user')
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionPro
vider.java:87)
        at org.hibernate.tool.hbm2ddl.SuppliedConnectionProviderConnectionHelper.prepare(SuppliedConnectionProviderConnect
ionHelper.java:51)
        at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:168)
        at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:375)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1872)
        at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:795)
        at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:717
)
        at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.jav
a:191)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowire
CapableBeanFactory.java:1688)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCap
ableBeanFactory.java:1626)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapab
leBeanFactory.java:553)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapable
BeanFactory.java:481)
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistr
y.java:230)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
        at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1088)
        at org.jasig.portlet.announcements.SchemaCreator.create(SchemaCreator.java:90)
        at org.jasig.portlet.announcements.SchemaCreator.main(SchemaCreator.java:73)
Caused by: java.sql.SQLSyntaxErrorException: SELECT command denied to user 'uportal'@'localhost' for table 'user'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
        at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
        at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936)
        at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
        at org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:658)
        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1558)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
        ... 21 common frames omitted
ERROR [main] o.h.tool.hbm2ddl.SchemaUpdate 2022-06-13 12:58:15,831 - could not complete schema update

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (SELECT command denied to user 'uportal'@'localhost' for table 'user')
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:87)
        at org.hibernate.tool.hbm2ddl.SuppliedConnectionProviderConnectionHelper.prepare(SuppliedConnectionProviderConnectionHelper.java:51)
        at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:168)
        at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:375)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1872)
        at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:795)
        at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:717)
        at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:191)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1688)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1626)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481)
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
        at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1088)
        at org.jasig.portlet.announcements.SchemaCreator.create(SchemaCreator.java:90)
        at org.jasig.portlet.announcements.SchemaCreator.main(SchemaCreator.java:73)
Caused by: java.sql.SQLSyntaxErrorException: SELECT command denied to user 'uportal'@'localhost' for table 'user'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
        at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
        at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936)
        at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
        at org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:658)
        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1558)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
        ... 21 common frames omitted
INFO  [main] o.h.cache.UpdateTimestampsCache 2022-06-13 12:58:15,836 - starting update timestamps cache at region: org.hibernate.cache.UpdateTimestampsCache
INFO  [main] o.hibernate.cache.StandardQueryCache 2022-06-13 12:58:15,837 - starting query cache at region: org.hibernate.cache.StandardQueryCache
ERROR [main] o.j.p.announcements.SchemaCreator 2022-06-13 12:58:15,853 - Failed to initialize & invoke the SchemaExport tool

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (SELECT command denied to user 'uportal'@'localhost' for table 'user')
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.jasig.portlet.announcements.SchemaCreator.create(SchemaCreator.java:93)
        at org.jasig.portlet.announcements.SchemaCreator.main(SchemaCreator.java:73)
Caused by: java.sql.SQLSyntaxErrorException: SELECT command denied to user 'uportal'@'localhost' for table 'user'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
        at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
        at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936)
        at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
        at org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:658)
        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1558)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
        ... 4 common frames omitted
INFO  [Thread-1] o.s.c.s.GenericApplicationContext 2022-06-13 12:58:15,854 - Closing org.springframework.context.support.GenericApplicationContext@74ad1f1f: startup date [Mon Jun 13 12:58:12 UTC 2022]; root of context hierarchy
INFO  [Thread-1] o.s.o.h.LocalSessionFactoryBean 2022-06-13 12:58:15,859 - Closing Hibernate SessionFactory
INFO  [Thread-1] o.hibernate.impl.SessionFactoryImpl 2022-06-13 12:58:15,872 - closing

Any ideas how to fix this?

Cheers,

Tom Reijnders

Apereo Xerte core developer

[Tom Reijnders]

It has to do with the use of MySQL (actually mariadb). Because If I do a clean checkout and run ./gradlew portalInit folowed by ./gradlew tomcatStart, it works.

Tom

[Benito Gonzalez]

Hi Tom,

Did you follow the directions in https://github.com/uPortal-Project/uPortal-start/blob/master/docs/en/database/mariadb.md ?

-bjagg

--
You received this message because you are subscribed to the Google Groups "uPortal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to uportal-dev...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/uportal-dev/15e207d2-b323-4bd0-a6d6-1ee0d29a5492n%40apereo.org.

[Tom Reijnders]

Tahnks for replying!

Yes, (wel actually I found the French one) but the information is the same.

I suppose I do NOT have to 'You should copy/paste this configuration for each customized database portlet/uPortal context see global datasource documentation' as I did not customize anything (other than the database)

Cheers,

Tom

[Benito Gonzalez]

Correct!

[Julien Gribonvald]

Hi Tom,

please read again the updated doc (there was a change not merged on the doc).

I think this will be better if you use a best suited dialect (adapted to you mysql version), but set that in the global property files to apply it to all portlets. Let us known if you have succeed or have other problems. If not working try to find here: https://github.com/hibernate/hibernate-orm/tree/main/hibernate-core/src/main/java/org/hibernate/dialect the dialect to use depending on your mysql version and the hibernate version. Normaly last version of mysql are already on the good conf (innodb and for the good engine).

Thanks,

Julien Gribonvald

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/uportal-dev/a3c9fe74-d48d-47d7-967b-bbc4c2808a6fn%40apereo.org.

[Tom Reijnders]

I used the one from April 21 (I just installed uPortal-start today) and I am using mariadb 10.3 (Ubuntu 20.04)

This is what I have in etc/portal/global.properties:

hibernate.connection.driver_class=com.mysql.jdbc.Driver
hibernate.connection.url=jdbc:mysql://localhost/uportal?useSSL=false
hibernate.connection.username=xxxxx
hibernate.connection.password=xxxxx
hibernate.connection.validationQuery=select 1 from mysql.user
hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
#hibernate.dialect = org.apereo.portal.utils.MySQL5InnoDBCompressedDialect

Cheers,

Tom

[Julien Gribonvald]

Hi,

Ok I think that's coming from the announcment portlet that doesn't have a hibernate lib up to date, as it's in 3.6 version.

To fix this problem you will need to provide next to uPortal.properties (in the same directory) a property file for the announcementPortlet like here https://github.com/uPortal-Project/uPortal-start/blob/master/.travis/conf/database/mariadb/announcements.properties (keep the name)

You may need to do the same for somes other portlets, if you get also errors, watch on other files in the same directory.

Thanks,

Julien Gribonvald

[Tom Reijnders]

Thank you Julien,

However, that file clearly specifies a different database schema (announcement). That means that either that database should already exist, or the user must have permission to create a new schema. In the mariadb.md, the uportal user explicitly only gets rights for the uportal.* tables. Could that be the issue?

Or should I change the database in that file to the same database that the rest of uportal is using?

Cheers,

Tom

[Julien Gribonvald]

That's like you want, it's not mandatory to use the same database like setting a new one (I'm doing like that for a better management).

So you can set in this file only parts that you want to override from the global.properties. This property file will only override properties already set, in this case it's only usefull for the dialect, for other properties it's depending of your needs. And so you can comment the first line on.

If you watch here https://github.com/uPortal-Project/AnnouncementsPortlet/blob/master/src/main/resources/context/databaseContext.xml#L27 you will find from which files iand in which order properties are loaded, each files will be using to override previous file. And it's like that for all portlets, it's a nice feature for customizations when having a default property files for everyone.

Thanks

Julien Gribonvald

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/uportal-dev/c7a19e90-e40b-4962-b808-e56c58fe543en%40apereo.org.

[Tom Reijnders]

Regrettably, it does not work. Any idea which user table it is trying to access?

ERROR [main] o.h.util.JDBCExceptionReporter 2022-06-14 08:22:04,569 - Cannot create PoolableConnectionFactory (SELECT command

denied to user 'uportal'@'localhost' for table 'user')

[Julien Gribonvald]

I didn't see that before, but you've configured this property:

hibernate.connection.validationQuery=select 1 from mysql.user

it should be only "select 1" as I think that only few users can access to mysql tables !

regards,

Julien Gribonvald

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/uportal-dev/a498a01d-1216-4373-8b69-de02fa4de2bcn%40apereo.org.

[Tom Reijnders]

Duhh

That did it!

Thank you!

[Trach bell]

Hi every body,

need more details about how we do to put a properties file of annoucement and where

thanks in advance

TRach

[Julien Gribonvald]

Hi,

first you should know that you have a default property file into each portlet webapp, after these properties are overrided by global.properties from ${uportal.home} path provided by environnement and finally by ${uportal.home}/announcements.properties

The description of the bean loading properties is here: https://github.com/uPortal-Project/AnnouncementsPortlet/blob/master/src/main/resources/context/databaseContext.xml#L27

After it depends where you have located your properties path, by default it's in your TOMCAT_PATH/portal (copied from uPortal-start/etc/portal, else see on this file: https://github.com/uPortal-Project/uPortal-start/blob/master/gradle/tasks/properties.gradle to understand how you can define a custom path.

Thanks

Julien Gribonvald

[Trach bell]

Hi,

And thanks for your replay, but when i put the file https://github.com/uPortal-Project/uPortal-start/blob/master/.travis/conf/database/mariadb/announcements.properties  in my folder /home/uportal-start/overlays/Announcements/  it does'nt work when I use datainit.

could you help me where is the mistake?

thanks

Trach

[Benito Gonzalez]

Hi Trach,

The properties files are saved in the etc/portal/ directory where the global.properties and uPortal.properties are. This is just to capture them in your repo. They are copied to the Tomcat portal/ directory for actual use. The Gradle task is `tomcatInstall`; however, you can just copy the properties files in the right location without blowing away your current Tomcat and installed uPortal apps. This is in .gradle/tomcat/portal/ by default.

Hope this helps!

-bjagg

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/uportal-dev/5a8a23dd-9922-4a04-952f-b1508bedd9c2n%40apereo.org.

[Trach bell]

Hi bgonzalez et thank's for your help,

I put file 'announcements.properties' into folders 'uportal-start/.gradle/tomcat/portal/'  and '/uportal-start/etc/portal/' in the same time, but got again the same error.

noting in the first action an execute './gradlew tomcatInstall' and after './gradlew dataInit' according to what I understood.

thanks for all

[Benito Gonzalez]

I think you need to run './gradlew tomcatDeploy' to get the code compiled and the files copied out to Tomcat.

-bjagg

[Trach bell]

Hi,

not work, after tomacatDeploy I execute Data Init and have the same error

any other solution?

Regards

[Benito Gonzalez]

Did you change the following property in the global.properties in both directories?

hibernate.connection.validationQuery=select 1

-bjagg

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/uportal-dev/1aeb064a-74f1-4414-b791-9ef476343fean%40apereo.org.

[Trach bell]

hi

I Put it like the file under mariadb folder is

contains :

hibernate.connection.url=jdbc:mysql://127.0.0.1:3306/announcements
hibernate.dialect = org.apereo.portal.utils.hibernate3.dialects.MySQL5InnoDBCompressedDialect

thanks