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
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 = ''
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
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
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
Try running another query before it such as
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
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 :(
Yes exactly
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
Try
SET sql_mode='';
SET SESSION sql_mode = '';
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
Change back to this for group by
GROUP BY t.tid
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?
i was thinking, what if i use the export/import from phpmyadmin, with the entire database ?
The trouble are the missing data
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....
did it, same issues, i can't, i can't and i can't
Locally? then you should be able to edit your my.cnf or my.ini and change the sql_mode in the file