🐛 Fixed migrations for SQLite database users (#19839)

[markstos]

SQLite has limit of 500 items in a compound select statement.

This limit could be hit when a complex select statement was being generated as part of a batch insert statement.

Lowering the batch size will have minimal impact on migration performance while improving SQLite compatibility.

One of these bulk inserts is confirmed to be affected through the linked issue. I did confirm if the other two cases would trigger it, but the change won't hurt there either.

Ref: https://www.sqlite.org/limits.html Issue: #19839

[github-actions[bot]]

It looks like this PR contains a migration 👀 Here's the checklist for reviewing migrations:

General requirements

• [ ] Satisfies idempotency requirement (both up() and down())
• [ ] Does not reference models
• [ ] Filename is in the correct format (and correctly ordered)
• [ ] Targets the next minor version
• [ ] All code paths have appropriate log messages
• [ ] Uses the correct utils
• [ ] Contains a minimal changeset
• [ ] Does not mix DDL/DML operations
• [ ] Tested in MySQL and SQLite

Schema changes

• [ ] Both schema change and related migration have been implemented
• [ ] For index changes: has been performance tested for large tables
• [ ] For new tables/columns: fields use the appropriate predefined field lengths
• [ ] For new tables/columns: field names follow the appropriate conventions
• [ ] Does not drop a non-alpha table outside of a major version

Data changes

• [ ] Mass updates/inserts are batched appropriately
• [ ] Does not loop over large tables/datasets
• [ ] Defends against missing or invalid data
• [ ] For settings updates: follows the appropriate guidelines

[stromfeldt]

Just thought I'd add that I ran into this problem when updating a local instance from 5.53.3 to 5.96.0, with about 600 posts. Changing the value in .../5.55/2023-07-10-05-16-55-add-built-in-collection-posts.js from 1000 to 450 didn't work, but when I changed it to 100, as @DigitalLeaves suggested, it then worked. Supposing I'm correctly following what I did.

[allouis]

The default for chunkSize for batchInsert is 1000 - but this PR only updates values where they've been set explicitly.

It seems like 450 isn't low enough to fix the problem either!

I'm going to close this PR as it doesn't seem particularly active - if someone wants to fix it, then please check out the original issue where I'll leave some more information.

[allouis]

@markstos I wasn't sure if you have permissions to reopen this - so I've done it for you!

[markstos]

Commit pushed to lower batch size to 100.