0.45.1 SQL Connections - Don't know how to map column type 'bigint identity'

Bug reports
#1

Since the latest update, we're having problems with SQL Connections. New views aren't being added and existing views in the data model are showing this:

image
image2145×112 8.13 KB

From the logs, I see:
5eb-f677-4cbc-9eed-60693bb74858] 2022-12-08T10:34:36+00:00 WARN metabase.driver.sql-jdbc.sync.describe-table Don't know how to map column type 'bigint identity' to a Field base_type, falling back to :type/.
[7276a5eb-f677-4cbc-9eed-60693bb74858] 2022-12-08T10:34:36+00:00 WARN metabase.driver.sql-jdbc.sync.describe-table Don't know how to map column type 'bigint identity' to a Field base_type, falling back to :type/.
[7276a5eb-f677-4cbc-9eed-60693bb74858] 2022-12-08T10:34:37+00:00 WARN metabase.sync.util Error checking if Fields ("Date" "RoomId" "BookedPoints" "WeekCommencing" "BookingTypeId" "SessionStart" "SessionTemplateId" "BookingCount" "AppointmentTypeId" "EndoscopistId" "PerformedPoints" "SiteId" "ServiceType" "BookingType") need to be created or reactivated
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "metabase_field_pkey"
Detail: Key (id)=(8566) already exists.

I assume it's related to the SQL driver problems yesterday.

#2

Hi @AndrewMBaines
I think there's several things involved, but not related to the driver problem in 45.0.

The bigint indentity column type has never been recognized: https://github.com/metabase/metabase/blob/release-x.45.x/modules/drivers/sqlserver/src/metabase/driver/sqlserver.clj#L45-L83

It's difficult to tell what is going on with the constraint error. Something makes me think there might be a case-sensitivity problem.
Think there's something more in the log.

I'm not having any problems with views.

Can you provide DDL to reproduce?

#3

It worked two days ago! We're rolling back so may not be able to provide much more information.
The columns in the views are defined as bigints. They just reflect what's in the underlying database that hasn't changed. i have no control over that bit.
I'll try casting the identifiers to something smaller.

Edit: This is SQL Server, so no case sensitivity.

2nd Edit: They're not identity columns, just defined as keys in the Data Model.

#4

@AndrewMBaines I think you're mixing different things together. You like have some bigint identity columns, but likely completely unrelated to your problem, and those warnings have likely always been there.

There's case-sensitivity in Postgres, which is your Metabase application database.

Something is causing Metabase to try to re-create metabase_field.id=8566 (whatever that field is).
I'm sure there's other logs.

#6

Correction - it's not working in 0.44.6. I think the customer's developers have changed something as they've changed the security too.

#7

I'm going round in circles!
Nothing has changed in the database. I'll let you know if I get any further. None of this makes sense.

#8

@flamber
I think I'm close. Nothing to do with data types, it's that Postgres pkey error. Even the simplest view gives:

ERROR: duplicate key value violates unique constraint "metabase_table_pkey"
  Detail: Key (id)=(980) already exists.

Am I able to manually increment the value it's expecting to use to avoid the clash?

#9

@AndrewMBaines It sounds like you've lost the auto-increment on your application database, since it should never ever try to insert a conflicting ID. You are having problems with both metabase_table and metabase_field (and likely others too).

Changing any IDs will result in broken questions etc.

Have a look here: https://stackoverflow.com/questions/4448340/postgresql-duplicate-key-violates-unique-constraint

1 Like
#10

Thanks for that. All working now. Oddly, the key that I updated wasn't the table one. I have a feeling I'll be doing this again!