[ https://issues.apache.org/jira/browse/ARTEMIS-1653?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16430196#comment-16430196 ]
ASF GitHub Bot commented on ARTEMIS-1653: ----------------------------------------- Github user franz1981 commented on a diff in the pull request: https://github.com/apache/activemq-artemis/pull/1997#discussion_r180007702 --- Diff: artemis-jdbc-store/src/main/java/org/apache/activemq/artemis/jdbc/store/drivers/AbstractJDBCDriver.java --- @@ -179,32 +179,61 @@ private void createTableIfNotExists(String tableName, String... sqls) throws SQL logger.tracef("Validating if table %s didn't exist before creating", tableName); try { connection.setAutoCommit(false); + final boolean tableExists; try (ResultSet rs = connection.getMetaData().getTables(null, null, tableName, null)) { - if (rs != null && !rs.next()) { + if ((rs == null) || (rs != null && !rs.next())) { + tableExists = false; 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)); + if (rs != null) { + final SQLWarning sqlWarning = rs.getWarnings(); + if (sqlWarning != null) { + logger.warn(JDBCUtils.appendSQLExceptionDetails(new StringBuilder(), sqlWarning)); + } } } else { - try (Statement statement = connection.createStatement(); - ResultSet cntRs = statement.executeQuery(sqlProvider.getCountJournalRecordsSQL())) { - if (rs.next() && rs.getInt(1) > 0) { - logger.tracef("Table %s did exist but is not empty. Skipping initialization.", tableName); - } else { - sqls = Arrays.copyOfRange(sqls, 1, sqls.length); + tableExists = true; + } + } + if (tableExists) { + logger.tracef("Validating if the existing table %s is initialized or not", tableName); + try (Statement statement = connection.createStatement(); + ResultSet cntRs = statement.executeQuery(sqlProvider.getCountJournalRecordsSQL())) { + logger.tracef("Validation of the existing table %s initialization is started", tableName); + int rows; + if (cntRs.next() && (rows = cntRs.getInt(1)) > 0) { + logger.tracef("Table %s did exist but is not empty. Skipping initialization. Found %d rows.", tableName, rows); + if (logger.isDebugEnabled()) { + final long expectedRows = Stream.of(sqls).map(String::toUpperCase).filter(sql -> sql.contains("INSERT INTO")).count(); + if (rows < expectedRows) { + logger.debug("Table " + tableName + " was expected to contain " + expectedRows + " rows while it has " + rows + " rows."); + } } + connection.commit(); + return; + } else { + assert sqls[0].toUpperCase().contains("CREATE TABLE") : "The first SQL statement must be a CREATE TABLE"; + sqls = Arrays.copyOfRange(sqls, 1, sqls.length); + logger.tracef("Table %s did exist but is empty. Starting initialization.", tableName); + } + } catch (SQLException e) { + logger.warn(JDBCUtils.appendSQLExceptionDetails(new StringBuilder("Can't verify the initialization of table ").append(tableName).append(" due to:"), e, sqlProvider.getCountJournalRecordsSQL())); + try { + connection.rollback(); + } catch (SQLException rollbackEx) { + logger.debug("Rollback failed while validating initialization of a table", rollbackEx); } + connection.setAutoCommit(false); --- End diff -- it is driver dependents what to do after a rollback :( > Allow database tables to be created externally > ---------------------------------------------- > > Key: ARTEMIS-1653 > URL: https://issues.apache.org/jira/browse/ARTEMIS-1653 > Project: ActiveMQ Artemis > Issue Type: Improvement > Components: Broker > Affects Versions: 2.4.0 > Reporter: Niels Lippke > Assignee: Francesco Nigro > Priority: Major > Fix For: 2.5.1 > > > In some environments (e.g. production) it is not allowed that applications > create their own schema. It's common practice to pass DDL-Statetements to > DBAs instead prior to rollout. > Currently the broker does not support this scenario. If the required tables > already exist the broker fails to start. > A better approach is that if the broker detects empy tables and initializes > them in the very same way it does if the tables dont't exist. > See also discussion in > [forum|http://activemq.2283324.n4.nabble.com/ARTEMIS-Server-doesn-t-start-if-JDBC-store-is-used-and-table-NODE-MANAGER-STORE-is-empty-td4735779.html]. -- This message was sent by Atlassian JIRA (v7.6.3#76005)