Converting from IPB 3,.2 error help

Started by daveo1, October 19, 2020, 12:45:14 PM

Previous topic - Next topic

daveo1

Im helping a friend convert a forum and I ran into this while going thru topics. It really doesnt give too many details on it. Another non working forum! ;D

Converting...
Converting topics...
Unsuccessful!
This query:

    SELECT
    t.tid AS id_topic, t.pinned AS is_sticky, t.forum_id AS id_board,
    t.starter_id AS id_member_started, t.last_poster_id AS id_member_updated,
    IFNULL(pl.pid,0) AS id_poll, t.posts AS num_replies, t.views AS num_views,
    MIN(p.pid) AS id_first_msg, MAX(p.pid) AS id_last_msg,
    t.state = 'closed' AS locked, approved
    FROM (`doording_ipbforumold`.ipbtopics AS t, `doording_ipbforumold`.ipbposts AS p)
    LEFT JOIN `doording_ipbforumold`.ipbpolls AS pl ON (pl.tid = t.tid)
    WHERE p.topic_id = t.tid
    GROUP BY t.tid
    HAVING id_first_msg != 0
    AND id_last_msg != 0
    LIMIT 0, 500;

Caused the error:

    1146

vbgamer45

It means a table does not exist.
Try to run that query manually to get the full error message via phpmyadmin or mysql command prompt
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

daveo1

 MySQL said: Documentation
#1146 - Table 'doording_ipbforumold.ipbtopics' doesn't exist

Damnit, Im looking thru his back ups now

daveo1

Some "genius" converted the forum from fusionbb to IPB and the topics table that is in there was rigged somehow to work with IBP. Its the only one rigged to work. I renamed it to the correct table and it spits up another error. I guess cause the structure is different....Im not so sure I can make this table work.

MySQL said: Documentation
#1054 - Unknown column 't.tid' in 'field list'


vbgamer45

Do a mysql below and post back
show create table doording_ipbforumold.ipbtopics;
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

daveo1

Thank you again! Does this help?


Your SQL query has been executed successfully.
show create table doording_ipbforumold.ipbtopics
Profiling [Edit inline] [ Edit ] [ Create PHP code ] [ Refresh]
+ Options
Table    Create Table    
ipbtopics    CREATE TABLE `ipbtopics` (
`topic_id` mediumint(...

daveo1

    SELECT
    t.tid AS id_topic, t.pinned AS is_sticky, t.forum_id AS id_board,
    t.starter_id AS id_member_started, t.last_poster_id AS id_member_updated,
    IFNULL(pl.pid,0) AS id_poll, t.posts AS num_replies, t.views AS num_views,
    MIN(p.pid) AS id_first_msg, MAX(p.pid) AS id_last_msg,
    t.state = 'closed' AS locked, approved
    FROM (`doording_ipbforumold`.ipbtopics AS t, `doording_ipbforumold`.ipbposts AS p)
    LEFT JOIN `doording_ipbforumold`.ipbpolls AS pl ON (pl.tid = t.tid)
    WHERE p.topic_id = t.tid
    GROUP BY t.tid
    HAVING id_first_msg != 0
    AND id_last_msg != 0
    LIMIT 0, 500;

Caused the error:

    1054

vbgamer45

Quote from: daveo1 on October 19, 2020, 08:31:06 PM
Thank you again! Does this help?


Your SQL query has been executed successfully.
show create table doording_ipbforumold.ipbtopics
Profiling [Edit inline] [ Edit ] [ Create PHP code ] [ Refresh]
+ Options
Table    Create Table    
ipbtopics    CREATE TABLE `ipbtopics` (
`topic_id` mediumint(...

close but need to see beyond the ...
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

daveo1

This?

CREATE TABLE `ipbtopics` (
`topic_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_username` varchar(100) NOT NULL DEFAULT '',
`topic_name_color` varchar(15) NOT NULL DEFAULT '',
`topic_group_flag` varchar(30) NOT NULL DEFAULT '',
`topic_subject` varchar(255) DEFAULT NULL,
`topic_prefix` varchar(100) NOT NULL DEFAULT '',
`topic_replies` mediumint(4) unsigned DEFAULT '0',
`topic_views` mediumint(8) unsigned DEFAULT '0',
`topic_first_post_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_first_post_time` int(11) unsigned DEFAULT NULL,
`topic_last_post_id` mediumint(8) NOT NULL DEFAULT '0',
`topic_last_post_time` int(11) unsigned NOT NULL DEFAULT '0',
`topic_last_poster` varchar(100) NOT NULL DEFAULT '',
`topic_is_sticky` tinyint(1) unsigned NOT NULL DEFAULT '0',
`topic_is_closed` tinyint(1) unsigned NOT NULL DEFAULT '0',
`topic_icon` varchar(50) DEFAULT NULL,
`topic_files` tinyint(4) unsigned DEFAULT '0',
`topic_polls` tinyint(4) unsigned DEFAULT '0',
`topic_thumb` varchar(150) DEFAULT NULL,
`topic_moved` mediumint(8) NOT NULL DEFAULT '0',
`topic_preview` varchar(200) DEFAULT NULL,
`topic_in_portal` tinyint(1) unsigned NOT NULL DEFAULT '0',
`import_insert_id` int(11) DEFAULT NULL,
`topic_event` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_event_start` int(11) unsigned NOT NULL DEFAULT '0',
`topic_event_end` int(11) unsigned NOT NULL DEFAULT '0',
`is_moderated` tinyint(1) NOT NULL DEFAULT '0',
`rating` int(3) NOT NULL DEFAULT '0',
`topic_meta_desc` text NOT NULL,
`topic_meta_kywd` text NOT NULL,
`topic_announcement` tinyint(1) NOT NULL DEFAULT '0',
`topic_last_poster_id` mediumint(8) NOT NULL,
PRIMARY KEY (`topic_id`),
KEY `user_index` (`user_id`),
KEY `last_poster_index` (`topic_last_poster`),
KEY `username_index` (`topic_username`),
KEY `forum_index` (`forum_id`,`topic_is_sticky`,`topic_last_post_time`),
KEY `moved_ndx` (`topic_moved`),
KEY `topic_thumb_index` (`topic_thumb`),
FULLTEXT KEY `topic_subject` (`topic_subject`)
) ENGINE=MyISAM AUTO_INCREMENT=74667 DEFAULT CHARSET=latin1

vbgamer45

Change

    SELECT
    t.tid AS id_topic, t.pinned AS is_sticky, t.forum_id AS id_board,
    t.starter_id AS id_member_started, t.last_poster_id AS id_member_updated,
    IFNULL(pl.pid,0) AS id_poll, t.posts AS num_replies, t.views AS num_views,
    MIN(p.pid) AS id_first_msg, MAX(p.pid) AS id_last_msg,
    t.state = 'closed' AS locked, approved
    FROM (`doording_ipbforumold`.ipbtopics AS t, `doording_ipbforumold`.ipbposts AS p)
    LEFT JOIN `doording_ipbforumold`.ipbpolls AS pl ON (pl.tid = t.tid)
    WHERE p.topic_id = t.tid
    GROUP BY t.tid
    HAVING id_first_msg != 0
    AND id_last_msg != 0
    LIMIT 0, 500;


To


    SELECT
    t.topic_id AS id_topic, t.topic_is_sticky AS is_sticky, t.forum_id AS id_board,
    t.user_id AS id_member_started, t.topic_last_poster_id AS id_member_updated,
    IFNULL(pl.pid,0) AS id_poll, t.topic_replies AS num_replies, t.topic_views AS num_views,
    MIN(p.pid) AS id_first_msg, MAX(p.pid) AS id_last_msg,
    t.opic_is_closed AS locked, approved
    FROM (`doording_ipbforumold`.ipbtopics AS t, `doording_ipbforumold`.ipbposts AS p)
    LEFT JOIN `doording_ipbforumold`.ipbpolls AS pl ON (pl.tid = t.topic_id)
    WHERE p.topic_id = t.topic_id
    GROUP BY t.topic_id
    HAVING id_first_msg != 0
    AND id_last_msg != 0
    LIMIT 0, 500;
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

daveo1

That fixed it! I got another >:(

Here is the error...
Converting posts (this may take some time)... Unsuccessful!
This query:

    SELECT
    t.topic_id AS id_topic, t.topic_is_sticky AS is_sticky, t.forum_id AS id_board,
    t.user_id AS id_member_started, t.topic_last_poster_id AS id_member_updated,
    IFNULL(pl.pid,0) AS id_poll, t.topic_replies AS num_replies, t.topic_views AS num_views,
    MIN(p.pid) AS id_first_msg, MAX(p.pid) AS id_last_msg,
    t.opic_is_closed AS locked, approved
    FROM (`doording_ipbforumold`.ipbtopics AS t, `doording_ipbforumold`.ipbposts AS p)
    LEFT JOIN `doording_ipbforumold`.ipbpolls AS pl ON (pl.tid = t.topic_id)
    WHERE p.topic_id = t.topic_id
    GROUP BY t.topic_id
    HAVING id_first_msg != 0
    AND id_last_msg != 0
    TRUNCATE `doording_smf`.qza_messages;

Caused the error:

    1064

Here is the table in the db

CREATE TABLE `ipbposts` (
`pid` int(10) NOT NULL AUTO_INCREMENT,
`append_edit` tinyint(1) DEFAULT '0',
`edit_time` int(10) DEFAULT NULL,
`author_id` mediumint(8) NOT NULL DEFAULT '0',
`author_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`use_sig` tinyint(1) NOT NULL DEFAULT '0',
`use_emo` tinyint(1) NOT NULL DEFAULT '0',
`ip_address` varchar(46) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_date` int(10) DEFAULT NULL,
`post` mediumtext COLLATE utf8mb4_unicode_ci,
`queued` tinyint(1) NOT NULL DEFAULT '0',
`topic_id` int(10) NOT NULL DEFAULT '0',
`new_topic` tinyint(1) DEFAULT '0',
`edit_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`post_key` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
`post_htmlstate` smallint(1) NOT NULL DEFAULT '0',
`post_edit_reason` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_bwoptions` int(10) unsigned NOT NULL DEFAULT '0',
`pdelete_time` int(11) NOT NULL DEFAULT '0',
`post_field_int` int(10) NOT NULL DEFAULT '0',
`post_field_t1` text COLLATE utf8mb4_unicode_ci,
`post_field_t2` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`pid`),
KEY `topic_id` (`topic_id`,`queued`,`pid`,`post_date`),
KEY `author_id` (`author_id`,`post_date`,`queued`),
KEY `post_date` (`post_date`),
KEY `ip_address` (`ip_address`),
KEY `post_key` (`post_key`),
KEY `queued` (`queued`,`pdelete_time`),
FULLTEXT KEY `post` (`post`)
) ENGINE=MyISAM AUTO_INCREMENT=359789 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

daveo1

Script from the converter

SELECT
   p.pid AS id_msg, p.topic_id AS id_topic, p.post_date AS poster_time,
   CASE WHEN p.author_name = '' THEN 0 ELSE p.author_id END AS id_member, SUBSTRING(t.title, 1, 255) AS subject,
   SUBSTRING(p.author_name, 1, 255) AS poster_name,
   SUBSTRING(p.ip_address, 1, 255) AS poster_ip, p.use_emo AS smileys_enabled,
   IFNULL(p.edit_time, 0) AS modified_time, SUBSTRING(p.edit_name, 1, 255) AS modified_name,
   t.forum_id AS id_board, REPLACE(p.post, '<br />', '') AS body,
   SUBSTRING(IFNULL(m.email, '[email protected]'), 1, 255) AS poster_email, 'xx' AS icon
FROM {$from_prefix}posts AS p
   LEFT JOIN {$from_prefix}topics AS t ON (t.tid = p.topic_id)
   LEFT JOIN {$from_prefix}members AS m ON (m.member_id = p.author_id)
WHERE t.forum_id IS NOT NULL;
---*

vbgamer45

Quote from: daveo1 on October 20, 2020, 02:38:15 PM
That fixed it! I got another >:(

Here is the error...
Converting posts (this may take some time)... Unsuccessful!
This query:

    SELECT
    t.topic_id AS id_topic, t.topic_is_sticky AS is_sticky, t.forum_id AS id_board,
    t.user_id AS id_member_started, t.topic_last_poster_id AS id_member_updated,
    IFNULL(pl.pid,0) AS id_poll, t.topic_replies AS num_replies, t.topic_views AS num_views,
    MIN(p.pid) AS id_first_msg, MAX(p.pid) AS id_last_msg,
    t.opic_is_closed AS locked, approved
    FROM (`doording_ipbforumold`.ipbtopics AS t, `doording_ipbforumold`.ipbposts AS p)
    LEFT JOIN `doording_ipbforumold`.ipbpolls AS pl ON (pl.tid = t.topic_id)
    WHERE p.topic_id = t.topic_id
    GROUP BY t.topic_id
    HAVING id_first_msg != 0
    AND id_last_msg != 0
    TRUNCATE `doording_smf`.qza_messages;

Caused the error:

    1064

Here is the table in the db

CREATE TABLE `ipbposts` (
`pid` int(10) NOT NULL AUTO_INCREMENT,
`append_edit` tinyint(1) DEFAULT '0',
`edit_time` int(10) DEFAULT NULL,
`author_id` mediumint(8) NOT NULL DEFAULT '0',
`author_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`use_sig` tinyint(1) NOT NULL DEFAULT '0',
`use_emo` tinyint(1) NOT NULL DEFAULT '0',
`ip_address` varchar(46) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_date` int(10) DEFAULT NULL,
`post` mediumtext COLLATE utf8mb4_unicode_ci,
`queued` tinyint(1) NOT NULL DEFAULT '0',
`topic_id` int(10) NOT NULL DEFAULT '0',
`new_topic` tinyint(1) DEFAULT '0',
`edit_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`post_key` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
`post_htmlstate` smallint(1) NOT NULL DEFAULT '0',
`post_edit_reason` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_bwoptions` int(10) unsigned NOT NULL DEFAULT '0',
`pdelete_time` int(11) NOT NULL DEFAULT '0',
`post_field_int` int(10) NOT NULL DEFAULT '0',
`post_field_t1` text COLLATE utf8mb4_unicode_ci,
`post_field_t2` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`pid`),
KEY `topic_id` (`topic_id`,`queued`,`pid`,`post_date`),
KEY `author_id` (`author_id`,`post_date`,`queued`),
KEY `post_date` (`post_date`),
KEY `ip_address` (`ip_address`),
KEY `post_key` (`post_key`),
KEY `queued` (`queued`,`pdelete_time`),
FULLTEXT KEY `post` (`post`)
) ENGINE=MyISAM AUTO_INCREMENT=359789 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Minor change for first one

    SELECT
    t.topic_id AS id_topic, t.topic_is_sticky  AS is_sticky, t.forum_id AS id_board,
    t.user_id AS id_member_started, t.topic_last_poster_id AS id_member_updated,
    IFNULL(pl.pid,0) AS id_poll, t.topic_replies AS num_replies, t.topic_views AS num_views,
    MIN(p.pid) AS id_first_msg, MAX(p.pid) AS id_last_msg,
    t.topic_is_closed AS locked, approved
    FROM (`doording_ipbforumold`.ipbtopics AS t, `doording_ipbforumold`.ipbposts AS p)
    LEFT JOIN `doording_ipbforumold`.ipbpolls AS pl ON (pl.tid = t.topic_id)
    WHERE p.topic_id = t.topic_id
    GROUP BY t.topic_id
    HAVING id_first_msg != 0
    AND id_last_msg != 0
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

daveo1

Thank you! I'll be messing with it again tomorrow afternoon. Beer time right now, I cant mess with this and drink! Lol...I hope I can get it, and not waste a bunch of your time! ;D

Thank you again!

daveo1

Got a little further! ;D

Converting...
Converting members...
Successful.
Converting categories... Successful.
Converting boards... Successful.
Converting topics...Error in convert script - line 154!
Error in convert script - line 157!
Successful.
Converting posts (this may take some time)... Unsuccessful!
This query:

    SELECT
    t.topic_id AS id_topic, t.topic_is_sticky AS is_sticky, t.forum_id AS id_board,
    t.user_id AS id_member_started, t.topic_last_poster_id AS id_member_updated,
    IFNULL(pl.pid,0) AS id_poll, t.topic_replies AS num_replies, t.topic_views AS num_views,
    MIN(p.pid) AS id_first_msg, MAX(p.pid) AS id_last_msg,
    t.topic_is_closed AS locked, approved
    FROM (`doording_ipbforumold`.ipbtopics AS t, `doording_ipbforumold`.ipbposts AS p)
    LEFT JOIN `doording_ipbforumold`.ipbpolls AS pl ON (pl.tid = t.topic_id)
    WHERE p.topic_id = t.topic_id
    GROUP BY t.topic_id
    HAVING id_first_msg != 0
    AND id_last_msg != 0
    TRUNCATE `doording_smf`.qza_messages;

Caused the error:

    1064

vbgamer45

There should be a ; after
AND id_last_msg != 0

Since those are two different queries
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

daveo1

Quote from: vbgamer45 on October 21, 2020, 11:45:29 AM
There should be a ; after
AND id_last_msg != 0

Since those are two different queries

I think I tried that, but I'm going to do it again to rule it out.

daveo1

I did and this is the error now....

Converting...
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.user_id AS id_member_started, t.topic_last_poster_id AS id_member_updated,
    IFNULL(pl.pid,0) AS id_poll, t.topic_replies AS num_replies, t.topic_views AS num_views,
    MIN(p.pid) AS id_first_msg, MAX(p.pid) AS id_last_msg,
    t.topic_is_closed AS locked, approved
    FROM (`doording_ipbforumold`.ipbtopics AS t, `doording_ipbforumold`.ipbposts AS p)
    LEFT JOIN `doording_ipbforumold`.ipbpolls AS pl ON (pl.tid = t.topic_id)
    WHERE p.topic_id = t.topic_id
    GROUP BY t.topic_id
    HAVING id_first_msg != 0
    AND id_last_msg != 0
    LIMIT 0, 500;

Caused the error:

    1146


vbgamer45

1146 is table does not exist. Try running the query manually and see what the full error message is
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

daveo1

It doesnt exist, like I said in the begging, some genius that originally converted the forum from Fusion to IPB, somehow used the fbb_topics table and made it work with IPB. No idea why, I'm sure there was a good reason or maybe not  >:(

Advertisement: