You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@activemq.apache.org by franz1981 <gi...@git.apache.org> on 2018/04/06 14:28:15 UTC

[GitHub] activemq-artemis pull request #1997: ARTEMIS-1653 Allow database tables to b...

GitHub user franz1981 opened a pull request:

    https://github.com/apache/activemq-artemis/pull/1997

    ARTEMIS-1653 Allow database tables to be created externally

    The previous commit about this feature wasn't using the row count query
    ResultSet.
    The mechanics has been changed to allow the row count query
    to fail, because DROP and CREATE aren't transactional and immediate
    in most DBMS.
    It includes a test that stress its mechanics if used with DBMS like
    DB2 10.5 and Oracle 12c.
    Additional checks and logs have been added to trace each steps.

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/franz1981/activemq-artemis ARTEMIS-1653

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/activemq-artemis/pull/1997.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #1997
    
----
commit 1e2b5270854ba19a87a3ba0fa308751ce9cc5122
Author: Francesco Nigro <ni...@...>
Date:   2018-04-03T08:11:04Z

    ARTEMIS-1653 Allow database tables to be created externally
    
    The previous commit about this feature wasn't using the row count query
    ResultSet.
    The mechanics has been changed to allow the row count query
    to fail, because DROP and CREATE aren't transactional and immediate
    in most DBMS.
    It includes a test that stress its mechanics if used with DBMS like
    DB2 10.5 and Oracle 12c.
    Additional checks and logs have been added to trace each steps.

----


---

[GitHub] activemq-artemis pull request #1997: ARTEMIS-1653 Allow database tables to b...

Posted by franz1981 <gi...@git.apache.org>.
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 :(


---

[GitHub] activemq-artemis pull request #1997: ARTEMIS-1653 Allow database tables to b...

Posted by nlippke <gi...@git.apache.org>.
Github user nlippke commented on a diff in the pull request:

    https://github.com/apache/activemq-artemis/pull/1997#discussion_r180005903
  
    --- 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())) {
    --- End diff --
    
    `connection.getMetaData().getTables()` can return _null_?


---

[GitHub] activemq-artemis pull request #1997: ARTEMIS-1653 Allow database tables to b...

Posted by nlippke <gi...@git.apache.org>.
Github user nlippke commented on a diff in the pull request:

    https://github.com/apache/activemq-artemis/pull/1997#discussion_r180006216
  
    --- 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 --
    
    Isn't `connetion` already configured as such?


---

[GitHub] activemq-artemis issue #1997: ARTEMIS-1653 Allow database tables to be creat...

Posted by nlippke <gi...@git.apache.org>.
Github user nlippke commented on the issue:

    https://github.com/apache/activemq-artemis/pull/1997
  
    @franz1981 I totally agree. Broker should not run any DDL-Statements if tables are managed by a DBA.


---

[GitHub] activemq-artemis pull request #1997: ARTEMIS-1653 Allow database tables to b...

Posted by franz1981 <gi...@git.apache.org>.
Github user franz1981 commented on a diff in the pull request:

    https://github.com/apache/activemq-artemis/pull/1997#discussion_r180007558
  
    --- 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())) {
    --- End diff --
    
    Nice feature on some DB2 drivers :)


---

[GitHub] activemq-artemis issue #1997: ARTEMIS-1653 Allow database tables to be creat...

Posted by franz1981 <gi...@git.apache.org>.
Github user franz1981 commented on the issue:

    https://github.com/apache/activemq-artemis/pull/1997
  
    @clebertsuconic @nlippke I know that the failing COUNT sounds weird, but it is quite simple to be reproduced with DB2 through the test attached :+1: 


---

[GitHub] activemq-artemis issue #1997: ARTEMIS-1653 Allow database tables to be creat...

Posted by franz1981 <gi...@git.apache.org>.
Github user franz1981 commented on the issue:

    https://github.com/apache/activemq-artemis/pull/1997
  
    @nlippke @mtaylor I have updated this PR to avoid issues with concurrent creations of tables/indexes for the JDBC Journal with Oracle (there is a test attached): when a table seems to exists both CREATE TABLE and CREATE INDEX statements are no longer used to initialize it.
    That is useful to allow a shared store to concurrently start 2 journals on the same database and while rebooting the broker with an empty journal (table already existent, but empty ie no need to create index twice).
    That means that a DBA should take care to create table and indexes (if any), but if it will just create tables, the broker won't create any index on them, wdyt?


---

[GitHub] activemq-artemis pull request #1997: ARTEMIS-1653 Allow database tables to b...

Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:

    https://github.com/apache/activemq-artemis/pull/1997


---

[GitHub] activemq-artemis issue #1997: ARTEMIS-1653 Allow database tables to be creat...

Posted by nlippke <gi...@git.apache.org>.
Github user nlippke commented on the issue:

    https://github.com/apache/activemq-artemis/pull/1997
  
    👍 


---

[GitHub] activemq-artemis issue #1997: ARTEMIS-1653 Allow database tables to be creat...

Posted by mtaylor <gi...@git.apache.org>.
Github user mtaylor commented on the issue:

    https://github.com/apache/activemq-artemis/pull/1997
  
    +1 Franz.
    
    On Tue, 17 Apr 2018, 07:27 nlippke, <no...@github.com> wrote:
    
    > @franz1981 <https://github.com/franz1981> I totally agree. Broker should
    > not run any DDL-Statements if tables are managed by a DBA.
    >
    > —
    > You are receiving this because you were mentioned.
    > Reply to this email directly, view it on GitHub
    > <https://github.com/apache/activemq-artemis/pull/1997#issuecomment-381861933>,
    > or mute the thread
    > <https://github.com/notifications/unsubscribe-auth/AAOCy20Z6HWeWC2cCB2yTAh1OstwXlDeks5tpYtjgaJpZM4TKJau>
    > .
    >



---