package com.zendesk.maxwell; import static org.hamcrest.CoreMatchers.*; import static org.junit.Assert.assertThat; import static org.junit.Assert.assertEquals; import java.io.IOException; import java.nio.charset.Charset; import java.sql.SQLException; import java.sql.Connection; import java.util.List; import java.util.ArrayList; import java.util.Arrays; import com.zendesk.maxwell.replication.BinlogPosition; import com.zendesk.maxwell.replication.Position; import org.apache.commons.lang3.StringUtils; import org.junit.Before; import org.junit.Test; import com.zendesk.maxwell.schema.*; import com.zendesk.maxwell.schema.ddl.InvalidSchemaError; import com.zendesk.maxwell.schema.columndef.IntColumnDef; import com.zendesk.maxwell.schema.columndef.ColumnDef; import com.zendesk.maxwell.schema.columndef.DateTimeColumnDef; import com.zendesk.maxwell.schema.columndef.TimeColumnDef; public class MysqlSavedSchemaTest extends MaxwellTestWithIsolatedServer { private Schema schema; private Position position; private MysqlSavedSchema savedSchema; private CaseSensitivity caseSensitivity = CaseSensitivity.CASE_SENSITIVE; String ary[] = { "delete from `maxwell`.`positions`", "delete from `maxwell`.`schemas`", "CREATE TABLE shard_1.latin1 (id int(11), str1 varchar(255), str2 varchar(255) character set 'utf8') charset = 'latin1'", "CREATE TABLE shard_1.enums (id int(11), enum_col enum('foo', 'bar', 'baz'))", "CREATE TABLE shard_1.pks (id int(11), col2 varchar(255), col3 datetime, PRIMARY KEY(col2, col3, id))", "CREATE TABLE shard_1.pks_case (id int(11), Col2 varchar(255), COL3 datetime, PRIMARY KEY(col2, col3))", "CREATE TABLE shard_1.signed (badcol int(10) unsigned, CaseCol char)" }; ArrayList<String> schemaSQL = new ArrayList(Arrays.asList(ary)); private MaxwellContext context; @Before public void setUp() throws Exception { if ( server.getVersion().equals("5.6") ) { schemaSQL.add("CREATE TABLE shard_1.time_with_length (id int (11), dt2 datetime(3), ts2 timestamp(6), t2 time(6))"); schemaSQL.add("CREATE TABLE shard_1.without_col_length (badcol datetime(3))"); } server.executeList(schemaSQL); this.position = MaxwellTestSupport.capture(server.getConnection()); this.context = buildContext(position); this.schema = new SchemaCapturer(server.getConnection(), context.getCaseSensitivity()).capture(); this.savedSchema = new MysqlSavedSchema(this.context, this.schema, position); } @Test public void testSave() throws SQLException, IOException, InvalidSchemaError { this.savedSchema.save(context.getMaxwellConnection()); MysqlSavedSchema restoredSchema = MysqlSavedSchema.restore(context, context.getInitialPosition()); List<String> diff = this.schema.diff(restoredSchema.getSchema(), "captured schema", "restored schema"); assertThat(StringUtils.join(diff, "\n"), diff.size(), is(0)); } @Test public void testRestorePK() throws Exception { this.savedSchema.save(context.getMaxwellConnection()); MysqlSavedSchema restoredSchema = MysqlSavedSchema.restore(context, context.getInitialPosition()); Table t = restoredSchema.getSchema().findDatabase("shard_1").findTable("pks"); assertThat(t.getPKList(), is(not(nullValue()))); assertThat(t.getPKList().size(), is(3)); assertThat(t.getPKList().get(0), is("col2")); assertThat(t.getPKList().get(1), is("col3")); assertThat(t.getPKList().get(2), is("id")); } @Test public void testPKCase() throws Exception { this.savedSchema.save(context.getMaxwellConnection()); MysqlSavedSchema restoredSchema = MysqlSavedSchema.restore(context, context.getInitialPosition()); Table t = restoredSchema.getSchema().findDatabase("shard_1").findTable("pks_case"); assertThat(t.getPKList().get(0), is("Col2")); assertThat(t.getPKList().get(1), is("COL3")); } @Test public void testTimeWithLengthCase() throws Exception { if ( !server.getVersion().equals("5.6") ) return; this.savedSchema.save(context.getMaxwellConnection()); MysqlSavedSchema restored = MysqlSavedSchema.restore(context, context.getInitialPosition()); DateTimeColumnDef cd = (DateTimeColumnDef) restored.getSchema().findDatabase("shard_1").findTable("time_with_length").findColumn("dt2"); assertThat(cd.getColumnLength(), is(3L)); cd = (DateTimeColumnDef) restored.getSchema().findDatabase("shard_1").findTable("time_with_length").findColumn("ts2"); assertThat(cd.getColumnLength(), is(6L)); TimeColumnDef cd2 = (TimeColumnDef) restored.getSchema().findDatabase("shard_1").findTable("time_with_length").findColumn("t2"); assertThat(cd2.getColumnLength(), is(6L)); } @Test public void testFixUnsignedColumnBug() throws Exception { Connection c = context.getMaxwellConnection(); this.savedSchema.save(c); c.createStatement().executeUpdate("update maxwell.schemas set version = 0 where id = " + this.savedSchema.getSchemaID()); c.createStatement().executeUpdate("update maxwell.columns set is_signed = 1 where name = 'badcol'"); MysqlSavedSchema restored = MysqlSavedSchema.restore(context, context.getInitialPosition()); IntColumnDef cd = (IntColumnDef) restored.getSchema().findDatabase("shard_1").findTable("signed").findColumn("badcol"); assertThat(cd.isSigned(), is(false)); } @Test public void testFixColumnCasingOnUpgrade() throws Exception { Connection c = context.getMaxwellConnection(); this.savedSchema.save(c); c.createStatement().executeUpdate("update maxwell.schemas set version = 1 where id = " + this.savedSchema.getSchemaID()); c.createStatement().executeUpdate("update maxwell.columns set name = 'casecol' where name = 'CaseCol'"); MysqlSavedSchema restored = MysqlSavedSchema.restore(context, context.getInitialPosition()); ColumnDef cd = restored.getSchema().findDatabase("shard_1").findTable("signed").findColumn("casecol"); assertThat(cd.getName(), is("CaseCol")); } @Test public void testUpgradeSchemaStore() throws Exception { Connection c = context.getMaxwellConnection(); c.createStatement().executeUpdate("alter table `maxwell`.`schemas` drop column deleted, " + "drop column base_schema_id, drop column deltas, drop column version, drop column position_sha"); c.createStatement().executeUpdate("alter table maxwell.positions drop column client_id"); c.createStatement().executeUpdate("alter table maxwell.positions drop column gtid_set"); c.createStatement().executeUpdate("alter table maxwell.schemas drop column gtid_set"); SchemaStoreSchema.upgradeSchemaStoreSchema(c); // just verify no-crash. } @Test public void testUpgradeAddColumnLength() throws Exception { if ( !server.getVersion().equals("5.6") ) return; Connection c = context.getMaxwellConnection(); this.savedSchema.save(c); c.createStatement().executeUpdate("alter table `maxwell`.`columns` drop column column_length"); SchemaStoreSchema.upgradeSchemaStoreSchema(c); // just verify no-crash. Schema schemaBefore = MysqlSavedSchema.restoreFromSchemaID(this.savedSchema, context).getSchema(); DateTimeColumnDef cd1 = (DateTimeColumnDef) schemaBefore.findDatabase("shard_1").findTable("without_col_length").findColumn("badcol"); assertEquals((Long) 0L, (Long) cd1.getColumnLength()); } @Test public void testUpgradeAddColumnLengthForExistingSchemas() throws Exception { if ( !server.getVersion().equals("5.6") ) return; Connection c = context.getMaxwellConnection(); this.savedSchema.save(c); c.createStatement().executeUpdate("update maxwell.schemas set version = 2 where id = " + this.savedSchema.getSchemaID()); c.createStatement().executeUpdate("update maxwell.columns set column_length = NULL where name = 'badcol'"); SchemaStoreSchema.upgradeSchemaStoreSchema(c); Schema schemaBefore = MysqlSavedSchema.restoreFromSchemaID(savedSchema, context).getSchema(); DateTimeColumnDef cd1 = (DateTimeColumnDef) schemaBefore.findDatabase("shard_1").findTable("without_col_length").findColumn("badcol"); assertEquals((Long) 0L, (Long) cd1.getColumnLength()); MysqlSavedSchema restored = MysqlSavedSchema.restore(context, context.getInitialPosition()); DateTimeColumnDef cd = (DateTimeColumnDef) restored.getSchema().findDatabase("shard_1").findTable("without_col_length").findColumn("badcol"); assertEquals((Long) 3L, (Long) cd.getColumnLength()); } private Schema buildSchema() { String charset = Charset.defaultCharset().toString(); List<Database> databases = new ArrayList<>(); return new Schema(databases, charset, caseSensitivity); } private void populateSchemasSurroundingTarget( Connection c, Long serverId, Position targetPosition, Long lastHeartbeat, String previousFile, String newerFile ) throws SQLException { BinlogPosition targetBinlog = targetPosition.getBinlogPosition(); // newer binlog file new MysqlSavedSchema( serverId, caseSensitivity, buildSchema(), makePosition(targetBinlog.getOffset() - 100L, newerFile, lastHeartbeat) ).saveSchema(c); // newer binlog position new MysqlSavedSchema( serverId, caseSensitivity, buildSchema(), makePosition(targetBinlog.getOffset() + 100L, targetBinlog.getFile(), lastHeartbeat) ).saveSchema(c); // different server ID new MysqlSavedSchema( serverId + 1L, caseSensitivity, buildSchema(), targetPosition ).saveSchema(c); // older binlog file new MysqlSavedSchema( serverId, caseSensitivity, buildSchema(), makePosition(targetBinlog.getOffset(), previousFile, lastHeartbeat) ).saveSchema(c); } private Position makePosition(long position, String file, Long lastHeartbeat) { return new Position(new BinlogPosition(position, file), lastHeartbeat); } @Test public void testFindSchemaReturnsTheLatestSchemaForTheCurrentBinlog() throws Exception { if (context.getConfig().gtidMode) { return; } Connection c = context.getMaxwellConnection(); long serverId = 100; long targetPosition = 500; long lastHeartbeat = 9000L; String targetFile = "binlog08"; String previousFile = "binlog07"; String newerFile = "binlog09"; Position targetBinlogPosition = makePosition(targetPosition, targetFile, lastHeartbeat + 10L); MysqlSavedSchema expectedSchema = new MysqlSavedSchema(serverId, caseSensitivity, buildSchema(), makePosition(targetPosition - 50L, targetFile, lastHeartbeat) ); expectedSchema.save(c); // older binlog position new MysqlSavedSchema( serverId, caseSensitivity, buildSchema(), makePosition(targetPosition - 200L, targetFile, lastHeartbeat) ).saveSchema(c); // Newer binlog position but older heartbeat new MysqlSavedSchema( serverId, caseSensitivity, buildSchema(), makePosition(targetPosition - 1L, targetFile, lastHeartbeat - 1000L) ).saveSchema(c); populateSchemasSurroundingTarget(c, serverId, targetBinlogPosition, lastHeartbeat, previousFile, newerFile); MysqlSavedSchema foundSchema = MysqlSavedSchema.restore(context.getMaxwellConnectionPool(), serverId, caseSensitivity, targetBinlogPosition); assertThat(foundSchema.getBinlogPosition(), equalTo(expectedSchema.getBinlogPosition())); assertThat(foundSchema.getSchemaID(), equalTo(expectedSchema.getSchemaID())); } @Test public void testFindSchemaReturnsTheLatestSchemaForPreviousBinlog() throws Exception { if (context.getConfig().gtidMode) { return; } Connection c = context.getMaxwellConnection(); long serverId = 100; long targetPosition = 500; long lastHeartbeat = 9000L; String targetFile = "binlog08"; String previousFile = "binlog07"; String newerFile = "binlog09"; Position targetBinlogPosition = makePosition(targetPosition, targetFile, lastHeartbeat + 10L); // the newest schema: MysqlSavedSchema expectedSchema = new MysqlSavedSchema(serverId, caseSensitivity, buildSchema(), makePosition(targetPosition + 50L, previousFile, lastHeartbeat) ); expectedSchema.save(c); // Newer binlog position but older heartbeat new MysqlSavedSchema( serverId, caseSensitivity, buildSchema(), makePosition(targetPosition - 1L, targetFile, lastHeartbeat - 1000L) ).saveSchema(c); populateSchemasSurroundingTarget(c, serverId, targetBinlogPosition, lastHeartbeat, previousFile, newerFile); MysqlSavedSchema foundSchema = MysqlSavedSchema.restore(context.getMaxwellConnectionPool(), serverId, caseSensitivity, targetBinlogPosition); assertThat(foundSchema.getBinlogPosition(), equalTo(expectedSchema.getBinlogPosition())); assertThat(foundSchema.getSchemaID(), equalTo(expectedSchema.getSchemaID())); } }