User profiles on SQLite: four storage bugs
With full profiles turned on ([UserProfiles] ProfileServiceURL and the local [UserProfilesService]), a SQLite
standalone stores picks, classifieds, notes, interests and the About text correctly, but four things fail in
SQLiteUserProfilesData and its migration, all SQLite-only (the MySQL and PostgreSQL code is right):
| # | where | the bug | what a user sees |
|---|---|---|---|
| 1 | Resources/UserProfiles.migrations, version 3 |
the CREATE TABLE usersettings (...) statement has no ; before commit;, so the statement is a syntax error; the migration logs it and records version 3 anyway, so the usersettings table is never made and never retried |
every login logs [PROFILES_DATA]: Get preferences exception; the privacy preferences (IM to email, online status) are never stored |
| 2 | GetUserPreferences |
the parameter is added as ?Id while the query names :Id; the INSERT for a user with no row never gets its CommandText; the binary(1) flags are read with (string) casts |
even with the table present, the first request fails ("SQLite error") and nothing is stored |
| 3 | DeleteClassifiedRecord |
the query names :ClasifiedId (one s), the parameter is :ClassifiedId |
deleting a classified fails; it stays in the profile |
| 4 | GetUserAppData / SetUserAppData |
the INSERT for a new tag is appended to the SELECT text and never set as the command, its first parameter is :Id where the INSERT says :UserId; the UPDATE stores DataKey as the value |
the per-user application data store fails (upstream's UserProfilesService.SetUserAppData is a stub that stores nothing, so this path is reached only through the data layer) |
The fix (../sqlite-profiles.diff, two files):
UserProfiles.migrations: version 3 gets its;, and a new version 4 runs the sameCREATE TABLE IF NOT EXISTS usersettings, so a database already stuck at version 3 without the table gets it.SQLiteUserProfilesData.cs::ClassifiedId;:Idand the missingCommandTextinGetUserPreferences, which now reads the flags withCAST(... AS TEXT)and a smallFlagIsSethelper (the INSERT stores'false', a bound bool is stored as1/0; both read correctly); the INSERT command and:UserIdinGetUserAppData;DataValinSetUserAppData.
Written 2026-09-27 while turning profiles on for Paracosm (ship plan "profiles decided").
Apply to a fresh opensim-src
build/build_server.py applies it with the rest of the series. By hand: patch -p1 < sqlite-profiles.diff at the
tree root, then build OpenSim.Data.SQLite.csproj (no Prebuild run is needed: no file is added). The DLL is
bin/OpenSim.Data.SQLite.dll; swap it in during a restart. Both files are upstream's with CRLF line endings, and
the diff carries them.
Config (config/paracosm.ini and config/StandaloneCommon-overlay.ini write it)
; bin/OpenSim.ini
[UserProfiles]
ProfileServiceURL = "http://127.0.0.1:9000" ; the simulator's own HTTP port (PARACOSM_PORT)
; bin/config-include/StandaloneCommon.ini
[UserProfilesService]
Enabled = true
ConnectionString = "URI=file:profiles.db,version=3"
Tested (2026-09-27, offline; no OpenSim process was started)
Built with the whole series into a scratch folder:
- Before the patch, against upstream's DLL: the migration logged the syntax error and left no
usersettingstable;UserPreferencesRequest,ClassifiedDeleteandRequestUserAppDatafailed. - After: through
UserProfilesServiceon a freshprofiles.db, every call passed: properties (request, update, read back), interests, notes, picks, classifieds (add, list, info, delete, gone), preferences (request creates the row, update, read back), app data (data layer), image assets. A database copied from the unpatched run (version 3, no table) was upgraded to version 4 and its preferences then worked. - End to end over HTTP: the config as
tools/make_config.pywrites it, loaded by OpenSim'sConfigurationLoader;LocalUserProfilesServicesConnectorregistered its JSON-RPC methods on a test HTTP server (127.0.0.1:19417), and OpenSim'sJsonRpcRequestManager(the clientUserProfileModuleuses) calledavatar_properties_request,avatar_properties_update,user_preferences_request,user_preferences_update,avatar_notes_updateandavatarnotesrequest: all answered, and every value read back.
Not tested: a viewer's profile window against a running region.