You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
sqlite can throw errors of the form sql insert error: 'database table is locked:... under concurrency. This has been observed in the aggressive concurrency robot tests as per the below evidence.
For some discussion on a related, probably non-identical issue, see this forum thread.
Acceptance Criteria
The brief for this work is to avoid or mitigate this scenario, with a fulsome explanation documented.
Evidence
From an aggressive concurrency robot test run:
<test id="s1-s2-t124" name="Select Subquery Join With Path Parameters inside IN Scalars inside WHERE Clause Returns Expected Result" line="2787">
<kw name="Catenate" library="BuiltIn">
<var>${inputStr}</var>
<arg>select</arg>
<arg>subnets.subnetwork,</arg>
<arg>s2.proj</arg>
<arg>from</arg>
<arg>(</arg>
<arg>select</arg>
<arg>ipCidrRange,</arg>
<arg>subnetwork</arg>
<arg>from google.container."projects.aggregated.usableSubnetworks"</arg>
<arg>where</arg>
<arg>projectsId in ('testing-project', 'another-project', 'yet-another-project')</arg>
<arg>order by subnetwork desc</arg>
<arg>) subnets</arg>
<arg>inner join</arg>
<arg>(</arg>
<arg>select</arg>
<arg>ipCidrRange,</arg>
<arg>subnetwork,</arg>
<arg>split_part(subnetwork, '/', 2) as proj</arg>
<arg>from google.container."projects.aggregated.usableSubnetworks"</arg>
<arg>where projectsId in ('testing-project', 'another-project', 'yet-another-project')</arg>
<arg>order by subnetwork desc</arg>
<arg>) s2</arg>
<arg>on</arg>
<arg>subnets.subnetwork = s2.subnetwork</arg>
<arg>order by subnets.subnetwork desc</arg>
<arg>;</arg>
<doc>Catenates the given items together and returns the resulted string.</doc>
<msg timestamp="20240111 04:10:14.455" level="INFO">${inputStr} = select subnets.subnetwork, s2.proj from ( select ipCidrRange, subnetwork from google.container."projects.aggregated.usableSubnetworks" where projectsId in ('testing-project', 'another-project', 'yet-a...</msg>
<status status="PASS" starttime="20240111 04:10:14.455" endtime="20240111 04:10:14.455"/>
</kw>
<kw name="Catenate" library="BuiltIn">
<var>${outputStr}</var>
<arg>SEPARATOR=\n</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}subnetwork${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}proj${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-02${SPACE}|${SPACE}yet-another-project${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-01${SPACE}|${SPACE}yet-another-project${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/testing-project/regions/australia-southeast1/subnetworks/sn-02${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}testing-project${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/testing-project/regions/australia-southeast1/subnetworks/sn-01${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}testing-project${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/another-project/regions/australia-southeast1/subnetworks/sn-02${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}another-project${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/another-project/regions/australia-southeast1/subnetworks/sn-01${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}another-project${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<doc>Catenates the given items together and returns the resulted string.</doc>
<msg timestamp="20240111 04:10:14.457" level="INFO">${outputStr} = |-----------------------------------------------------------------------------|---------------------|
| subnetwork | proj ...</msg>
<status status="PASS" starttime="20240111 04:10:14.455" endtime="20240111 04:10:14.457"/>
</kw>
<kw name="Should Stackql Exec Inline Equal" library="StackQLInterfaces">
<arg>${STACKQL_EXE}</arg>
<arg>${OKTA_SECRET_STR}</arg>
<arg>${GITHUB_SECRET_STR}</arg>
<arg>${K8S_SECRET_STR}</arg>
<arg>${REGISTRY_NO_VERIFY_CFG_STR}</arg>
<arg>${AUTH_CFG_STR}</arg>
<arg>${SQL_BACKEND_CFG_STR_CANONICAL}</arg>
<arg>${inputStr}</arg>
<arg>${outputStr}</arg>
<arg>stdout=/home/runner/work/stackql/stackql/test/robot/functional/tmp/Select-Subquery-Join-With-Path-Parameters-inside-IN-Scalars-inside-WHERE-Clause-Returns-Expected-Result.tmp</arg>
<msg timestamp="20240111 04:10:14.457" level="INFO">Environment variable 'OKTA_SECRET_KEY' set to value 'some-dummy-api-key'.</msg>
<msg timestamp="20240111 04:10:14.457" level="INFO">Environment variable 'GITHUB_SECRET_KEY' set to value 'some-dummy-github-key'.</msg>
<msg timestamp="20240111 04:10:14.457" level="INFO">Environment variable 'K8S_SECRET_KEY' set to value 'some-k8s-token'.</msg>
<msg timestamp="20240111 04:10:14.457" level="INFO">Environment variable 'AZ_ACCESS_TOKEN' set to value 'dummy_azure_token'.</msg>
<msg timestamp="20240111 04:10:14.458" level="INFO">Environment variable 'SUMO_CREDS' set to value 'somesumologictoken'.</msg>
<msg timestamp="20240111 04:10:14.458" level="INFO">Environment variable 'DIGITALOCEAN_TOKEN' set to value 'somedigitaloceantoken'.</msg>
<msg timestamp="20240111 04:10:14.458" level="INFO">Environment variable 'DUMMY_DIGITALOCEAN_USERNAME' set to value 'myusername'.</msg>
<msg timestamp="20240111 04:10:14.458" level="INFO">Environment variable 'DUMMY_DIGITALOCEAN_PASSWORD' set to value 'mypassword'.</msg>
<msg timestamp="20240111 04:10:14.458" level="INFO">Starting process:
/home/runner/work/stackql/stackql/build/stackql exec "--registry={\"url\": \"file:///home/runner/work/stackql/stackql/test/registry-mocked\", \"localDocRoot\": \"/home/runner/work/stackql/stackql/test/registry-mocked\", \"verifyConfig\": {\"nopVerify\": true}}" "--auth={\"google\": {\"credentialsfilepath\": \"/home/runner/work/stackql/stackql/test/assets/credentials/dummy/google/functional-test-dummy-sa-key.json\", \"type\": \"service_account\"}, \"okta\": {\"credentialsenvvar\": \"OKTA_SECRET_KEY\", \"type\": \"api_key\"}, \"aws\": {\"type\": \"aws_signing_v4\", \"credentialsfilepath\": \"/home/runner/work/stackql/stackql/test/assets/credentials/dummy/aws/functional-test-dummy-aws-key.txt\", \"keyID\": \"NON_SECRET\"}, \"github\": {\"type\": \"basic\", \"credentialsenvvar\": \"GITHUB_SECRET_KEY\"}, \"k8s\": {\"credentialsenvvar\": \"K8S_SECRET_KEY\", \"type\": \"api_key\", \"valuePrefix\": \"***"}, \"azure\": {\"type\": \"api_key\", \"valuePrefix\": \"***", \"credentialsenvvar\": \"AZ_ACCESS_TOKEN\"}, \"sumologic\": {\"type\": \"basic\", \"credentialsenvvar\": \"SUMO_CREDS\"}, \"digitalocean\": {\"type\": \"bearer\", \"username\": \"myusername\", \"password\": \"mypassword\"}}" --sqlBackend={} --tls.allowInsecure=true --execution.concurrency.limit=-1 "select subnets.subnetwork, s2.proj from ( select ipCidrRange, subnetwork from google.container.\"projects.aggregated.usableSubnetworks\" where projectsId in ('testing-project', 'another-project', 'yet-another-project') order by subnetwork desc ) subnets inner join ( select ipCidrRange, subnetwork, split_part(subnetwork, '/', 2) as proj from google.container.\"projects.aggregated.usableSubnetworks\" where projectsId in ('testing-project', 'another-project', 'yet-another-project') order by subnetwork desc ) s2 on subnets.subnetwork = s2.subnetwork order by subnets.subnetwork desc ;"</msg>
<msg timestamp="20240111 04:10:14.462" level="INFO">Waiting for process to complete.</msg>
<msg timestamp="20240111 04:10:14.585" level="INFO">Process completed.</msg>
<msg timestamp="20240111 04:10:14.586" level="INFO"/>
<msg timestamp="20240111 04:10:14.586" level="INFO">sql insert error: 'database table is locked: google.container.projects.aggregated.usableSubnetworks.UsableSubnetwork.generation_1' from query: INSERT INTO "google.container.projects.aggregated.usableSubnetworks.UsableSubnetwork.generation_1" ("iql_generation_id" , "iql_session_id" , "iql_txn_id" , "iql_insert_id" , "iql_insert_encoded" , "ipCidrRange" , "network" , "secondaryIpRanges" , "statusMessage" , "subnetwork" , "projectsId" ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) </msg>
<msg timestamp="20240111 04:10:14.586" level="FAIL"> != |-----------------------------------------------------------------------------|---------------------|
| subnetwork | proj |
|-----------------------------------------------------------------------------|---------------------|
| projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-02 | yet-another-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-01 | yet-another-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/testing-project/regions/australia-southeast1/subnetworks/sn-02 | testing-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/testing-project/regions/australia-southeast1/subnetworks/sn-01 | testing-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/another-project/regions/australia-southeast1/subnetworks/sn-02 | another-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/another-project/regions/australia-southeast1/subnetworks/sn-01 | another-project |
|-----------------------------------------------------------------------------|---------------------|</msg>
<status status="FAIL" starttime="20240111 04:10:14.457" endtime="20240111 04:10:14.588"/>
</kw>
<kw name="Stackql Per Test Teardown" library="stackql" type="TEARDOWN">
<if>
<branch type="IF" condition=""${EXECUTION_PLATFORM}" == "docker" and "${SQL_BACKEND}" == "postgres_tcp"">
<kw name="Run Process" library="Process">
<var>${res}</var>
<arg>bash</arg>
<arg>\-c</arg>
<arg>docker kill $(docker ps \-\-filter name\=execrun \-q)</arg>
<doc>Runs a process and waits for it to complete.</doc>
<status status="NOT RUN" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</kw>
<kw name="Log" library="BuiltIn">
<arg>Container killed</arg>
<doc>Logs the given message with the given level.</doc>
<status status="NOT RUN" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</kw>
<kw name="Run Process" library="Process">
<var>${restwo}</var>
<arg>bash</arg>
<arg>\-c</arg>
<arg>docker rm $(docker ps \-\-filter status\=exited \-q)</arg>
<doc>Runs a process and waits for it to complete.</doc>
<status status="NOT RUN" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</kw>
<kw name="Log" library="BuiltIn">
<arg>Container removed</arg>
<doc>Logs the given message with the given level.</doc>
<status status="NOT RUN" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</kw>
<status status="NOT RUN" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</branch>
<status status="PASS" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</if>
<status status="PASS" starttime="20240111 04:10:14.588" endtime="20240111 04:10:14.589"/>
</kw>
<status status="FAIL" starttime="20240111 04:10:14.454" endtime="20240111 04:10:14.589"> != |-----------------------------------------------------------------------------|---------------------|
| subnetwork | proj |
|-----------------------------------------------------------------------------|---------------------|
| projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-02 | yet-another-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-01 | yet-another-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/testing-project/regions/australia-southeast1/subnetworks/sn-02 | testing-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/testing-project/regions/australia-southeast1/subnetworks/sn-01 | testing-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/another-project/regions/australia-southeast1/subnetworks/sn-02 | another-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/another-project/regions/australia-southeast1/subnetworks/sn-01 | another-project |
|-----------------------------------------------------------------------------|---------------------|</status>
</test>
The text was updated successfully, but these errors were encountered:
Background
sqlite
can throw errors of the formsql insert error: 'database table is locked:...
under concurrency. This has been observed in the aggressive concurrencyrobot
tests as per the below evidence.For some discussion on a related, probably non-identical issue, see this forum thread.
Acceptance Criteria
The brief for this work is to avoid or mitigate this scenario, with a fulsome explanation documented.
Evidence
From an aggressive concurrency
robot
test run:The text was updated successfully, but these errors were encountered: