Import data SSH

[mwerder]

Hi,

Is there a possibility to manually import an sql dump?

I think I can't use the import tool as it cannot connect using SSH, right?
We got a Ubuntu machine running a mariadb docker container.

Thanks in advance

[orware]

Hello @mwerder,

This should be possible if you already have the SQL dump file handy.

Typically what we recommend in these cases is to use our pscale CLI and the shell command to get to a MySQL shell for your PlanetScale database first.

Once you have a MySQL shell open, you can then use a command like the following one to import your dump file into your PlanetScale database:

source <YOUR_DUMP_FILE>.sql;

I've also been able to import dump files via MySQL GUI clients like DBeaver too.

When importing dump files there are a few caveats to be aware of as sometimes the dump file may have everything wrapped in a START TRANSACTION; / COMMIT; transaction which will result in the import timing out if it takes more than 20 seconds to complete due to our 20 second transaction timeout limit so you will want to make sure those are removed prior to beginning an import.

Separately, there may be a few other types of output that are sometimes included at the beginning of a dump file that may need to be removed if they seem to be resulting in issues (e.g. GTID coordinate information and other metadata that may not be supported).

You could also run into an issue during import due to specific unsupported features such as the one we have related to foreign key constraints, but if you aren't using those currently you shouldn't run into that type of issue.

In most cases though importing a dump file should go fairly smoothly so I hope the above information is helpful!

[orware]

Please note that my comment above about foreign key constraints not being available has changed since we do now support them:
https://planetscale.com/docs/concepts/foreign-key-constraints

[mwerder]

Wow you're a life savior @orware
I didn't think about using a GUI client but it worked like a charm!
Even though it was an SQL dump from our MariaDB, the only problem was the foreign key constraints as you mentioned. I deleted them for now.
Need to do a lot of testing but I think I'll get there.
Thank you so much!