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

47 views
Skip to first unread message

Tom Reijnders

unread,
Jun 13, 2022, 9:08:13 AMJun 13
to uPortal Developers
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

unread,
Jun 13, 2022, 9:58:33 AMJun 13
to uPortal Developers, 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

unread,
Jun 13, 2022, 11:26:49 AMJun 13
to Tom Reijnders, uPortal Developers

--
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

unread,
Jun 13, 2022, 12:58:23 PMJun 13
to uPortal Developers, bgonzalez, uPortal Developers, 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

unread,
Jun 13, 2022, 1:09:01 PMJun 13
to Tom Reijnders, uPortal Developers
Correct!

Julien Gribonvald

unread,
Jun 13, 2022, 1:14:37 PMJun 13
to uport...@apereo.org

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

Tom Reijnders

unread,
Jun 13, 2022, 5:24:48 PMJun 13
to uPortal Developers, julien.g...@recia.fr

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

unread,
Jun 14, 2022, 2:25:45 AMJun 14
to Tom Reijnders, uPortal Developers

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

unread,
Jun 14, 2022, 3:42:37 AMJun 14
to uPortal Developers, julien.g...@recia.fr, 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

unread,
Jun 14, 2022, 4:14:09 AMJun 14
to uport...@apereo.org

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

Tom Reijnders

unread,
Jun 14, 2022, 4:52:34 AMJun 14
to uPortal Developers, julien.g...@recia.fr
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

unread,
Jun 14, 2022, 10:04:21 AMJun 14
to uport...@apereo.org

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

Tom Reijnders

unread,
Jun 14, 2022, 10:40:31 AMJun 14
to uPortal Developers, julien.g...@recia.fr
Duhh

That did it!

Thank you!

Trach bell

unread,
Jul 9, 2022, 9:51:05 AMJul 9
to uPortal Developers, ajjrei...@gmail.com, julien.g...@recia.fr
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

unread,
Jul 11, 2022, 2:55:47 AMJul 11
to Trach bell, uPortal Developers, ajjrei...@gmail.com

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

unread,
Jul 11, 2022, 9:58:30 AMJul 11
to uPortal Developers, julien.g...@recia.fr, ajjrei...@gmail.com, 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

unread,
Jul 11, 2022, 1:37:09 PMJul 11
to Trach bell, uPortal Developers, julien.g...@recia.fr, ajjrei...@gmail.com
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


Trach bell

unread,
Jul 12, 2022, 6:43:56 AMJul 12
to uPortal Developers, bgonzalez, uPortal Developers, julien.g...@recia.fr, ajjrei...@gmail.com, 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

unread,
Jul 12, 2022, 10:07:05 AMJul 12
to Trach bell, uPortal Developers, julien.g...@recia.fr, ajjrei...@gmail.com
I think you need to run './gradlew tomcatDeploy' to get the code compiled and the files copied out to Tomcat.

-bjagg

Trach bell

unread,
Jul 12, 2022, 11:30:37 AMJul 12
to uPortal Developers, bgonzalez, uPortal Developers, julien.g...@recia.fr, ajjrei...@gmail.com, Trach bell
Hi,
not work, after tomacatDeploy I execute Data Init and have the same error
any other solution?
Regards

Benito Gonzalez

unread,
Jul 12, 2022, 3:13:25 PMJul 12
to Trach bell, uPortal Developers, julien.g...@recia.fr, ajjrei...@gmail.com
Did you change the following property in the global.properties in both directories?

hibernate.connection.validationQuery=select 1

-bjagg


Trach bell

unread,
Jul 13, 2022, 3:55:55 AMJul 13
to uPortal Developers, bgonzalez, uPortal Developers, julien.g...@recia.fr, ajjrei...@gmail.com, 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
Reply all
Reply to author
Forward
0 new messages