vBulletin import issues

alejandrocobo commented 8 years ago

Hi, I have tried to import a vB 3.8 database to nodebb but I get tons of errors. The first one is with private messages. The final state is this: State: idle, by event: | Phase: messagesImportStart Progress: 251/6795, 3.6938926%

Nodebb log said this: SELECT pm.pmid as _mid, pmtext.fromuserid as _fromuid, pm.userid as _touid, pmtext.message as _content, pmtext.dateline as _timestamp FROM pm JOIN pmtext ON pmtext.pmtextid=pm.pmtextid AND pmtext.fromuserid != pm.userid LIMIT 0,600000

returned: 6795 results 1/9 22:04 [1476] - error: TypeError: Cannot read property 'uid' of undefined

So I have truncated that table from mysql database and it has "solved" this problem. The next problem comes from attachments with this status: State: idle, by event: | Phase: topicsImportStart Progress: 0/1, 0.0000000%

Nodebb log said this: nodebb-plugin-import-vbulletin] { [Error: ER_NO_SUCH_TABLE: Table 'forum.filedata' doesn't exist] code: 'ER_NO_SUCH_TABLE', errno: 1146, sqlState: '42S02', index: 0 } exporter.error [2016-09-01T20:21:54.536Z] Error: ER_NO_SUCH_TABLE: Table 'forum.filedata' doesn't exist 1/9 22:21 [2637] - error: TypeError: Cannot read property 'forEach' of undefined

By deleting those table appears another error.

I have upgraded the database to vBulletin 4.2.2 but I get the same errors.

What can I do?

Thanks.

akhoury commented 8 years ago

I've tested 4.2.0 not too long ago, and it was fine, I double the simple 4.2.2 patch have change the db structure

Can you show me your table structure? use db_name; show tables;
Could you possibly have a table prefix that you're not setting? i.e. forum.xyz_filedata then you need to set xyz_ in the importer config

alejandrocobo commented 8 years ago

Thank you.

alejandrocobo commented 8 years ago

BTW, I got the same errors when I use the original vBulletin 3.8 database. The list of tables shown above are from the vB 3.8 version.

akhoury commented 8 years ago

it doesn't support VB3 only VB4 and VB5 (coming soon), so can you show me the list from VB4?

alejandrocobo commented 8 years ago

Thanks for the support. Before upgrading to vB 4.2.2, I have uninstalled all products and plugins. Furthermore I have stablished English as default forum language and default theme deleting extra languages and themes. Then, I have optimised and repaired the tables. The show table gives me now this output:

Thank you.

akhoury commented 8 years ago

ok good, so now what is the error that you get if you run using VB4, since I can see the filedata table in there.

>| faq |
> | filedata |  <--------
>| forum |
> | forumpermission |
> | forumprefixset |
> | forumread |

so you shoudnlt get that error you shared in the original post

[Error: ER_NO_SUCH_TABLE: Table 'forum.filedata' doesn't exist]

alejandrocobo commented 8 years ago

Right. In my previous importing from vB4.4..2, I got an error related with table attachment but I cannot remember it exactly. Since then, I have transfered attached files from database to filesystem so I don't know if my current good results are because of those changes o because you have improve the code (now there is more log information during the importing so probably you have changed something).

Now I get the same pm tables error. I have deleted this part of the vbulletin importer code:

Exporter.countMessages = function(callback) {
        var custom = Exporter.config('custom') || {};
        custom.messagesPlugin = (custom.messagesPlugin || '').toLowerCase();

        if (supportedPlugins[custom.messagesPlugin]) {
            return supportedPlugins[custom.messagesPlugin].count(callback);
        }

        callback = !_.isFunction(callback) ? noop : callback;
        var prefix = Exporter.config('prefix');
        var timemachine = Exporter.config('custom').timemachine;

        var query = 'SELECT count(*) '
            + 'FROM ' + prefix + 'pm '
            + 'JOIN ' + prefix + 'pmtext ON ' + prefix + 'pmtext.pmtextid=' + prefix + 'pm.pmtextid '
            + 'AND ' + prefix + 'pmtext.fromuserid != ' + prefix + 'pm.userid '
            + (timemachine.messages.from ? ' AND ' + prefix + 'pmtext.dateline >= ' + timemachine.messages.from : ' ')
            + (timemachine.messages.to ? ' AND  ' + prefix + 'pmtext.dateline <= ' + timemachine.messages.to : ' ')
            + '';

        Exporter.query(query,
            function(err, rows) {
                if (err) {
                    Exporter.error(err);
                    return callback(err);
                }
                callback(null, rows[0]['count(*)']);
            });
    };

    Exporter.getMessages = function(callback) {
        return Exporter.getPaginatedMessages(0, -1, callback);
    };

    Exporter.getPaginatedMessages = function(start, limit, callback) {
        var custom = Exporter.config('custom') || {};
        custom.messagesPlugin = (custom.messagesPlugin || '').toLowerCase();

        if (supportedPlugins[custom.messagesPlugin]) {
            return supportedPlugins[custom.messagesPlugin].get(start, limit, callback);
        }

        callback = !_.isFunction(callback) ? noop : callback;

        var startms = +new Date();
        var prefix = Exporter.config('prefix') || '';
        var timemachine = Exporter.config('custom').timemachine;

        var query = 'SELECT '
            + prefix + 'pm.pmid as _mid, '
            + prefix + 'pmtext.fromuserid as _fromuid, '
            + prefix + 'pm.userid as _touid, '
            + prefix + 'pmtext.message as _content, '
            + prefix + 'pmtext.dateline as _timestamp '
            + 'FROM ' + prefix + 'pm '
            + 'JOIN ' + prefix + 'pmtext ON ' + prefix + 'pmtext.pmtextid=' + prefix + 'pm.pmtextid '
            + 'AND ' + prefix + 'pmtext.fromuserid != ' + prefix + 'pm.userid '
            + (timemachine.messages.from ? ' AND ' + prefix + 'pmtext.dateline >= ' + timemachine.messages.from : ' ')
            + (timemachine.messages.to ? ' AND  ' + prefix + 'pmtext.dateline <= ' + timemachine.messages.to : ' ')
            + (start >= 0 && limit >= 0 ? ' LIMIT ' + start + ',' + limit : '');

        Exporter.query(query,
            function(err, rows) {
                if (err) {
                    Exporter.error(err);
                    return callback(err);
                }
                //normalize here
                var map = {};
                rows.forEach(function(row) {
                    row._timestamp = ((row._timestamp || 0) * 1000) || startms;
                    map[row._mid] = row;
                });

                callback(null, map);
            });
    };

And the "messages" error has disappeared. Although this is a "solution", I would like to preserve private messages. Is there any way to know what part of the table is the conflicting one by knowing in what messages the importer crashes? I know it crashes in message no 251, but what does mean?

Right now my testing server continues the import proccess (almost 500000 posts) but it runs out of memory and it is going very slow. When it finish, I will reply the results.

Thank you very much.

akhoury commented 8 years ago

i dont think I can debug it that way, do you think you can send me a copy of your data?

alejandrocobo commented 8 years ago

I am sorry, I cannot, it has private data from third party users I shouldn't share.

Is there a way to skip the importation of a particular item? I mean, to skip message number 252 which probably is the only one item problematic instead deleting all private messages.

Thank you.

akhoury commented 8 years ago

Is there any way to know what part of the table is the conflicting one by knowing in what messages the importer crashes? I know it crashes in message no 251, but what does mean?

you need to reproduce the error then and show me the error.

I cannot, it has private data from third party users I shouldn't share. can you obscure the data somehow? for example, replace all emails by: email-1@example.com,email-2@example.com ... etc...

alejandrocobo commented 8 years ago

Well, right now I am preforming some tests. When I know what the problem is, I will post here my results.

alejandrocobo commented 8 years ago

I found the issue. The problem is there are private message from and/or to deleted users. If I create new users and change their uid to deleted users which have pm, it works fine.

akhoury commented 8 years ago

ok I think we can avoid that in the importer core

akhoury / nodebb-plugin-import

vBulletin import issues #207