Job already exists

[nitishxp]

Python: 3.9 Cloud function runtime

Lib:
google-cloud-bigquery==2.15.0
google-auth==1.30.0
google-api-python-client==2.7.0

"/layers/google.python.pip/pip/lib/python3.8/site-packages/google/cloud/bigquery/client.py", line 3149, in query query_job._begin(retry=retry, timeout=timeout) File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/cloud/bigquery/job/query.py", line 1044, in _begin super(QueryJob, self)._begin(client=client, retry=retry, timeout=timeout) File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/cloud/bigquery/job/base.py", line 461, in _begin api_response = client._call_api( File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/cloud/bigquery/client.py", line 685, in _call_api return call() File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/api_core/retry.py", line 285, in retry_wrapped_func return retry_target( File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/api_core/retry.py", line 188, in retry_target return target() File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/cloud/_http.py", line 483, in api_request raise exceptions.from_http_response(response) google.api_core.exceptions.Conflict: 409 POST https://bigquery.googleapis.com/bigquery/v2/projects/<project_id>/jobs?prettyPrint=false: Already Exists: Job <project>:US.<Job_id>(job ID: <Job_id>)

I got the following error in one of production environment, its first time i have encountered this error.

[plamut]

@nitishxp Does your application run queries (and create query jobs behind the scenes) by explicitly passing the job_id argument? Could it be that the same "create query job" request was sent twice in a short time span using the same job_id?

[nitishxp]

@plamut

No I don't do any of those things

Below snippet code I use

job_config = bigquery.QueryJobConfig() 
job_config.destination = dest_tbl_ref
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
svtt_job = gbq.query(view_query, job_config=job_config).job_id

Traceback (most recent call last): File "/workspace/common/functions.py", line 55, in wrapper response = wrapped_func(*args, **kwargs) File "/workspace/main.py", line 370, in bq_load job_id = <function_name>) File "/workspace/main.py", line 242, in <function_name> svtt_job = gbq.query(view_query, job_config=job_config) File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/cloud/bigquery/client.py", line 3149, in query query_job._begin(retry=retry, timeout=timeout) File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/cloud/bigquery/job/query.py", line 1044, in _begin super(QueryJob, self)._begin(client=client, retry=retry, timeout=timeout) File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/cloud/bigquery/job/base.py", line 461, in _begin api_response = client._call_api( File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/cloud/bigquery/client.py", line 685, in _call_api return call() File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/api_core/retry.py", line 285, in retry_wrapped_func return retry_target( File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/api_core/retry.py", line 188, in retry_target return target() File "/layers/google.python.pip/pip/lib/python3.8/site-packages/google/cloud/_http.py", line 483, in api_request raise exceptions.from_http_response(response) google.api_core.exceptions.Conflict: 409 POST

[plamut]

@nitishxp I see, interesting. There's indeed nothing special about the way the query is run.

@tswast Have you seen this before? Could it happen that the server receives the request successfully, but the client does not know that because the response gets lost (or happens just before the timeout expires), and thus the client retries the very same request, even though it already succeeded?

[tswast]

@tswast Have you seen this before? Could it happen that the server receives the request successfully, but the client does not know that because the response gets lost (or happens just before the timeout expires), and thus the client retries the very same request, even though it already succeeded?

I haven't seen it before, but it is something we anticipate can happen. I suspect this issue was disguised before we started retrying connection/transport-layer errors.

We have implemented the fix for this in Java. I guess you just missed that part of the spec when we did the Python client implementation.

https://github.com/googleapis/java-bigquery/blob/e755470ad3699eee8816f55cdec4dd995cf85d52/google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/BigQueryImpl.java#L385-L401