[SMF Converter] UBB.threads 7.5

[forumfjott]

error:

Converting members... Successful.
Converting categories... Successful.
Converting boards... Successful.
Converting topics... Unsuccessful!
This query:
SELECT
t.TOPIC_ID AS id_topic, t.TOPIC_IS_STICKY AS is_sticky, t.FORUM_ID AS id_board,
t.POST_ID AS id_first_msg, t.TOPIC_LAST_POST_ID AS id_last_msg,
t.USER_ID AS id_member_started, t.TOPIC_LAST_POSTER_ID AS id_member_updated,
$row['id_member_updated'] = (int) $row['id_member_updated']
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 '['id_member_updated'] = (int) $row['id_member_updated']
LIMIT 0, 500' at line 5

edited xxx.sql:

Code Select Expand

/******************************************************************************/
--- Converting topics...
/******************************************************************************/

TRUNCATE {$to_prefix}topics;
TRUNCATE {$to_prefix}log_topics;
TRUNCATE {$to_prefix}log_boards;
TRUNCATE {$to_prefix}log_mark_read;

---* {$to_prefix}topics
---{
$row['id_last_msg'] = (int) $row['id_last_msg'];
$ignore = true;
---}
SELECT
t.TOPIC_ID AS id_topic, t.TOPIC_IS_STICKY AS is_sticky, t.FORUM_ID AS id_board,
t.POST_ID AS id_first_msg, t.TOPIC_LAST_POST_ID AS id_last_msg,
t.USER_ID AS id_member_started, t.TOPIC_LAST_POSTER_ID AS id_member_updated,
$row['id_member_updated'] = (int) $row['id_member_updated'];
IFNULL(ps.POLL_ID, 0) AS id_poll,
t.TOPIC_REPLIES AS num_replies, t.TOPIC_VIEWS AS num_views,
t.TOPIC_STATUS = 'C' AS locked, t.TOPIC_IS_APPROVED as approved
FROM {$from_prefix}TOPICS AS t
LEFT JOIN {$from_prefix}POLL_DATA as ps ON (ps.POST_ID = t.POST_ID);
---*

[margarett]

Not like that

Code Select Expand


---* {$to_prefix}topics
---{
$row['id_last_msg'] = (int) $row['id_last_msg'];
$row['id_member_updated'] = (int) $row['id_member_updated'];

$ignore = true;
---}


[forumfjott]

I moved the last entry to the line under tha last one like you show, and now things are happening ... I think

GEt one msg:

Converting...
Converting posts (this may take some time)...
Duplicate entry '143276' for key 1

but it looks like it's working with something.
A progress bar or icon would be usefull.

[margarett]

Yes, you've past topics conversion

That error can also be bypassed with:

Code Select Expand


/******************************************************************************/
--- Converting posts (this may take some time)...
/******************************************************************************/

TRUNCATE {$to_prefix}messages;

---* {$to_prefix}messages 200
---{
$row['subject'] = str_replace(array("'", '"'), array("'", """), $row['subject']);
$row['body'] = strtr(str_replace(array("'", '"'), array("'", "&quot;"), $row['body']), array("\n" => '<br />'));
$ignore = true;
---}

(note the new "$ignore=true" line )

[forumfjott]

It looks like it's stuck.
I'll add this last line and run again.

EDIT: I see the count rising in the URL adress bar! 288200 and counting... 585000 all together
Thanks a lot for your help so far

[forumfjott]

will the converter continue working if I take my PC offline? I have to leave to take the train home and PC will be offline for 45 minutes... and it still have to count 140.000 posts + all the next processes

[margarett]

No, it won't The only way to do that is via CLI (server side) but:
- not sure if it works (probably no one uses that for a long time)
- you need to have SSH access to do do that (and you probably don't)

[forumfjott]

hope it picks up from where it left then
If I leave it here, it will go to sleep mode anyway.

[margarett]

You can disable sleep mode in power options

I will restart. You see that every database operation starts with "TRUNCATE" to the destination table, which means empty it.

[forumfjott]

cannot change properties for sleep mode on my company computer
had to restart, but the first 200.000 post went quick. Now it counts 600 post pr. cycle, but ony 120.000 to go before next step of the convertion

[forumfjott]

Converting posts (this may take some time)...
Successful.
Recounting post counts...Wrong value type sent to the database. Integer expected. (date_registered)

Nothing is straight forward with this convert

EDIT: tried to insert a line like those above, and it started counting posts again...cr@p

[margarett]

Following same logics:

Code Select Expand


/******************************************************************************/
--- Recounting post counts...
/******************************************************************************/

---* {$to_prefix}members 20
---{
$type = 'replace';
---}

Replace with:

Code Select Expand


/******************************************************************************/
--- Recounting post counts...
/******************************************************************************/

---* {$to_prefix}members 20
---{
$type = 'replace';
$row['date_registered'] = (int) $row['date_registered'];
---}


Nothing is straight forward with this convert

Eeeeexactly!
Converters are really complicated pieces of software, dealing with *very* different server setups, databases, etc etc... Unfortunately it is, most of the times, difficult...

[forumfjott]

hehehe... I see the light in the end of the tunnel, so I bear with it.

I did see the similarity in the script, so I inserted that line, but over the $type line. does it matter? hope not.
I also want to increse the amount of posts since it will be a lot of counting i presume.

[margarett]

It shouldn't matter, no.

Yes, you should be able to do that, yes. Maybe that "200" in "---* {$to_prefix}messages 200"

[forumfjott]

i set 600 and forum still seems quick and OK.
I also raised the members counting to 35. It has about 5500 members.

[margarett]

Please note that you can increase that as much as you want. It will only stop when you hit some limit (memory, timeout, max connections, etc) or if your MySQL "dies" due to excessive connections and the converter hangs.
In any case, just decrease and retry

[forumfjott]

Converting posts (this may take some time)...
Successful.
Recounting post counts...Wrong value type sent to the database. Integer expected. (id_group)

This is just the second of the list for the current process:

Code Select Expand

SELECT m.id_member, mem.member_name, mem.date_registered, COUNT(*) as posts, mem.id_group, mem.lngfile, mem.last_login, mem.real_name, mem.instant_messages, mem.unread_messages,

mem.new_pm, mem.buddy_list, mem.pm_ignore_list, mem.pm_prefs, mem.mod_prefs, mem.message_labels, mem.passwd, mem.openid_uri, mem.email_address, mem.personal_text, mem.gender,

mem.birthdate, mem.website_title, mem.website_url, mem.location, mem.icq, mem.aim, mem.yim, mem.msn, mem.hide_email, mem.show_online, mem.time_format, mem.signature,

mem.time_offset, mem.avatar, mem.pm_email_notify, mem.karma_bad, mem.karma_good, mem.usertitle, mem.notify_announcements, mem.notify_regularity, mem.notify_send_body,

mem.notify_types, mem.member_ip, mem.member_ip2, mem.secret_question, mem.secret_answer, mem.id_theme, mem.is_activated, mem.validation_code, mem.id_msg_last_visit,

mem.additional_groups, mem.smiley_set, mem.id_post_group, mem.total_time_logged_in, mem.password_salt, mem.ignore_boards, mem.warning, mem.passwd_flood, mem.pm_receive_from
FROM {$to_prefix}messages as m

Could just as well make new lines for every item?
I cannot run through all posts so many times as there is values

[margarett]

Well, we're still to find out why is it triggering that error! Because the origin fields are, for sure, integers... And, surprisingly enough, it is now in SMF tables already...

Anyway, you can do that for the integer fields only.

I would advise you to skip that part and have, later, SMF itself to recount totals.

[forumfjott]

aaaand how to set a skip command?

[margarett]

Now isn't that the 1.000.000$ question?

I'm not sure, TBH, but I *think* that you just need to remove all of this (DO BACKUP your .sql file as you have already some changes to the one from here):

Code Select Expand


/******************************************************************************/
--- Recounting post counts...
/******************************************************************************/

---* {$to_prefix}members 20
---{
$type = 'replace';
---}

SELECT m.id_member, mem.member_name, mem.date_registered, COUNT(*) as posts, mem.id_group, mem.lngfile, mem.last_login, mem.real_name, mem.instant_messages, mem.unread_messages, mem.new_pm, mem.buddy_list, mem.pm_ignore_list, mem.pm_prefs, mem.mod_prefs, mem.message_labels, mem.passwd, mem.openid_uri, mem.email_address, mem.personal_text, mem.gender, mem.birthdate, mem.website_title, mem.website_url, mem.location, mem.icq, mem.aim, mem.yim, mem.msn, mem.hide_email, mem.show_online, mem.time_format, mem.signature, mem.time_offset, mem.avatar, mem.pm_email_notify, mem.karma_bad, mem.karma_good, mem.usertitle, mem.notify_announcements, mem.notify_regularity, mem.notify_send_body, mem.notify_types, mem.member_ip, mem.member_ip2, mem.secret_question, mem.secret_answer, mem.id_theme, mem.is_activated, mem.validation_code, mem.id_msg_last_visit, mem.additional_groups, mem.smiley_set, mem.id_post_group, mem.total_time_logged_in, mem.password_salt, mem.ignore_boards, mem.warning, mem.passwd_flood, mem.pm_receive_from
FROM {$to_prefix}messages as m
LEFT JOIN {$to_prefix}members as mem ON (m.id_member = mem.id_member)
WHERE m.id_member != 0
GROUP BY m.id_member;
---*'


emanuele?