Out of sort memory, consider increasing server sort buffer size (errno 1038) for Long Posts on PlanetScale Database about beam HOT 9 CLOSED

I was able to get it to work in TablePlus (Database GUI tool) by using the set sort_buffer_size=2097152 command.

from beam.

Should be fixed by #96. If you have an existing installation you'll have to update your database schema to add the index.

from beam.

I am facing this same issue

from beam.

@orware Do you have any insight into when PlanetScale will move to 8.0.28 or above? I'm not sure what our upgrade processes are.

from beam.

I reached out to support at Planetscale for this issue and this is what they sent me:

Hello Ansh!

Thank you for reporting this problem that you ran into on your end!

This issue has been something our users have been running into every so often and is generally triggered by JSON columns in the schema and is related to a MySQL bug one of the Engineers on our team submitted to MySQL back in October and should be included as part of MySQL 8.0.28 (https://bugs.mysql.com/bug.php?id=105304). (Another Stack Overflow thread that talks about the same sort of issue can be found here as well if interested: https://stackoverflow.com/questions/29575835/error-1038-out-of-sort-memory-consider-increasing-sort-buffer-size)

I think the only factor internally allowing it to still occur is that the PlanetScale platform is currently using MySQL 8.0.23 at the moment (I don't have an exact ETA as to when we will be updating to the current version of MySQL within our platform however but it likely won't be too long now since we've had the discussion about this issue a few times already and the problem was fixed recently in MySQL now).

If you have the option of running a SET query after you setup your connection on your end, you should be able to workaround the problem in the meantime by increasing the sort_buffer_size value dynamically for your current connection/session using something like the following:

set sort_buffer_size=2097152

It's definitely an odd problem, but I hope the workaround above does the trick for now (and we'll solve it more permanently once we deploy MySQL 8.0.28+ on the PlanetScale platform in the not too distant future).

Please let me know if this helps to resolve the issue for you!

from beam.

I'm also experiencing this issue, so I guess just go to console and run the command above? Tried it by going to planetscale console (yes, you can access console to the database directly in the dashboard, there is a page for it). The command executed successfully but didn't resolve the issue. What resolved it is by simply not select certain big JSON fields in the table which is not what we want.

from beam.

@pencilcheck,

The SET query currently would need to be integrated into the Beam code somewhere to execute right after connections are being established, rather than separately via the console, which would work only for that web console session, but not for the application more generally since it is a setting that would have to be adjusted for every connection/session.

One potential thought might be to add a line like the following:

await prisma.$executeRaw`set sort_buffer_size=2097152`;

Right after the line where the prisma variable is created within the prisma.ts file to achieve that.

from beam.

I tried, not sure how to tell if it works, since I'm still getting the same error

from beam.

@pencilcheck,

If you are also a PlanetScale user, you can file a ticket with us over at https://support.planetscale.com/ and I can work with you more closely on the issue and perform some extra troubleshooting together with your specific dataset.

from beam.