vBulletin 3.5 converter errors

[B3aker]

Hi. I'm working on converting a vBulletin 3.5.4 forum to SMF 2.0. I did run into a couple errors during the conversion process but the past topics here have helped me fix the situation. However, I came across this error to converting the posts and I didn't see anything that might help. Any help is appreciated 

Converting polls...Wrong value type sent to the database. Integer expected. (id_member)

[kat]

In the converter script, is there a block of code, like this? (Sorry, I'm not terribly familiar with the converters and you don't say which converter you're using)

Code Select Expand

$week = date("W",$now);
$year = date("Y",$now);

If there is, try changing it to this:

Code Select Expand

$week = (int) date("W",$now);
$year = (int) date("Y",$now);

[B3aker]

Hi. Sorry. Forgot to mention I'm using the vbulletin 3.5 to smf 2.0.5 converter. I searched in the included scripts and didn't see that line of code anywhere.

[kat]

I got the converter and the only bit of code, doing polls, is this:

Code Select Expand

if (!isset($indexes['poll']))
$smcFunc['db_add_index']($to_prefix . 'topics', array(
'type' => 'UNIQUE',
'name' => 'poll',
'columns' => array('id_poll', 'id_topic')),
array('no_prefix' => true));

The only part that seems similar, to me, is the $smcFunc thing. As you can tell, I'm WAY in over my head, here. But, I'm wondering making:

Code Select Expand

$smcFunc['db_add_index']($to_prefix . 'topics', array(


into:

Code Select Expand

$smcFunc (int) ['db_add_index']($to_prefix . 'topics', array(

would sort that?

(I really don't have a clue, though)

[B3aker]

Got this when I typed in mydomain.com/forum/convert.php

Parse error: syntax error, unexpected T_INT_CAST in /home/content/79/11133879/html/forum/convert.php on line 2014

[kat]

That kinda proves my point, then.

I'll flag this up and see if anyone else can help-out.

[B3aker]

I appreciate you giving it a shot. Thank you 

[emanuele]

I'd try with:

Code (find) Select Expand

t.postuserid AS id_member
FROM {$from_prefix}poll AS p


Code (replace with) Select Expand

IFNULL(t.postuserid, 0) AS id_member
FROM {$from_prefix}poll AS p


[kat]

OO! I was almost kinda on the right track, there!

Ta, Manny!

[B3aker]

Went a little farther this time. This is what it says now.

Converting posts (this may take some time)...
Successful.
Converting polls...Wrong value type sent to the database. Integer expected. (expire_time)

[emanuele]

mmm...this is odd on multiple levels...

expire_time is the result of an arithmetic operation:

Code Select Expand

IF(p.timeout = 0, 0, p.dateline + p.timeout * 86400) AS expire_time
how it could result in something different than an integer is not clear to me.

As a workaround you may set all the polls as without expiration or all expired.
For the first:

Code Select Expand

0 AS expire_time
for the latter:

Code Select Expand

UNIX_TIMESTAMP() AS expire_time

[B3aker]

mmm...this is odd on multiple levels...

Been my thoughts exactly since I took over this message board, lol. I changed it to the first one and I'm running the converter now. I'll let you know how it plays out

[B3aker]

mmm...this is odd on multiple levels...

expire_time is the result of an arithmetic operation:

Code Select Expand

IF(p.timeout = 0, 0, p.dateline + p.timeout * 86400) AS expire_time
how it could result in something different than an integer is not clear to me.

As a workaround you may set all the polls as without expiration or all expired.
For the first:

Code Select Expand

0 AS expire_time
for the latter:

Code Select Expand

UNIX_TIMESTAMP() AS expire_time

I tried the first code. It didn't work. Got this

Converting posts (this may take some time)...
Successful.
Converting polls... Unsuccessful!
This query:

    SELECT
    p.pollid AS id_poll, SUBSTRING(p.question, 1, 255) AS question,
    IF(p.active = 0, 1, 0) AS voting_locked, p.multiple AS max_votes,
    SUBSTRING(IFNULL(t.postusername, 'Guest'), 1, 255) AS poster_name,
    IF(p.timeout = 0, 0, p.dateline + p.timeout * 86400) 0 AS expire_time,
    IFNULL(t.postuserid, 0) AS id_member
    FROM `PSX4CentralSMF`.poll AS p
    LEFT JOIN `PSX4CentralSMF`.thread AS t ON (t.pollid = p.pollid)
    LIMIT 0, 500;

Caused the error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 AS expire_time,
    IFNULL(t.postuserid, 0) AS id_member
    FROM `PSX4CentralSMF`.po' at line 5

I typed this in as the code

Code Select Expand

IF(p.timeout = 0, 0, p.dateline + p.timeout * 86400) 0 AS expire_time,

Was that right? If that was, I'm confused on where to put the full code with your second method (not very good at PHP). Does it go like this?

Code Select Expand

UNIX_TIMESTAMP() AS expire_time

[emanuele]

You left there the old piece of the code.

Code Select Expand

IF(p.timeout = 0, 0, p.dateline + p.timeout * 86400) 0 AS expire_time,
should be only:

Code Select Expand

0 AS expire_time,

[B3aker]

You left there the old piece of the code.

Code Select Expand

IF(p.timeout = 0, 0, p.dateline + p.timeout * 86400) 0 AS expire_time,
should be only:

Code Select Expand

0 AS expire_time,

Haha. Ok. I'll fix that and run it again and let you know how it works. I'm new to this PHP stuff. I know HTML much better. Since I took over this message board, PHP and SQL coding is all new to me.

[B3aker]

Sweet. I got past the polls. Now I got a new error.

Converting personal messages (step 1)...
Successful.
Converting personal messages (step 2)...Error in convert script - line 222!
Error in convert script - line 225!
Successful.
Converting topic notifications... Unsuccessful!
This query:

    SELECT
    pm.pmid AS id_pm, pm.touserid AS id_member, pm.readtime != 0 AS is_read,
    '-1' AS labels
    FROM `PSX4CentralSMF`.pm AS pm
    TRUNCATE `PSX4CentralSMF`.smf_log_notify;

Caused the error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRUNCATE `PSX4CentralSMF`.smf_log_notify' at line 5

[emanuele]

This is really a but in the converter, in the .sql file, change:

Code Select Expand

FROM {$from_prefix}pm AS pm
to:

Code Select Expand

FROM {$from_prefix}pm AS pm;

[B3aker]

Went a bit further but got this error

Converting posts (this may take some time)...
Successful.
Converting polls... Successful.
Converting poll options... Successful.
Converting poll votes... Successful.
Converting personal messages (step 1)... Successful.
Converting personal messages (step 2)... Unsuccessful!
This query:

    SELECT
    pm.pmid AS id_pm, pm.touserid AS id_member, pm.readtime != 0 AS is_read,
    '-1' AS labels
    FROM `PSX4CentralSMF`.pm AS pm
    LIMIT 0, 500;

Caused the error:

    Unknown column 'pm.touserid' in 'field list'

[emanuele]

...are you sure you are using vB 3.5? Asking because it seems strange there are so many (unnoticed before) errors.

TBH I have no idea how to fix that one without looking *at least* at the table schema.

[B3aker]

...are you sure you are using vB 3.5? Asking because it seems strange there are so many (unnoticed before) errors.

TBH I have no idea how to fix that one without looking *at least* at the table schema.

Yeah. Its the vB3.5 converter. Not surprised there's a lot of errors to be honest. When I took over the website, it took 2 months to just get the vBulletin to work on my server. It also wasn't really maintained within the last 7 years.

What do you need to see the table schema? A screenshot of my tables in phpmyadmin? Let me know what you need

[emanuele]

From phpmyadmin, export ONLY the structure of the tables. Be careful, only the structure, not the data!
You can select what you want to export from phpmyadmin by clicking "custom" in the export screen.
If you are unsure send it to me by PM.

[emanuele]

Wait scratch that.

Here there is the solution (I hope):
http://www.simplemachines.org/community/index.php?topic=216076.msg2506124;topicseen#msg2506124

[B3aker]

Got a different error this time.

Converting poll votes...
Successful.
Converting personal messages (step 1)... Successful.
Converting personal messages (step 2)... Unsuccessful!
This query:

    WHERE pm.folderid != '-1'
    LIMIT 0, 500;

Caused the error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE pm.folderid != '-1'
    LIMIT 0, 500' at line 1

[B3aker]

Tried to run the converter again. Still same issue. emanuelle, want to see the structure of my tables? Let me know and I'll PM it to you. Thanks!

[emanuele]

It seems you removed part of the query or added some semi colon somewhere in the query.

Sorry for the late answer, I was not frequently on-line these days...

[B3aker]

It seems you removed part of the query or added some semi colon somewhere in the query.

Sorry for the late answer, I was not frequently on-line these days...

Wait. I'm confused. I'm not a programmer. Dunno where I could have messed up (the code is all greek to me though I am slowly learning ). This is the code from the SQL file for Step 2.

/******************************************************************************/
--- Converting personal messages (step 2)...
/******************************************************************************/

TRUNCATE {$to_prefix}pm_recipients;

---* {$to_prefix}pm_recipients
SELECT
   pm.pmid AS id_pm, pm.userid AS id_member,
   '-1' AS labels
FROM {$from_prefix}pm AS pm;
WHERE pm.folderid != '-1';
---*

[emanuele]

Yep, remove the semicolon at the end of this line:

Code Select Expand

FROM {$from_prefix}pm AS pm;

The final code should look like:

Code Select Expand

/******************************************************************************/
--- Converting personal messages (step 2)...
/******************************************************************************/

TRUNCATE {$to_prefix}pm_recipients;

---* {$to_prefix}pm_recipients
SELECT
   pm.pmid AS id_pm, pm.userid AS id_member,
   '-1' AS labels
FROM {$from_prefix}pm AS pm
WHERE pm.folderid != '-1';
---*

[B3aker]

Aha. Success....sorta. Got past the personal messages. Now have this error

Converting personal messages (step 1)...
Successful.
Converting personal messages (step 2)... Successful.
Converting topic notifications... Successful.
Converting board notifications... Successful.
Converting smileys...
Parse error: syntax error, unexpected T_STRING, expecting ')' in /home/content/79/11133879/html/forum/convert.php(1091) : eval()'d code on line 35

[emanuele]

OMFG this converter is quite broken... >_<

Code Select Expand

$rows[] = array($code, $name . '.gif, $name, $count);

should be:

Code Select Expand

$rows[] = array($code, $name . '.gif', $name, $count);


Can't see any other syntax error, let's hope everything else is fine.
When we finish I'll collect all the changes and at least post the file somewhere.

[B3aker]

This one is weird. What does this mean? Never had this before.

Converting topics...
Incorrect key file for table '/tmp/mysqltmp/#sql_1339_0.MYI'; try to repair it

[emanuele]

This one is a mysql error, and it seems you have some problem on one of the tables.

Converting topics, so it means one of those involved in the conversion of...topics obviously.
Now, I have no idea if it is vb side or smf side, so you should check all of them and try a repair.

On vb side it may be one of:
* thread
* post
* user
on smf side is topics (I omitted all the prefixes).

To repair you can use phpmyadmin on the page where all the tables are listed, you can selec some (or all, usually is faster ) and from the dropdown box at the bottom select "repair".

[B3aker]

Went past topics now getting the same error on posts

Converting posts (this may take some time)...
Incorrect key file for table '/tmp/mysqltmp/#sql_1339_0.MYI'; try to repair it

[emanuele]

Are you working on a "live" database (i.e. the vb one is the one your forum is actively using)?
If so it may be some problem with the multiple accesses to tables and maybe a spike of traffic.
In any case you should work on a "backup" for testing and do the conversion of the live site only when you want to move and putting the site in some kind of maintenance mode (dunno the vb equivalent) in order to avoid changes in the db while converting (that would cause inconsistency of the data).

[B3aker]

Are you working on a "live" database (i.e. the vb one is the one your forum is actively using)?
If so it may be some problem with the multiple accesses to tables and maybe a spike of traffic.
In any case you should work on a "backup" for testing and do the conversion of the live site only when you want to move and putting the site in some kind of maintenance mode (dunno the vb equivalent) in order to avoid changes in the db while converting (that would cause inconsistency of the data).

I'm using a completely different database that is not live. What I did is copy the vbulletin folder (was mydomain.com/forums) and renamed the duplicate folder forums2 and have been using that for the conversion process. I then used an older SQL (the one the original owner of the site gave me) file and imported it in a completely different database. I then installed the SMF forum to a new name (named it forum). The vBulletin forum that is currently live has been corrupted (database and tables are a mess) since I assumed ownership so I decided to start from scratch for the conversion on a new database that is not active with the original database. The plan is to then redirect to the new SMF board. We'll lose a month or two worth of posts but the site doesn't get that much traffic so it really isn't a big deal. If any of this makes sense 

[emanuele]

Then it seems that the copy is being corrupted as well. :-S
And that is not very good *at all*...

At that point, I'd be a bit afraid of using the live database for further tests (and to be completely honest I'd have some doubts on using the host for my site as well). What I would do is use a "local" (i.e. on your computer) server to continue the test. You can install xampp or alternatives (see "localhost webservers" in the first message of this topic) and have everything ready with just a couple of clicks.
Then, when you are sure the converter works, you can do the conversion "live", so data won't be broken.

[B3aker]

I downloaded xampp. While I was waiting for it to download and install, for the heck of it, I ran the convert.php again just to see if the error popped up again with mysql. Went right through without a problem (perhaps it could have been a hiccup with GoDaddy?). I hit this error. What are your thoughts? Should I just go ahead with xampp?

Converting topic notifications...
Successful.
Converting board notifications... Successful.
Converting smileys...
Warning: array_combine() [function.array-combine]: Both parameters should have an equal number of elements in /home/content/79/11133879/html/forum/Sources/Subs-Db-mysql.php on line 627
The database value you're trying to insert does not exist: variable

[emanuele]

sigh...

Find this line:

Code Select Expand

convert_insert('smileys', array('variable', 'value'), $rows, 'replace');


and change it to:

Code Select Expand

convert_insert('smileys', array('code', 'filename', 'description', 'smiley_order'), $rows, 'replace');


[B3aker]

sigh...

Look on the bright side. I've had every error known to man for vBulletin. Should be able to help out anyone who has any type of problem

Anyway, got a lot further this time. Looks like I'm toward the end. Got this error when converting avatars.

Converting attachments...
Successful.
Converting avatars...The database value you're trying to insert does not exist: filename

[emanuele]

Good news is the last step. YAY!

Not so good news, I'm not sure why it returns this error...

Can you check the vb database, table customavatar and verify if a column "filename" exists?

[B3aker]

Good news is the last step. YAY!

Not so good news, I'm not sure why it returns this error...

Can you check the vb database, table customavatar and verify if a column "filename" exists?

I see a column that is named filename. It then says in order from right to left, type "varchar(100)", collation "latin1_swedish_ci", attributes nothing listed, Null "Yes", Default "NULL". Hope that helps

[emanuele]

Well, well...at the moment I don't really have any idea of what is broken...

I'll try again tomorrow morning...

[B3aker]

Hey. Was just curious if you thought of a solution. Thanks!

[B3aker]

I was looking at the code a bit. I wonder if it's an error in the code perhaps? Maybe not matching up to my vb database tables?

Code Select Expand

******************************************************************************/
--- Converting avatars...
/******************************************************************************/

---* {$to_prefix}attachments
---{
$no_add = true;

if (!isset($vb_settings))
{
$result = convert_query("
SELECT varname, value
FROM {$from_prefix}setting
WHERE varname IN ('attachfile', 'attachpath', 'usefileavatar', 'avatarpath')
LIMIT 4");
$vb_settings = array();
while ($row2 = convert_fetch_assoc($result))
{
if (substr($row2['value'], 0, 2) == './')
$row2['value'] = $_POST['path_from'] . substr($row2['value'], 1);
$vb_settings[$row2['varname']] = $row2['value'];
}
convert_free_result($result);
}

$file_hash = getAttachmentFilename($row['filename'], $id_attach, null, true);
$physical_filename = $id_attach . '_' . $file_hash;

if (strlen($physical_filename) > 255)
return;
if (empty($vb_settings['usefileavatar']))
{
$fp = @fopen($attachmentUploadDir . '/' . $physical_filename, 'wb');
if (!$fp)
return;

fwrite($fp, $row['filedata']);
fclose($fp);
}
elseif (!copy($vb_settings['avatarpath'] . '/avatar' . $row['id_member'] . '_' . $row['avatarrevision'] . '.gif', $attachmentUploadDir . '/' . $physical_filename))
return;

[emanuele]

The error comes from SMF's "database abstraction layer", it complains because it finds that a certain field should be inserted/read ('filename'), but the corresponding value is not present in the arguments of the query... meh, that explanation doesn't sound right, let's do an example:

Code Select Expand

$smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}attachments
WHERE filename LIKE {string:filename}',
array(
'not_filename' => 'a string',
)
);
in that case, in the array there is no index "filename" at it would raise that error.
Correct version would be:

Code Select Expand

$smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}attachments
WHERE filename LIKE {string:filename}',
array(
'filename' => 'a string',
)
);

Something similar may happen with db_insert.
Though I can't see where and how that could fail...

The fallback is as before remove that piece of code, it is not nice for your users though...

ETA: another thing you may try, is to add "debug=1" to the url when you start the conversion, this should give you more info on the actual error (hopefully...), so for example:

Code Select Expand

http://localhost/forum/index.php?debug=1

[B3aker]

I'll give the debug method a try and let you know. If I remove that bit of code, what would happen to the users? They would lose their avatars?

[B3aker]

This is the first message that pops up when I did the debug.

Notice: Undefined index: convert_script in /home/content/79/11133879/html/forum/convert.php on line 66Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /home/content/79/11133879/html/forum/convert.php:3167) in /home/content/79/11133879/html/forum/convert.php on line 79Notice: Undefined index: step in /home/content/79/11133879/html/forum/convert.php on line 104Notice: Undefined index: start in /home/content/79/11133879/html/forum/convert.php on line 105

and then this

Notice: Undefined index: convert_script in /home/content/79/11133879/html/forum/convert.php on line 66Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /home/content/79/11133879/html/forum/convert.php:3167) in /home/content/79/11133879/html/forum/convert.php on line 79Notice: Undefined index: start in /home/content/79/11133879/html/forum/convert.php on line 105

[emanuele]

As long as the converter continues his work these should be irrelevant...

[B3aker]

The error comes from SMF's "database abstraction layer", it complains because it finds that a certain field should be inserted/read ('filename'), but the corresponding value is not present in the arguments of the query... meh, that explanation doesn't sound right, let's do an example:

Code Select Expand

$smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}attachments
WHERE filename LIKE {string:filename}',
array(
'not_filename' => 'a string',
)
);
in that case, in the array there is no index "filename" at it would raise that error.
Correct version would be:

Code Select Expand

$smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}attachments
WHERE filename LIKE {string:filename}',
array(
'filename' => 'a string',
)
);

I just looked for this line of code in the convert.php and the vbulletin35_to_smf.sql file and I can't find it anywhere to modify or remove it. Maybe that could be the problem?

[emanuele]

Sorry for being slow in this period and sorry for the misunderstanding: that was just an example of code, not actual code you would find in the converter.
What you can remove is any thing after this:

Code Select Expand

******************************************************************************/
--- Converting avatars...
/******************************************************************************/
including that three lines.

[B3aker]

Sorry for being slow in this period and sorry for the misunderstanding: that was just an example of code, not actual code you would find in the converter.
What you can remove is any thing after this:

Code Select Expand

******************************************************************************/
--- Converting avatars...
/******************************************************************************/
including that three lines.

Ah. Gotcha. I'll remove that today and give it a run. If the members lose their avatars, so be it. They can reupload them. I posted a topic a couple days ago telling them to save them if they really care about them. Just hope they read it and followed my advice.

[B3aker]

Ugh. I took out the converting avatars section from the converter and ran it again. I keep getting this error in the beginning. I repaired and optimized all my tables as I did the last time I had this error come up and I still keep getting it

Converting members...
Incorrect key file for table '/tmp/mysqltmp/#sql_7ea0_0.MYI'; try to repair it

[emanuele]

That again is a mysql error and an issue with a corrupt table, nothing that the converter can cause or solve...

What I would try if you continue getting them is reduce the number of members processed at a time changing:

Code Select Expand

---* {$to_prefix}members
to for example:

Code Select Expand

---* {$to_prefix}members 50
or something smaller than 50, this should reduce the load, not sure if it helps in any way...

[B3aker]

*sigh* New error I've never seen before. I wonder if all the tries of the conversion somehow corrupted my database. Would it make sense to do a fresh install on a new database?

Converting topics...
Unsuccessful!
This query:

    SELECT
    t.threadid AS id_topic, t.forumid AS id_board, t.sticky AS is_sticky,
    t.pollid AS id_poll, t.views AS num_views, t.postuserid AS id_member_started,
    ul.userid AS id_member_updated, t.replycount AS num_replies,
    IF(t.open, 0, 1) AS locked, MIN(p.postid) AS id_first_msg,
    MAX(p.postid) AS id_last_msg
    FROM `PSX4CentralSMF`.thread AS t
    INNER JOIN `PSX4CentralSMF`.post AS p ON (p.threadid = t.threadid)
    LEFT JOIN `PSX4CentralSMF`.user AS ul ON (ul.username = t.lastposter)
    GROUP BY t.threadid
    HAVING id_first_msg != 0
    AND id_last_msg != 0
    LIMIT 0, 500;

Caused the error:

    Table './PSX4CentralSMF/post' is marked as crashed and last (automatic?) repair failed

[mentalist]

*sigh* New error I've never seen before. I wonder if all the tries of the conversion somehow corrupted my database. Would it make sense to do a fresh install on a new database?

Don't believe so... It is probably a server's harddrive failure .. Contatct your host.

[emanuele]

You should really export the db and work on a local copy on your computer...
All these errors are caused by the server itself, the converter can't do anything, I'm sorry.

[B3aker]

Holy crap dude. Completely forgot to respond back yesterday. I let the converter run over night a couple nights ago and it worked on my working database. I then made a new updated database with the latest posts and everything, ran it again and it worked first time. I was so excited I dove right into fixing forum settings and getting things in order and learning how to use my admin control panel I completely forgot to respond (I am really enjoying SMF and looking forward to seeing what other cool addons and stuff this community has to offer).

It must have been mostly server errors. Thinking about it, Go Daddy did a complete overhaul on their site at the time I started to see these weird errors that had nothing to do with the converter. I was having issues logging in and even looking at my files so I'm sure they did some work to their infrastructure too and resulted in being a bit buggy for a few days (seems to be fine now).

Thank you so much for all your support you gave me here and your patience. If it wasn't for you, I don't know what I would have done. Thank you again