[source-mssql] Disabled Field Used as Ordered Column

[SeanMiltyASU]

Connector Name

source-mssql

Connector Version

4.1.22

What step the error happened?

During the sync

Relevant information

I've disabled a field in the Airbyte UI (schoolID). I've done this because the field is always NULL.

This field is listed as a clustered index in the source schema information.

The connector chooses this field as the ordered column despite the field being disabled in the UI.

Relevant log output

2025-02-28 10:33:13 source INFO main i.a.i.s.m.MssqlQueryUtils(getIndexInfoForStreams):76 Discovering indexes for table "dbo"."BehaviorType"
2025-02-28 10:33:13 source INFO main i.a.i.s.m.MssqlQueryUtils(lambda$getIndexInfoForStreams$3):84 Index Index[name=IX_BehaviorType_typeGUID, description=nonclustered, unique located on PRIMARY, keys=typeGUID]
2025-02-28 10:33:13 source INFO main i.a.i.s.m.MssqlQueryUtils(lambda$getIndexInfoForStreams$3):84 Index Index[name=PK_BehaviorType, description=nonclustered, unique, primary key located on PRIMARY, keys=typeID]
2025-02-28 10:33:13 source INFO main i.a.i.s.m.MssqlQueryUtils(lambda$getIndexInfoForStreams$3):84 Index Index[name=XX_BehaviorType_schoolID, description=clustered located on PRIMARY, keys=schoolID]
2025-02-28 10:33:13 source INFO main i.a.c.i.s.j.AbstractJdbcSource(discoverTable):563 Discover table: dbo.BehaviorType
2025-02-28 10:33:13 source INFO main i.a.c.i.s.r.AbstractDbSource(discoverWithoutSystemTables):301 Discovered table: dbo.BehaviorType: TableInfo(nameSpace=dbo, name=BehaviorType, fields=[CommonField{name='typeID', type=INTEGER, properties=null}, CommonField{name='schoolID', type=INTEGER, properties=null}, CommonField{name='code', type=VARCHAR, properties=null}, CommonField{name='name', type=VARCHAR, properties=null}, CommonField{name='category', type=VARCHAR, properties=null}, CommonField{name='alignment', type=SMALLINT, properties=null}, CommonField{name='demerits', type=SMALLINT, properties=null}, CommonField{name='stateEventCode', type=VARCHAR, properties=null}, CommonField{name='regionalEventCode', type=VARCHAR, properties=null}, CommonField{name='federalEventCode', type=VARCHAR, properties=null}, CommonField{name='startDate', type=TIMESTAMP, properties=null}, CommonField{name='endDate', type=TIMESTAMP, properties=null}, CommonField{name='archived', type=BIT, properties=null}, CommonField{name='typeGUID', type=CHAR, properties=null}, CommonField{name='districtID', type=INTEGER, properties=null}, CommonField{name='classification', type=VARCHAR, properties=null}, CommonField{name='availableInReferral', type=BIT, properties=null}, CommonField{name='allowProtectedClass', type=BIT, properties=null}, CommonField{name='standardizedEventCode', type=VARCHAR, properties=null}], primaryKeys=[], cursorFields=[typeID, schoolID, code, name, category, alignment, demerits, stateEventCode, regionalEventCode, federalEventCode, startDate, endDate, districtID, classification, standardizedEventCode])
2025-02-28 10:33:13 source INFO main i.a.c.i.s.r.s.CursorManager(createCursorInfoForStream$io_airbyte_airbyte_cdk_java_airbyte_cdk_airbyte_cdk_db_sources):221 No cursor field set in catalog but not present in state. Stream: dbo_BehaviorType, New Cursor Field: null. Resetting cursor value
2025-02-28 10:33:13 source INFO main i.a.i.s.m.i.MssqlInitialReadUtil(streamsForInitialOrderedColumnLoad):486 raw state message: [io.airbyte.protocol.models.v0.AirbyteStateMessage@3703bf3c[type=STREAM,stream=io.airbyte.protocol.models.v0.AirbyteStreamState@34cf5a97[streamDescriptor=<null>,streamState=<null>,additionalProperties={}],global=<null>,data=<null>,sourceStats=<null>,destinationStats=<null>,additionalProperties={}]]
2025-02-28 10:33:13 source INFO main i.a.i.s.m.i.MssqlInitialReadUtil(streamsForInitialOrderedColumnLoad):513 alreadySeenStreamPairs: []
2025-02-28 10:33:13 source INFO main i.a.i.s.m.i.MssqlInitialReadUtil(streamsForInitialOrderedColumnLoad):522 streamsForOcSync: [io.airbyte.protocol.models.v0.ConfiguredAirbyteStream@1dab9dd6[stream=io.airbyte.protocol.models.v0.AirbyteStream@247667dd[name=BehaviorType,jsonSchema={"type":"object","properties":{"code":{"type":"string"},"name":{"type":"string"},"typeID":{"type":"number","airbyte_type":"integer"},"endDate":{"type":"string","format":"date-time","airbyte_type":"timestamp_without_timezone"},"archived":{"type":"boolean"},"category":{"type":"string"},"demerits":{"type":"number","airbyte_type":"integer"},"typeGUID":{"type":"string"},"startDate":{"type":"string","format":"date-time","airbyte_type":"timestamp_without_timezone"},"districtID":{"type":"number","airbyte_type":"integer"},"classification":{"type":"string"},"stateEventCode":{"type":"string"},"federalEventCode":{"type":"string"},"regionalEventCode":{"type":"string"},"allowProtectedClass":{"type":"boolean"},"availableInReferral":{"type":"boolean"},"standardizedEventCode":{"type":"string"}}},supportedSyncModes=[full_refresh, incremental],sourceDefinedCursor=false,defaultCursorField=[],sourceDefinedPrimaryKey=[[typeID]],namespace=dbo,isResumable=true,additionalProperties={}],syncMode=full_refresh,cursorField=[],destinationSyncMode=overwrite,primaryKey=[[typeID]],generationId=15,minimumGenerationId=15,syncId=1926,additionalProperties={}]]
2025-02-28 10:33:14 source INFO main i.a.i.s.m.i.MssqlInitialReadUtil(getOrderedColumnInfo):432 selected ordered column field name: schoolID
2025-02-28 10:33:14 source INFO main i.a.i.s.m.MssqlQueryUtils(getMaxOcValueForStream):105 Querying for max oc value:   SELECT MAX("schoolID") as max_oc FROM "dbo"."BehaviorType";
2025-02-28 10:33:14 source INFO main i.a.i.s.m.MssqlQueryUtils(getMaxOcValueForStream):111 Max PK is null for table "dbo"."BehaviorType" - this could indicate an empty table
2025-02-28 10:33:14 source INFO main i.a.i.s.m.MssqlSource(getIncrementalIterators):457 using CDC: false
2025-02-28 10:33:14 source INFO main i.a.i.s.m.MssqlQueryUtils(getTableEstimate):241 Querying for table estimate size: EXEC sp_spaceused N'"dbo"."BehaviorType"'
2025-02-28 10:33:14 source INFO main i.a.i.s.m.MssqlQueryUtils(lambda$getTableSizeInfoForStreams$6):157 Stream "BehaviorType"."dbo" size estimate is 16384, average row size estimate is 143
2025-02-28 10:33:14 source INFO main i.a.i.s.m.i.MssqlInitialLoadHandler(calculateChunkSize):263 Chunk size determined for pair: dbo_BehaviorType, is 7508684
2025-02-28 10:33:14 source INFO main i.a.i.s.m.i.MssqlInitialLoadRecordIterator(isCdcSync):195 Not running a cdc sync
2025-02-28 10:33:14 source INFO main i.a.i.s.m.i.MssqlInitialLoadRecordIterator(computeNext):107 Subquery number : 0
2025-02-28 10:33:14 source INFO main i.a.i.s.m.i.MssqlInitialLoadRecordIterator(getOcPreparedStatement):136 Preparing query for table: "dbo"."BehaviorType"
2025-02-28 10:33:14 replication-orchestrator INFO Stream status TRACE received of status: STARTED for stream dbo:BehaviorType
2025-02-28 10:33:14 replication-orchestrator INFO Sending update for dbo:BehaviorType - null -> RUNNING
2025-02-28 10:33:14 replication-orchestrator INFO Stream Status Update Received: dbo:BehaviorType - RUNNING
2025-02-28 10:33:14 replication-orchestrator INFO Creating status: dbo:BehaviorType - RUNNING
2025-02-28 10:33:14 destination INFO main i.a.i.d.b.t.BigQueryDestinationHandler(existingSchemaMatchesStreamConfig):271 Alter Table Report [] [] []; Clustering true; Partitioning true
2025-02-28 10:33:14 source INFO main i.a.c.d.j.s.AdaptiveStreamingQueryConfig(initialize):24 Set initial fetch size: 10 rows
2025-02-28 10:33:14 destination INFO sync-operations-1 i.a.i.b.d.t.TyperDeduperUtil(runMigrationsAsync$lambda$12):165 Maybe executing BigQueryDV2Migration migration for stream raw_production_tables_dev.testing_BehaviorType.
2025-02-28 10:33:14 destination INFO sync-operations-1 i.a.i.d.b.m.BigQueryDV2Migration(migrateIfNecessary):27 Initializing DV2 Migration check
2025-02-28 10:33:14 destination INFO sync-operations-1 i.a.i.b.d.t.BaseDestinationV1V2Migrator(migrateIfNecessary):19 Assessing whether migration is necessary for stream testing_BehaviorType
2025-02-28 10:33:14 destination INFO sync-operations-1 i.a.i.b.d.t.BaseDestinationV1V2Migrator(shouldMigrate):43 Checking whether v1 raw table _airbyte_raw_testing_BehaviorType in dataset raw_production_tables_dev exists
2025-02-28 10:33:14 source INFO main i.a.i.s.m.i.MssqlInitialLoadRecordIterator(getOcPreparedStatement):148 Executing query for table BehaviorType: SELECT TOP 7508684 "typeID", "code", "name", "category", "demerits", "stateEventCode", "regionalEventCode", "federalEventCode", "startDate", "endDate", "archived", "typeGUID", "districtID", "classification", "availableInReferral", "allowProtectedClass", "standardizedEventCode" FROM "dbo"."BehaviorType" ORDER BY "schoolID"
2025-02-28 10:33:15 source INFO main i.a.c.d.j.s.AdaptiveStreamingQueryConfig(initialize):24 Set initial fetch size: 10 rows
2025-02-28 10:33:15 source INFO main i.a.c.i.s.r.AbstractDbSource(read$lambda$5):174 Closing database connection pool.
2025-02-28 10:33:15 source INFO main c.z.h.HikariDataSource(close):349 HikariPool-1 - Shutdown initiated...
2025-02-28 10:33:15 source INFO main c.z.h.HikariDataSource(close):351 HikariPool-1 - Shutdown completed.
2025-02-28 10:33:15 source INFO main i.a.c.i.s.r.AbstractDbSource(read$lambda$5):176 Closed database connection pool.
2025-02-28 10:33:15 source ERROR main i.a.c.i.u.ConnectorExceptionHandler(handleException):68 caught exception! io.airbyte.cdk.integrations.source.relationaldb.state.FailedRecordIteratorException: java.lang.NullPointerException: Cannot invoke "com.fasterxml.jackson.databind.JsonNode.asText()" because the return value of "com.fasterxml.jackson.databind.JsonNode.get(String)" is null
	at io.airbyte.cdk.integrations.source.relationaldb.state.SourceStateIterator.computeNext(SourceStateIterator.kt:75) ~[airbyte-cdk-db-sources-0.48.7.jar:?]
	at io.airbyte.cdk.integrations.source.relationaldb.state.SourceStateIterator.computeNext(SourceStateIterator.kt:18) ~[airbyte-cdk-db-sources-0.48.7.jar:?]
	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.3.0-jre.jar:?]
	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.3.0-jre.jar:?]
	at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.kt:42) ~[airbyte-cdk-dependencies-0.48.7.jar:?]
	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.3.0-jre.jar:?]
	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.3.0-jre.jar:?]
	at com.google.common.collect.TransformedIterator.hasNext(TransformedIterator.java:46) ~[guava-33.3.0-jre.jar:?]
	at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.kt:42) ~[airbyte-cdk-dependencies-0.48.7.jar:?]
	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.3.0-jre.jar:?]
	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.3.0-jre.jar:?]
	at io.airbyte.commons.util.CompositeIterator.computeNext(CompositeIterator.kt:67) ~[airbyte-cdk-dependencies-0.48.7.jar:?]
	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.3.0-jre.jar:?]
	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.3.0-jre.jar:?]
	at com.google.common.collect.TransformedIterator.hasNext(TransformedIterator.java:46) ~[guava-33.3.0-jre.jar:?]
	at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.kt:42) ~[airbyte-cdk-dependencies-0.48.7.jar:?]
	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.3.0-jre.jar:?]
	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.3.0-jre.jar:?]
	at io.airbyte.commons.util.CompositeIterator.computeNext(CompositeIterator.kt:67) ~[airbyte-cdk-dependencies-0.48.7.jar:?]
	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.3.0-jre.jar:?]
	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.3.0-jre.jar:?]
	at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.kt:42) ~[airbyte-cdk-dependencies-0.48.7.jar:?]
	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.3.0-jre.jar:?]
	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.3.0-jre.jar:?]
	at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.kt:42) ~[airbyte-cdk-dependencies-0.48.7.jar:?]
	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.3.0-jre.jar:?]
	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.3.0-jre.jar:?]
	at java.base/java.util.Iterator.forEachRemaining(Iterator.java:132) ~[?:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.produceMessages(IntegrationRunner.kt:234) ~[airbyte-cdk-core-0.48.7.jar:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.readSerial(IntegrationRunner.kt:291) ~[airbyte-cdk-core-0.48.7.jar:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:190) [airbyte-cdk-core-0.48.7.jar:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:119) [airbyte-cdk-core-0.48.7.jar:?]
	at io.airbyte.integrations.source.mssql.MssqlSource.main(MssqlSource.java:598) [io.airbyte.airbyte-integrations.connectors-source-mssql.jar:?]

Contribute

• Yes, I want to contribute