related bug at createrepo_c 
https://github.com/rpm-software-management/createrepo_c/issues/220

I've looked into this and it really looks like a hard limit on path length in the sqlite code. I guess we can do 2 things about this :
- Setup an explicit error when this path length is used (saying we don't support this, sadly)
- Trying to push an improvement to sqlite to remove this limitation

https://github.dev/sqlite/sqlite/blob/master/src/os_unix.c#L179-L180 :
#define MAX_PATHNAME 512

This looks quite low...

I can reproduce this limitation with the "sqlite3" CLI tool :
 T="aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/aaaaaaaaa/"
 # T is 480 char long
 sqlite3 t.db 'create table tbl(x int);' # create t.db
 mkdir -p "$T" # create long named directory

 sqlite3 t.db .dump # Opens OK
 cp t.db $T/
 sqlite3 $T/t.db .dump # fails to open
 Error: unable to open database "aaaaaaaaa/[..snip]/aaaaaaaaa//t.db": unable to open database file

=> This confirms this bug is a limitation of sqlite

Some elements discussed during the "Yocto Project Technical Team Meeting / Engineering Sync":
* SQlite ticket about this limit closed as "Won't fix" : https://sqlite.org/cgi/src/tktview/c060923a5422590b3734
* meta/classes-global/sanity.bbclass contains a Sanity test:
  * status.addresult(check_path_length(tmpdir, "TMPDIR", 410))
* My guess is that bitbake tries to open a sqlite database *before* reaching the sanity.bbclass tests (Will check)
* Maybe a trick where we exchange path length between CWD and "relative path" ? (Did not find it at first, will try further)
* We may need to put this check in bitbake itself

> Some elements discussed during the "Yocto Project Technical Team Meeting /
> Engineering Sync":
> * meta/classes-global/sanity.bbclass contains a Sanity test:
>   * status.addresult(check_path_length(tmpdir, "TMPDIR", 410))
> * My guess is that bitbake tries to open a sqlite database *before* reaching
> the sanity.bbclass tests (Will check)

Confirmed, bitbake does connect to the sqlite database before the sanity.bbclass checks.
Note: those checks are not made every time (remove cache/sanity_info to force this sanity check)

 $ rm cache/sanity_info ; bitbake -p
NOTE: Connecting to self.cachefile='.../cache/bb_persist_data.sqlite3' (len(self.cachefile)=109)
# bitbake/lib/bb/persist_data.py:103
NOTE: Checking path length of TMPDIR : tmpdir='.../tmp' len(tmpdir)=83 vs 410
# => sanity.bbclass:735
Loading cache: 100% |##########################################################################################################################################################| Time: 0:00:00
Loaded 2897 entries from dependency cache.

Backtrace of the call that happens before the sanity check :
NOTE: bb.persist_data.persist called here : .../poky/bitbake/lib/bb/fetch2/__init__.py:501 (fetcher_init)
NOTE: backtrace = 
NOTE:     File ".../poky/bitbake/bin/bitbake-server", line 54, in <module>
    bb.server.process.execServer(lockfd, readypipeinfd, lockname, sockname, timeout, xmlrpcinterface, profile)

NOTE:     File ".../poky/bitbake/lib/bb/server/process.py", line 636, in execServer
    server.run()

NOTE:     File ".../poky/bitbake/lib/bb/server/process.py", line 153, in run
    ret = self.main()

NOTE:     File ".../poky/bitbake/lib/bb/server/process.py", line 274, in main
    self.command_channel_reply.send(self.cooker.command.runCommand(command, self))

NOTE:     File ".../poky/bitbake/lib/bb/command.py", line 90, in runCommand
    result = command_method(self, commandline)

NOTE:     File ".../poky/bitbake/lib/bb/command.py", line 292, in updateConfig
    command.cooker.updateConfigOpts(options, environment, cmdline)

NOTE:     File ".../poky/bitbake/lib/bb/cooker.py", line 536, in updateConfigOpts
    self.reset()

NOTE:     File ".../poky/bitbake/lib/bb/cooker.py", line 1783, in reset
    self.initConfigurationData()

NOTE:     File ".../poky/bitbake/lib/bb/cooker.py", line 393, in initConfigurationData
    self.databuilder.parseBaseConfiguration()

NOTE:     File ".../poky/bitbake/lib/bb/cookerdata.py", line 272, in parseBaseConfiguration
    bb.fetch.fetcher_init(self.data)

sqlite (either CLI tool or sqlite3.connect()) fails for absolute path >=505 chars.
persist_data.py opens "${TOPDIR}/cache/bb_persist_data.sqlite3".
len("/cache/bb_persist_data.sqlite3") = 30
=> this does limit $TOPDIR to 474 chars (which is above the sanity check at ~410 btw)

I've sent a patch [1] handling the error thrown by sqlite to display clear error message instead of the stacktrace...

[1]: https://lore.kernel.org/bitbake-devel/20230111223729.3472181-1-yoann.congal@smile.fr/

V2 here (changed python string formating method) :
https://lore.kernel.org/bitbake-devel/20230112193255.38994-1-yoann.congal@smile.fr/

Fixed by https://git.yoctoproject.org/poky/commit/?id=e1a661e3d2d4d62aa0c47c2abb0a6824e1b17ede

=> Print a nice error message instead of a backtrace

FYI : The new SQlite post about this limit is here https://sqlite.org/forum/forumpost/0b1b8b5116 (no answer as of now)