Migration error:

[evelant]

Bug report

• [X ] I confirm this is a bug with Supabase, not with my own application.
• [ X] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

When attempting to supabase db reset my migration to set up my initial schema (generated with supabase db dump) fails with

Resetting database...
Initialising schema...
Applying migration 20230426000000_initial_schema.sql...
Error: bufio.Scanner: token too long
After statement 214:
GRANT USAGE ON SCHEMA "public" TO "service_role";

Potenitally related issue (and PR illustrating fix) here: influxdata/influxdb#19586 influxdata/influxdb#19662

It seems that there's a default configuration in whatever golang library that's being used where it errors if a single line is greater than 64kb? I'm not really familiar with go but it seems this might be an easy fix.

To Reproduce

supabase db dump -f supabase/migrations/20230401000000_initial_schema.sql on a project with a large schema
supabase db reset to reset local db and apply the schema
observe failure with Error: bufio.Scanner: token too long

Expected behavior

Migrations of any size should work, especially those generated with supabase db dump

Screenshots

N/A

System information

• OS: macOS
• Browser (if applies) N/A
• Version of supabase-cli: 1.49.4 (can't use later versions yet due to v1.50.4 unusable due to edge functions timeout while caching deps #1012)
• Version of Node.js: 16.19.0

Additional context

[evelant]

More detail -- the issue is caused by thousands of lines of comments inserted into the .sql file by supabase db dump. There are many thousands of lines of commented out grant statements in the file just after the point where it fails GRANT USAGE ON SCHEMA "public" TO "service_role";. There are a few more statements near the end of the file after the thousands of lines of comments.

If I remove the thousands of lines of comments the migration can run. I'm not sure why db dump is generating a file that's 80% comments but that's the behavior that's triggering this issue.

[evelant]

This bug also extends to seed.sql in cases where a row has a very long JSON value. If you've got a value that's above whatever the limit is it fails with the same Error: bufio.Scanner: token too long

[sweatybridge]

I've opened a PR to address the documentation issue for SUPABASE_SCANNER_BUFFER_SIZE env var. You can set it to a higher value in the current CLI version to increase the limit.

The comments in pg_dump are indeed unnecessary. They will be removed by default starting from the next minor release. If anyone would like to keep them, they may pass in --keep-comments flag to db dump.

[evelant]

@sweatybridge It seems fairly easy to trigger this problem, is there a reason not to make the default size larger? Even if it's documented it will still likely confuse a lot of people.

[sweatybridge]

I'd imagine the right default size depends on the use case. The last time we increased to 256KB was to handle storing of geographical data.

Perhaps a nicer way to handle this error is to programmatically double the buffer size and retry up to a certain upper bound. Once the upper bound is determined, users can save the env var under .env file of their project folder. CLI will be able to pick up the config and skip future retries.

[sweatybridge]

I've updated default max buffer size to be the length of migration file. This should handle most common use cases.

[evelant]

Thank you yet again for delivering a great quick fix!