News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Postgre to Mysql?

Started by maarawoe, April 09, 2013, 07:21:33 AM

Previous topic - Next topic

maarawoe

Hi,

I am running my smf 2.0.4 on postgre but due to lot of incompatibilities, I'd like to move to MySql....
I have ftp access and pgadmin and mysql admin access through the web admin interfaces but no direct access to the database prompts.
Could anyone help me how to migrate to mysql keeping all the data in the forum etc.?

Many thanks!

emanuele

#1
Looking at your posts history it appears you are having issues with mods, yeah, unfortunately many mods are not written to support more than MySQL... :( (not that SMF itself is perfect when using Postgre).

Googling a bit, I found this question (at stackoverflow) that mentions the MySQL Workbench.
Did you already try it?


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.

maarawoe

yes - I am facing problems mainly with mods. For the core functionality on pgsql I can't complain.
The problem is that 99% of mods is written for mysql without any compatibility to other databases what is painful :(
Even the workbench is already on my computer I didn't tried that yet but I will give it a try.
I am asking here if there is some way like installing a second smf running on mysql and importing the data or whatever.

emanuele

No, not really...
I wrote something to migrate from SQLite to MySQL, I think it should be possible to adapt it for Postgre (even though I should first fix it since I think it's broken :P).


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.

Oldiesmann

As far as the mods go, I'm wondering if it would be worth our while to add compatibility with Postgres and SQLite as a requirement for approval. The problem is most mod authors aren't familiar with either of them and don't know how to make their code work properly with it, but I guess we could always give them pointers in that area.
Michael Eshom
Christian Metal Fans

maarawoe

Quote from: Oldiesmann on April 09, 2013, 11:47:16 AM
As far as the mods go, I'm wondering if it would be worth our while to add compatibility with Postgres and SQLite as a requirement for approval. The problem is most mod authors aren't familiar with either of them and don't know how to make their code work properly with it, but I guess we could always give them pointers in that area.

Sounds reasonable as the current status is killing the smf experience.
You know - smf works great on non-mysql but thats all... :( there is a planety of mods and tweaks but they are not supported on the platform where the smf itself supported is - this is ridiculous...
Moreover you never know if it will be working or not as the database scripts are not being verified like the file changes are if they will execute properly when installing a mod and so I end up very often with partialy installed mod and messed up database because some queries are simply mysql only... Noone even bothers to write "guys, I am using mysql functions, be aware!"

Arantor

QuoteNoone even bothers to write "guys, I am using mysql functions, be aware!"

Actually there are mods out there do that but they're very few and far between because of what I've seen, there should only be a handful of cases where it's actually legitimately a problem. Trouble is, most of those share a common fault with SMF itself.

If you really want to get into what's ridiculous, the way cross-DB support is actually implemented as a whole - SMF is *built* first and foremost for MySQL and anything else is a bonus, to be honest.

The biggest problem for compatibility is the use of FIND_IN_SET, and the number of places that use FIND_IN_SET to handle group access or other similar cases. That is actually a design issue in SMF that's been carried over from a long time ago - back some years ago it was more efficient to do that, but in more recent times it's actually better not to do that.

maarawoe

Quote from: Arantor on April 09, 2013, 12:00:31 PM
SMF is *built* first and foremost for MySQL and anything else is a bonus, to be honest.

I realized that too late and now it seems nearly impossible to migrate to the mainstream mysql.
Postgre has a big potential and I love to use it but the problem is with its support from the developers. I know that the most of the users are using the mysql but honestly only one platform should be fully supported and if you want to support more, you should have to support all of them equaly and possibly force developers to make their code functional on all of the supported platforms (or at least do not allow to install such incompatible mods).

Anyway - all I need now is to move my forum to mysql to get all the necessary mods fully working. We are small comunity and I am trying to bring more people in by integrating to already existing social services what is currently not possible. Same as if lose my forum data or force people to re-register etc. it would kill the starting community... :(

Is there way to install another smf on a subdomain and import all the data from the production one?

Arantor

Quoteyou should have to support all of them equaly and possibly force developers to make their code functional on all of the supported platforms (or at least do not allow to install such incompatible mods).

This would entail largely rewriting SMF. Even SMF's support for PGSQL and SQLite is somewhat ridiculous - it takes MySQL queries and bashes them into something that PGSQL and SQLite can use for the most part, there's almost no instances of actual dedicated support.

QuoteIs there way to install another smf on a subdomain and import all the data from the production one?

The general process is straightforward enough, copy the files from the production to the test site, dump and restore the database then run repair_settings.php (from the download area) to fix paths etc.

Oldiesmann

Quote from: Arantor on April 09, 2013, 12:00:31 PM
QuoteNoone even bothers to write "guys, I am using mysql functions, be aware!"

Actually there are mods out there do that but they're very few and far between because of what I've seen, there should only be a handful of cases where it's actually legitimately a problem. Trouble is, most of those share a common fault with SMF itself.

If you really want to get into what's ridiculous, the way cross-DB support is actually implemented as a whole - SMF is *built* first and foremost for MySQL and anything else is a bonus, to be honest.

The biggest problem for compatibility is the use of FIND_IN_SET, and the number of places that use FIND_IN_SET to handle group access or other similar cases. That is actually a design issue in SMF that's been carried over from a long time ago - back some years ago it was more efficient to do that, but in more recent times it's actually better not to do that.

We declared two compatibility functions for find_in_set in PostgreSQL:

CREATE OR REPLACE FUNCTION FIND_IN_SET(needle text, haystack text) RETURNS integer AS '
SELECT i AS result
FROM generate_series(1, array_upper(string_to_array($2,'',''), 1)) AS g(i)
WHERE  (string_to_array($2,'',''))[i] = $1
UNION ALL
SELECT 0
LIMIT 1'
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION FIND_IN_SET(needle integer, haystack text) RETURNS integer AS '
SELECT i AS result
FROM generate_series(1, array_upper(string_to_array($2,'',''), 1)) AS g(i)
WHERE  (string_to_array($2,'',''))[i] = CAST($1 AS text)
UNION ALL
SELECT 0
LIMIT 1'
LANGUAGE 'sql';


If there are still errors being generated from a usage of FIND_IN_SET, please let me know.
Michael Eshom
Christian Metal Fans

maarawoe

It smells like postgre version restricted because as far as I remember "generate_series" was not always available right?
My hosting is on 8.2 what is quite oldschool so I am affraid it will not work for me but I will definitely give it a try.... ;-)

Oldiesmann

SMF should work with versions as old as 8.0. I also found a post on one site about the function dating back to June of 2006 - well before Postgres 8.0 was released and also well before SMF even supported it.
Michael Eshom
Christian Metal Fans

Advertisement: