UseBB 0.5.1 to SMF Converter - Wrong value type sent

[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

[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?

[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.

[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

[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

[Wolpo]

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

[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]

... that it's YYYYMMDD which we need to change into YYYY-MM-DD.

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