News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

UseBB 0.5.1 to SMF Converter - Wrong value type sent

Started by Wolpo, August 14, 2015, 12:02:37 AM

Previous topic - Next topic

Wolpo

Hello,

I'd like to thank the people who developed this converter. You are angels!

Unfortunately, my convertion has stopped 10 minutes ago with this message:

Converting members...Wrong value type sent to the database. Date expected. (birthdate)

I don't know what to do. Does anybody know a solution?

I didn't select the "UTF-8" checkbox on the converter's intro page.

It's UseBB version 1.0.16, but I think this shouldn't make any difference to version 0.5.1 regarding the database.

Please let me know if you need further details.


Thank you!

Wolpo

margarett

Hi

I never checked that software but I would expect database changes...

BTW, 10 minutes and still converting members? How big is your forum?

Finally: I need to check the converter code when I'm at the computer and suggest a fix, maybe you have NULL values
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

Wolpo

Hi, thank you for your quick reply!

My board has about 30,000 posts, circa 3000 topics and circa 1000 members.

I wrote "10 minutes" to indicate that it probably hangs rather than being in progress. The status is still the same now after over 3 hours.

By the way, I'm using MySQL version 5.5.38.


Regards,

Wolpo

Wolpo

Hello again,

I just took a look at the file usebb_to_smf.sql and noticed the following line (that I marked in red):

--- snip ---

/******************************************************************************/
--- Converting members...
/******************************************************************************/

TRUNCATE {$to_prefix}members;

---* {$to_prefix}members
SELECT
   id AS id_member, SUBSTRING(name, 1, 80) AS member_name,
   SUBSTRING(email, 1, 255) AS email_address, email_show = 0 AS hide_email,
   SUBSTRING(passwd, 1, 64) AS passwd, regdate AS date_registered,
   IF(level = 3, 1, 0) AS id_group, active AS is_activated,
   SUBSTRING(active_key, 1, 10) AS validation_code,
   last_pageview AS last_login, hide_from_online_list = 0 AS show_online, posts,
   SUBSTRING(avatar_remote, 1, 255) AS avatar,
   SUBSTRING(displayed_name, 1, 255) AS real_name,

   SUBSTRING(signature, 1, 65534) AS signature, birthday AS birthdate,


--- snip ---

I assume this is the command which leads to that error message in the converter:

"Converting members...Wrong value type sent to the database. Date expected. (birthdate)"

Does anybody have a suggestion as to how I could edit that line ("birthday AS birthdate") to get rid of this error?

Many thanks!


Regards,

Wolpo

margarett

Can you go to your phpmyadmin and check which birthday values you have in your original database? Maybe you have a NULL value?
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

Wolpo

Hi Margarett,

I'm now on my phpmyAdmin page, but I don't know much about all these tools (yet). Could you tell me where I can check a "value"?

I see the menu at the top:

Structure | SQL | Search | Query | Export | Import | Operations

Under "Structure" there is a list:

useBB_forums
useBB_members
useBB_moderators
etc.

Each list item has a checkbox. At the bottom of the page there is a related combo box that reads "With selected:" -- And with that I can do:

Empty
Drop
Print view
Check table
etc.

(Some of these jobs sound dangerous :-))

Or do I have to look under "SQL" or "Search" or ...?

Many thanks!


Cheers,

Wolpo

margarett

Go to the table "members", there you'll see a lot of columns, one of which is "birthdate". Click on it, it should order the table records. Most likely you have some "other" contents than not just birthdays.
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

Wolpo

Thank you!

The attribute is called "birthday" and all the 1000+ values in that column are integers as far as I can see; most of them are "0", the rest are in a format like "19501230" (e.g. 1950 December 30).

What kind of value does the smf converter expect there?

Edit: PHPmyAdmin tells me that useBB uses int(8) for birthday. I guess this means an integer with max 8 digits.

Wolpo

When I click on smf_members and check the structure, I see in the field birthdate the default value 0001-01-01.

Could it be that the converter expects dashes within the birthdate format? E.g. does it expect "1950-12-30" instead of "19501230"?

margarett

Not on smf_members, on your origin members table. There you need to check if you can find content which should be a birthdate but it isn't
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

Wolpo

Yes, I first checked my origin members table (useBB_members). Did you see my post #7 above?

Under useBB_members I just have mostly zeros and some 8-digit integers like 19501230.

Will the converter complain when a birthdate is zero?

margarett

Oh sorry i missed that other post. That format shouldn't be accepted by SMF, not... I did that already for another converter, I'd suggest that it's YYYYMMDD which we need to change into YYYY-MM-DD.

Let me get to the computer
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

Wolpo

#12
99% Success! :-)

In usebb_to_smf.sql I removed this:

birthday AS birthdate,

... and also removed the rest starting from here:

/******************************************************************************/
--- Converting censored words...
/******************************************************************************/



With this modification the convertion ran without any error messages. All birthdates are now reset to zero, but I can live with that. In a previous attempt it also froze at the censored words; that probably happened because I had no censored words. To prevent further problems I also removed the moderator stuff; I have only two and can add them manually anyway.

As far as I can see, there's now only one problem (or three) remaining in the converted posts:

All apostrophes ' are preceded by a backslash \'
All quotation marks " are preceded by a backslash \"
All BBCode tags for font size are not applied, i.e. the tags are shown as normal text and have no effect.


All other format functions were converted correctly.

I tried two convertions, the first with the default latin setting, and the second with UTF-8. I also tried the Admin maintanace function "Convert HTML-entities to UTF-8 characters". But it didn't fix it.

Is this a known problem?

Anyway, I thank all you experts for the convertion tools! The current result is already a huge success.


Regards,

Wolpo

margarett

Check the latest converters, they include some fixes for the backslashes stuff ;)

Sorry, I'm now on the phone, vacations whoooo :)
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

Wolpo

I checked this: http://download.simplemachines.org/?converters;software=usebb

But it's still the same.

convert.php - Created 4 June 2011, modified 4 June 2011

usebb_to_smf.sql - Created 24 October 2010, modified 19 August 2015 - but I don't see any changes.

Margarett, did you change anything in usebb_to_smf.sql?

Is there any SQL command in PHPmyAdmin that enables me to remove all backslashes from all body fields within the smf_messages table?

Wolpo

I just removed the backslash stuff myself. Actually, it was a double-backslash. I did this:

- In PHPmyAdmin I exported smf_messages to a zipped SQL file.
- Loaded the downloaded unzipped SQL file in a text editor and replaced all \\' to ' and all \\" to ".
- Saved and zipped it.
- In PHPmyAdmin I dropped the smf_messages table.
- In PHPmyAdmin I imported my zipped, modified SQL file.

Done :-)




CnZhx

Quote from: margarett on August 17, 2015, 03:19:57 PM
... that it's YYYYMMDD which we need to change into YYYY-MM-DD.


Quote from: Wolpo on August 19, 2015, 06:46:10 AM
99% Success! :-)

In usebb_to_smf.sql I removed this:

birthday AS birthdate,


IMHO, in usebb_to_smf.sql, the content,

birthday AS birthdate,

can be changed to,

IF(birthday = 0, '0001-01-01', convert(birthday, DATE)) AS birthdate,

to avoid the first error.

Regards,
CnZhx

Advertisement: