Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLIC

[JohanLM]

Hi all,
Are on my (troubled way) to convert from phpbb2.0.23 > SMF2 rc3 and I ran into a snag;

Converting...
Converting ranks... Successful.
Converting groups... Successful.
Converting members... Unsuccessful!
This query:
SELECT
u.user_id AS id_member, SUBSTRING(u.username, 1, 80) AS member_name,
SUBSTRING(u.username, 1, 255) AS real_name,
SUBSTRING(u.user_password, 1, 64) AS passwd, u.user_lastvisit AS last_login,
u.user_regdate AS date_registered,
SUBSTRING(u.user_from, 1, 255) AS location,
u.user_posts AS posts, IF(u.user_level = 1, 1, IFNULL(mg.id_group, 0)) AS id_group,
u.user_new_privmsg AS instant_messages,
SUBSTRING(u.user_email, 1, 255) AS email_address,
u.user_unread_privmsg AS unread_messages,
SUBSTRING(u.user_msnm, 1, 255) AS msn,
SUBSTRING(u.user_aim, 1, 16) AS aim,
SUBSTRING(u.user_icq, 1, 255) AS icq,
SUBSTRING(u.user_yim, 1, 32) AS yim,
SUBSTRING(u.user_website, 1, 255) AS website_title,
SUBSTRING(u.user_website, 1, 255) AS website_url,
u.user_allow_viewonline AS show_online, u.user_timezone AS time_offset,
IF(u.user_viewemail = 1, 0, 1) AS hide_email, u.user_avatar AS avatar,
REPLACE(u.user_sig, '\n', '<br />') AS signature,
u.user_sig_bbcode_uid AS signature_uid, u.user_avatar_type,
u.user_notify_pm AS pm_email_notify, u.user_active AS is_activated,
'' AS lngfile, '' AS buddy_list, '' AS pm_ignore_list, '' AS message_labels,
'' AS personal_text, '' AS time_format, '' AS usertitle,
'' AS secret_question, '' AS secret_answer, '' AS validation_code,
'' AS additional_groups, '' AS smiley_set, '' AS password_salt,
'' AS member_ip, '' AS member_ip2
FROM `johanlm_vansinne`.phpbb_users AS u
LEFT JOIN `johanlm_vansinne`.phpbb_ranks AS r ON (r.rank_id = u.user_rank AND r.rank_special = 1)
LEFT JOIN `johanlm_vansinne`.smf_membergroups AS mg ON (mg.group_name = CONCAT('phpBB ', r.rank_title))
WHERE u.user_id != -1
GROUP BY u.user_id
LIMIT 0, 500;
Caused the error:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

I looked around some and figured that the Collation is off;
phpbb2 = utf8_unicode_ci
SMF2 = utf8_general_ci

Now I am NO way near a PHPcoder, I manage to find this line of code to alter the db Table's Collation's

Code Select Expand

ALTER TABLE phpbb_users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

So I went into phpMyAdmin section of my webhotells cpanel and ran that under the SQL section and altered the above.
Thing is I still get the same error and suspect I alter it the right way, but at the wrong place? Any clue where I should go do this more excatly?
As in wich table, or maybe UNDer wich table?

[JohanLM]

That above try I used the files that comes along with the converter.
Now I have tried the file that you can DL from the sticky post;
http://www.simplemachines.org/community/index.php?topic=142124.0

But then I only got this far;

Converting...
Converting ranks... Unsuccessful!
This query:
DELETE FROM `johanlm_vansinne`.smf_membergroups
WHERE groupName LIKE 'phpBB %';
Caused the error:

Unknown column 'groupName' in 'where clause'

I did not even got past the 1st step on that one and I understand it even less.
I tried and posted about it however since maybe it have an easier sollution then the one I tried first?!

*goes back to the searching after sollutions ...*

[JohanLM]

Ok ...
Backtracked and went back to post 1.
Instead of fiddeling around inside phpMyAdmin settings, I downloaded/exported the phpbb2 database to my desktop and then opened it up with wordpad.
The tracked to each and every utf8_unicode_ci part in the code and then replaced it with utf8_general_ci.
Saved the file and then went back into the phpMyAdmin, went into the [Structure] tab, deleted all the phpbb2 tables and then [Import]:ed the altered database file.
And then ran the converter again.

This time success! Yey!
No errors or anything anywhere.

But when I went to the SMF forum with the converted database I am greeted with this message;

An Error Has Occurred!
You are not allowed to access this section

And I cannot do anything, not logout or get to another page.
All links goes to that message.

*back to the drawingboard ...*

[JohanLM]

FINALLY!
As you can see above I tried every possible angle. Lost count of how many times I downloaded & uploaded + installed, reinstalled, deleted and started over.
But what did it for me was NOTHING of the above.

What is written BELOW this line solved it!

I figured since phpbb2 is a "dead" project and phpbb3 is not, that + SMF2 should mix better than with something "dead". So ...

Had a phpBB2 >
I installed/upgraded that to phpBB3 >
Installed SMF2 > converted the phpBB3 using the Phpbb 3 to SMF 2-0-rc3
=
All seems to be just fine.

note ...
I put all the installations in the same database, but named the tables for phpbb3 to phpbb3 instead of phpbb. Do not know if it matters, but that's what I did. Do not know if this will help "you", but thought I write everything I do out and maybe save "you" some hours of struggling.

[Norv]

Ah, good to hear you got it sorted out!

It seems our phpbb2 converter may have a problem... Honestly I don't remember which which versions it was last time tested. Something to keep in mind...
Thank you for sharing, as well.

[JohanLM]

No worries, you SHOULD spend some time sorting it out though. Since I got different results depending on what file I used one could asume 1 (or both) are no longer functional?!
Alternative is to just kill phpbb2 conversion support and recommend they allways first upgrade to phpbb3 THEN convert.
Then you only got 1 type to worry about and that one is still supported on "both sides" so to speak.