Advertisement:

Author Topic: Converting XMB to SMF  (Read 2086 times)

Offline Tom84

  • Newbie
  • *
  • Posts: 9
Converting XMB to SMF
« on: September 22, 2020, 08:53:17 AM »
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 = t.author)
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

Offline vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 22,953
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Converting XMB to SMF
« Reply #1 on: September 22, 2020, 10:28:41 AM »
https://stackoverflow.com/questions/36207042/error-code-1055-incompatible-with-sql-mode-only-full-group-by

You will need to set your sql_mode in your my.cnf in MySQL config to 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

Offline Tom84

  • Newbie
  • *
  • Posts: 9
Re: Converting XMB to SMF
« Reply #2 on: September 22, 2020, 10:48:39 AM »
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

Offline vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 22,953
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Converting XMB to SMF
« Reply #3 on: September 22, 2020, 10:58:10 AM »
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

Offline Tom84

  • Newbie
  • *
  • Posts: 9
Re: Converting XMB to SMF
« Reply #4 on: September 22, 2020, 11:13:18 AM »
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 = t.author)
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

Offline vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 22,953
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Converting XMB to SMF
« Reply #5 on: September 22, 2020, 11:15:43 AM »
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

Offline Tom84

  • Newbie
  • *
  • Posts: 9
Re: Converting XMB to SMF
« Reply #6 on: September 22, 2020, 11:21:10 AM »
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 = t.author)
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 t.id_topic, uf.uid AS id_member_updated
FROM {$to_prefix}topics AS t
   INNER JOIN {$from_prefix}posts AS p ON (p.pid = t.id_last_msg)
   INNER JOIN {$from_prefix}members AS uf ON (uf.username = p.author);
---*




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

Offline vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 22,953
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Converting XMB to SMF
« Reply #7 on: September 22, 2020, 11:24:31 AM »
Yes exactly
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

Offline Tom84

  • Newbie
  • *
  • Posts: 9
Re: Converting XMB to SMF
« Reply #8 on: September 22, 2020, 11:26:15 AM »
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 = t.author)
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

Offline vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 22,953
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Converting XMB to SMF
« Reply #9 on: September 22, 2020, 11:28:11 AM »
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

Offline Tom84

  • Newbie
  • *
  • Posts: 9
Re: Converting XMB to SMF
« Reply #10 on: September 22, 2020, 12:06:47 PM »
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 = t.author)
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 t.id_topic, uf.uid AS id_member_updated
FROM {$to_prefix}topics AS t
   INNER JOIN {$from_prefix}posts AS p ON (p.pid = t.id_last_msg)
   INNER JOIN {$from_prefix}members AS uf ON (uf.username = p.author);
---*




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

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 = t.author)
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

Offline vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 22,953
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Converting XMB to SMF
« Reply #11 on: September 22, 2020, 12:11:44 PM »
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

Offline Tom84

  • Newbie
  • *
  • Posts: 9
Re: Converting XMB to SMF
« Reply #12 on: September 22, 2020, 12:16:20 PM »
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?

Offline Tom84

  • Newbie
  • *
  • Posts: 9
Re: Converting XMB to SMF
« Reply #13 on: September 23, 2020, 07:39:05 AM »
i was thinking, what if i use the export/import from phpmyadmin, with the entire database ?

The trouble are the missing data

Offline vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 22,953
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Converting XMB to SMF
« Reply #14 on: September 23, 2020, 08:14:14 AM »
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

Offline Tom84

  • Newbie
  • *
  • Posts: 9
Re: Converting XMB to SMF
« Reply #15 on: September 23, 2020, 05:21:37 PM »
did it, same issues, i can't, i can't and i can't

Offline vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 22,953
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Converting XMB to SMF
« Reply #16 on: September 23, 2020, 05:33:17 PM »
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