[ERROR] [MY-013131] [Server] Out of sort memory, consider increasing server sort buffer size!
nneven
PROOP

2 years ago

Hi, I am getting this issue on MySQL database. I am unable to query it anymore and am not sure how to resolve it. Please help, thank you!

0 Replies

nneven
PROOP

2 years ago

c4aab2e0-3c93-4c07-b086-ac5d458bd1b1

nneven
PROOP

2 years ago

Should I try restarting or redploying my DB? What will happen to my data?

brody
EMPLOYEE

2 years ago

I would first upgrade to the hobby plan and then grow the volume

nneven
PROOP

2 years ago

I am on the Pro Plan

brody
EMPLOYEE

2 years ago

is your project located within a pro workspace

nneven
PROOP

2 years ago

Yes

brody
EMPLOYEE

2 years ago

what's the size of the attached volume

nneven
PROOP

2 years ago

1.46 GB

brody
EMPLOYEE

2 years ago

what's the total size

nneven
PROOP

2 years ago

50 GB

nneven
PROOP

2 years ago

max size?

nneven
PROOP

2 years ago

What do you suggest I do this is my prod db

brody
EMPLOYEE

2 years ago

it's completely offline?

nneven
PROOP

2 years ago

I am unable to connect or query to it

nneven
PROOP

2 years ago

 ⨯ PrismaClientUnknownRequestError: 
Invalid `prisma.dataRoom.findMany()` invocation:


Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1038, message: "Out of sort memory, consider increasing server sort buffer size", state: "HY001" })), transient: false })
    at DataRoomsPage (dashboard/page.tsx:49:23)
 ⨯ PrismaClientUnknownRequestError: 
Invalid `prisma.dataRoom.findMany()` invocation:
brody
EMPLOYEE

2 years ago

to get it temporarily back online, have you tried to redeploy?

nneven
PROOP

2 years ago

no

nneven
PROOP

2 years ago

will restarting or redeploying persist my data?

nneven
PROOP

2 years ago

I should probably restart before redeploy?

brody
EMPLOYEE

2 years ago

unless you have severaly misconfigured something, no your data should be in tact

nneven
PROOP

2 years ago

would it make sense to fork the whole environment

nneven
PROOP

2 years ago

before I try this

brody
EMPLOYEE

2 years ago

that doesnt copy any data

nneven
PROOP

2 years ago

as a backup?

nneven
PROOP

2 years ago

what can I do then to make sure it is backed up safely?

brody
EMPLOYEE

2 years ago

do you not take backups?

nneven
PROOP

2 years ago

no not externally

brody
EMPLOYEE

2 years ago

wdym externally?

nneven
PROOP

2 years ago

I guess should do that before I hit restart/redeploy?

nneven
PROOP

2 years ago

i just deployed a mysql db instance on railway with an attached volume

nneven
PROOP

2 years ago

i dont have backups beyond that

brody
EMPLOYEE

2 years ago

that means you dont have any backups, since railway does not do backups for you

brody
EMPLOYEE

2 years ago

go ahead and restart the database

nneven
PROOP

2 years ago

dont think I should create backup first?

nneven
PROOP

2 years ago

its a very vanilla deployment i didnt mess with config

brody
EMPLOYEE

2 years ago

you said you cant access the database?

nneven
PROOP

2 years ago

ok i will try restart

nneven
PROOP

2 years ago

hmm seems like I can access the DB from prisma studio but not the client sdk

nneven
PROOP

2 years ago

I'm gonna try and setup backups first before trying restart/redeploy and will come back if none of that fixes it, thanks for help thus far

brody
EMPLOYEE

2 years ago

i dont think fragly's suggested fix would persist between deploys

brody
EMPLOYEE

2 years ago

you can permanently double the sort buffer size by appending --sort_buffer_size=512K onto your mysql start command

nneven
PROOP

2 years ago

@Brody I backed up and restarted it, still getting same error

nneven
PROOP

2 years ago

Will redeploy persist / reload my data?

brody
EMPLOYEE

2 years ago

again, yes, unless you have severely missconfigured something

nneven
PROOP

2 years ago

ok will do, and wil try the sort buffer size command after if no luck

brody
EMPLOYEE

2 years ago

ideally you would write a more efficient query, but increasing the sort buffer size is alright

nneven
PROOP

2 years ago

yeah changing sort in my query also fixes it, thanks for the help 🙏

brody
EMPLOYEE

2 years ago

no problem

Loading...