Not able to change keycloak-10.0.1 databace configuration from H2 to Postgresql

pahade · May 21, 2020, 11:38am

I have follow the below steps as per keyckoak:-
https://www.keycloak.org/docs/latest/server_installation/index.html#package-the-jdbc-driver

path: /keycloak-10.0.1\modules\system\layers\keycloak\org\postgresql\main

module.xml

<?xml version="1.0" ?>

postgresql-9.4.1212.jar
after adding jar i have to change the standalone.xml file.
Path: keycloak-10.0.1\standalone\configuration

jdbc:postgresql://localhost:5432/keycloak-dev postgresql postgres password org.postgresql.xa.PGXADataSource

After change the configuration when we restart the server : -> standalone.bat

console logs:

Calling “D:\Users\itteam\Downloads\keycloak-10.0.1\bin\standalone.conf.bat”
Setting JAVA property to “C:\Program Files\Java\jdk1.8.0_251\bin\java”

JBoss Bootstrap Environment

JBOSS_HOME: “D:\Users\itteam@tekmindz.com\Downloads\keycloak-10.0.1 - postgresql”

JAVA: “C:\Program Files\Java\jdk1.8.0_251\bin\java”

JAVA_OPTS: "-Dprogram.name=standalone.bat -Xms64M -Xmx512M -XX:MetaspaceSize=96M -XX:MaxMetaspaceSize=256m -Djava.net.preferIPv4Stack=true -Djboss.modules.system.pkgs=org.jboss.byteman "

===============================================================================

17:07:25,060 INFO [org.jboss.modules] (main) JBoss Modules version 1.10.0.Final
17:07:25,939 INFO [org.jboss.msc] (main) JBoss MSC version 1.4.11.Final
17:07:25,994 INFO [org.jboss.threads] (main) JBoss Threads version 2.3.3.Final
17:07:26,428 INFO [org.jboss.as] (MSC service thread 1-4) WFLYSRV0049: Keycloak 10.0.1 (WildFly Core 11.1.1.Final) starting
17:07:28,271 INFO [org.wildfly.security] (ServerService Thread Pool – 20) ELY00001: WildFly Elytron version 1.11.4.Final
17:07:28,882 INFO [org.jboss.as.controller] (Controller Boot Thread) OPVDX002: Failed to pretty print validation error: null
17:07:28,891 ERROR [org.jboss.as.server] (Controller Boot Thread) WFLYSRV0055: Caught exception during boot: org.jboss.as.controller.persistence.ConfigurationPersistenceException: WFLYCTL0085: Failed to parse configuration
at org.jboss.as.controller.persistence.XmlConfigurationPersister.load(XmlConfigurationPersister.java:143)
at org.jboss.as.server.ServerService.boot(ServerService.java:392)
at org.jboss.as.controller.AbstractControllerService$1.run(AbstractControllerService.java:416)
at java.lang.Thread.run(Thread.java:748)
Caused by: javax.xml.stream.XMLStreamException: com.ctc.wstx.exc.WstxParsingException: Received non-all-whitespace CHARACTERS or CDATA event in nextTag().
at [row,col {unknown-source}]: [142,21]
at org.jboss.as.connector.subsystems.datasources.DataSourcesExtension$DataSourceSubsystemParser.readElement(DataSourcesExtension.java:650)
at org.jboss.as.connector.subsystems.datasources.DataSourcesExtension$DataSourceSubsystemParser.readElement(DataSourcesExtension.java:204)
at org.jboss.staxmapper.XMLMapperImpl.processNested(XMLMapperImpl.java:122)
at org.jboss.staxmapper.XMLExtendedStreamReaderImpl.handleAny(XMLExtendedStreamReaderImpl.java:69)
at org.jboss.as.server.parsing.StandaloneXml_10.parseServerProfile(StandaloneXml_10.java:587)
at org.jboss.as.server.parsing.StandaloneXml_10.readServerElement(StandaloneXml_10.java:222)
at org.jboss.as.server.parsing.StandaloneXml_10.readElement(StandaloneXml_10.java:119)
at org.jboss.as.server.parsing.StandaloneXml.readElement(StandaloneXml.java:126)
at org.jboss.as.server.parsing.StandaloneXml.readElement(StandaloneXml.java:52)
at org.jboss.staxmapper.XMLMapperImpl.processNested(XMLMapperImpl.java:122)
at org.jboss.staxmapper.XMLMapperImpl.parseDocument(XMLMapperImpl.java:76)
at org.jboss.as.controller.persistence.XmlConfigurationPersister.load(XmlConfigurationPersister.java:126)
… 3 more
Caused by: com.ctc.wstx.exc.WstxParsingException: Received non-all-whitespace CHARACTERS or CDATA event in nextTag().
at [row,col {unknown-source}]: [142,21]
at com.ctc.wstx.sr.StreamScanner.constructWfcException(StreamScanner.java:634)
at com.ctc.wstx.sr.StreamScanner.throwParseError(StreamScanner.java:504)
at com.ctc.wstx.sr.StreamScanner.throwParseError(StreamScanner.java:488)
at com.ctc.wstx.sr.BasicStreamReader.nextTag(BasicStreamReader.java:1223)
at org.jboss.staxmapper.XMLExtendedStreamReaderImpl.nextTag(XMLExtendedStreamReaderImpl.java:152)
at org.jboss.as.connector.subsystems.datasources.DsParser.parseDataSource_4_0(DsParser.java:1927)
at org.jboss.as.connector.subsystems.datasources.DsParser.parseDataSources(DsParser.java:229)
at org.jboss.as.connector.subsystems.datasources.DsParser.parse(DsParser.java:189)
at org.jboss.as.connector.subsystems.datasources.DataSourcesExtension$DataSourceSubsystemParser.readElement(DataSourcesExtension.java:643)
… 14 more

17:07:28,898 FATAL [org.jboss.as.server] (Controller Boot Thread) WFLYSRV0056: Server boot has failed in an unrecoverable manner; exiting. See previous messages for details.
17:07:28,965 INFO [org.jboss.as] (MSC service thread 1-3) WFLYSRV0050: Keycloak 10.0.1 (WildFly Core 11.1.1.Final) stopped in 20ms
Press any key to continue . . .

kuylim · May 23, 2020, 3:30pm

Hi,
From your stack-trace: Caused by: com.ctc.wstx.exc.WstxParsingException: Received non-all-whitespace CHARACTERS or CDATA event in nextTag().

It’s not keycloak problem, you might copied the those configurations from document-page, which is contains non white-space albeit invisible characters. So its cause jboss to read XML configuration.

Better solution, by duplicate existing configuration (h2 db) and modify it to postgresql.

Hope its answer to your problem. Thanks.

pahade · May 27, 2020, 9:03am

Hi Kuylim,

I have change database configuration as per given keycloak docs:-
https://www.keycloak.org/docs/latest/server_installation/index.html#package-the-jdbc-driver

\keycloak-10.0.1\modules\system\layers\keycloak\org\postgresql\main

module.xml

<?xml version="1.0" ?>
  <module xmlns="urn:jboss:module:1.3" name="org.postgresql">
<resources>
    <resource-root path="postgresql-9.4.1212.jar"/>
</resources>
<dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
</dependencies>

Add below jar file:
postgresql-9.4.1212.jar - add the jar

standalone.xml - modify the this file.

<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" 
      enabled="true" use-java-context="true">
             <connection-url>jdbc:postgresql://localhost:5432/keycloak</connection-url>
             <driver>postgresql</driver>
 			<pool>
 				<max-pool-size>20</max-pool-size>
 			</pool>
             <security>
                 <user-name>postgres</user-name>
                 <password>password</password>
             </security>
         </datasource>
         <drivers>
             <driver name="postgresql" module="org.postgresql">
                 <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
             </driver>
         </drivers>
     </datasources>

After change the configuration when i start the standalone.bat then we are getting the bellow error logs.

Caused by: javax.xml.stream.XMLStreamException: com.ctc.wstx.exc.WstxParsingException: Received non-all-whitespace CHARACTERS or CDATA event in nextTag().

zonaut · May 27, 2020, 9:25am

First of all, you say you’re switching to PostgreSQL but are configuring a mysql driver in your module?
Second, it seems that your module xml is invalid on first glance, where is the closing module tag at the end?
Remove also all whitespaces at the end in those files, maybe you copy pasted some wrong things

The Keycloak documentation clearly states that you need the following in your module.xml

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.3" name="org.postgresql">

    <resources>
        <resource-root path="postgresql-9.4.1212.jar"/>
    </resources>

    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

pahade · May 27, 2020, 9:35am

I have change the module.xml it’s put by mi-stack but now also getting the same error.

zonaut · May 27, 2020, 9:39am

Also check your module.xml and standalone-ha.xml file for non white space albeit invisible characters.
Consult your IDE on how to view these or remove all white spaces all together in the file

When reading your error log it says at [row,col {unknown-source}]: [142,21], so it seems to be your standalone.xml file, take a look in your editor what’s on that row and column

pahade · May 27, 2020, 9:49am

I have modify the below content on standalone-ha.xml , standalone.xml both file.

 <datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
      <connection-url>jdbc:postgresql://localhost:5432/keycloak</connection-url>
            <driver>postgresql</driver>
				<pool>
					<max-pool-size>20</max-pool-size>
				</pool>
                <security>
                    <user-name>postgres</user-name>
                    <password>password</password>
                </security>
            </datasource>
            <drivers>
                <driver name="postgresql" module="org.postgresql">
                    <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
                </driver>
            </drivers>
        </datasources>

zonaut · May 27, 2020, 9:50am

Maybe copy the full file to pastebin or something

pahade · May 27, 2020, 9:57am

I am not getting your point what we have to do…? If it’s possible to please share the database configuration on git.

zonaut · May 27, 2020, 10:00am

Maybe you share it on Github and we can take a look at it.
I use Docker for my setup, an example can be found on https://github.com/zonaut/keycloak-extensions/blob/master/_resources/demo-config/standalone-ha.xml

You could copy the <subsystem xmlns="urn:jboss:domain:datasources:5.0"> block and replace all variable placeholders or keep them as is and use environment variables

Also check if it’s needed to add the postgreSQL module, mine in the Docker container is located under /opt/jboss/keycloak/modules/system/layers/base/org/postgresql/jdbc/main and it’s already present

pahade · May 27, 2020, 11:44am

Hi Zonaut,

I have change as per given git repo but it’s not working please find the source github repo https://github.com/GaneshPahade/keyclock.git

zonaut · May 27, 2020, 1:05pm

I’m running Linux but I gave it a quick run and like I mentioned you should have looked at row 142 and played around with it a bit. There are some invisible chars in the file → Unicode Character 'ZERO WIDTH SPACE' (U+200B)
If you use Intellij you can take a look at https://plugins.jetbrains.com/plugin/7448-zero-width-characters-locator
In Vi this is visible by default

replace the whole block with the one I post here.

<subsystem xmlns="urn:jboss:domain:datasources:5.0">
    <datasources>
        <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true"
                    use-java-context="true"
                    statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
            <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
            <driver>h2</driver>
            <security>
                <user-name>sa</user-name>
                <password>sa</password>
            </security>
        </datasource>
        <datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
            <connection-url>jdbc:postgresql://localhost:5432/keycloak</connection-url>
            <driver>postgresql</driver>
            <pool>
                <max-pool-size>20</max-pool-size>
            </pool>
            <security>
                <user-name>postgres</user-name>
                <password>password</password>
            </security>
        </datasource>
        <drivers>
            <driver name="postgresql" module="org.postgresql">
                <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
            </driver>
        </drivers>
    </datasources>
</subsystem>

pahade · May 28, 2020, 4:46am

Hi Zonaut,

Finally postgres database is connected in local, Thanks for the support…Thank you very much.

bnrosa · October 9, 2020, 1:46pm

I don’t get what was the issue after all, is it just formatting? (I’m experiencing the same problem)

zonaut · October 10, 2020, 7:50am

Those <200b> characters in the file I’ve shown in the screenshots can’t be there. Keycloak will fail while parsing the configuration file because these are there. It’s like parsing malformed JSON but in this case it’s XML.

These characters can popup from copy pasting stuff from websites and so on. It’s better to use the plugin I’ve mentioned or use an editor than can view these characters when you experience similar problems.

The error itself says it all
17:07:28,891 ERROR [org.jboss.as.server] (Controller Boot Thread) WFLYSRV0055: Caught exception during boot: org.jboss.as.controller.persistence.ConfigurationPersistenceException: WFLYCTL0085: Failed to parse configuration
at org.jboss.as.controller.persistence.XmlConfigurationPersister.load(XmlConfigurationPersister.java:143)
at org.jboss.as.server.ServerService.boot(ServerService.java:392)
at org.jboss.as.controller.AbstractControllerService$1.run(AbstractControllerService.java:416)
at java.lang.Thread.run(Thread.java:748)
Caused by: javax.xml.stream.XMLStreamException: com.ctc.wstx.exc.WstxParsingException: Received non-all-whitespace CHARACTERS or CDATA event in nextTag().
at [row,col {unknown-source}]: [142,21]

bnrosa · October 10, 2020, 1:55pm

Thanks for explaining it, I was able to solve the issue.

Topic		Replies	Views
How to make changes in standalone.xml effective without restarting Keycloak server Configuring the server	1	882	July 23, 2022
Keycloak 13.0.1 with PostgreSQL 42.2.22 driver error Configuring the server	0	613	June 16, 2021
Keycloak X preview with PostgreSQL Database Configuring the server database	2	3432	December 7, 2021
Keycloak postgres is failing Configuring the server	0	1493	December 15, 2020
Configuring MySQL with KeyCloak Configuring the server	6	1171	March 12, 2021

Not able to change keycloak-10.0.1 databace configuration from H2 to Postgresql

Calling “D:\Users\itteam\Downloads\keycloak-10.0.1\bin\standalone.conf.bat” Setting JAVA property to “C:\Program Files\Java\jdk1.8.0_251\bin\java”

Related Topics

Calling “D:\Users\itteam\Downloads\keycloak-10.0.1\bin\standalone.conf.bat”
Setting JAVA property to “C:\Program Files\Java\jdk1.8.0_251\bin\java”