Simple Machines Community Forum

SMF Support => Converting to SMF => IPB => Topic started by: LeYoyo on June 05, 2014, 08:43:33 AM

Title: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 05, 2014, 08:43:33 AM
Hi all,

I'm trying to convert from IPB 2.0.0 trial to the latest SMF.

I have downloaded and installed the latest conversion tool.

By running it, I had first the "birthday" error. Fixed by adding a solution found here

                     if(!preg_match('/\d{4}-\d{2}-\d{2}/', $row['birthdate']))
                        $row['birthdate'] = '0001-01-01';


Now I have another issue: Converting members...Wrong value type sent to the database. Integer expected. (instant_messages)

Any advice about this one ?

Thanks in advance
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 05, 2014, 09:09:45 AM
Which converter are you using?
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 05, 2014, 09:55:35 AM
Invision 2 to SMF 2-0
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 05, 2014, 09:57:20 AM
Of course :P Did you get it from the downloads page or one of the sticky topics in this board?
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 05, 2014, 11:21:28 AM
From the download page : http://download.simplemachines.org/?converters;software=invision
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 05, 2014, 11:44:02 AM
Thanks ;) I needed to know which file to pick.

That issue is caused by your original database (IPB) has something other than in integer in that column (most likely a NULL). So we have to tell the converter to make all those rows in integers.
Open the .sql file and find:
Code: [Select]
$row['signature'] = substr(strtr(strtr($row['signature'], '<>', '[]'), array('[br /]' => '<br />')), 0, 65534);Add after:
Code: [Select]
$row['instant_messages'] = (int)$row['instant_messages'];
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 06, 2014, 05:41:38 AM
Added here:

Code: [Select]
), ltrim($row['signature'])));
$row['signature'] = substr(strtr(strtr($row['signature'], '<>', '[]'), array('[br /]' => '<br />')), 0, 65534);
$row['instant_messages'] = (int)$row['instant_messages'];


Same error :(  Converting members...Wrong value type sent to the database. Integer expected. (instant_messages)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 08, 2014, 11:17:03 AM
Just wondering about one point : do I use the right converter ?
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 08, 2014, 11:19:00 AM
It seems you are, if you downloaded the latest version from the downloads page ;)

I forgot this issue, sorry. But I can't really understand why you're still having that error :(
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 10, 2014, 04:45:54 AM
Is there any way to drop the IPB instant message values ?
It's a workaround, but at least, after migration I could inform the members that they have to refill these informations.
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 10, 2014, 06:28:55 AM
Maybe the sense is different here. What values do you have in IPB's table in the corresponding field?
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 10, 2014, 10:57:10 AM
Humm, strange... I couldn't find an instant_message field in the member tables...

In the ibf_member_extra table I have:

aim_name = varchar(40)
icq = int(15)
yahoo = varchar(40)
msn = varchar(200)
skype = varchar(250)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: Oldiesmann on June 10, 2014, 01:15:46 PM
"instant_messages" is the name of the column in SMF. It should be called "msg_total" in the IPB database.
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 11, 2014, 03:21:15 AM
OK, sorry ^^

msg_total     smallint(5)

Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 11, 2014, 04:12:25 PM
Give your IPB table a look, check for non-integer values in that column
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 13, 2014, 01:49:29 PM
I did a double check, but can't find something else as integers (from 0 to 403) or NULL value
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 13, 2014, 02:12:44 PM
Hummmm how odd...

Check the latest row that was added to SMF members table, get his id, then go to IPB members table and check what the next id has in msg_total, please
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 14, 2014, 12:13:04 AM
No row were added to the SMF members DB
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 14, 2014, 03:37:29 AM
Hu, right on the first row? Damn... And what value has your first row in IPB's table?
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 14, 2014, 08:08:03 AM
1100
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 14, 2014, 10:53:02 AM
No idea :o Can you please attach the converter and .sql file you are using?
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 14, 2014, 12:00:21 PM
I deleted everything and restart the conversion.

First I got one more time the birthdate issue, then the instant_message, but this time (don't ask me why..) it worked.
After that, I got the same issue for hide_email. Solved in the same way (I hope it's OK...)

Code: [Select]
$row['signature'] = substr(strtr(strtr($row['signature'], '<>', '[]'), array('[br /]' => '<br />')), 0, 65534);
$row['instant_messages'] = (int)$row['instant_messages'];
$row['hide_email'] = (int)$row['hide_email'];

Finally :
Code: [Select]
$row['signature'] = substr(strtr(strtr($row['signature'], '<>', '[]'), array('[br /]' => '<br />')), 0, 65534);
$row['instant_messages'] = (int)$row['instant_messages'];
$row['hide_email'] = (int)$row['hide_email'];
$row['pm_email_notify'] = (int)$row['pm_email_notify'];


Next error : Converting topics...Wrong value type sent to the database. Integer expected. (id_poll)

Can I do the same (int cast) as for the other one and at the same place ?
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 14, 2014, 03:16:35 PM
As long as it's integers, the cast works.
I just have no idea why that happens :o
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 14, 2014, 06:47:17 PM
it don't work. I cannot find where to put the cast :/ for the id_poll

Any advice ?
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 17, 2014, 01:39:30 AM
Still working on the conversion. I think I'm not far from goal. After making some changes I found in different topics, now I'm blocked here...

I used [mywebfiles] to mask the real hosting url

Code: [Select]
Converting polls... Successful.
Converting poll options...
Warning: array_keys() [function.array-keys]: The first argument should be an array in [mywebfiles]/convert.php on line 1291

...  Many many of these eror lines

Warning: Invalid argument supplied for foreach() in [mywebfiles]/convert.php on line 2627

Warning: array_combine() [function.array-combine]: Both parameters should have an equal number of elements in [mywebfiles]/forum/Sources/Subs-Db-mysql.php on line 627
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 ')' at line 3


Any idea about this issue (at the moment, I couldn't find a way of solution)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 17, 2014, 06:02:40 AM
I tried to jump directly to the step 10.

The conversion is reaching the end and everything works well. Except the polls of course. So as expected, This is the last issue to fix and the full conversion will be possible :)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 17, 2014, 07:48:12 AM
Can you please attach the converter you're using now?
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 17, 2014, 08:35:18 AM
Here is it.
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 17, 2014, 09:12:28 AM
Please add convert.php also ;)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 17, 2014, 09:18:14 AM
Here is it (I didn't change it)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 19, 2014, 12:32:46 PM
I have no idea, sorry :(

Maybe you can try to use the code from emanuele's converter for 3.x in that particular topic (poll options)
Code: [Select]
/******************************************************************************/
--- Converting poll options...
/******************************************************************************/

---* {$to_prefix}poll_choices
---{
$no_add = true;
$keys = array('id_poll', 'id_choice', 'label', 'votes');
$choices = @unserialize(stripslashes($row['choices']));

if (is_array($choices))
{
foreach ($choices as $choice)
{
// Put the slashes back
$choice = addslashes_recursive($choice);

// Since we modified the poll thing, we need to stick the question in here
$pollquestion = $choice['question'];
$query = convert_query("
UPDATE {$to_prefix}polls
SET question = '$pollquestion'
WHERE id_poll = '$row[id_poll]'");

// Now that we've handled the question, go ahead with our choices and votes
foreach($choice['choice'] AS $choiceid => $label)
{
// The keys of the votes array correspond to the keys of the choice array,
// which are the ID_CHOICE values
$votes = $choice['votes'][$choiceid];

// Try to work around the multiple-questions-per-poll issue...
if(isset($current_choices[$row['id_poll']][$choiceid]))
continue;
else
$current_choices[$row['id_poll']][$choiceid] = $label;

// Finally - a row of information!
                        $rows[] = array(
                   'id_poll' => $row['id_poll'],
                   'id_choice' => $choiceid,
                   'label' => substr(addslashes($label), 0, 255),
                   'votes' => $votes,
                );
}
}
}
---}
SELECT pid AS id_poll, choices
FROM {$from_prefix}polls;
---*
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 19, 2014, 01:16:35 PM
conversion restarted. Let's see if it works.

If not, then I will try to remove all the poll tables migration. It's probably better than have just some of them migrated...
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 19, 2014, 03:44:14 PM
So, conversion finished

Conversion Complete
Congratulations, the conversion has completed successfully. If you have or had any problems with this converter, or need help using SMF, please feel free to look to us for support.



But no poll options in the polls. I think I will let this like it is, as no solution is available at this time...

But I have other issues...

1. before each ' there is a \
2. in the section descriptions, I have <br /> each time the description starts a new row
3. All the letters with accents (it's a french forum) are missing.

Example :
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 19, 2014, 04:29:44 PM
accent issue solved : I had to install the french package instead of the frenchUTF8 package
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 23, 2014, 08:24:16 AM
So, last update:

- I changed the < br/> and the \ manually.
- I couldn't find a solution for the poll options, so they will stay so (poll topics still visible, but no way to see the poll options)
- attachements is an issue too, but I will let them in the status they are after migration. Too much effort to do this manually.


So, everything isn't fixed, the migration is, let say, 99% OK, so thanks a lot to everybody for the help !

Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on June 23, 2014, 09:04:12 AM
I'm glad you fixed the wrong stuff yourself and I'm sorry for not being as helpful as you'd probably expect :(

Let us know if you need further help ;)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on June 23, 2014, 11:39:06 AM
Thanks a lot for your help ;)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on August 03, 2014, 06:01:28 AM
After a few days to use, I have two issues.

1. During migration, all the post text that includes the '  caracter were changed to \\\'
Any idea how I could easy change all the \\\' in the messages by '  ?

2. I have several time the same error in the error log (the topicID can be different, it's not only for one topic):
http://myforum.com/forum/index.php?topic=205.0
512: loadMemberContext(): member id 2 not previously loaded by loadMemberData()
File: /myhomepages/forum/Sources/Load.php
Line: 1092


Any Idea/Help/Advice about that ?

Thanks in advance
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on August 04, 2014, 05:43:48 AM
Would this SQL commands in phpmyadmin work ?

UPDATE smf_messages SET body=REPLACE(body,'\\\','')

UPDATE smf_messages SET subject=REPLACE(subject,'\\\','')
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on August 05, 2014, 09:45:43 AM
That query should work, yes ;) Backup first, just in case ;)

As for your error, can you please attach your Load.php? Did you install any MODs? I've seen some errors on that but no specific solutions. Also, please make sure you run the maintenance tasks ;)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on August 05, 2014, 10:32:27 PM
Here are the load.php and the installed mods.
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on August 06, 2014, 08:31:58 AM
Can you please PM me an admin test account and a link to your forum so that I can have a look at that topic?
I have an idea on how to fix it but I need to see what's happening ;)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on August 08, 2014, 01:17:50 PM
OK, so I was able to replicate the problem and I understand why it happens now.

In SMF, when you delete an account (and choose not to delete this user's posts), all posts that he created are marked with "id_member" = 0 (in smf_messages table).

Apparently, IPB doesn't :P So your account ID=2 is a removed account, his posts were not deleted and IPB's posts table kept his ID associated with his posts. When you converted to SMF, the relevant tables were copied pretty much directly (no verification is performed) so this "detail" was also transferred.
When SMF shows you a topic, it loads also, for each post, its author's details. If the post's author ID is 0, then it's a guest and only "poster_name" is shown. If the ID is != 0, then it tries to load the user's details. Well, if the user was removed, the details cannot be found :)
So, this is your issue, apparently: your "smf_messages" table has posts recorded from users that no longer exist.

This can automagically be fixed by SMF if you run the Maintenance Task "Find and repair any errors". For example, in my test forum where I deliberately caused this error:
Quote
The following errors are fouling up your forum:
    Message #11 was posted by member #4, who is now missing.
Of course, a good database backup should be performed first. Just in case :P
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on September 05, 2014, 10:38:52 AM
Everything work fine now.

Many thanks for the help !
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on September 05, 2014, 10:40:49 AM
Nice :)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on September 06, 2014, 02:22:23 AM
Humm, I was happy to early...

After double check, in all the topics, the ' caracter is now indicated as
Code: [Select]
&# 39;
:/
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on September 06, 2014, 03:06:11 AM
Can you give an example?
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on September 06, 2014, 09:25:52 AM
http://www.jihell.com/forum/index.php/topic,9709.0.html

first message (as example). Maybe the cause could be the two update queries mentionned above to replace the \\\' by ' in the smf_message table...

The \\\ are gone, but the ' seems to be replaced too...
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on September 08, 2014, 07:51:53 AM
As screenshot all this codes should be a '
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on September 09, 2014, 03:38:33 PM
I assume that happened due to some collation stuff. Please check the table smf_messages, find your affected post (one of them) and see what shows up in the column "body".

In my test board, if I write this:
Quote
Quelq&# 39;un
My  database has:
Code: [Select]
Quelq&#38;# 39;un
Edit: you can try this in "SQL" tab: (remember to BACKUP first)
Code: [Select]
UPDATE smf_messages SET body = REPLACE(body, '&#38;# 39;','\'')In my test forum it worked.
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on September 10, 2014, 02:05:49 PM
I tried your update query, It changed 1 line.

I changed the code and ran this one :
Code: [Select]
UPDATE smf_messages SET body = REPLACE(body, '&# 39;','\'')
And now, after a first look, it seems to be OK !!!

I will continue the checks. But I think the goal is not very far.  I usually try to do most of the things by myself (the best way to learn) but sometime, know how is really missing...

Thanks for the many help and effort you did !

Regards
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on September 10, 2014, 02:18:45 PM
Cool :)
Let us know if you need further help ;)
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on September 12, 2014, 06:48:30 AM
found another issue (but I don't know what to do in this case) It looks like every 0 (zero) were dropped from the subject/body..

As exemple, in the sale part of the forum, a price was 550€ and now : 55 €... A camera reference was Canon 400D and now Canon 4  D

Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: margarett on September 12, 2014, 07:56:02 AM
:o

That's odd. Is it like that also in the database? A 0 shouldn't get into collation problems...
No idea, sorry...
Title: Re: 2.0.0 to SMF: Converting members...Wrong value type sent to the database.
Post by: LeYoyo on September 12, 2014, 08:25:59 AM
Yes, just checked in the DB :

"Neuf 4  € vendu 18 €"

Should be "Neuf 400€ vendu 180€". Means that the 0 were replaced by spaces. May be this happens when I tried to correct the '  issue...

I will check the different backups I did during the process. May be I will find when the problem appears (even if it's to late to fix it :/ )