PHPBB2 - problem with MySQL

[MrPrise]

Thanks for all the fix.
There is a bug in the avatar conversion. If the avatar has the size bbcode the size value will not has the pt after the number as SMF wants it.

[kainelderan]

I just converted over from phpBB2 and I'm now getting this error on my bridged Joomla.

Notice: Undefined index: custom_avatar_url in /var/www/html/si/silverfalls.org/SMF/Sources/Subs.php on line 3047

Can anyone help me fix this? I tried looking at the code in the Subs.php but I couldn't figure out where the problem was.

[Ferny]

I just converted over from phpBB2 and I'm now getting this error on my bridged Joomla.

Notice: Undefined index: custom_avatar_url in /var/www/html/si/silverfalls.org/SMF/Sources/Subs.php on line 3047

Can anyone help me fix this? I tried looking at the code in the Subs.php but I couldn't figure out where the problem was.

You can undo the second modification in this post and it will work: http://www.simplemachines.org/community/index.php?topic=107868.msg699910#msg699910

But it's simpler to do what the "important" note in that post says

IMPORTANT: if you want to do this change, BEFORE converting your forum you should go to SMF control panel, and in Avatar settings you need to change this:

Upload avatars to... -> Specific directory
Upload directory -> [Select the directory you want]

This is necessary in order to create 'custom_avatar_dir' entry in settings table.

This weekend I'll post a modification so that this error doesn't occur.

Ferny

[Person]

You can do a little modification that if custom_avatar_url is undefined that then it use standard path

[Ferny]

It was easier than I expected

You only have to replace the entire "Converting members" section with this:

Code Select Expand

/******************************************************************************/
--- Converting members...
/******************************************************************************/

TRUNCATE {$to_prefix}members;
TRUNCATE {$to_prefix}attachments;

---* {$to_prefix}members
---{
// Got the board timezone?
if (!isset($board_timezone))
{
$request2 = convert_query("
SELECT config_value
FROM {$from_prefix}config
WHERE config_name = 'board_timezone'
LIMIT 1");
list ($board_timezone) = mysql_fetch_row($request2);
mysql_free_result($request2);

// Find out where uploaded avatars go
$request2 = convert_query("
SELECT value
FROM {$to_prefix}settings
WHERE variable = 'custom_avatar_enabled'
LIMIT 1");
if (mysql_num_rows($request2))
list ($custom_avatar_enabled) = mysql_fetch_row($request2);
else
$custom_avatar_enabled = false;
mysql_free_result($request2);

if ($custom_avatar_enabled)
{
// Custom avatar dir.
$request2 = convert_query("
SELECT value
FROM {$to_prefix}settings
WHERE variable = 'custom_avatar_dir'
LIMIT 1");
list ($avatar_dir) = mysql_fetch_row($request2);
$attachmentType = '1';
}
else
{
// Attachments dir.
$request2 = convert_query("
SELECT value
FROM {$to_prefix}settings
WHERE variable = 'attachmentUploadDir'
LIMIT 1");
list ($avatar_dir) = mysql_fetch_row($request2);
$attachmentType = '0';
}
mysql_free_result($request2);

$request2 = convert_query("
SELECT config_value
FROM {$from_prefix}config
WHERE config_name = 'avatar_path'
LIMIT 1");
$phpbb_avatar_upload_path = $_POST['path_from'] . '/' . mysql_result($request2, 0, 'config_value');
mysql_free_result($request2);
}

// timeOffset = phpBB user TZ - phpBB board TZ.
$row['timeOffset'] = $row['timeOffset'] - $board_timezone;

if ($row['user_avatar_type'] == 0)
$row['avatar'] = '';
// If the avatar type is uploaded (type = 1) copy avatar with the correct name.
elseif ($row['user_avatar_type'] == 1 && strlen($row['avatar']) > 0)
{
$smf_avatar_filename = 'avatar_' . $row['ID_MEMBER'] . strrchr($row['avatar'], '.');
@copy($phpbb_avatar_upload_path . '/' . $row['avatar'], $avatar_dir . '/' . $smf_avatar_filename);

convert_query("
INSERT INTO {$to_prefix}attachments
(ID_MSG, ID_MEMBER, filename, attachmentType)
VALUES (0, $row[ID_MEMBER], SUBSTRING('" . addslashes($smf_avatar_filename) . "', 1, 255), " . $attachmentType . ")");
$row['avatar'] = '';
}
elseif ($row['user_avatar_type'] == 3)
$row['avatar'] = substr('gallery/' . $row['avatar'], 0, 255);
unset($row['user_avatar_type']);

$row['signature'] = preg_replace('~\[size=([789]|[012]\d)\]~i', '[size=$1px]', $row['signature']);
if ($row['signature_uid'] != '')
$row['signature'] = preg_replace('~(:u:|:1:|:)' . preg_quote($row['signature_uid'], '~') . '~i', '', $row['signature']);
$row['signature'] = substr($row['signature'], 0, 65534);
unset($row['signature_uid']);
---}
SELECT
u.user_id AS ID_MEMBER, SUBSTRING(u.username, 1, 80) AS memberName,
SUBSTRING(u.username, 1, 255) AS realName,
SUBSTRING(u.user_password, 1, 64) AS passwd, u.user_lastvisit AS lastLogin,
u.user_regdate AS dateRegistered,
SUBSTRING(u.user_from, 1, 255) AS location,
u.user_posts AS posts, IF(u.user_level = 1, 1, mg.ID_GROUP) AS ID_GROUP,
u.user_new_privmsg AS instantMessages,
SUBSTRING(u.user_email, 1, 255) AS emailAddress,
u.user_unread_privmsg AS unreadMessages,
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 websiteTitle,
SUBSTRING(u.user_website, 1, 255) AS websiteUrl,
u.user_allow_viewonline AS showOnline, u.user_timezone AS timeOffset,
IF(u.user_viewemail = 1, 0, 1) AS hideEmail, 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 messageLabels,
'' AS personalText, '' AS timeFormat, '' AS usertitle, '' AS memberIP,
'' AS secretQuestion, '' AS secretAnswer, '' AS validation_code,
'' AS additionalGroups, '' AS smileySet, '' AS passwordSalt,
'' AS memberIP2
FROM {$from_prefix}users AS u
LEFT JOIN {$from_prefix}ranks AS r ON (r.rank_id = u.user_rank AND r.rank_special = 1)
LEFT JOIN {$to_prefix}membergroups AS mg ON (BINARY mg.groupName = CONCAT('phpBB ', r.rank_title))
WHERE u.user_id != -1
GROUP BY u.user_id;
---*

If custom_avatar_enabled is not defined or its value is 0, then avatars go to attachments dir, else if it's defined, then avatars go to custom_avatar_dir



I think that the modification about following line (removing comments) is not necessary, but if somebody has problems with it, we can apply it

Code Select Expand

/* // !!! WHERE t.topic_moved_id = 0 *



This is the latest version, with member conversion change included. It also includes smilies import, which wasn't in the latest files we uploaded (I thought it was, but not)

[EDIT] There's a new version, see some posts below

Please Person do the modifications for UTF-8 version

[Ferny]

Thanks for all the fix.
There is a bug in the avatar conversion. If the avatar has the size bbcode the size value will not has the pt after the number as SMF wants it.

I think it's not a bug from the converter. Size bbcode does not work in the same way on posts and user signatures (avatars?) as in phpbb, but it can be solved if you edit Sources/Subs.php and do the following change:

Find:

Code Select Expand

array(
'tag' => 'size',
'type' => 'unparsed_equals',
'test' => '[\d]\]',
// !!! line-height
'before' => '<font size="$1" style="line-height: 1.3em;">',
'after' => '</font>',
),

Replace it:

Code Select Expand

array(
'tag' => 'size',
'type' => 'unparsed_equals',
'test' => '([\d]{1,2})\]',
// !!! line-height
'before' => '<font style="font-size: $1px; line-height: 1.3em;">',
'after' => '</font>',
),

This is more simpler than to modify the converter

[Ferny]

Another fix. Record online users was not imported because the name of the variable is incorrect

In SQL file, find:

Code Select Expand

elseif ($row['config_name'] == 'record_users_online')

Replace by:

Code Select Expand

elseif ($row['config_name'] == 'record_online_users')

phpbb2_to_smf_2006_09_02.zip


Ferny

[frwjd]

Hi everyone

I'm trying to convert from phpBB 2.0.19 but get the following error message

Converting members... Unsuccessful!
This query:
SELECT
u.user_id AS ID_MEMBER, SUBSTRING(u.username, 1, 80) AS memberName,
SUBSTRING(u.username, 1, 255) AS realName,
SUBSTRING(u.user_password, 1, 64) AS passwd, u.user_lastvisit AS lastLogin,
u.user_regdate AS dateRegistered,
SUBSTRING(u.user_from, 1, 255) AS location,
u.user_posts AS posts, IF(u.user_level = 1, 1, hxxp:mg.id [nonactive]_GROUP) AS ID_GROUP,
u.user_new_privmsg AS instantMessages,
SUBSTRING(u.user_email AS emailAddress, 1, 255) AS emailAddress,
u.user_unread_privmsg AS unreadMessages,
SUBSTRING(u.user_msnm AS MSN, 1, 255) AS MSN,
SUBSTRING(u.user_aim AS 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 websiteTitle,
SUBSTRING(u.user_website, 1, 255) AS websiteUrl,
u.user_allow_viewonline AS showOnline, u.user_timezone AS timeOffset,
IF(u.user_viewemail = 1, 0, 1) AS hideEmail, 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 messageLabels,
'' AS personalText, '' AS timeFormat, '' AS usertitle, '' AS memberIP,
'' AS secretQuestion, '' AS secretAnswer, '' AS validation_code,
'' AS additionalGroups, '' AS smileySet, '' AS passwordSalt,
'' AS memberIP2
FROM `frenchwi_phpbb1`.phpbb_users AS u
LEFT JOIN `frenchwi_phpbb1`.phpbb_ranks AS r ON (r.rank_id = u.user_rank AND r.rank_special = 1)
LEFT JOIN `frenchwi_asdwforum`.smf_membergroups AS mg ON (BINARY mg.groupName = CONCAT('phpBB ', r.rank_title))
WHERE u.user_id != -1
GROUP BY u.user_id
LIMIT 0, 500;
Caused the error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS emailAddress, 1, 255) AS emailAddress,
u.user_unread_privms

I am very new to all this and would appreciate a dummys guide to resolving this problem

thanks

[Ferny]

frwjd: see my post above yours and download that converter It fixes this error and some others

[frwjd]

Ferny

What can I say? Superb!

sincere thanks

[Kakao]

Worked perfectly for me. Thanks.

[janmartin]

Using phpbb2_to_smf_2006_09_02.zip from above i got this error 23 times:

Warning: copy(/home/mysite/public_html/smf/Smileys/default/icon_confused.gif) [function.copy]: failed to open stream: Permission denied in /home/mysite/public_html/smf/convert.php(653) : eval()'d code on line 16

Any idea?
The rest seems to have worked.

[Ferny]

I think you have to make your directory writable
Chmod it to 777 and try again

If you don't know how to do it or if you can't, you can also & paste the files from one directory to another. Affected files are smilies from phpbb. If you do it, you don't have to run the script again

[JayBachatero]

It might be that the file is missing.  Make sure that the file actually exists.

[janmartin]

Using phpbb2_to_smf_2006_09_02.zip from above with and smf 1.1 RC3 I noticed that the thumbnails are not converted and therefore not displayed.
So  attachments are displayed as a link only.

First I thought its a misconfiguaration in smf, but then I created a testuser and posted a new topic with an attachment. A thumbnail has been created and is displayed properly as a resized thumbnail and also link to the full size picture.

However thumbnails for converted attachments are missing in the smf/attachments folder and also the entries in the smf_attachments table.

Any idea?

The phpBB2 thumbs folder has right 0777 and the thumbnails 0644.

This is what phpmyadmin displays.
The top line is the thumbnail for the line under it.
All other lines are just attachments, but no thumbnails.

Thanks.

ID_ATTACH Descending       ID_THUMB       ID_MSG       ID_MEMBER       attachmentType       filename       size       downloads       width       height 68    0    192    0    3    Who is this again.jpg_thumb    206866    0    400    300 67    68     192    0    0    Who is this again.jpg    600535    1    2048    1536 66    0    190    0    0    Ikea Job Interview.jpg    39305    2    0    0 65    0    178    0    0    P7050025.jpg    282518    1    0    0 64    0    177    0    0    rotk_hobbitfoot.jpg    138667    0    0    0

[JayBachatero]

At the moment the conversion doesn't support converting thumbnails.

[stockkarts]

we also have a problem

http://www.simplemachines.org/community/index.php?topic=112262.msg727938#new

we would really like to convert

[j3cubcapt]

I WAS having trouble converting from PHPbb 2.0.21 to SMF 1.1 RC3, until I found this post. I used the phpbb2_to_smf_2006_09_02 Converter and everything including the Avatars came over.

Thanks
Will

[JayBachatero]

stockkarts did you try thos file?
http://person.acentermedia.net/SMF/phpbb2_to_smf_2006_08_26-3-utf8.zip

[stockkarts]

I got this

Notice: Undefined index: convert_script in /var/www/html/stockkarts2/convert.php on line 572

Warning: Invalid argument supplied for foreach() in /var/www/html/stockkarts2/convert.php on line 575

Notice: Undefined index: convert_script in /var/www/html/stockkarts2/convert.php on line 580
Successful.
Recalculating forum statistics...
Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 922

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 932

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 940

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 950

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 963

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 976

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1022

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1024

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1030

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1036

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1047

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1048

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1056

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1058

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1064

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1065

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1071

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1072

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1096

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1098

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1104

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1116

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1141

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1143

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1179

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1180

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1218

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1223

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1284

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1286

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1322

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1341

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1817

Notice: Undefined variable: result in /var/www/html/stockkarts2/convert.php on line 1818

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/html/stockkarts2/convert.php on line 1373
Successful.

but still no change to the new forum...