Hello ppl. We are trying to upgrade to v0.10.1 but...
# troubleshoot
w
Hello ppl. We are trying to upgrade to v0.10.1 but are running into errors when trying to ingest from AWS glue. Was wondering if I could get any tips. The error message we see follows in the 🧵.
Ingestion run error message:
Copy code
2023-04-04 17:11:08,877 [INFO] [__main__] Running /config/staging/glue-rest2.yml.
2023-04-04 17:11:09,731 [INFO] [datahub.ingestion.run.pipeline] Sink configured successfully. DataHubRestEmitter: configured to talk to https://our-gms-host:443
/opt/python/3.9/lib/python3.9/site-packages/datahub/ingestion/source/aws/glue.py:292: ConfigurationWarning: env is deprecated and will be removed in a future release. Please use platform_instance instead.
  config = GlueSourceConfig.parse_obj(config_dict)
2023-04-04 17:11:09,812 [INFO] [botocore.credentials] Found credentials in environment variables.
2023-04-04 17:11:09,868 [INFO] [botocore.credentials] Found credentials in environment variables.
2023-04-04 17:11:10,007 [INFO] [datahub.ingestion.run.pipeline] Source configured successfully.
2023-04-04 17:13:36,096 [ERROR] [datahub.ingestion.run.pipeline]  failed to write record with workunit urn:li:dataset:(urn:li:dataPlatform:glue,dummy_database.dummy_table,DEV)-subTypes with ('Unable to emit metadata to DataHub GMS', {'exceptionClass': 'com.linkedin.restli.server.RestLiServiceException', 'stackTrace': 'com.linkedin.restli.server.RestLiServiceException [HTTP Status:500]: javax.persistence.PersistenceException: Error when batch flush on sql: update metadata_aspect_v2 set metadata=?, createdOn=?, createdBy=?, createdFor=?, systemmetadata=? where urn=? and aspect=? and version=?\n\tat com.linkedin.metadata.restli.RestliUtil.toTask(RestliUtil.java:42)\n\tat com.linkedin.metadata.restli.RestliUtil.toTask(RestliUtil.java:50)', 'message': 'javax.persistence.PersistenceException: Error when batch flush on sql: update metadata_aspect_v2 set metadata=?, createdOn=?, createdBy=?, createdFor=?, systemmetadata=? where urn=? and aspect=? and v', 'status': 500, 'id': 'urn:li:dataset:(urn:li:dataPlatform:glue,dummy_database.dummy_table,DEV)'}) and info {'exceptionClass': 'com.linkedin.restli.server.RestLiServiceException', 'stackTrace': 'com.linkedin.restli.server.RestLiServiceException [HTTP Status:500]: javax.persistence.PersistenceException: Error when batch flush on sql: update metadata_aspect_v2 set metadata=?, createdOn=?, createdBy=?, createdFor=?, systemmetadata=? where urn=? and aspect=? and version=?\n\tat com.linkedin.metadata.restli.RestliUtil.toTask(RestliUtil.java:42)\n\tat com.linkedin.metadata.restli.RestliUtil.toTask(RestliUtil.java:50)', 'message': 'javax.persistence.PersistenceException: Error when batch flush on sql: update metadata_aspect_v2 set metadata=?, createdOn=?, createdBy=?, createdFor=?, systemmetadata=? where urn=? and aspect=? and v', 'status': 500, 'id': 'urn:li:dataset:(urn:li:dataPlatform:glue,dummy_database.dummy_table,DEV)'}
gms-error-message
We are running an aurora postgres cluster as our database.
Other ingestions such as the kafka-source seem to be working fine.
I'll mention that I've managed to reproduce this using the quickstart.sh setup and only replacing the EBEAN configs for the "datahub-gms" and "datahub-upgrade" services to point to our aurora cluster.
Now also reproduced with the standard postgres docker container.
Issue #7757 reported.
b
l
We will take a look
g
Hey @wonderful-quill-11255 - this seems like an issue with our backend, specifically related to the postgres backend. We haven’t seen this one with mysql, but we’re looking into it
b
Was the default transaction isolation level changed from
READ COMMITTED
to something else?
w
Hi @brainy-tent-14503. Replying a bit late due to Easter. I'm not sure if that's a question for me, but I don't think we have changed any such configuration. And the default for postgres is
READ COMMITTED
.
b
@wonderful-quill-11255 - The error
Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update
should not be possible if postgres is configured for
READ COMMITTED
is my understanding. Can you double check your AWS configuration?
w
Hey David. But I reproduced this using only the docker postgres. Are you not able to reproduce that behaviour?
a
I found what I believe is the cause, a change to transaction handling. Will follow up with the author and see what the intent was here.
r
fyi, we are experiencing the same error when trying to upload dbt data using the 0.10.1 CLI. Also into AWS postgres (serverless v2)
a
I have some additional context here. For
postgresql
there is an issue with parallel transactions which is not present with
mysql
, A change in the default
max_threads
from 1 to 15 in the default options as part of v0.10.1 is from this change. In order to workaround this, please update the ingestion recipes back to
max_threads: 1
. Additionally a note was added to the latest release notes about this.
w
Awesome. I'll try to test this as soon as possible. Thanks David!
The workaround seems to work. thankyou1
f
Hello guys! I'm still having the exact same problem since
v0.10.1
. Currently I've already upgrade to
v0.10.3
and still having the same issue. As it was already commented here, my backend DB is aurora postgres as well. I've tried to set on the recipe
sink.config.max_threads: 1
but it didn't help 😕 This issue happens mostly in our
snowflake
sinks. We are running GMS with 3 replicas (in k8s), and also running standalone MAE and MCE consumers (with 1 replica each) @brainy-tent-14503 any suggestions here? Help would be appreciated 🙏 Thanks!
Getting a related error on DBT ingestion as well.
Copy code
Detail: Key (urn, aspect, version)=(urn:li:assertion:eff38e71b70cf7871ce86ff3e9f8faf9, assertionInfo, 3223) already exists.  Call getNextException to see other errors in the batch.

ERROR: duplicate key value violates unique constraint "pk_metadata_aspect_v2"

Caused by: io.ebean.DuplicateKeyException: Error when batch flush on sql: insert into metadata_aspect_v2 (urn, aspect, version, metadata, createdOn, createdBy, createdFor, systemmetadata) values (?,?,?,?,?,?,?,?)
It seems related to the same underlying problem of concurrent operations on the backend db
g
We’re encountering the same error with the following scenarios: datahub: v0.10.3 (kubernetes without helm) dedicated on-prem postgresql-14 1. 3 GMS replicas and max_threads=1 2. 1 GMS replica and max_threads=1 GMS Logs:
Copy code
2023-05-30 08:24:47,532 [qtp522764626-219] INFO c.l.m.filter.RestliLoggingFilter:55 - POST /aspects?action=ingestProposal - ingestProposal - 500 - 165ms
2023-05-30 08:24:47,533 [qtp522764626-219] ERROR c.l.m.filter.RestliLoggingFilter:38 - Rest.li error:
com.linkedin.restli.server.RestLiServiceException: javax.persistence.PersistenceException: Error when batch flush on sql: update metadata_aspect_v2 set metadata=?, createdOn=?, createdBy=?, createdFor=?, systemmetadata=? where urn=? and aspect=? and version=?
PostgreSQL Logs:
Copy code
2023-05-30 11:24:17 +03 [4610] user=***,db=***,app=PostgreSQL JDBC Driver,client=[local] ERROR:  could not serialize access due to concurrent update
2023-05-30 11:24:17 +03 [4610] user=***,db=***,app=PostgreSQL JDBC Driver,client=[local] STATEMENT:  update metadata_aspect_v2 set metadata=$1, createdOn=$2, createdBy=$3, createdFor=$4, systemmetadata=$5 where urn=$6 and aspect=$7 and version=$8
CC: @glamorous-wire-83850
g
We solved that is issue with changing the sink method rest to kafka
a
@bland-gigabyte-28270 - Please start a new thread with your details similar to those in this thread. It helps with visibility, thanks!
s
We are also having this issue, therefore I have tried to understand the what is happening and why the same issue does not occur with a MySQL database...see here for more information: https://github.com/datahub-project/datahub/issues/8257
Do you also have the log of the GMS with the error? The PersistenceException is a base class for all persistency related exceptions...maybe the underlying issue in your case is not related to the serialization error which could be prevented by using the the max_threads parameter? Usually if there are not other ingestion jobs running in parallel there shouldn't be any serialization errors? However there is a PR with a fix which should solve the issue with the serialization error: https://github.com/datahub-project/datahub/pull/8278
b
I can try and check
Does look like it’s from serialization error:
Copy code
Caused by: java.sql.BatchUpdateException: Batch entry 0 update metadata_aspect_v2 set metadata='{"paths":["/prod/snowflake/arene/aha"]}', createdOn='2023-06-21 06:20:14.555+00', createdBy='urn:li:corpuser:__datahub_system', createdFor=NULL, systemmetadata='{"registryVersion":"0.0.0.0-dev","runId":"34bd9821-13ef-4a97-ae48-3e2ab7a95c59","registryName":"unknownRegistry","lastObserved":1687328414542}' where urn='urn:li:dataset:(urn:li:dataPlatform:snowflake,arene.aha.kfk_aha_epic,PROD)' and aspect='browsePaths' and version=0 was aborted: ERROR: could not serialize access due to concurrent update  Call getNextException to see other errors in the batch.
With your proposed PR, should it also fix for the cases where we don’t set
max_threads
parameter?
Also, seems like it also happens with MySQL. Is this true?
We are thinking for migrating to MySQL since it seems to be first-class support for DataHub instead of Postgres, however we are not sure.
b
Hello, we face the same problem with the ingestion of dbt and snowflake data. We currently use datahub and the CLI in v.0.10.3 and added to the corresponding recipes the 'max_threads' configuration. The database is running on postgres. The following error is logged for the ingestion:
Copy code
{
  "error": "Unable to emit metadata to DataHub GMS: javax.persistence.PersistenceException: Error when batch flush on sql: update metadata_aspect_v2 set metadata=?, createdOn=?, createdBy=?, systemmetadata=? where urn=? and aspect=? and version=?",
  "info": {
    "exceptionClass": "com.linkedin.restli.server.RestLiServiceException",
    "message": "javax.persistence.PersistenceException: Error when batch flush on sql: update metadata_aspect_v2 set metadata=?, createdOn=?, createdBy=?, systemmetadata=? where urn=? and aspect=? and version=?",
    "status": 500,
    "id": "urn:li:assertion:093266a24bb02758afec92c840b49ee1"
  }
}
In the gms log we can see the following (shortened) error:
Copy code
Caused by: java.sql.BatchUpdateException: Batch entry 0 update metadata_aspect_v2 set metadata='...', createdOn='2023-06-23 06: 07: 24.116+00', createdBy='urn:li:corpuser:example@example.com', systemmetadata='...' where urn='urn:li:assertion:093266a24bb02758afec92c840b49ee1' and aspect='assertionInfo' and version=0 was aborted: ERROR: could not serialize access due to concurrent update  Call getNextException to see other errors in the batch.
I suppose this is the same kind of error? Thanks already for your great help here in Slack!
s
Technically something similar happens when you are using a MySQL database, but as the locking mechanism works differently* in the PostgreSQL database there are no serialization errors, instead there are duplicate key errors - in case of a duplicate key error the corresponding transaction is simply retried by DataHub. This is also the solution to solve the serialization error with the PostgreSQL database. Of course it could also happen that after retrying the transaction the same error happens again, but the probability is quite low for this to happen (according to the current logic in total a single transaction is retried three times). As far as I understand the logic, the workaround with the max_threads parameter should actively prevent the occurrence of serialization errors as long as there are no concurrent writes from another place (ingestion job, the UI, ...) to the same rows in the database (e.g. the same aspects of the same entity?): The max_threads parameters ensures that only one aspect is sent to the GMS by the datahub-rest sink at the same time, the GMS responds to the request once the corresponding transaction was successful and the datahub-rest sink would then send the next aspect to be written to the database - this means a single ingestion job should not lead to concurrent writes to the database when only one thread is used for sending the aspects. I have not seen a serialization error when only one thread is used in our deployment, but our deployment is currently only a sandbox deployment, therefore there are definitely no concurrent writes to the database from anywhere else...my assumption would be that in your cases there were concurrent writes from somewhere else...but this is only a guess? *One of our database experts tried to explain it to me and I hopefully remember it correctly - no guarantee that I remember it correctly of course: The PostgreSQL makes a copy of the affected rows - "the rows are serialized" - and after processing the steps in the transaction before committing the transaction the database checks with the serialized rows whether there were any changes in the meantime...the MySQL database on the other hand somehow locks the affected rows before the transaction and when the transaction is then committed the duplicate key error is thrown.
b
Hello @shy-scooter-37097, thanks also for your message. I understand what you describe/technically should happen. But I am not sure why we face this problem even if only one ingestion is running at the same time and we configured the max_threads parameter. Running multiple ingestion at the same time should not be the problem in our case. What I saw and helped to temporarily "fix" the problem is to retry also the requests returned with status code 500. It seems that this status code is not included in the list of default response codes that are retried: https://datahubproject.io/docs/metadata-ingestion/sink_docs/datahub/#config-details So I added the list (including '500') in our recipes, this works for now better but of course does not fix the problem in general.