Examples with JdbcConnection io.debezium.jdbc.JdbcConnection used on opensource projects

Example 11 with JdbcConnection

use of io.debezium.jdbc.JdbcConnection in project debezium by debezium.

the class BinlogReaderIT method inconsistentSchema.

private void inconsistentSchema(EventProcessingFailureHandlingMode mode) throws InterruptedException, SQLException {
    if (mode == null) {
        config = simpleConfig().build();
    } else {
        config = simpleConfig().with(MySqlConnectorConfig.INCONSISTENT_SCHEMA_HANDLING_MODE, mode).build();
    }
    context = new MySqlTaskContext(config);
    context.start();
    // start from beginning
    context.source().setBinlogStartPoint("", 0L);
    context.initializeHistory();
    reader = new BinlogReader("binlog", context);
    // Start reading the binlog ...
    reader.start();
    // Poll for records ...
    // Testing.Print.enable();
    // only the inserts for our 4 tables in this database and 1 create table
    int expected = 9 + 9 + 4 + 5 + 1;
    int consumed = consumeAtLeast(expected);
    assertThat(consumed).isGreaterThanOrEqualTo(expected);
    reader.stop();
    reader.start();
    reader.context.dbSchema().applyDdl(context.source(), DATABASE.getDatabaseName(), "DROP TABLE customers", null);
    try (final MySQLConnection db = MySQLConnection.forTestDatabase(DATABASE.getDatabaseName());
        final JdbcConnection connection = db.connect();
        final Connection jdbc = connection.connection();
        final Statement statement = jdbc.createStatement()) {
        statement.executeUpdate("INSERT INTO customers VALUES (default,'John','Lazy','john.lazy@acme.com')");
    }
}

Example 12 with JdbcConnection

use of io.debezium.jdbc.JdbcConnection in project debezium by debezium.

the class MySqlConnectorIT method shouldEmitNoTombstoneOnDelete.

@Test
@FixFor("DBZ-582")
public void shouldEmitNoTombstoneOnDelete() throws Exception {
    config = DATABASE.defaultConfig().with(MySqlConnectorConfig.SNAPSHOT_MODE, MySqlConnectorConfig.SnapshotMode.NEVER).with(CommonConnectorConfig.TOMBSTONES_ON_DELETE, false).build();
    // Start the connector ...
    start(MySqlConnector.class, config);
    // ---------------------------------------------------------------------------------------------------------------
    // Consume all of the events due to startup and initialization of the database
    // ---------------------------------------------------------------------------------------------------------------
    // 6 DDL changes
    SourceRecords records = consumeRecordsByTopic(9 + 9 + 4 + 5 + 6);
    assertThat(records.recordsForTopic(DATABASE.topicForTable("orders")).size()).isEqualTo(5);
    try (MySQLConnection db = MySQLConnection.forTestDatabase(DATABASE.getDatabaseName())) {
        try (JdbcConnection connection = db.connect()) {
            connection.execute("UPDATE orders SET order_number=10101 WHERE order_number=10001");
        }
    }
    // Consume the update of the PK, which is one insert followed by a delete...
    records = consumeRecordsByTopic(2);
    List<SourceRecord> updates = records.recordsForTopic(DATABASE.topicForTable("orders"));
    assertThat(updates.size()).isEqualTo(2);
    assertDelete(updates.get(0), "order_number", 10001);
    assertInsert(updates.get(1), "order_number", 10101);
    try (MySQLConnection db = MySQLConnection.forTestDatabase(DATABASE.getDatabaseName())) {
        try (JdbcConnection connection = db.connect()) {
            connection.execute("DELETE FROM orders WHERE order_number = 10101;");
            connection.execute("DELETE FROM orders WHERE order_number = 10002;");
        }
    }
    records = consumeRecordsByTopic(2);
    updates = records.recordsForTopic(DATABASE.topicForTable("orders"));
    assertThat(updates.size()).isEqualTo(2);
    assertDelete(updates.get(0), "order_number", 10101);
    assertDelete(updates.get(1), "order_number", 10002);
    stopConnector();
}

Example 13 with JdbcConnection

use of io.debezium.jdbc.JdbcConnection in project debezium by debezium.

the class MySqlConnectorIT method shouldConsumeAllEventsFromDatabaseUsingSnapshot.

@Test
public void shouldConsumeAllEventsFromDatabaseUsingSnapshot() throws SQLException, InterruptedException {
    String masterPort = System.getProperty("database.port", "3306");
    String replicaPort = System.getProperty("database.replica.port", "3306");
    boolean replicaIsMaster = masterPort.equals(replicaPort);
    if (!replicaIsMaster) {
        // Give time for the replica to catch up to the master ...
        Thread.sleep(5000L);
    }
    // Use the DB configuration to define the connector's configuration to use the "replica"
    // which may be the same as the "master" ...
    config = Configuration.create().with(MySqlConnectorConfig.HOSTNAME, System.getProperty("database.replica.hostname", "localhost")).with(MySqlConnectorConfig.PORT, System.getProperty("database.replica.port", "3306")).with(MySqlConnectorConfig.USER, "snapper").with(MySqlConnectorConfig.PASSWORD, "snapperpass").with(MySqlConnectorConfig.SERVER_ID, 18765).with(MySqlConnectorConfig.SERVER_NAME, DATABASE.getServerName()).with(MySqlConnectorConfig.SSL_MODE, SecureConnectionMode.DISABLED).with(MySqlConnectorConfig.POLL_INTERVAL_MS, 10).with(MySqlConnectorConfig.DATABASE_WHITELIST, DATABASE.getDatabaseName()).with(MySqlConnectorConfig.DATABASE_HISTORY, FileDatabaseHistory.class).with(MySqlConnectorConfig.INCLUDE_SCHEMA_CHANGES, true).with(FileDatabaseHistory.FILE_PATH, DB_HISTORY_PATH).build();
    // Start the connector ...
    start(MySqlConnector.class, config);
    // Testing.Print.enable();
    // ---------------------------------------------------------------------------------------------------------------
    // Consume all of the events due to startup and initialization of the database
    // ---------------------------------------------------------------------------------------------------------------
    // 11 schema change records + 1 SET statement
    SourceRecords records = consumeRecordsByTopic(5 + 9 + 9 + 4 + 11 + 1 + 2);
    assertThat(records.recordsForTopic(DATABASE.getServerName()).size()).isEqualTo(12);
    assertThat(records.recordsForTopic(DATABASE.topicForTable("products")).size()).isEqualTo(9);
    assertThat(records.recordsForTopic(DATABASE.topicForTable("products_on_hand")).size()).isEqualTo(9);
    assertThat(records.recordsForTopic(DATABASE.topicForTable("customers")).size()).isEqualTo(4);
    assertThat(records.recordsForTopic(DATABASE.topicForTable("orders")).size()).isEqualTo(5);
    assertThat(records.topics().size()).isEqualTo(5);
    assertThat(records.databaseNames().size()).isEqualTo(2);
    assertThat(records.ddlRecordsForDatabase(DATABASE.getDatabaseName()).size()).isEqualTo(11);
    assertThat(records.ddlRecordsForDatabase("readbinlog_test")).isNull();
    assertThat(records.ddlRecordsForDatabase("").size()).isEqualTo(1);
    records.ddlRecordsForDatabase(DATABASE.getDatabaseName()).forEach(this::print);
    // Check that all records are valid, can be serialized and deserialized ...
    records.forEach(this::validate);
    // Check that the last record has snapshots disabled in the offset, but not in the source
    List<SourceRecord> allRecords = records.allRecordsInOrder();
    SourceRecord last = allRecords.get(allRecords.size() - 1);
    SourceRecord secondToLast = allRecords.get(allRecords.size() - 2);
    assertThat(secondToLast.sourceOffset().containsKey(SourceInfo.SNAPSHOT_KEY)).isTrue();
    // not snapshot
    assertThat(last.sourceOffset().containsKey(SourceInfo.SNAPSHOT_KEY)).isFalse();
    assertThat(((Struct) secondToLast.value()).getStruct(Envelope.FieldName.SOURCE).getBoolean(SourceInfo.SNAPSHOT_KEY)).isTrue();
    assertThat(((Struct) last.value()).getStruct(Envelope.FieldName.SOURCE).getBoolean(SourceInfo.SNAPSHOT_KEY)).isTrue();
    // ---------------------------------------------------------------------------------------------------------------
    // Stopping the connector does not lose events recorded when connector is not running
    // ---------------------------------------------------------------------------------------------------------------
    // Make sure there are no more events and then stop the connector ...
    waitForAvailableRecords(3, TimeUnit.SECONDS);
    int totalConsumed = consumeAvailableRecords(this::print);
    System.out.println("TOTAL CONSUMED = " + totalConsumed);
    // assertThat(totalConsumed).isEqualTo(0);
    stopConnector();
    // Make some changes to data only while the connector is stopped ...
    try (MySQLConnection db = MySQLConnection.forTestDatabase(DATABASE.getDatabaseName())) {
        try (JdbcConnection connection = db.connect()) {
            connection.query("SELECT * FROM products", rs -> {
                if (Testing.Print.isEnabled())
                    connection.print(rs);
            });
            connection.execute("INSERT INTO products VALUES (default,'robot','Toy robot',1.304);");
            connection.query("SELECT * FROM products", rs -> {
                if (Testing.Print.isEnabled())
                    connection.print(rs);
            });
        }
    }
    // Testing.Print.enable();
    // Restart the connector and read the insert record ...
    Testing.print("*** Restarting connector after inserts were made");
    start(MySqlConnector.class, config);
    records = consumeRecordsByTopic(1);
    assertThat(records.recordsForTopic(DATABASE.topicForTable("products")).size()).isEqualTo(1);
    assertThat(records.topics().size()).isEqualTo(1);
    List<SourceRecord> inserts = records.recordsForTopic(DATABASE.topicForTable("products"));
    assertInsert(inserts.get(0), "id", 110);
    Testing.print("*** Done with inserts and restart");
    Testing.print("*** Stopping connector");
    stopConnector();
    Testing.print("*** Restarting connector");
    start(MySqlConnector.class, config);
    // ---------------------------------------------------------------------------------------------------------------
    try (MySQLConnection db = MySQLConnection.forTestDatabase(DATABASE.getDatabaseName())) {
        try (JdbcConnection connection = db.connect()) {
            connection.execute("INSERT INTO products VALUES (1001,'roy','old robot',1234.56);");
            connection.query("SELECT * FROM products", rs -> {
                if (Testing.Print.isEnabled())
                    connection.print(rs);
            });
        }
    }
    // And consume the one insert ...
    records = consumeRecordsByTopic(1);
    assertThat(records.recordsForTopic(DATABASE.topicForTable("products")).size()).isEqualTo(1);
    assertThat(records.topics().size()).isEqualTo(1);
    inserts = records.recordsForTopic(DATABASE.topicForTable("products"));
    assertInsert(inserts.get(0), "id", 1001);
    // ---------------------------------------------------------------------------------------------------------------
    try (MySQLConnection db = MySQLConnection.forTestDatabase(DATABASE.getDatabaseName())) {
        try (JdbcConnection connection = db.connect()) {
            connection.execute("UPDATE products SET id=2001, description='really old robot' WHERE id=1001");
            connection.query("SELECT * FROM products", rs -> {
                if (Testing.Print.isEnabled())
                    connection.print(rs);
            });
        }
    }
    // And consume the update of the PK, which is one insert followed by a delete followed by a tombstone ...
    records = consumeRecordsByTopic(3);
    List<SourceRecord> updates = records.recordsForTopic(DATABASE.topicForTable("products"));
    assertThat(updates.size()).isEqualTo(3);
    assertDelete(updates.get(0), "id", 1001);
    assertTombstone(updates.get(1), "id", 1001);
    assertInsert(updates.get(2), "id", 2001);
    Testing.print("*** Done with PK change");
    // ---------------------------------------------------------------------------------------------------------------
    try (MySQLConnection db = MySQLConnection.forTestDatabase(DATABASE.getDatabaseName())) {
        try (JdbcConnection connection = db.connect()) {
            connection.execute("UPDATE products SET weight=1345.67 WHERE id=2001");
            connection.query("SELECT * FROM products", rs -> {
                if (Testing.Print.isEnabled())
                    connection.print(rs);
            });
        }
    }
    // And consume the one update ...
    records = consumeRecordsByTopic(1);
    assertThat(records.topics().size()).isEqualTo(1);
    updates = records.recordsForTopic(DATABASE.topicForTable("products"));
    assertThat(updates.size()).isEqualTo(1);
    assertUpdate(updates.get(0), "id", 2001);
    updates.forEach(this::validate);
    Testing.print("*** Done with simple update");
    // Add a column with default to the 'products' table and explicitly update one record ...
    try (MySQLConnection db = MySQLConnection.forTestDatabase(DATABASE.getDatabaseName())) {
        try (JdbcConnection connection = db.connect()) {
            connection.execute(String.format("ALTER TABLE %s.products ADD COLUMN volume FLOAT, ADD COLUMN alias VARCHAR(30) NULL AFTER description", DATABASE.getDatabaseName()));
            connection.execute("UPDATE products SET volume=13.5 WHERE id=2001");
            connection.query("SELECT * FROM products", rs -> {
                if (Testing.Print.isEnabled())
                    connection.print(rs);
            });
        }
    }
    // And consume the one schema change event and one update event ...
    records = consumeRecordsByTopic(2);
    assertThat(records.topics().size()).isEqualTo(2);
    assertThat(records.recordsForTopic(DATABASE.getServerName()).size()).isEqualTo(1);
    updates = records.recordsForTopic(DATABASE.topicForTable("products"));
    assertThat(updates.size()).isEqualTo(1);
    assertUpdate(updates.get(0), "id", 2001);
    updates.forEach(this::validate);
    Testing.print("*** Done with schema change (same db and fully-qualified name)");
    // Connect to a different database, but use the fully qualified name for a table in our database ...
    try (MySQLConnection db = MySQLConnection.forTestDatabase("emptydb")) {
        try (JdbcConnection connection = db.connect()) {
            connection.execute(String.format("CREATE TABLE %s.stores (" + " id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT," + " first_name VARCHAR(255) NOT NULL," + " last_name VARCHAR(255) NOT NULL," + " email VARCHAR(255) NOT NULL );", DATABASE.getDatabaseName()));
        }
    }
    // And consume the one schema change event only ...
    records = consumeRecordsByTopic(1);
    assertThat(records.topics().size()).isEqualTo(1);
    assertThat(records.recordsForTopic(DATABASE.getServerName()).size()).isEqualTo(1);
    records.recordsForTopic(DATABASE.getServerName()).forEach(this::validate);
    Testing.print("*** Done with PK change (different db and fully-qualified name)");
    // Do something completely different with a table we've not modified yet and then read that event.
    try (MySQLConnection db = MySQLConnection.forTestDatabase(DATABASE.getDatabaseName())) {
        try (JdbcConnection connection = db.connect()) {
            connection.execute("UPDATE products_on_hand SET quantity=20 WHERE product_id=109");
            connection.query("SELECT * FROM products_on_hand", rs -> {
                if (Testing.Print.isEnabled())
                    connection.print(rs);
            });
        }
    }
    // And make sure we consume that one update ...
    records = consumeRecordsByTopic(1);
    assertThat(records.topics().size()).isEqualTo(1);
    updates = records.recordsForTopic(DATABASE.topicForTable("products_on_hand"));
    assertThat(updates.size()).isEqualTo(1);
    assertUpdate(updates.get(0), "product_id", 109);
    updates.forEach(this::validate);
    Testing.print("*** Done with verifying no additional events");
    // ---------------------------------------------------------------------------------------------------------------
    // Stop the connector ...
    // ---------------------------------------------------------------------------------------------------------------
    stopConnector();
    // ---------------------------------------------------------------------------------------------------------------
    // Restart the connector to read only part of a transaction ...
    // ---------------------------------------------------------------------------------------------------------------
    Testing.print("*** Restarting connector");
    CompletionResult completion = new CompletionResult();
    start(MySqlConnector.class, config, completion, (record) -> {
        // We want to stop before processing record 3003 ...
        Struct key = (Struct) record.key();
        Number id = (Number) key.get("id");
        if (id.intValue() == 3003) {
            return true;
        }
        return false;
    });
    BinlogPosition positionBeforeInserts = new BinlogPosition();
    BinlogPosition positionAfterInserts = new BinlogPosition();
    BinlogPosition positionAfterUpdate = new BinlogPosition();
    try (MySQLConnection db = MySQLConnection.forTestDatabase(DATABASE.getDatabaseName())) {
        try (JdbcConnection connection = db.connect()) {
            connection.query("SHOW MASTER STATUS", positionBeforeInserts::readFromDatabase);
            connection.execute("INSERT INTO products(id,name,description,weight,volume,alias) VALUES " + "(3001,'ashley','super robot',34.56,0.00,'ashbot'), " + "(3002,'arthur','motorcycle',87.65,0.00,'arcycle'), " + "(3003,'oak','tree',987.65,0.00,'oak');");
            connection.query("SELECT * FROM products", rs -> {
                if (Testing.Print.isEnabled())
                    connection.print(rs);
            });
            connection.query("SHOW MASTER STATUS", positionAfterInserts::readFromDatabase);
            // Change something else that is unrelated ...
            connection.execute("UPDATE products_on_hand SET quantity=40 WHERE product_id=109");
            connection.query("SELECT * FROM products_on_hand", rs -> {
                if (Testing.Print.isEnabled())
                    connection.print(rs);
            });
            connection.query("SHOW MASTER STATUS", positionAfterUpdate::readFromDatabase);
        }
    }
    // Testing.Print.enable();
    // And consume the one insert ...
    records = consumeRecordsByTopic(2);
    assertThat(records.recordsForTopic(DATABASE.topicForTable("products")).size()).isEqualTo(2);
    assertThat(records.topics().size()).isEqualTo(1);
    inserts = records.recordsForTopic(DATABASE.topicForTable("products"));
    assertInsert(inserts.get(0), "id", 3001);
    assertInsert(inserts.get(1), "id", 3002);
    // Verify that the connector has stopped ...
    completion.await(10, TimeUnit.SECONDS);
    assertThat(completion.hasCompleted()).isTrue();
    assertThat(completion.hasError()).isTrue();
    assertThat(completion.success()).isFalse();
    assertNoRecordsToConsume();
    assertConnectorNotRunning();
    // ---------------------------------------------------------------------------------------------------------------
    // Stop the connector ...
    // ---------------------------------------------------------------------------------------------------------------
    stopConnector();
    // Read the last committed offsets, and verify the binlog coordinates ...
    SourceInfo persistedOffsetSource = new SourceInfo();
    persistedOffsetSource.setServerName(config.getString(MySqlConnectorConfig.SERVER_NAME));
    Map<String, ?> lastCommittedOffset = readLastCommittedOffset(config, persistedOffsetSource.partition());
    persistedOffsetSource.setOffset(lastCommittedOffset);
    Testing.print("Position before inserts: " + positionBeforeInserts);
    Testing.print("Position after inserts:  " + positionAfterInserts);
    Testing.print("Offset: " + lastCommittedOffset);
    Testing.print("Position after update:  " + positionAfterUpdate);
    if (replicaIsMaster) {
        // Same binlog filename ...
        assertThat(persistedOffsetSource.binlogFilename()).isEqualTo(positionBeforeInserts.binlogFilename());
        assertThat(persistedOffsetSource.binlogFilename()).isEqualTo(positionAfterInserts.binlogFilename());
        // Binlog position in offset should be more than before the inserts, but less than the position after the inserts ...
        assertThat(persistedOffsetSource.binlogPosition()).isGreaterThan(positionBeforeInserts.binlogPosition());
        assertThat(persistedOffsetSource.binlogPosition()).isLessThan(positionAfterInserts.binlogPosition());
    } else {
    // the replica is not the same server as the master, so it will have a different binlog filename and position ...
    }
    // Event number is 2 ...
    assertThat(persistedOffsetSource.eventsToSkipUponRestart()).isEqualTo(2);
    // GTID set should match the before-inserts GTID set ...
    // assertThat(persistedOffsetSource.gtidSet()).isEqualTo(positionBeforeInserts.gtidSet());
    Testing.print("*** Restarting connector, and should begin with inserting 3003 (not 109!)");
    start(MySqlConnector.class, config);
    // And consume the insert for 3003 ...
    records = consumeRecordsByTopic(1);
    assertThat(records.topics().size()).isEqualTo(1);
    inserts = records.recordsForTopic(DATABASE.topicForTable("products"));
    if (inserts == null) {
        updates = records.recordsForTopic(DATABASE.topicForTable("products_on_hand"));
        if (updates != null) {
            fail("Restarted connector and missed the insert of product id=3003!");
        }
    }
    // Read the first record produced since we've restarted
    SourceRecord prod3003 = inserts.get(0);
    assertInsert(prod3003, "id", 3003);
    // Check that the offset has the correct/expected values ...
    assertOffset(prod3003, "file", lastCommittedOffset.get("file"));
    assertOffset(prod3003, "pos", lastCommittedOffset.get("pos"));
    assertOffset(prod3003, "row", 3);
    assertOffset(prod3003, "event", lastCommittedOffset.get("event"));
    // Check that the record has all of the column values ...
    assertValueField(prod3003, "after/id", 3003);
    assertValueField(prod3003, "after/name", "oak");
    assertValueField(prod3003, "after/description", "tree");
    assertValueField(prod3003, "after/weight", 987.65d);
    assertValueField(prod3003, "after/volume", 0.0d);
    assertValueField(prod3003, "after/alias", "oak");
    // And make sure we consume that one extra update ...
    records = consumeRecordsByTopic(1);
    assertThat(records.topics().size()).isEqualTo(1);
    updates = records.recordsForTopic(DATABASE.topicForTable("products_on_hand"));
    assertThat(updates.size()).isEqualTo(1);
    assertUpdate(updates.get(0), "product_id", 109);
    updates.forEach(this::validate);
    // Start the connector again, and we should see the next two
    Testing.print("*** Done with simple insert");
}