Idea phpBB 3 to Kunena 3 Migration Script

Chris,

Sorry for the late reply. I have been very disturbed by a VPS service provider, paid for 12 months in upfront, them not delivering the service at all. I cancelled as per their ToS, they say that I am not eligible for refund. They want to take the whole 12 month payment, the service I got in return was not even a minute of the server. Since MixERP project is a non profit community, I feel very bad to loose the money as a result of an unethical business practice done by a company based in Washington.

On the happy note, I am glad to hear that you have sorted out the attachment issue. Good luck.

A strange discovery from the few words that return results:

I suspected the language files for quite a while, and thought "worst case I just rename those to English, and configure the site to be fully English (maybe even rename the dutch files to en-US, with fallback to en-UK) while it's not; and next update it will break but I'll re-overwrite them"... But that doesn't seem to be the problem.

Search on non-English words does give strange results: Search for "Olen" (a village, that I saw mentioned in a post) gave "watermolen" (=watermill) as result; search for "zee" (=sea) gave "zeer" (=very if adverb, or pain if noun) while there were matches enough about the sea... But this may be a typical problem? I haven't looked it up.

But, a possible lead to a breakthrough:
What I noticed was that all results came either from (1) self-written posts (i.e., through Kunena frontend, not through script), or (2) last posts in any topic. And indeed, I can find any post quite consistently (say 19 out of 20) if it's using my search term and it's the last in a topic. And if I post another message in the topic, then the now one-before-last post still gets found...

[So there is a ludicrous solution: Insert every topic with only its first message, then recount stats, then insert second message in each topic if it exists, recount, third message, recount, fourth, ... --- until the longest topics (54 and 56 posts) are fully inserted! Madness.]

OK, back to the grindstone. Maybe I have to turn off search, but that makes it almost useless.

Hi,

Can you provide me your database so I that I fully understand what the problem is? Since the scope of problem of this problem might be out of my domain, I cannot guarantee I will be able to solve this problem.

I am primarily a C++/C# developer, I have been just guessing and poking PHP with some success.

I would be free on the weekend to see your problem. So, if you're interested, send me an email at binodnp^outlook@com.

This topic has become very big and difficult to read. So, henceforward I would want to offer help in my own forum instead of here.

mixerp.org/forum

Please create a separate topic in MixERP forums under the category "phpBB to Kunena Migration Forum". See you there.

Dear Binod,

Thanks for such a kind offer. I'll give it another go or two this week first...

There's of course privacy issues in sending the actual database. So I'll see that I can replicate my problem with some fake data! I couldn't last time, because I hadn't put enough data in --- "hey this all works!" --- to notice only last messages in topics popped up.

I understand, it's okay. Best of luck!

Thank you very much for the help binod.

I tryed your script and I get this:


Is possible to you give me some help? I have no idea how to work with DB scripts. Only the basics.
Thank you!

Just google the error, <#1267 - Illegal mix of collations> and follow the answers on StackOverflow:

Your original forum's database must contain columns or entire tables with a different collation from the others. So use phpmyadmin or so to get them the same.

[Both are with UTF8 encoding -- so you're in luck and all accented letters are encoded in the same way; only their collation is different which means their (alphabetic) ordering and "what it means to be identical" in a comparison... Example, Scandinavian languages have 'extra' letters like an O with a slash through or A with a little circle on (which are NOT 'just an accent-on-a-normal-letter', as  in Portuguese(?) or French) -- in Non-scandinavian Latin1 collations you'd just treat the first as O the second as A; in Swedish and Norwegian they would be seen as different (in comparisons); and the 'extra' letters in most languages are inserted in very specific places in the alphabet, meaning that the same input will be differently ordered under different collations. Hence this warning/error you have.]

Good luck!

[E.g. on 'extra letters' en.wikipedia.org/wiki/%C3%85 ]

Thank you Marvin McDuck!
So, I must to change the collation of the kunena database or the old phpbb forum database?
I'm not sure if I know how to do this :blink:
There is no phpbb_users or user_from table on the old database.


EDIT:
I used Phoca Changing Collation tool and done, but now I get this:

Errr... The old one? Because Binod has correctly written the script (and nowhere in the script it mentions collation at all)...

Let's think about it logically: the error happens DURING the

CALL armada_oldforum.create_missing_columns('phpbb', 'phpbb_users', 'user_from', 'varchar(256)');

So it's happening while mysql is CREATING the tables you mention --- using the old database... It's working with the old database and runs into a comparison ("=") where on one side the ordering differs from the other (think about e.g., O-or-Ø being identical on one side but having several letters between them on the other side; the fact that it's not an actual problem because your data contain Portuguese not Scandinavian words --- mysql cannot know that! and you might have a norwegian member one day!).

So you have to look in the old database at the tables USED to construct 'phpbb', 'phpbb_users', 'user_from' (either you know which are those tables, or you look inside Binod's script for the definition of the <create_missing_columns> function). So from top to bottom: In phpmyadmin (or sequelpro or whatever you use) go to top level where all databases you have are, and see what they say for 'collation' -- logically your Kunena and PHPBB would be the same collation as they are in the same language. But then click on your PHPBB database and look at the collation of all tables needed for the error-causing call, and check/change that they have all the same collation as the whole database (ideally and logically, ALL tables in that database should have the same collation, but those not used in the error-causing call are not your worry now). Then, click one by one on those few tables, and see that all their fields have the same collation again...

(For all we know, the same problematic collation-mix can be happening already in your joomla/kunena database fields, so check that possibly. And again, that would not be due to Binod's script in any way.)

(The database I'm trying to salvage now, has not just collation but encoding problems --- fields that are Latin1 encoded think they're UTF8 encoded and vice versa, turning any accented letter plus many typographic markers and symbols like € into gobbledigook... but also because of the consequence that is alphabetic-ordering-errors over time, there are posts with a lower ID than their parents, or replies with an earlier timestamp than what they react to, and so forth. So if your website at the moment has various collation inconsistencies, I don't know if you'd have to correct all immediately or be left alone, "don't-fix-unless-broken"; depends on your definition of broken. I think correct all, but I have almost zero programming or DB skills.)

I am extremely sorry. :silly: I think I made a stupid mistake here:

should be:

I have updated it to GitHub. Could you please try this once again?