UBB.threads PHP Forum Software Community Forums UBB.threads 7 Importer Support Error importing Posts from 6.5.2

I get the following error while importing from 6.5.2 into a fresh install of 7. Looks like the import_map table includes a required not-null field called old_forum_id that isn't getting created in the insert statement in the importer:

Processing topics (1 - 20) of 78158 total topics.

Script:
Line#:
SQL Error:
SQL Error #: 0
Query: insert into ubbt_IMPORT_MAP (POST_ID,OLD_TOPIC_ID,OLD_POST_ID,SITE_ID) values ( 4 , 1 , 1 , 1 )

Running the same statement in Query Browser gives the expected error, "Field 'OLD_FORUM_ID' doesn't have a default value".

Thanks in advance for any help.

-D

You can run the following SQL command from something like phpmyadmin to fix this:

ALTER TABLE ubbt_IMPORT_MAP
change OLD_FORUM_ID OLD_FORUM_ID int(9) unsigned not null default '1'

Thanks, Rick. This appears to have let the importer continue. But won't this break any old links to showflat.php that include a forum ID?

I'm now encountering a different problem, though. I've got about 50,000 posts imported out of 78,000 and I'm getting an error while doing an insert into ubbt_Posts. Running the same query in QB says :

Data too long for column 'POST_DEFAULT_BODY' at row 1

This column's type is TEXT though, so I can't see why it would be giving that error. I'm using MySQL 5.0.18 on WinXP if it matters.

Also, the tables are about half MyISAM and half InnoDB. Is it safe (or even desirable) to switch them all to Inno to gain the transactional and row-locking support?

Thanks again,

-D

Forgot to mention - the size of the body being inserted is about 130k, and I have plenty of disk space. Table size is currently 0.9GB with 350MB of indexes (90MB if I remove the fulltext index on body).

The FORUM_ID in the mapping table is only used for UBB.classic. UBB.threads will always go solely off the post and ignore the forum.

Hmm, how big is the post that's being inserted when you get the "Data too long" error? It could be that it's exceeding the character limitation for a TEXT field?

As for changing all to InnoDB. All of the .threads tables, currently need to be MyISAM. There are some quirks with running under InnoDB.

Hi Rick,

Glad to hear the forum ID won't matter for my installation.

The post body that's being rejected is about 68kb, although the post in the old system was exactly 65k (which, I've just found, is the limit for TEXT - I'm more used to MSSQL and thought TEXT meant <=2GB). The reason for the overflow looks like it's because old <img src=""> tags are being converted to [img]url[/img] and the post is nothing but thousands of emoticons (bleh).

I'm OK with truncating this particular post, but there may be other long ones in the system that are legitimate. Do I have to truncate them as well, or can I change the default_body field type to mediumtext? If I have to truncate them I'll probably just change the importer to do it for me (unless you plan on doing that in an importer update any time soon).

Thanks for your help!

-D

Your best route is to probably change the fields to MEDIUMTEXT. You'd need to change the POST_BODY and the POST_DEFAULT_BODY field.

It sounds like you're fairly familiar with MySQL so you probably know how to convert those fields already, but if you need the SQL syntax, let me know.

Also, my test database defaults to InnoDB, and the following tables were created as such since the ENGINE= arguments were missing from the create table calls:

${PREFIX}cattemp
${PREFIX}filetemp
${PREFIX}forumtemp
${PREFIX}grouptemp
${PREFIX}importconfig
${PREFIX}memtemp
${PREFIX}polltemp
${PREFIX}reserved_names
${PREFIX}topic_views

I assume the *temp ones will be dropped after import, and I'll change the others to MyISAM.

Yes, the temp ones will all be dropped. I just noticed the others weren't set to MyISAM in the createtable script, just changed that.