2.0.0 to SMF: Converting members...Wrong value type sent to the database.

Started by LeYoyo, June 05, 2014, 08:43:33 AM

Previous topic - Next topic

LeYoyo

Sous SMF 2.0.8

margarett

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 ;)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

margarett

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:
QuoteThe 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
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

LeYoyo

Sous SMF 2.0.8

margarett

Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

LeYoyo

Humm, I was happy to early...

After double check, in all the topics, the ' caracter is now indicated as &# 39;

:/
Sous SMF 2.0.8

margarett

Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

LeYoyo

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...
Sous SMF 2.0.8

LeYoyo

Sous SMF 2.0.8

margarett

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:
QuoteQuelq&# 39;un
My  database has:
Quelq&# 39;un

Edit: you can try this in "SQL" tab: (remember to BACKUP first)
UPDATE smf_messages SET body = REPLACE(body, '&# 39;','\'')
In my test forum it worked.
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

LeYoyo

I tried your update query, It changed 1 line.

I changed the code and ran this one :
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
Sous SMF 2.0.8

margarett

Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

LeYoyo

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

Sous SMF 2.0.8

margarett

:o

That's odd. Is it like that also in the database? A 0 shouldn't get into collation problems...
No idea, sorry...
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

LeYoyo

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 :/ )
Sous SMF 2.0.8

Advertisement: