[ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

Archibald
Hi all,

2.4.0 introduced a new table NODE_MANAGER_STORE. This table is being created
on startup if it doesn't exist.
But if it does exist but is empty server fails to start (seen on a oracle
database) with

08:43:20,510 INFO  [org.apache.activemq.artemis.integration.bootstrap]
AMQ101000: Starting ActiveMQ Artemis Server
activemq_1  | can't write NODE_ID on the JDBC Node Manager Store!

The issue is that the database user it is not always allowed (has the proper
grants) to create/modify a schema.

Can this be fixed?

Thanks,
Archibald



--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
Reply | Threaded
Open this post in threaded view
|

Re: [ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

nigro_franz
Hi!

AFAIK that table is part of the effort to provide HA Shared Store feature
when a JDBC Journal is configured, hence I suppose that the same issue
would be likely to happen
for the other JDBC Journal tables as well.

The solutions I see are:
1 - have the user granted to create/modify schema in order to have the
whole JDBC features working properly
2 - manually create the missing table(s) (it is not the best EVER solution
but it solves the issue)

IMO having proper granted users would be the best one.

I hope this could help :)

Cheers,
Franz


Il giorno ven 26 gen 2018 alle ore 09:52 Archibald <[hidden email]> ha
scritto:

> Hi all,
>
> 2.4.0 introduced a new table NODE_MANAGER_STORE. This table is being
> created
> on startup if it doesn't exist.
> But if it does exist but is empty server fails to start (seen on a oracle
> database) with
>
> 08:43:20,510 INFO  [org.apache.activemq.artemis.integration.bootstrap]
> AMQ101000: Starting ActiveMQ Artemis Server
> activemq_1  | can't write NODE_ID on the JDBC Node Manager Store!
>
> The issue is that the database user it is not always allowed (has the
> proper
> grants) to create/modify a schema.
>
> Can this be fixed?
>
> Thanks,
> Archibald
>
>
>
> --
> Sent from:
> http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
>
Reply | Threaded
Open this post in threaded view
|

Re: [ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

Archibald
Thanks Franz,

the other tables do work pretty well if they already exist but are empty. It
is just the NODE_MANAGE_STORE which somehow "picky"...

Br,
Archibald



--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
Reply | Threaded
Open this post in threaded view
|

Re: [ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

nigro_franz
I understand; to help i need a couple of other things too:

1) which DB vendor has been used
2) a more verbose log of the error, if possible
3) have you tried the last master too?

All these 3 questions obviously will fall if you could raise the privileges
of the DB user :)

Cheers,
Franz




Il giorno ven 26 gen 2018 alle ore 11:41 Archibald <[hidden email]> ha
scritto:

> Thanks Franz,
>
> the other tables do work pretty well if they already exist but are empty.
> It
> is just the NODE_MANAGE_STORE which somehow "picky"...
>
> Br,
> Archibald
>
>
>
> --
> Sent from:
> http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
>
Reply | Threaded
Open this post in threaded view
|

Re: [ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

Archibald
Hi Franz,

to your questions:
1) Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit
Production
2) not more is being written to the log (not even in debug)
3) no I haven't tried the latest master. But since I do not find a related
issue in JIRA I assume that it still exist.

BTW: I currenty own the required grants since oracle is running on my own
laptop. But I know that this will cause trouble when installing Artemis in
our production environment.

HTH,
Archibald



--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
Reply | Threaded
Open this post in threaded view
|

Re: [ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

Archibald
And I can see that the broker is holding a lock on that table. So it tries to
insert something which fails but does not properly rollback...



--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
Reply | Threaded
Open this post in threaded view
|

Re: [ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

nigro_franz
> BTW: I currenty own the required grants since oracle is running on my own
laptop. But I know that this will cause trouble when installing Artemis in
our production environment.

Probably it could be a latency issue too: there are several configuration
properties
<https://activemq.apache.org/artemis/docs/latest/persistence.html> that
could be tuned to allow working with high latency connection (but it is not
reccomended TBH):

   - *jdbc-lock-acquisition-timeout* The max allowed time in milliseconds
   while trying to acquire a JDBC lock. The default value is 60000
   milliseconds (ie 60 seconds).
   - *jdbc-lock-renew-period* The period in milliseconds of the keep alive
   service of a JDBC lock. The default value is 2000 milliseconds (ie 2
   seconds).
   - *jdbc-lock-expiration* The time in milliseconds a JDBC lock is
   considered valid without keeping it alive. The default value is 20000
   milliseconds (ie 20 seconds).

*jdbc-lock-acquisition-timeout *could be turned to -1 ie infinite and the
others could be raised to appropiate values considering the DBMS latencies
(eg I would say renew to 10 seconds and expiration to 60 seconds).

> 3) no I haven't tried the latest master. But since I do not find a related
issue in JIRA I assume that it still exist.

Some time ago I've opened (and it is already merged on master) Make the
JDBC Node Manager more resilient on failures
<https://issues.apache.org/jira/browse/ARTEMIS-1541> to address cases like
this, hopefully it will solve it.

> And I can see that the broker is holding a lock on that table. So it
tries to
insert something which fails but does not properly rollback...

That's strange but not impossible: there is a rare case of a leaking and
unkilleable Oracle driver thread called:
oracle.jdbc.driver.BlockSource.ThreadedCachingBlockSource.BlockReleaser
<https://support.oracle.com/knowledge/Middleware/2241504_1.html>.
It could be the one actually locking that table.

Cheers,
Franz



Il giorno ven 26 gen 2018 alle ore 14:44 Archibald <[hidden email]> ha
scritto:

> And I can see that the broker is holding a lock on that table. So it tries
> to
> insert something which fails but does not properly rollback...
>
>
>
> --
> Sent from:
> http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
>
Reply | Threaded
Open this post in threaded view
|

Re: [ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

Archibald
This post was updated on .
Thanks Franz,

this sounds all supi-dupi interesting but has nothing to with the issue ;)
It is as simple as I noticed.
From the code:

JdbcSharedStateManager.java:
protected void createSchema() throws SQLException {
      try {
         createTable(sqlProvider.createNodeManagerStoreTableSQL(),
sqlProvider.createNodeIdSQL(), sqlProvider.createStateSQL(),
sqlProvider.createLiveLockSQL(), sqlProvider.createBackupLockSQL());
      } catch (SQLException e) {
         //no op: if a table already exists is not a problem in this case,
the prepareStatements() call will fail right after it if the table is not
correctly initialized
         if (logger.isDebugEnabled()) {
            logger.debug("Error while creating the schema of the JDBC shared
state manager", e);
         }
      }
   }

Obviously here the table is being created along with some other initial data
import.

Then later in AbstractJDBCDriver.java:

private static void createTableIfNotExists(Connection connection,
                                              String tableName,
                                              String... sqls) throws
SQLException {
      logger.tracef("Validating if table %s didn't exist before creating",
tableName);
      try {
         connection.setAutoCommit(false);
         try (ResultSet rs = connection.getMetaData().getTables(null, null,
tableName, null)) {
            if (rs != null && !rs.next()) {
               if (logger.isTraceEnabled()) {
                  logger.tracef("Table %s did not exist, creating it with
SQL=%s", tableName, Arrays.toString(sqls));
               }
               final SQLWarning sqlWarning = rs.getWarnings();
               if (sqlWarning != null) {
                  logger.warn(JDBCUtils.appendSQLExceptionDetails(new
StringBuilder(), sqlWarning));
               }
               try (Statement statement = connection.createStatement()) {
                  for (String sql : sqls) {
                     statement.executeUpdate(sql);
                     final SQLWarning statementSqlWarning =
statement.getWarnings();
                     if (statementSqlWarning != null) {
                        logger.warn(JDBCUtils.appendSQLExceptionDetails(new
StringBuilder(), statementSqlWarning, sql));
                     }
                  }
               }
            }
         }
         connection.commit();
         ...

So if table exists, don't do anything. If it doesn't exist, fill with
initial data.

This implementation could be improved by the case where the table exists,
but is empty which should lead to the same initial setup as the case where
the table does not exist.

Can this be fixed?

Thanks,
Archibald



--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
Reply | Threaded
Open this post in threaded view
|

Re: [ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

nigro_franz
Great, I've missed the most obvious solution, well done!
It can be fixed and it would be awesome a test with a PR too :)

Thanks,
Franz



--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
Reply | Threaded
Open this post in threaded view
|

Re: [ARTEMIS] Server doesn't start if JDBC store is used and table NODE_MANAGER_STORE is empty

Archibald
Thanks Franz,

See my changes in PR #1822.

Br, A.



--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html