malformed SQL statement error during block migration

[belforte]

Impact of the bug
CRAB

Describe the bug
a block can not be migrated from global to phys03 due to "malformed SQL statement"

How to reproduce it
try again to migrate block

/QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v4/GEN-SIM-RAW#32b9e041-56b8-4ca1-ab62-42865ba17568

from 'prod/global' to 'prod/phys03'.
May need to remove any existing migration for that block. E.g. along the lines of https://github.com/dmwm/CRABServer/blob/master/scripts/Utils/RemoveFailedMigration.py

Expected behavior
blocks should always be migrated w/o errors

Additional context and error message
from /cephfs/product/dbs-logs/dbs2go-phys03-migration-644c89d6cb-rvbn9.log-20260424 on vocms0755

[2026-04-24 07:46:02.065996318 +0000 UTC m=+70695.377961860] bulkblocks2.go:933: 8be5afa72544a45a50ec6d241b49ec09939a1f7b1eca9fcc848f6a5ad4
fe26e4 unable to find dataset_id for /QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26GS-150X_mcRun3_2026_realistic
_v2-v4/GEN-SIM, error DBSError Code:103 Description:DBS DB query error, e.g. mailformed SQL statement Function:dbs.GetID Message: Error: sq
l: no rows in result set
[2026-04-24 07:46:02.385482271 +0000 UTC m=+70695.697447803] migrate.go:1116: insert bulkblocks for mid 6503521 error DBSError Code:131 Des
cription:Not defined Function:dbs.bulkblocks.InsertBulkBlocksConcurrently Message:8be5afa72544a45a50ec6d241b49ec09939a1f7b1eca9fcc848f6a5ad
4fe26e4 unable to find dataset_id for /QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26GS-150X_mcRun3_2026_realisti
c_v2-v4/GEN-SIM, error DBSError Code:103 Description:DBS DB query error, e.g. mailformed SQL statement Function:dbs.GetID Message: Error: s
ql: no rows in result set Error: nested DBSError Code:103 Description:DBS DB query error, e.g. mailformed SQL statement Function:dbs.GetID 
Message: Error: sql: no rows in result set
[2026-04-24 07:46:02.385977374 +0000 UTC m=+70695.697942906] migrate.go:1119: insert block dump record failed with DBSError Code:131 Descri
ption:Not defined Function:dbs.bulkblocks.InsertBulkBlocksConcurrently Message:8be5afa72544a45a50ec6d241b49ec09939a1f7b1eca9fcc848f6a5ad4fe
26e4 unable to find dataset_id for /QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26GS-150X_mcRun3_2026_realistic_v
2-v4/GEN-SIM, error DBSError Code:103 Description:DBS DB query error, e.g. mailformed SQL statement Function:dbs.GetID Message: Error: sql:
 no rows in result set Error: nested DBSError Code:103 Description:DBS DB query error, e.g. mailformed SQL statement Function:dbs.GetID Mes
sage: Error: sql: no rows in result set
[2026-04-24 07:46:02.3862927 +0000 UTC m=+70695.698258232] migrate.go:1378: update migration request 6503521 to status 3

The first message unable to find dataset_id is strange/worrying, since

belforte@lxplus802/Pub> dasgoclient --query 'dataset dataset= /QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26GS-150X_mcRun3_2026_realistic_v2-v4/GEN-SIM' --json|jq|grep dataset_id

        "dataset_id": 15535052,
        "dataset_id": 15535052,
        "dataset_id": 15535052,
belforte@lxplus802/Pub> 
belforte@lxplus802/Pub> 

[belforte]

For your convenience here's the status of the migration in previous log, from

status = apiMig.statusMigration(migration_rqst_id=migrationId)
pprint(status)

where apiMig.url is https://cmsweb-prod.cern.ch:8443/dbs/prod/phys03/DBSMigrate

[{'create_by': '/DC=ch/DC=cern/OU=Organic '
               'Units/OU=Users/CN=cmscrab/CN=373708/CN=Robot: cms crab',
  'creation_date': 1777016728,
  'last_modification_date': 1777016728,
  'last_modified_by': '/DC=ch/DC=cern/OU=Organic '
                      'Units/OU=Users/CN=cmscrab/CN=373708/CN=Robot: cms crab',
  'migration_input': '/QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v4/GEN-SIM-RAW#06b5dfa1-a4d1-40de-9263-4cf649a85963',
  'migration_request_id': 6503521,
  'migration_server': 'dbs2go-phys03-migration-644c89d6cb-rvbn9',
  'migration_status': 9,
  'migration_url': 'https://cmsweb-prod.cern.ch:8443/dbs/prod/global/DBSReader',
  'retry_count': 4}]

[belforte]

here's another instance, which is the underlying reason for recent user problem report https://cms-talk.web.cern.ch/t/publication-failure/144394?u=belforte
this time the complain includes the unable to find parent LFN

from dbs2go-phys03-migration-694ff69b48-tmsd4.log-20260426

[2026-04-26 10:19:25.05700834 +0000 UTC m=+356277.744188869] logging.go:230: HTTP/1.1 200 GET /dbs/prod/phys03/DBSMigration/metrics [data: 0 in 10249 out] [remoteAddr: 10.100.164.6:56506] [X-Forwarded-For: ] [X-Forwarded-Host: ] [auth: no-TLS cipher-none "no-auth-cert" no-auth-login no-auth-method] [ref: "-" "Prometheus/2.1.0"] [req: 1.007601404s proxy-resp: 0]
[2026-04-26 10:19:26.339042972 +0000 UTC m=+356279.026223517] migrate.go:1116: insert bulkblocks for mid 6505993 error DBSError Code:101 Description:DBS DB error Function:dbs.bulkblocks.InsertBulkBlocksConcurrently Message:unable to find parent lfn /store/mc/Run3Winter26Digi/GJ-4Jets_Bin-HT-400to600-PTG-10to100_Par-dRGJ-0p25_TuneCP5_13p6TeV_madgraphMLM-pythia8/GEN-SIM-RAW/150X_mcRun3_2026_realistic_v4-v3/1210015/74917419-58da-4700-b67b-1e90a5ee6934.root Error: nested DBSError Code:103 Description:DBS DB query error, e.g. mailformed SQL statement Function:dbs.GetID Message: Error: sql: no rows in result set
[2026-04-26 10:19:26.339658259 +0000 UTC m=+356279.026838788] migrate.go:1119: insert block dump record failed with DBSError Code:101 Description:DBS DB error Function:dbs.bulkblocks.InsertBulkBlocksConcurrently Message:unable to find parent lfn /store/mc/Run3Winter26Digi/GJ-4Jets_Bin-HT-400to600-PTG-10to100_Par-dRGJ-0p25_TuneCP5_13p6TeV_madgraphMLM-pythia8/GEN-SIM-RAW/150X_mcRun3_2026_realistic_v4-v3/1210015/74917419-58da-4700-b67b-1e90a5ee6934.root Error: nested DBSError Code:103 Description:DBS DB query error, e.g. mailformed SQL statement Function:dbs.GetID Message: Error: sql: no rows in result set
[2026-04-26 10:19:26.339997464 +0000 UTC m=+356279.027178005] migrate.go:1378: update migration request 6505993 to status 3

[vkuznet]

All SQL error messages comes from missing part of injection payload. Due to nested and very complex constrains it is hard to pin point actual issue but the code tries very hard to show that. In last posted message the issue is clear that parent LFN is not found and therefore somewhere in a chain of SQL foreign constrains we can't obtain the id of required piece, i.e. in this case the parent LFN. My advice is to carefully look into nested error message to understand the underlying issue. In originaly reported error message it is unable to find dataset_id for /QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26GS-150X_mcRun3_2026_realistic_v 2-v4/GEN-SIM which means that in order to migrate original dataset/block this dataset should be present, etc.

[belforte]

thanks @vkuznet
in last example the messge is
unable to find /store/mc/Run3Winter26Digi/GJ-4Jets_Bin-HT-400to600-PTG-10to100_Par-dRGJ-0p25_TuneCP5_13p6TeV_madgraphMLM-pythia8/GEN-SIM-RAW/150X_mcRun3_2026_realistic_v4-v3/1210015/74917419-58da-4700-b67b-1e90a5ee6934.root
Indeed that file is no present in phys03

belforte@lxplus802/Pub> dasgoclient -query 'file file=/store/mc/Run3Winter26Digi/GJ-4Jets_Bin-HT-400to600-PTG-10to100_Par-dRGJ-0p25_TuneCP5_13p6TeV_madgraphMLM-pythia8/GEN-SIM-RAW/150X_mcRun3_2026_realistic_v4-v3/1210015/74917419-58da-4700-b67b-1e90a5ee6934.root instance=prod/phys03' --json
[
]
belforte@lxplus802/Pub> 

Looking in global, it belongs to block

belforte@lxplus802/Pub> dasgoclient -query 'block file=/store/mc/Run3Winter26Digi/GJ-4Jets_Bin-HT-400to600-PTG-10to100_Par-dRGJ-0p25_TuneCP5_13p6TeV_madgraphMLM-pythia8/GEN-SIM-RAW/150X_mcRun3_2026_realistic_v4-v3/1210015/74917419-58da-4700-b67b-1e90a5ee6934.root'
/GJ-4Jets_Bin-HT-400to600-PTG-10to100_Par-dRGJ-0p25_TuneCP5_13p6TeV_madgraphMLM-pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v3/GEN-SIM-RAW#8e176fa1-1ea4-4783-8123-f4d50321535e

That block was not migrated to phys03

    
belforte@lxplus802/Pub> dasgoclient -query 'block block=/GJ-4Jets_Bin-HT-400to600-PTG-10to100_Par-dRGJ-0p25_TuneCP5_13p6TeV_madgraphMLM-pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v3/GEN-SIM-RAW#8e176fa1-1ea4-4783-8123-f4d50321535e instance=prod/phys03' --json
[
]

I can not say if the message Message:unable to find parent lfn refers to "not found in phys03" (which is true) or
"not found in global" (which is false).

That dataset is present in phys03, but with only 47 out of 55 blocks.

belforte@lxplus802/Pub> dasgoclient -query 'block dataset=/GJ-4Jets_Bin-HT-400to600-PTG-10to100_Par-dRGJ-0p25_TuneCP5_13p6TeV_madgraphMLM-pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v3/GEN-SIM-RAW instance=prod/phys03'|wc
     47      47    8789
belforte@lxplus802/Pub> dasgoclient -query 'block dataset=/GJ-4Jets_Bin-HT-400to600-PTG-10to100_Par-dRGJ-0p25_TuneCP5_13p6TeV_madgraphMLM-pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v3/GEN-SIM-RAW instance=prod/global'|wc
     55      55   10285
belforte@lxplus802/Pub> 

I agree that debugging migration inside DBS code can be difficult, anyhow surely it not something I can do.
Maybe it may help to have a some utilities which can be fed failed migration ID's and retries them doing all operations
sequentially rather than concurrently ? It may take time of course, but could me more solid and as long as this is rare
give us the needed crutch to limp along waiting for a "no migrations anymore" future ?

[belforte]

A drastic solution would be to jump ahead into the future:

• allow CRAB credential to write into production DB, so we publish USER dataset w/o migration

That's surely an easy change on CRAB side.

If we do that users will not be able to invalidate/re-validate files/datasets anymore.
That could be addressed in at least 4 different ways:

1. change current CRABClient crab set[file|dataset]status commands to be requests to CRAB server who can do the action using CRAB credential after validation that dataset tier is USER.
   • not easy. Since all CRAB client/server communications are based on a task name which will be missing here, it will be a ugly hack. I am not particularly happy of adding such a new feature to an obsolescent tool
2. users bug DM operators when they want to change status of user files. I can easily imagine operators not being happy.
3. could "restrict identities outside prod group to act on USER datasets/files only " be done in DBS2GO ?
4. another client/server product which can be leveraged for this

Note: this may require small tweaks to DBS/Rucio syncronization process

Since, IIUC, we want to have USER datasets in global DBS in "Run4+" i.e. with new WMS tool, spending a bit of effort in clarifying the requirements and identifying the road to be traveled, may be a good thing to do for the WMS team anyhow. And if something can be tested/used early.. why not ?

[belforte]

Anyhow, for you convenience in further debugging block migrations, here's an example.
This block has no parents (IMHO this is fine for a GEN-SIM-RAW) and contains 500 files. But failed to migrate to phys03
/QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v4/GEN-SIM-RAW#06b5dfa1-a4d1-40de-9263-4cf649a85963

Looking at one random file in that block, it has just 1 lumi. So it looks a perfectly normal block to me.

OTOH, DAS Web UI indicates that this GEN-SIM-RAW has a parent dataset (!!!) [1]. I do not know how parentage for datasets is tracked in DBS, I thought that only files have parents and info if propagated from files to blocks at query time. In CRAB code I was told (by Yuyi and Manuel at the time) to build the list of parent blocks to be migrated by checking parents for each file.

So maybe parent migration fails because there's confusion about parents in DBS to begin with ?

[1]
https://cmsweb.cern.ch/das/request?instance=prod/global&input=parent+dataset%3D%2FQCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8%2FRun3Winter26Digi-150X_mcRun3_2026_realistic_v4-v4%2FGEN-SIM-RAW

I am confused since dasgoclient can't reproduce that, so am not sure what the parent keyword means here

]
belforte@lxplus802/Pub> dasgoclient -query 'parents dataset=/QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v4/GEN-SIM-RAW'       
/QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v4/GEN-SIM-RAW
belforte@lxplus802/Pub>

---

Should it help, I just created a new migration request for this block with ID 6506785
which just failed with
unable to find dataset_id for /QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26GS-150X_mcRun3_2026_realistic_v2-v4/GEN-SIM

dbs2go-phys03-migration-694ff69b48-tmsd4.log-20260427:[2026-04-27 09:59:00.317380361 +0000 UTC m=+13601.948880446] migrate.go:1116: insert bulkblocks for mid 6506785 error DBSError Code:131 Description:Not defined Function:dbs.bulkblocks.InsertBulkBlocksConcurrently Message:8be5afa72544a45a50ec6d241b49ec09939a1f7b1eca9fcc848f6a5ad4fe26e4 unable to find dataset_id for /QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26GS-150X_mcRun3_2026_realistic_v2-v4/GEN-SIM, error DBSError Code:103 Description:DBS DB query error, e.g. mailformed SQL statement Function:dbs.GetID Message: Error: sql: no rows in result set Error: nested DBSError Code:103 Description:DBS DB query error, e.g. mailformed SQL statement Function:dbs.GetID Message: Error: sql: no rows in result set

That dataset is in DBS global and its dataset_id is 15535052.

[vkuznet]

@belforte , when we talk about DBS let's address DBS APIs, DAS is an intermediate layer between user and DBS. If you or others need to know how DAS queries DBS APIs simply supply -verbose 1 option to DAS CLI. So, the parents api is done through this datasetparents API:

dasgoclient -query="parent dataset=/QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v4/GEN-SIM-RAW" -verbose 1
...
https://cmsweb.cern.ch:8443/dbs/prod/global/DBSReader/datasetparents/?dataset=/QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v4/GEN-SIM-RAW

There are three level of parentage in DBS, at datasets, blocks, and files level and as you can imaging during migration (I think) all of them are checked due to various DB foreign keys constrains.

[belforte]

Thanks for clarification @vkuznet
It comes out that I used --query 'parents <dataset>' instead of --query 'parent <dataset>' (an extra s at the end of parent). I was misled by the parents button in DAS WEB UI. My bad.
Sorry for noise.

So parentage is clear. Why the parent dataset_id could not be found by migraion server is a different story.

Since it is rare, but in this case reproducible, I wonder if this could be due to some problem in how dataset was first inserted in global DBS, rather than something in migration code.

I.e. the fact that there is a parent at dataset level, but not at file level is worrying, isn't it ?

belforte@lxplus922/~> curl -s --cert $X509_USER_PROXY  --key $X509_USER_PROXY  https://cmsweb.cern.ch:8443/dbs/prod/global/DBSReader/files?logical_file_name=/store/mc/Run3Winter26Digi/QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/GEN-SIM-RAW/150X_mcRun3_2026_realistic_v4-v4/2830024/134ea9e8-fe51-4ca5-8bb2-73ebd434984c.root|jq
[
  {
    "logical_file_name": "/store/mc/Run3Winter26Digi/QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/GEN-SIM-RAW/150X_mcRun3_2026_realistic_v4-v4/2830024/134ea9e8-fe51-4ca5-8bb2-73ebd434984c.root"
  }
]
belforte@lxplus922/~> curl -s --cert $X509_USER_PROXY  --key $X509_USER_PROXY  https://cmsweb.cern.ch:8443/dbs/prod/global/DBSReader/fileparents?logical_file_name=/store/mc/Run3Winter26Digi/QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/GEN-SIM-RAW/150X_mcRun3_2026_realistic_v4-v4/2830024/134ea9e8-fe51-4ca5-8bb2-73ebd434984c.root|jq
[]

Maybe some WMA bug where some block(s) in GEN-SIM-RAW where inserted w/o file parentage pointing to GEN-SIM ? But parent name was put in there at dataset level ? I did not check all blocks in that dataset.

If parentage information in global is incomplete/inconsistent, it may explain why the migration code gets confused when trying to automatically find the parents for the blocks and the message about not finding dataset_id could not be
perfect.

Could we somehow insert the GEN-SIM-RAW in phys03 without parentage ? i.e. simply replicate the situation from global ?

I currently have the prejudice that if the dataset internals are fixed in global, then migration will work. But do now know if there's a way. Recovering file parentage is likely impossible. Deleting parent dataset info is appealing, but maybe there's some usefulness in having that relationship recorded.

[vkuznet]

@belforte , of course it should be investigated, but it is no longer my responsibility. One possible scenario I could think of is invalidation of data (e.g. files) once dataset was published. This may explain why parentage still exists at dataset level but not at a file level.I also can not exclude a possibility of some issue during WMS/DBS interaction.

[belforte]

I have verified that all blocks in /QCD_Bin-PT-15to7000_Par-PT-flat2022_TuneCP5_13p6TeV_pythia8/Run3Winter26Digi-150X_mcRun3_2026_realistic_v4-v4/GEN-SIM-RAW have an empty parent list via direct HTTP API query

for b in $BL; do
curl   --cert $X509_USER_PROXY  --key $X509_USER_PROXY   https://cmsweb.cern.ch:8443/dbs/prod/global/DBSReader/blockparents?block_name=$b
done

where $BL is the list of url-quoted block names (i.e. with # --> %23)

Then (details spared, hope you can trust me @todor-ivanov ) in the same way I fetched the list f the 28517 files in that dataset and asked DBS for the parent files.
All of them returned an empty list.

IMHO there are only 2 ways out:

1. declare that this is a valid situation and change migration code to cope
2. declare that this is not valid, and fix it somehow in global, and figure out what it happened so that it does not happen again. I do not care if it was in WMA, DBS or whatever.

The current practice of doing nothing is not good because recurrently put CRAB Publisher in troubles, creates extra-work for me, and let users confused and possibly frustrated and unhappy.

Thanks for the hint Valentin, but those files are still valid. I know that it is not your responsibility anymore, but it is not mine either. Shall we get @anpicci and @klannon involved ? What exactly is support for DBS currently if clear problems are I do not want to say "fixed", but no even "investigated" ?

Seems like I am the only person in CMS who tries to read DBS logs, and I am not only not expect to work in DBS,, but not expected to work at all !

Is anybody anywhere interested in the dataset parentage for that GEN-SIM-RAW dataset ? Is this a cute feature of StepChain processing ?

[todor-ivanov]

HI @belforte I am looking into the situation right now

[belforte]

@todor-ivanov here's another dataset which apparently has dataset-level parents in global, but lacks parents at block or file level. And that (of course) fails to migrate to phys03
/DisplacedSUSY_stopToLD_M_2200_30mm_TuneCP5_13TeV_madgraphMLM-pythia8/RunIISummer20UL18MiniAODv2-106X_upgrade2018_realistic_v16_L1v1-v2/MINIAODSIM