[SMF Converter] phpBB - 2.0.19+

[dusanp]

I have the same problem ... Wrong value type sent to the database. Integer expected. (id_member_updated)

Is there any way to go smf 2.0 direct or we must install smf1 and then update?
In that 2 cases ... result will be the same or not?

What to do?

If I must install smf1 where to find phpbb to smf1 converter? On download page there is only 2.0 version?
What is problem in php db?

(id_member_updated) ... I cant find that table ... or its a row? can i change that id or what to do

_____________________________
edit: I downloaded 1.1.14 smf and converter for smf 1 ... try to convert ... it was going ok but ... there is an error again

Converting posts (this may take some time)... Unsuccessful!
This query:

    SELECT
    hxxp:p.post [nonactive]_id AS ID_MSG, p.topic_id AS ID_TOPIC, hxxp:p.post [nonactive]_time AS posterTime,
    p.poster_id AS ID_MEMBER,
    SUBSTRING(IFNULL(u.user_email, ''), 1, 255) AS posterEmail,
    SUBSTRING(IF(IFNULL(pt.post_subject, '') = '', t.topic_title, hxxp:pt.post [nonactive]_subject), 1, 255) AS subject,
    SUBSTRING(IF(IFNULL(p.post_username, '') = '', u.username, hxxp:p.post [nonactive]_username), 1, 255) AS posterName,
    p.enable_smilies AS smileysEnabled, IF(p.post_edit_time > 0, hxxp:p.post [nonactive]_edit_time, 0) AS modifiedTime,
    CONCAT_WS('.', CONV(SUBSTRING(p.poster_ip, 1, 2), 16, 10), CONV(SUBSTRING(p.poster_ip, 3, 2), 16, 10), CONV(SUBSTRING(p.poster_ip, 5, 2), 16, 10), CONV(SUBSTRING(p.poster_ip, 7, 2), 16, 10)) AS posterIP,
    SUBSTRING(REPLACE(REPLACE(IF(pt.bbcode_uid = '', hxxp:pt.post [nonactive]_text, REPLACE(REPLACE(REPLACE(pt.post_text, CONCAT(':u:', pt.bbcode_uid), ''), CONCAT(':1:', pt.bbcode_uid), ''), CONCAT(':', pt.bbcode_uid), '')), '\n', '<br />'), '"', '"'), 1, 65534) AS body,
    p.forum_id AS ID_BOARD, '' AS modifiedName, 'xx' AS icon
    FROM (`sugdump`.phpbb_posts AS p, `sugdump`.phpbb_posts_text AS pt, `sugdump`.phpbb_topics AS t)
    LEFT JOIN `sugdump`.phpbb_users AS u ON (u.user_id = p.poster_id)
    WHERE hxxp:pt.post [nonactive]_id = hxxp:p.post [nonactive]_id
    AND t.topic_id = p.topic_id
    LIMIT 120800, 200;

Caused the error:

    MySQL server has gone away

what to do? I`ll do this in local ... on wamp program for local server ... my forum has 253720 posts, 12919 themes and 11626 users ... is that problem?

[LiveWire]

I am having the same issue with id_member_updated. I have been trying to figure out the originating phpBB column that I am assuming is null. I looked at the code, found where id_member_updated is being written and searched back to where the corresponding variable was being set. I copied the SQL that pulled the value in. It is below. I then ran that in phpAdmin with the thought being to find the bad data and correct. Every value returned as myid_first_msg and myid_last_msg is null. So I am skeptical that this is bad data specific to my database or the several others that have had the problem.

Code Select Expand


SELECT t.id_topic, MIN( m.id_msg ) AS myid_first_msg, t.id_first_msg, MAX( m.id_msg ) AS myid_last_msg, t.id_last_msg, COUNT( m.id_msg ) -1 AS my_num_replies, t.num_replies
FROM smf_topics AS t
LEFT JOIN smf_messages AS m ON ( m.id_topic = t.id_topic )
GROUP BY t.id_topic
HAVING id_first_msg != myid_first_msg
OR id_last_msg != myid_last_msg
OR num_replies != my_num_replies


[LiveWire]

It looks like it is probably earlier, I am looking the SQL file instead of convert.php. I added the order by and am getting one null.

SELECT
   t.topic_id AS id_topic, (t.topic_type = 1 OR t.topic_type = 2) AS is_sticky,
   t.topic_first_post_id AS id_first_msg, t.topic_last_post_id AS id_last_msg,
   t.topic_poster AS id_member_started, p.poster_id AS id_member_updated,
   t.forum_id AS id_board, IF(v.vote_id != 0, v.vote_id, 0) AS id_poll, t.topic_status = 1 AS locked,
   t.topic_replies AS num_replies, t.topic_views AS num_views
FROM phpbb_topics AS t
   LEFT JOIN phpbb_posts AS p ON (p.post_id = t.topic_last_post_id)
   LEFT JOIN phpbb_vote_desc AS v ON (v.topic_id = t.topic_id)

GROUP BY t.topic_id
HAVING id_first_msg != 0
   AND id_last_msg != 0
order by id_member_updated

[dusanp]

Convert to 1.1.14 and than to 2.0 its easy and fast, save your time

[LiveWire]

My concern about doing that was your last post still indicated you were having issues. I take it you resolved them.

I think I can fix the one topic that is giving the problem. The post indicated by the last post ID in the topic does not exist. I need to figure out the post ID of the real last post.

[dusanp]

i thought you have same issue because you wrote that in first post ...
I finished converting with smf 1 ...

you want me to look for id of the last post?  This is general problem ... not only in my case

If you want I can find that ... just tell me where to look

[LiveWire]

I was referring to your second error:

edit: I downloaded 1.1.14 smf and converter for smf 1 ... try to convert ... it was going ok but ... there is an error again

Anyway, the conversion is done straight to SMF 2. From the query I posted earlier which came from the convert sql file, I found the topic having problems. I searched in the database in the posts table for records linked to that topic. I copied the highest post ID and put that as the last post ID in the topic record. I reran convert and it is done. There was an issue during the recalc, but I can do that through the admin.

[dusanp]

awesome ... I didn't saw it at first ... tnx ... it will be much easier for all now
my second error was something else ... I forgot what ... I resolved that ...

[LiveWire]

I guess I never hit submit. This has been sitting in the quick reply window for a while.

There were problems with the data after. I got a fresh export of the DB from the old site and reran the convert. Had to change this:

   SUBSTRING(IF(IFNULL(p.post_username, '') = '', u.username, IFNULL(p.post_username, '')), 1, 255)

to:
   SUBSTRING(IF(IFNULL(p.post_username, '') = '', IFNULL(u.username, ''), IFNULL(p.post_username, '')), 1, 255)

then:
   SUBSTRING(uf.username, 1, 255) AS from_name,

to:
   SUBSTRING(ifnull(uf.username,''), 1, 255) AS from_name,

That got the convert to run and did not even get the errors during the recalc. All the boards are in the same category.

Since then, I noticed that attachments were all lost.

[TGSICD]

Error below when converting from phpBB2 to smf1.1.16:

Converting...

Recalculating forum statistics... Unsuccessful!

This query:



    UPDATE `pinecam`.smf_topics

    SET ID_FIRST_MSG = '',

    ID_MEMBER_STARTED = '0', ID_LAST_MSG = '',

    ID_MEMBER_UPDATED = '0', numReplies = '-1'

    WHERE ID_TOPIC = 42173

    LIMIT 1;



Caused the error:



    Duplicate entry '0-2' for key 2

I even typed in the topic id in my url and it got my no where.(Could not find this link)   /smf/index.php?topic=42173

Was told to change                    $sql = "SELECT * FROM users WHERE uid=".$uid;
with  (In forum)                         $sql = "SELECT * FROM users WHERE uid='".$uid."'";

as a last resort used the converter and sql from this forum, different error message this time:

SELECT
p.post_id AS ID_MSG, p.topic_id AS ID_TOPIC, hxxp:p.post [nonactive]_time AS posterTime,
p.poster_id AS ID_MEMBER,
SUBSTRING(IFNULL(u.user_email, ''), 1, 255) AS posterEmail,
SUBSTRING(IF(IFNULL(pt.post_subject, '') = '', t.topic_title, hxxp:pt.post [nonactive]_subject), 1, 255) AS subject,
SUBSTRING(IF(IFNULL(p.post_username, '') = '', u.username, hxxp:p.post [nonactive]_username), 1, 255) AS posterName,
p.enable_smilies AS smileysEnabled, IF(p.post_edit_time > 0, hxxp:p.post [nonactive]_edit_time, 0) AS modifiedTime,
CONCAT_WS('.', CONV(SUBSTRING(p.poster_ip, 1, 2), 16, 10), CONV(SUBSTRING(p.poster_ip, 3, 2), 16, 10), CONV(SUBSTRING(p.poster_ip, 5, 2), 16, 10), CONV(SUBSTRING(p.poster_ip, 7, 2), 16, 10)) AS posterIP,
SUBSTRING(REPLACE(REPLACE(IF(pt.bbcode_uid = '', hxxp:pt.post [nonactive]_text, REPLACE(REPLACE(REPLACE(pt.post_text, CONCAT(':u:', pt.bbcode_uid), ''), CONCAT(':1:', pt.bbcode_uid), ''), CONCAT(':', pt.bbcode_uid), '')), '\n', '<br />'), '"', '"'), 1, 65534) AS body,
p.forum_id AS ID_BOARD, '' AS modifiedName, 'xx' AS icon
FROM (`pinecam`.phpbb_posts AS p, `pinecam`.phpbb_posts_text AS pt, `pinecam`.phpbb_topics AS t)
LEFT JOIN `pinecam`.phpbb_users AS u ON (u.user_id = p.poster_id)
WHERE hxxp:pt.post [nonactive]_id = hxxp:p.post [nonactive]_id
AND t.topic_id = p.topic_id
LIMIT 485000, 200;
Caused the error:

When I check the scripts it seems to be ok. Is there something I am missing?

[Cirmius]

This is first time when I'm using SMF Converter. What should I do with these files?

[ThorstenE]

http://wiki.simplemachines.org/smf/Converting

[thuul]

EDIT: This was supposed to go in the phpBB3 thread

[stungate]

Trying to convert but have this error log:

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 `incubus_st****te`.phpbb_users AS u
LEFT JOIN `incubus_st****te`.phpbb_ranks AS r ON (r.rank_id = u.user_rank AND r.rank_special = 1)
LEFT JOIN `incubus_smf`.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 '='

Can this be fixed? Not sure what to change?

Thanks.

[margarett]

Hi and welcome to sm.org
That means that you have tables/columns in your phpBB database with different collations and MySQL can't work that out...
You need to convert everything to utf8_general_ci

[stungate]

Quick question: If I convert all of the utf8_unicode to utf8_general, will that affect the functioning of my current phpbb board? Have backup just in case...

[margarett]

You have to backup, of course

It shouldn't cause any issues but I really hate these collations problems