Advertisement:

Author Topic: From SQLite to MySQL  (Read 5008 times)

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
From SQLite to MySQL
« on: May 08, 2012, 04:27:34 PM »
I already had a couple of people wanting to move from SQLite to MySQL.
With the first I tried to google a bit, but I didn't find anything that worked, and so I failed in obtain a dump in a format compatible with phpMyAdmin.

Few days ago, instead (due also to the last issues with SMF backup utility that forced me to investigate how this works) I succeeded with just a couple of minor tweak to the exported db! :D

The script I used is attached here.

There is still an issues with special chars (of course), in the case I faced the most problematic was the one with the "€" symbol. The other issue was just in search log and few accented letters, strange enough in all the other tables the letters were correct... ???

Does anyone know how this can be fixed?
Funny thing is that the final backup is opened properly (with euro and all) by some editors (Kate), but not by others (Geany).


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

MrPhil

  • Guest
Re: From SQLite to MySQL
« Reply #1 on: May 08, 2012, 06:14:18 PM »
Are you certain that the character encoding is what you think it is? It sounds like you've got a Euro that's maybe in CP-1252 encoding rather than true UTF-8. Latin-1 does not have a Euro, so it's probably using an MS "Smart Quotes" abberation even if it claims to be Latin-1. There are a bunch of other Smart Quotes characters that may be pulling the same trick.

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: From SQLite to MySQL
« Reply #2 on: May 09, 2012, 04:12:37 PM »
Well...if they would be a smart quotes (...smart? From MS? :P) then they wouldn't even get to the database...no? (messages truncated?)


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

MrPhil

  • Guest
Re: From SQLite to MySQL
« Reply #3 on: May 09, 2012, 06:50:46 PM »
If the euro is byte value x80, it's a CP-1252 Smart Quote (yes, that is an oxymoron, coming from MS...). It won't necessarily do any harm going in, but coming out for display it can really mess up text (i.e., be treated as a control code).

Offline hollermann

  • Newbie
  • *
  • Posts: 3
sqlite_to_sql worked for me!
« Reply #4 on: February 12, 2015, 07:20:11 AM »
Thank you so much for the small above mentioned tool!

What I did:
- proceed as described to get the .sql-file, downloaded it (actually I wanted to see the code; letting it on the server would have been worked too)
- on webspace I duplicated the folder containing the forum-files
- emptied the original folder
- set up a new smf with connection to mysql database
- copied differential data from duplicate (former original) to original folders.
- opened settings.php in both duplicate and original version, corrected a few lines (you should see what to do)
- in phpmyadmin deleted all data in every table (took me about half an hour); be sure all tables are really empty!
- copied/pasted sql-code

After that the forum did work! All users can still login, all attachments are correct. I recognized some issues with templates (curve mobile won't work), and I had to redefine the language (german) in oder to see "Umlaute" (äöü etc).

After all worked perfectly again, I wanted to make a full copy of the forum and let it run on my own server (localhost). That turned out to be a bit difficult, it didn't use any template. To manage that I installed a new version on my own server. In settings.php you'll realize the way paths are written. I kept the settings.php and emtied the folder containing the forum files. I downloaded the files from hoster to that folder and modified the settings.php. After that, the forum startet without template (first it seemed to work with templates, but after disconnecting to internet it failed). For managing that I modified some path-entries in the relating table (something with "template"). Now everything is ok both on local machine as well on internet.

kr,
Bernd Hollermann
Germany