SQLite -> MySQL Transfer

Hi!
I’ve been trying to transfer from sv.db -> a MariaDB (MySQL) database on PHPMyAdmin.
First, I tried dumping the sqlite and importing it. Doesn’t work since the syntax between SQLite and MySQL is different (e.g. MANY errors such as AUTOINCREMENT becomes AUTO_INCREMENT, START TRANSACTION isn’t needed, all keys need length specified etc), and the data I am importing contains player names, with problems such as people ending their name with /, which makes the speech marks after their name be ignored and messes up all SQL after, and also people putting speech marks in their names.

I then tried the first 2 pages of google search results for tools to convert it. The only one I had any luck with was MySQL workbench, that failed at the end with ‘sqlite3.OperationalError: near “USE”: syntax error’

Any tried and tested solutions to upload from SQLite to MySQL?

(The only luck I have had is manually importing individual tables as .CSV, however this also breaks with some characters, and I have a LOT of tables to import by hand, having to rename them all manually after because PHPMyAdmin doesn’t take table name from the file. A way to loop through them, renaming them based on file name would be a nice solution too)

I just googled “sqlite to mysql” and there are plenty of tools available. Use one of them.

Like I said, I’ve tried everything on the first 2 pages of Google. All of them throw up errors/are paid/refuse to work. Furthest I got was with MySQL Workbench

The best way I can think of is continuing to try to import by using CSV, unless you want to write a script to do it. If you have any experience with Python, SQLAlchemy and Alembic would be exactly what you need. You can write a nice script that will do all of the conversions for you.

[editline]23rd August 2017[/editline]

Also using a professional database tool like DataGrip from Jetbrains might help but I’m not sure if they make you pay for it. I use it for my MySQL database and transferring between databases is super easy.

I’d do a PHP/Python script, but I only have access to the front-end PHPMyAdmin, and I couldn’t find any way to run anything but SQL queries.

Edit: Trying DataGrip

Spot on. You can also use http://sqlitebrowser.org/ to do it :slight_smile:

You may have to fiddle with the settings when exporting it as a CSV file.

SQLite browser was the first thing I tried, exporting as SQL doesn’t import right. CSV does, but importing each table manually would take a century, without a way to automate it via PHPMyAdmin

[editline]23rd August 2017[/editline]

DataGrip didn’t work. It’s the same kinda thing as trying to upload from a .sql file form SQLite Browser, little errors.
“BLOB/TEXT column ‘steamID’ can’t have a default value”

For this specific error, apparently editing the sql-mode= line will fix it, but I cant as I only have front end access to PHPMyAdmin. But thats besides the point, as there are many of these errors. See top post.
(In the UK, so I’m going offline for tonight. I’ll be back to check replies tomorrow morning)

Sadly, far as im aware, its the best way. Also you don’t have to import every table. Certain tables which are used by addons, the creators may have built a sql migration system which does it for you and you will find tables from addons you don’t used anymore which you won’t need.

Assuming you are using the DarkRP gamemode you only NEED to import the following tables for the players cash:


darkrp_player
playerinformation

You can also import things like if you don’t want to do them again in-game:


darkrp_doorjobs
darkrp_doorgroups
FPP_BLOCKED1
FPP_BLOCKEDMODELS1

but things like FAdmin settings can easily be done in game so there isn’t much point transferring a table across for it.

I advise you to look at contents of tables and make choices if you want to keep that data or not. It wouldn’t take that much time then!

Thanks, I’ll just get to work then doing the CSVs :slight_smile:

[editline]24th August 2017[/editline]

Nevermind, I still need help.
CSVs don’t work since they don’t import table settings, so things like auto_increment don’t work. This means IDs don’t work as they arent set automatically, and therefore the database isn’t working.

I still need a solution to import the database

You can setup the auto_increment manually though?

Yeah, but it’s not just that, it’s tons of small annoying errors.
Tomorrow morning, I’m gonna try manually generating the tables based off the existing settings, then importing CSVs to those tables, no guarantee I’ll be able to do it and come out sane though. Also, I’ll have to do it within a tight time frame since its on an active server.