• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

SQLite Convert SQLite to MySQL Database (With Pictures!)

I

Icy

Guest
Well to start this off I'll just say that I was trying to import my sqlite database into a MySQL database and had no idea. Well after many hours, sweat & tears etc... I've come up with an answer! :p

1. Download Firefox if you don't have it; Firefox Browser | Free ways to customize your Internet

jv0ht5.jpg



2. Download this sql2mysql converter addon; https://addons.mozilla.org/en-US/firefox/addon/5817
2qaigd2.jpg



3. When it's done installing, restart firefox, then go to Tools, "SQLite Manager"
26449c8.jpg



4. The program will open up, you should click on the "Connect Database" icon (looks like a folder).
2m5i539.jpg



5. Select and find your previous SQLite database (make sure you enable "all files" searching!)
6q8wn5.jpg



6. Go to "File", "Database" and select "Export Database" and save it in the same location, but with a ".sql" filetype.


7. Now I suggest downloading "Notepad++" (found here; Notepad++ ) to edit your server, but "Notepad" works fine as well.


8. Open your .sql server file that you just exported in notepad, notepad++ or whatever other text editor you have and use the "Find & Replace" feature (default hotkey is CTRL + H)


9. Now find all DoubleQuotes (") and replace them with backticks (`) and click Replace All, then save it.
f4pxyf.jpg



10. Open your mysql.sql example MySQL Database (by default located in the "schemas" folder) with your text editor, and "File" --> "Save As" --> "mysqlCopy.sql" in the same place. Don't close it!
f4pxyf.jpg



Now, here comes the tricky part, this is very important to understand!
Each chunk of data is called an SQL Query, that is a command that your SQL server will execute when the file is imported. It starts with an action verb (eg. CREATE, DROP, INSERT) and ends with a semicolon (;).

11. After each SQL Query that creates a table (seen below), you must copy over your INSERT Queries from your SQL database into your MySQL database that insert things (just look at the pics if you don't understand).
520t2s.jpg



12. Make sure you paste all the INSERT queries after each CREATE TABLE query!
15pk9c1.jpg

fbfd6w.jpg



13. You can copy & paste as many INSERT queries as you wish, but I would suggest at least copying over all of the;
  • accounts
  • houses
  • players
  • player_depotitems
  • player_items
  • player_skills
  • player_storage
  • tiles
  • tile_items


14. In the CREATE TABLE Query which creates "player_items", reverse cid & pid since MySQL & SQLite stores them differently.
t018hi.jpg

10qcry9.jpg



15. Go to http://localhost/phpmyadmin/ and import your mysqlCopy.sql file like you would the normal mysql.sql file if you were starting brand new.


16. Host your server!
 
Do you mean you can't install SQLite Manager on Firefox? What exactly are the problems?
If you want I can also help with teamviewer.
 
Whenever i try to convert it to MySQL file(SQL) it won't work for me. It just can't import it. That's why im requesting it.


Thanks for the comment so fast!
 
Do you get errors? Did you used the MySQL tables and only added the insert parts?
 
Hi,
I want to convert sqlite "test.db" file to mysql "test.sql" file.
But I can't find that step.
 
I did exactly as the guide says, double checked.

Error

SQL query:

INSERT INTO `accounts`
VALUES ( 1, '1', '1', 65535, 1457303064, '', '0', 0, 0, 1 ) ;

MySQL said:

#1136 - Column count doesn't match value count at row 1

TU7GKO5.jpg
 
Last edited:
Just delete the column `vip_time` from the table, since you are not using it.
 
I'm currently having the exact same error that hayrien has.. Anyone that has a solution for it perhaps? :)
 
I get lost at step 10. Where is this file: mysql.sql example MySQL Database (by default located in the "schemas" folder)
 
Back
Top