e107 0.7.16 to SMF 1.1

[mostym]

Hi, I love your forums, but im having some with issues with my conversion.  Any help would be appreciated.

Converting...
Converting members... Successful.
Converting additional member groups...
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/ndsunive/public_html/forums/convert.php(941) : eval()'d code on line 13

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ndsunive/public_html/forums/convert.php(941) : eval()'d code on line 37
Successful.
Converting categories... Successful.
Converting boards... Unsuccessful!
This query:
SELECT
DISTINCT hxxp:f.forum [nonactive]_id AS ID_BOARD, SUBSTRING(f.forum_name, 1, 255) AS name,
SUBSTRING(f.forum_description, 1, 65534) AS description,
CASE
WHEN hxxp:f.forum [nonactive]_parent > 500
THEN hxxp:f.forum [nonactive]_parent - 499
ELSE hxxp:f.forum [nonactive]_parent
END AS ID_CAT, hxxp:f.forum [nonactive]_sub AS ID_PARENT,
CASE WHEN hxxp:f.forum [nonactive]_sub = 0 THEN 0 ELSE 1 END AS childLevel, hxxp:f.forum [nonactive]_threads AS numTopics,
f.forum_threads + hxxp:f.forum [nonactive]_replies AS numPosts, hxxp:f.forum [nonactive]_order AS boardOrder,
CASE hxxp:f.forum [nonactive]_class
WHEN 252 THEN '-1'
WHEN 255 THEN ''
WHEN 253 THEN '0'
WHEN 251 THEN '0'
WHEN 254 THEN ''
WHEN 0 THEN '-1,0'
ELSE IFNULL(mg.ID_GROUP, '')
END AS memberGroups
FROM (`ndsunive_e107old`.e107_forum AS f, `ndsunive_e107old`.e107_forum AS c)
LEFT JOIN `ndsunive_e107old`.e107_userclass_classes AS uc ON (uc.userclass_id = hxxp:f.forum [nonactive]_class)
LEFT JOIN `ndsunive_e107old`.smf_membergroups AS mg ON (mg.groupName = CONCAT('e107 ', uc.userclass_name))
WHERE hxxp:f.forum [nonactive]_parent != 0
LIMIT 0, 500;
Caused the error:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='

[Norv]

It seems that some of your tables in the database have a collation latin1_swedish_ci, and others, latin1_general_ci. It would be useful to: connect with phpMyAdmin, and list the tables in your e107 database. What is the collation listed for them? Making a screenshot may help.
The same for your SMF database. What is the collation of the tables listed in phpMyAdmin, when you choose the database > Structure tab?

They should be the same, all, in order to avoid problems.

[mostym]

It looks like they were different, however, even setting the table to latin1_general_ci, im still getting the same error.

[Norv]

It looks like they were different, however, even setting the table to latin1_general_ci, im still getting the same error.

What do you mean, which table? There was only one table with latin1_swedish_ci?

[mostym]

the smf tables were all latin1_swedish_ci, and some of the e107 were latin1_swedish_ci as well.  So I changed all of the tables to latin1_general_ci

[Norv]

Can you please post the entire current error message?

[mostym]

This is the full error

Converting...
Converting members... Successful.
Converting additional member groups...
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/ndsunive/public_html/forums/convert.php(941) : eval()'d code on line 13

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ndsunive/public_html/forums/convert.php(941) : eval()'d code on line 37
Successful.
Converting categories... Successful.
Converting boards... Unsuccessful!
This query:
SELECT
DISTINCT hxxp:f.forum [nonactive]_id AS ID_BOARD, SUBSTRING(f.forum_name, 1, 255) AS name,
SUBSTRING(f.forum_description, 1, 65534) AS description,
CASE
WHEN hxxp:f.forum [nonactive]_parent > 500
THEN hxxp:f.forum [nonactive]_parent - 499
ELSE hxxp:f.forum [nonactive]_parent
END AS ID_CAT, hxxp:f.forum [nonactive]_sub AS ID_PARENT,
CASE WHEN hxxp:f.forum [nonactive]_sub = 0 THEN 0 ELSE 1 END AS childLevel, hxxp:f.forum [nonactive]_threads AS numTopics,
f.forum_threads + hxxp:f.forum [nonactive]_replies AS numPosts, hxxp:f.forum [nonactive]_order AS boardOrder,
CASE hxxp:f.forum [nonactive]_class
WHEN 252 THEN '-1'
WHEN 255 THEN ''
WHEN 253 THEN '0'
WHEN 251 THEN '0'
WHEN 254 THEN ''
WHEN 0 THEN '-1,0'
ELSE IFNULL(mg.ID_GROUP, '')
END AS memberGroups
FROM (`ndsunive_e107old`.e107_forum AS f, `ndsunive_e107old`.e107_forum AS c)
LEFT JOIN `ndsunive_e107old`.e107_userclass_classes AS uc ON (uc.userclass_id = hxxp:f.forum [nonactive]_class)
LEFT JOIN `ndsunive_e107old`.smf_membergroups AS mg ON (mg.groupName = CONCAT('e107 ', uc.userclass_name))
WHERE hxxp:f.forum [nonactive]_parent != 0
LIMIT 0, 500;
Caused the error:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='

[Norv]

Well, it seems there still are tables with those two collations.

You could also see them if you: select each database, and go to the Export tab. Export the structure (only the structure, not data), and look at the result. Search the result like any text file, for the text "latin1_swedish_ci" and please take a look at the line where you find it: most likely a statement starting with CREATE TABLE name_of_table_here.

Please note that if a table has a certain collation, then most likely the text-based fields in it also have the same collation. Probably that is the problem: those tables must be fully converted to latin1_general_ci, that is, their fields as well, not only the table itself.

Please make sure you make a full backup of your e107 database before converting character sets and collations, even though these two should be compatible; operations with databases and specially with character sets and collations are not famous for their reliability...

[mostym]

I dumped the database to a sql file then searched.  I did a search for latin1_swedish_ci, and it returned nothing.  Any other thoughts?

[Norv]

That's odd... Did it really dump the structure, are the other collations listed in the file?
Sorry for the delay. If this is still a problem, please tell, what exactly is the last error?