News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

[SMF Converter] e107 - 0.7.7

Started by JayBachatero, January 10, 2007, 01:26:50 PM

Previous topic - Next topic

ThorstenE

when re-starting the converter all data from SMF (not e107 data) will be lost! our converters empty all tables before inserting the source forums data.

hide all members email adresses is easy with phpMyAdmin:

UPDATE smf_members SET hideEmail = '1';


What is phpMyAdmin?

Newbster

Thanks for that. I'll try it on another testforum to see what is actually missing with the old error if nothing is missing then still thanks but won't do the convert again.

Newbster

Tried the sql file but still the same error coming up.

SleePy

Do you have any banned users in e107?

What version of e107 are you using btw?
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

kaloer

Hi!

I have a problem with the charset (utf8_danish_ci).
Both e107 and SMF have the utf8_danish_ci charset, but still, the danish characters(æøå) is displayed as Ã¥.
In the convert.php have I chosen the UTF8 charset.
How do I solve my problem?
Thanks in advance!

ThorstenE

Quote from: kaloer on February 13, 2009, 08:38:18 AM
In the convert.php have I chosen the UTF8 charset.
How do I solve my problem?
Do not select the UTF-8 checkbox in convert.php, both forums (source and destination) allready use the same charset, so you don't need to convert the characters again.

kaloer

I can select the charset from a dropdown menu (not a checkbox). Am I using an old convert.php, or which charset should I select?

ThorstenE

uups, you're right. the newer convert.php has a dropdown, not a select box .. try it with ISO-8859-1 (danish characters are latin based).

kaloer

Hmm. It doesn't work.
I got this error:


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

All the e107 and SMF tables are utf8_danish_ci. I also have some utf8_general_ci, but they are not a part of the e107 or SMF system.
Every time I use the converter, I got a lot of these warnings:

Warning: apache_reset_timeout() [function.apache-reset-timeout]: Cannot reset the Apache timeout in safe mode in {my directory}  on line 1963

ThorstenE

are you sure about the collation? maybe some table fields in e107 or SMF have utf8_general_ci and others have utf8_danish_ci?

You can export your database, then edit the .sql file with a text editor and replace all "utf8_general_ci" with "utf8_danish_ci", then import the modified script and try the conversion.


kaloer

I am sure all my e107 and SMF tables have the utf8_danish_ci collation. Because I am going to use SMF with Joomla, I also have my Joomla tables in the database. These tables have the utf8_general_ci collation.
What do you mean by exporting the database, and then replace all utf8_general_ci with utf8_danish_ci?
Do you think it would help if I converts the Joomla tables to utf8_danish_ci? And if so, will it reflect my Joomla installation/setup?

ThorstenE

not the tables but some table columns / fields are possible utf_8_genral_ci?
you got this error during conversion:
QuoteIllegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_danish_ci,IMPLICIT) for operation '='
do you know the step in convert.php? was it during the board_permissions conversion?

kaloer

I have tried to export the database, but there are not any search-result of utf8_general_ci.
The error occurres in the "Converting boards..." step with the following query:


SELECT
DISTINCT f.forum [nofollow]_id AS ID_BOARD, SUBSTRING(f.forum_name, 1, 255) AS name,
SUBSTRING(f.forum_description, 1, 65534) AS description,
CASE
WHEN f.forum [nofollow]_parent > 500
THEN f.forum [nofollow]_parent - 499
ELSE f.forum [nofollow]_parent
END AS ID_CAT, f.forum [nofollow]_sub AS ID_PARENT,
CASE WHEN f.forum [nofollow]_sub = 0 THEN 0 ELSE 1 END AS childLevel, f.forum [nofollow]_threads AS numTopics,
f.forum_threads + f.forum [nofollow]_replies AS numPosts, f.forum [nofollow]_order AS boardOrder,
CASE f.forum [nofollow]_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 (`db1_db`.e107_forum AS f, `db1_db`.e107_forum AS c)
LEFT JOIN `db1_db`.e107_userclass_classes AS uc ON (uc.userclass_id = f.forum [nofollow]_class)
LEFT JOIN `db1_db`.smf_membergroups AS mg ON (mg.groupName = CONCAT('e107 ', uc.userclass_name))
WHERE f.forum [nofollow]_parent != 0
LIMIT 0, 500;

ThorstenE

ok, you can try this: edit the e107_to_smf.sql and replace


/******************************************************************************/
--- Converting boards...
/******************************************************************************/

TRUNCATE {$to_prefix}boards;

DELETE FROM {$to_prefix}board_permissions
WHERE ID_BOARD != 0;

---* {$to_prefix}boards
SELECT
DISTINCT f.forum_id AS ID_BOARD, SUBSTRING(f.forum_name, 1, 255) AS name,
SUBSTRING(f.forum_description, 1, 65534) AS description,
CASE
WHEN f.forum_parent > 500
THEN f.forum_parent - 499
ELSE f.forum_parent
END AS ID_CAT, f.forum_sub AS ID_PARENT,
CASE WHEN f.forum_sub = 0 THEN 0 ELSE 1 END AS childLevel, f.forum_threads AS numTopics,
f.forum_threads + f.forum_replies AS numPosts, f.forum_order AS boardOrder,
CASE f.forum_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 ({$from_prefix}forum AS f, {$from_prefix}forum AS c)
LEFT JOIN {$from_prefix}userclass_classes AS uc ON (uc.userclass_id = f.forum_class)
LEFT JOIN {$to_prefix}membergroups AS mg ON (BINARY mg.groupName = CONCAT('e107 ', uc.userclass_name))
WHERE f.forum_parent != 0;
---*


with:

/******************************************************************************/
--- Converting boards...
/******************************************************************************/

TRUNCATE {$to_prefix}boards;

DELETE FROM {$to_prefix}board_permissions
WHERE ID_BOARD != 0;

---* {$to_prefix}boards
SELECT
DISTINCT f.forum_id AS ID_BOARD, SUBSTRING(f.forum_name, 1, 255) AS name,
SUBSTRING(f.forum_description, 1, 65534) AS description,
CASE
WHEN f.forum_parent > 500
THEN f.forum_parent - 499
ELSE f.forum_parent
END AS ID_CAT, f.forum_sub AS ID_PARENT,
CASE WHEN f.forum_sub = 0 THEN 0 ELSE 1 END AS childLevel, f.forum_threads AS numTopics,
f.forum_threads + f.forum_replies AS numPosts, f.forum_order AS boardOrder,
'-1' AS memberGroups
FROM ({$from_prefix}forum AS f, {$from_prefix}forum AS c)
LEFT JOIN {$from_prefix}userclass_classes AS uc ON (uc.userclass_id = f.forum_class)
WHERE f.forum_parent != 0;
---*


kaloer

Thank you. Now, I don't get the error any more.
But I get a new error message in the "Converting banned users..." step:


Fatal error: Cannot break/continue 1 level in /var/www/fifazone.dk/public_html/SMF/convert.php(929) : eval()'d code on line 13


The forum-topics/posts are converted, but still, the danish characters aren't shown right.

ThorstenE

ok, we are near the solution.. can you completely remove this block from e107_to_smf.sql?
It's not a big problem, only the ban-list is not converted then.


/******************************************************************************/
--- Converting banned users...
/******************************************************************************/

TRUNCATE {$to_prefix}ban_items;
TRUNCATE {$to_prefix}ban_groups;


---# Moving banned entries...
---{
$_REQUEST['start'] = isset($_REQUEST['start']) ? (int) $_REQUEST['start'] : 0;
while (true)
{
pastTime($substep);

$result = mysql_query("
SELECT banlist_ip, banlist_reason
FROM {$from_prefix}banlist
LIMIT $_REQUEST[start], 250");
$ban_time = time();
$ban_num = 0;
while ($row = mysql_fetch_assoc($result))
{
$ban_num++;
mysql_query("
INSERT INTO {$to_prefix}ban_groups
(name, ban_time, expire_time, notes, reason, cannot_access)
VALUES ('migrated_ban_$ban_num', $ban_time, NULL, '', '" . addslashes($row['banlist_reason']) . "', 1)");
$ID_BAN_GROUP = mysql_insert_id();

if (empty($ID_BAN_GROUP))
continue;

if (strpos($row['banlist_ip'], '@') !== false)
{
mysql_query("
INSERT INTO {$to_prefix}ban_items
(ID_BAN_GROUP, email_address, hostname)
VALUES ($ID_BAN_GROUP, '" . addslashes($row['banlist_ip']) . "', '')");
continue;
}
else
{
list ($octet1, $octet2, $octet3, $octet4) = explode('.', $row['banlist_ip']);

$ip_high1 = $octet1;
$ip_low1 = $octet1;

$ip_high2 = $octet2;
$ip_low2 = $octet2;

$ip_high3 = $octet3;
$ip_low3 = $octet3;

$ip_high4 = $octet4;
$ip_low4 = $octet4;

mysql_query("
INSERT INTO {$to_prefix}ban_items
(ID_BAN_GROUP, ip_low1, ip_high1, ip_low2, ip_high2, ip_low3, ip_high3, ip_low4, ip_high4, email_address, hostname)
VALUES ($ID_BAN_GROUP, $ip_low1, $ip_high1, $ip_low2, $ip_high2, $ip_low3, $ip_high3, $ip_low4, $ip_high4, '', '')");
continue;
}
}

$_REQUEST['start'] += 250;
if (mysql_num_rows($result) < 250)
break;

mysql_free_result($result);
}
$_REQUEST['start'] = 0;
---}
---#

---# Moving banned user...
---{
$request = mysql_query("
SELECT user_id
FROM {$from_prefix}user
WHERE user_ban = 1");
if (mysql_num_rows($request) > 0)
{
mysql_query("
INSERT INTO {to_prefix}ban_groups
(name, ban_time, expire_time, reason, notes, cannot_access)
VALUES ('migrated_ban_users', $ban_time, NULL, '', 'Imported from e107', 1)");
$ID_BAN_GROUP = mysql_insert_id();

if (empty($ID_BAN_GROUP))
continue;

$inserts = '';
while ($row = mysql_fetch_assoc($request))
$inserts .= "
($ID_BAN_GROUP, $row[user_id], '', ''),";
mysql_free_result($request);

mysql_query("
INSERT INTO {$to_prefix}ban_items
(ID_BAN_GROUP, ID_MEMBER, email_address, hostname)
VALUES " . substr($inserts, 0, -1));
}
---}
---#


kaloer

It isn't a problem to "ignore" the banned users, and now, the converter completes succesfull. The users and all threads are convertet, but still, the characters aren't showed correctly.
I have tried both the UTF8 and ISO-8859-1.

kaloer

As I said earlier, I am going to use the forum with Joomla! and JFusion.
Now, when I show the forum through JFusion (frameless), the danish characters are shown correctly.
Stange it doesn't work if I go directly to the SMF forum, but whatever - I don't need to :)

Thank you very much for your great help!

kaloer

Hmm.. It doesn't work any more. It turns to be very strange :S

kaloer

I got it working!
I made a php-script which used the utf8_decode function in php:


mysql_connect("HOST_ADDR", "DB_USERNAME", "DB_PASSWORD");
mysql_select_db("DB_NAME");

$query = mysql_query("SELECT ID_BOARD, name, description FROM smf_boards") or die(mysql_error());

while($row = mysql_fetch_array($query))
{
$name = utf8_decode($row['name']);
$description = utf8_decode($row['description']);
$id = $row['ID_BOARD'];
mysql_query("UPDATE smf_boards SET name='$name', description='$description' WHERE ID_BOARD='$id'") or die(mysql_error());
}

$query = mysql_query("SELECT ID_CAT, name FROM smf_categories") or die(mysql_error());

while($row = mysql_fetch_array($query))
{
$name = utf8_decode($row['name']);
$id = $row['ID_CAT'];
mysql_query("UPDATE smf_categories SET name='$name' WHERE ID_CAT='$id'") or die(mysql_error());
}

$query = mysql_query("SELECT ID_MEMBER, memberName, realName, signature, avatar, usertitle FROM smf_members") or die(mysql_error());

while($row = mysql_fetch_array($query))
{
$name = utf8_decode($row['memberName']);
$realName = utf8_decode($row['realName']);
$signature = utf8_decode($row['signature']);
$avatar = utf8_decode($row['avatar']);
$usertitle = utf8_decode($row['usertitle']);
$id = $row['ID_MEMBER'];
mysql_query("UPDATE smf_members SET memberName='$name', realName='$realName', signature='$signature', avatar='$avatar', usertitle='$usertitle' WHERE ID_MEMBER='$id'") or die(mysql_error());
}

$query = mysql_query("SELECT ID_PM, body FROM smf_personal_messages") or die(mysql_error());

while($row = mysql_fetch_array($query))
{
$name = utf8_decode($row['body']);
$id = $row['ID_OM'];
mysql_query("UPDATE smf_personal_messages SET body='$name' WHERE ID_PM='$id'") or die(mysql_error());
}

Advertisement: