News:

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

Main Menu

Converting XMB to SMF

Started by Tom84, September 22, 2020, 08:53:17 AM

Previous topic - Next topic

Tom84

Hello, i have an XMB forum, and i wanted to convert it to SMF.

I already had some issues and i have fixed them, but still i can't find out what still is wrong on it.


This is what i can't still find out what is wrong on it:

Converting topics...
Unsuccessful!
This query:
SELECT
t.tid AS id_topic, t.topped AS is_sticky, t.fid AS id_board,
IFNULL(uf.uid, 0) AS id_member_started, t.replies AS num_replies,
t.views AS num_views,
CASE WHEN t.closed = 'yes' THEN 1 ELSE 0 END AS locked, MIN(p.pid) AS id_first_msg,
MAX(p.pid) AS id_last_msg, IF(t.pollopts != '', t.tid, 0) AS id_poll
FROM `forocato_seN1zEt`.xmb_threads AS t
INNER JOIN `forocato_seN1zEt`.xmb_posts AS p ON (p.tid = t.tid)
LEFT JOIN `forocato_seN1zEt`.xmb_members AS uf ON (uf.username = hxxp:t.author [nonactive])
GROUP BY t.tid
HAVING id_first_msg != 0
AND id_last_msg != 0
LIMIT 0, 500;
Caused the error:
1055





This is what i fixed:

   END AS id_group, '' AS lngfile, '' AS buddy_list, '' AS pm_ignore_list,
   '' AS message_labels, '' AS time_format, '' AS usertitle, '' AS member_ip,
   '' AS secret_question, '' AS secret_answer, '' AS validation_code,
   '' AS additional_groups, '' AS smiley_set, '' AS password_salt,
   '' AS member_ip2, '' AS openid_uri, '' AS ignore_boards

The lastests 2 ''AS openid and the ignore_boards one were missing.
Until this, you will have all users, passwords and emails migrated.


if someone can help me, i would apreciate it a lot ! Thanks

vbgamer45

Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Tom84

i know on this case is a trouble of language between versions of mysql.

But i can't find where it is. I have another issue, i can't do what you say because it's on a web hosting provider the database, i can't modify anything except user privilege on it. That's why i'm asking for help. But thanks anyway

vbgamer45

Group by in new mysql versions require all select items to be in group by clause now

You would need to change the GROUP BY t.tid

To something like
GROUP BY t.tid, is_sticky, id_board,  id_member_started, num_views, num_replies,  id_first_msg, id_last_msg, id_poll
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Tom84

Converting...
Converting members... Successful.
Converting categories... Successful.
Converting boards... Successful.
Converting topics... Unsuccessful!
This query:
SELECT
t.tid AS id_topic, t.topped AS is_sticky, t.fid AS id_board,
IFNULL(uf.uid, 0) AS id_member_started, t.replies AS num_replies,
t.views AS num_views,
CASE WHEN t.closed = 'yes' THEN 1 ELSE 0 END AS locked, MIN(p.pid) AS id_first_msg,
MAX(p.pid) AS id_last_msg, IF(t.pollopts != '', t.tid, 0) AS id_poll
FROM `forocato_seN1zEt`.xmb_threads AS t
INNER JOIN `forocato_seN1zEt`.xmb_posts AS p ON (p.tid = t.tid)
LEFT JOIN `forocato_seN1zEt`.xmb_members AS uf ON (uf.username = hxxp:t.author [nonactive])
GROUP BY t.tid, is_sticky, id_board, id_member_started, num_views, num_replies, id_first_msg, id_last_msg, id_poll
HAVING id_first_msg != 0
AND id_last_msg != 0
LIMIT 0, 500;
Caused the error:
1056



it's an error group field now, hahaha, this is like a karma. i can't migrate the database, i'm trying since 2 days with all this

vbgamer45

Try running another query before it such as
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Tom84

you mean something like this ?

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

TRUNCATE {$to_prefix}topics;
TRUNCATE {$to_prefix}log_topics;
TRUNCATE {$to_prefix}log_boards;
TRUNCATE {$to_prefix}log_mark_read;
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

---* {$to_prefix}topics
SELECT
   t.tid AS id_topic, t.topped AS is_sticky, t.fid AS id_board,
   IFNULL(uf.uid, 0) AS id_member_started, t.replies AS num_replies,
   t.views AS num_views,
   CASE WHEN t.closed = 'yes' THEN 1 ELSE 0 END AS locked, MIN(p.pid) AS id_first_msg,
   MAX(p.pid) AS id_last_msg, IF(t.pollopts != '', t.tid, 0) AS id_poll
FROM {$from_prefix}threads AS t
   INNER JOIN {$from_prefix}posts AS p ON (p.tid = t.tid)
   LEFT JOIN {$from_prefix}members AS uf ON (uf.username = hxxp:t.author [nonactive])
GROUP BY t.tid, is_sticky, id_board,  id_member_started, num_views, num_replies,  id_first_msg, id_last_msg, id_poll
HAVING id_first_msg != 0
   AND id_last_msg != 0;
---*

---* {$to_prefix}topics (update id_topic)
SELECT hxxp:t.id [nonactive]_topic, uf.uid AS id_member_updated
FROM {$to_prefix}topics AS t
   INNER JOIN {$from_prefix}posts AS p ON (p.pid = hxxp:t.id [nonactive]_last_msg)
   INNER JOIN {$from_prefix}members AS uf ON (uf.username = hxxp:p.author [nonactive]);
---*




sorry for my ignorance, i just have a litle more knowledge than the average on this, but i'm not an expert :(

vbgamer45

Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Tom84

i've tryed it, still same error :(


SELECT
t.tid AS id_topic, t.topped AS is_sticky, t.fid AS id_board,
IFNULL(uf.uid, 0) AS id_member_started, t.replies AS num_replies,
t.views AS num_views,
CASE WHEN t.closed = 'yes' THEN 1 ELSE 0 END AS locked, MIN(p.pid) AS id_first_msg,
MAX(p.pid) AS id_last_msg, IF(t.pollopts != '', t.tid, 0) AS id_poll
FROM `forocato_seN1zEt`.xmb_threads AS t
INNER JOIN `forocato_seN1zEt`.xmb_posts AS p ON (p.tid = t.tid)
LEFT JOIN `forocato_seN1zEt`.xmb_members AS uf ON (uf.username = hxxp:t.author [nonactive])
GROUP BY t.tid, is_sticky, id_board, id_member_started, num_views, num_replies, id_first_msg, id_last_msg, id_poll
HAVING id_first_msg != 0
AND id_last_msg != 0
LIMIT 0, 500;
Caused the error:
1056

vbgamer45

Try
SET sql_mode='';
SET SESSION sql_mode = '';
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Tom84

sorry for the delay on the answer, same issue
This is what i have on the file:

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

TRUNCATE {$to_prefix}topics;
TRUNCATE {$to_prefix}log_topics;
TRUNCATE {$to_prefix}log_boards;
TRUNCATE {$to_prefix}log_mark_read;
SET sql_mode='';
SET SESSION sql_mode = '';

---* {$to_prefix}topics
SELECT
   t.tid AS id_topic, t.topped AS is_sticky, t.fid AS id_board,
   IFNULL(uf.uid, 0) AS id_member_started, t.replies AS num_replies,
   t.views AS num_views,
   CASE WHEN t.closed = 'yes' THEN 1 ELSE 0 END AS locked, MIN(p.pid) AS id_first_msg,
   MAX(p.pid) AS id_last_msg, IF(t.pollopts != '', t.tid, 0) AS id_poll
FROM {$from_prefix}threads AS t
   INNER JOIN {$from_prefix}posts AS p ON (p.tid = t.tid)
   LEFT JOIN {$from_prefix}members AS uf ON (uf.username = hxxp:t.author [nonactive])
GROUP BY t.tid, is_sticky, id_board,  id_member_started, num_views, num_replies,  id_first_msg, id_last_msg, id_poll
HAVING id_first_msg != 0
   AND id_last_msg != 0;
---*

---* {$to_prefix}topics (update id_topic)
SELECT hxxp:t.id [nonactive]_topic, uf.uid AS id_member_updated
FROM {$to_prefix}topics AS t
   INNER JOIN {$from_prefix}posts AS p ON (p.pid = hxxp:t.id [nonactive]_last_msg)
   INNER JOIN {$from_prefix}members AS uf ON (uf.username = hxxp:p.author [nonactive]);
---*




---------------------

This is what i have on the converting php web:

Converting members... Successful.
Converting categories... Successful.
Converting boards... Successful.
Converting topics... Unsuccessful!
This query:
SELECT
t.tid AS id_topic, t.topped AS is_sticky, t.fid AS id_board,
IFNULL(uf.uid, 0) AS id_member_started, t.replies AS num_replies,
t.views AS num_views,
CASE WHEN t.closed = 'yes' THEN 1 ELSE 0 END AS locked, MIN(p.pid) AS id_first_msg,
MAX(p.pid) AS id_last_msg, IF(t.pollopts != '', t.tid, 0) AS id_poll
FROM `forocato_seN1zEt`.xmb_threads AS t
INNER JOIN `forocato_seN1zEt`.xmb_posts AS p ON (p.tid = t.tid)
LEFT JOIN `forocato_seN1zEt`.xmb_members AS uf ON (uf.username = hxxp:t.author [nonactive])
GROUP BY t.tid, is_sticky, id_board, id_member_started, num_views, num_replies, id_first_msg, id_last_msg, id_poll
HAVING id_first_msg != 0
AND id_last_msg != 0
LIMIT 0, 500;
Caused the error:
1056

vbgamer45

Change back to this for group by
GROUP BY t.tid
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Tom84

i think on the group by there is the mistake.

without the rest of t.tid i have 1055 code, and with, i have 1056 :(


funny, isn't it?

Tom84

i was thinking, what if i use the export/import from phpmyadmin, with the entire database ?

The trouble are the missing data

vbgamer45

You could try installing php/mysql locally and run the convertor on your own computer that might work just will take a while to setup....
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Tom84

did it, same issues, i can't, i can't and i can't

vbgamer45

Locally? then you should be able to edit your my.cnf or my.ini and change the sql_mode in the file
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Advertisement: