e107 conversion

[LiroyvH]

Hey folks,

I couldnt figure out what causes this issue, so I need some help

Converting...
Converting ranks...Converting groups... Successful.
Converting members...Wrong value type sent to the database. Floating point number expected. (time_offset)

That's it... does nothing more. Any idea?

[Elmacik]

Does it give you a line number? For a try, you can empty the time offset values of members in e107. Then retry converting.

[LiroyvH]

Nope that's the problem, it doesnt give any details
I cant find a option to do that in e107 admin and doing it member by member manually is a no go

[Elmacik]

Don't go manually one by one of course. Just go to members table of e107. I am sure there must be a column for that. Say it is "offset", the only thing you should do is to run this query: update e107members_table_name set offset = ''

[Norv]

The problem seems to be related to the user_timezone column. Perhaps if you can run the following code in phpMyAdmin, and look at the results, you might notice which is not a numeric value in the second column. The ID of the user for which there's a problem in that column is the first column, so eventually it can be fixed if that is the problem (and if there are not too many).

Code Select Expand


SELECT u.user_id AS id, (
CASE
WHEN SUBSTRING(u.user_timezone, 1, 1) = '+'
THEN SUBSTRING(u.user_timezone, 2)
ELSE u.user_timezone
END) AS time_offset
FROM {$from_prefix}user AS u

(please use as {$from_prefix} your e107 prefix)

In case there are too many, perhaps setting the time_offset (the time_offset column exists in SMF) to 0 for all members, instead, may be an acceptable solution.

[LiroyvH]

I just looked in the db. There is no such table. It is called "user_timezone", not time_offset.
I checked it, some were blank or said GMT rather than a number.

Fixed those to 0 like it should if empty, but still no joy on the conversion, same error.


@Norv:

I ran that, it says:
#1064 - 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 '}user AS u
LIMIT 0, 30' at line 7

Then the screen on the left refreshes and says NO TABLES IN DATABASE. Untill I reopen the database manually.

I cant set anything in SMF to 0 I guess, as there are no members on it yet

[Norv]

I just looked in the db. There is no such table. It is called "user_timezone", not time_offset.

Yes, that's what I am saying. There is something about user_timezone that it doesn't like.

I checked it, some were blank or said GMT rather than a number.

Ah. The letters "GMT"? Probably that's the problem though.

I ran that, it says:
#1064 - 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 '}user AS u
LIMIT 0, 30' at line 7

Please replace {$from_prefix} with your actual e107 prefix.
(including the last } )

Alternatively, to set time_offset to 0 for all members, in the conversion SQL script, you can eventually try:

Code (find) Select Expand


  CASE
WHEN SUBSTRING(u.user_timezone, 1, 1) = '+'
THEN SUBSTRING(u.user_timezone, 2)
ELSE u.user_timezone
END AS time_offset,


Code (replace) Select Expand


0 AS time_offset

(e107_to_smf.sql)

[LiroyvH]

Please replace {$from_prefix} with your actual e107 prefix.
(including the last } )

I did, the query's I tried:

SELECT u.user_id AS id, (
CASE
WHEN SUBSTRING(u.user_timezone, 1, 1) = '+'
THEN SUBSTRING(u.user_timezone, 2)
ELSE u.user_timezone
END) AS time_offset
FROM {$e107_}user AS u

SELECT u.user_id AS id, (
CASE
WHEN SUBSTRING(u.user_timezone, 1, 1) = '+'
THEN SUBSTRING(u.user_timezone, 2)
ELSE u.user_timezone
END) AS time_offset
FROM {e107_}user AS u

SELECT u.user_id AS id, (
CASE
WHEN SUBSTRING(u.user_timezone, 1, 1) = '+'
THEN SUBSTRING(u.user_timezone, 2)
ELSE u.user_timezone
END) AS time_offset
FROM {$e107}user AS u

All returns same error.

I also already replaced all GMT to 0, and blank entries are set to 0 aswell.

[Norv]

I'm sorry, I don't understand: what is your e107 prefix of the tables?

[LiroyvH]

My prefix is e107_
Like e107_users for a table.


Replacing the SQL code led to:

Converting ranks...Converting groups... Successful.
Converting members... Unsuccessful!
This query:

    SELECT
    u.user_id AS id_member, SUBSTRING(u.user_loginname, 1, 80) AS member_name,
    u.user_join AS date_registered, u.user_forums AS posts,
    (CASE WHEN u.user_admin = 1 THEN 1 ELSE 0 END) AS id_group, u.user_lastvisit AS last_login,
    SUBSTRING(u.user_name, 1, 255) AS real_name,
    SUBSTRING(u.user_password, 1, 64) AS passwd,
    SUBSTRING(u.user_email, 1, 255) AS email_address, 0 AS gender,
    ue.user_birthday AS birthdate,
    SUBSTRING(REPLACE(ue.user_homepage, 'http://', ''), 1, 255) AS website_title,
    SUBSTRING(ue.user_homepage, 1, 255) AS website_url,
    SUBSTRING(ue.user_location, 1, 255) AS location,
    SUBSTRING(ue.user_icq, 1, 255) AS icq,
    SUBSTRING(ue.user_aim, 1, 16) AS aim,
    SUBSTRING(ue.user_msn, 1, 255) AS msn, u.user_hideemail AS hide_email,
    SUBSTRING(u.user_signature, 1, 65534) AS signature,
    0 AS time_offset
    SUBSTRING(u.user_image, 1, 255) AS avatar,
    SUBSTRING(u.user_customtitle, 1, 255) AS usertitle,
    SUBSTRING(u.user_ip, 1, 255) AS member_ip,
    SUBSTRING(u.user_ip, 1, 255) AS member_ip2, '' AS lngfile, '' AS buddy_list,
    '' AS pm_ignore_list, '' AS message_labels, '' AS personal_text, '' AS yim,
    '' AS time_format, '' AS secret_question, '' AS secret_answer, '' AS password_salt,
    '' AS validation_code, '' AS additional_groups, '' AS smiley_set
    FROM `teamrza_rzasite`.e107_user AS u
    LEFT JOIN `teamrza_rzasite`.e107_user_extended AS ue ON (ue.user_extended_id = u.user_id)
    WHERE u.user_id != 0
    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 'SUBSTRING(u.user_image, 1, 255) AS avatar,
    SUBSTRING(u.user_customtitle, 1, 255' at line 17

[Norv]

My prefix is e107_
Like e107_users for a table.

Then e107_user should be used
like:

Code Select Expand


SELECT u.user_id AS id, (
CASE
WHEN SUBSTRING(u.user_timezone, 1, 1) = '+'
THEN SUBSTRING(u.user_timezone, 2)
ELSE u.user_timezone
END) AS time_offset
FROM e107_user AS u


And argh!
In the last query: please replace

Code Select Expand


0 AS time_offset

with

Code Select Expand


0 AS time_offset,


[LiroyvH]

Ok, that worked and I fixed a final blank entry. Thnx!

Now, on to the next goddamn issue

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

Is it gonna biatch about all values now?

[Norv]

Well, it seems it doesn't like it, heh.
The column in e107 is user_birthday from the same user table. SMF expects it to be a date. Could you please take a look and tell if anything strikes you as different than a date?

[LiroyvH]

Well, it seems it doesn't like it, heh.
The column in e107 is user_birthday from the same user table. SMF expects it to be a date. Could you please take a look and tell if anything strikes you as different than a date?

Not really,

SELECT COUNT( * ) AS `Rijen` , `user_birthday`
FROM `e107_user_extended`
GROUP BY `user_birthday`
ORDER BY `user_birthday`
LIMIT 0 , 600

Returns 84 values, all proper dates, but there are like 550 members. 0o

[Norv]

The difference must be because many of them didn't enter a birth date.
Can you please give an example of one of those dates?

[LiroyvH]

Sure,

Rijen    user_birthday Oplopend
241    0000-00-00
1    1948-10-26
1    1950-01-24
1    1951-10-31
1    1952-08-22
1    1954-04-27
1    1954-12-08
1    1955-05-12
1    1957-11-19
1    1957-12-19
1    1960-11-28
1    1961-03-06
1    1962-03-07
1    1962-07-03

[Norv]

I'm afraid this one may require a little more thinking.

I suppose you are using the converter to SMF 2.0?
It may be a good idea to try using the converter to 1.1.10, then if you wish, you can upgrade the forum normally.
It is my understanding that the converters to 2.0 are considered still beta, it seems these bugs just ... are there. Though with your help, one is noted down, and this one, I'll debug it a little at a later time. (it seems I need to test it myself)

[LiroyvH]

I'm afraid this one may require a little more thinking.

I suppose you are using the converter to SMF 2.0?
It may be a good idea to try using the converter to 1.1.10, then if you wish, you can upgrade the forum normally.
It is my understanding that the converters to 2.0 are considered still beta, it seems these bugs just ... are there. Though with your help, one is noted down, and this one, I'll debug it a little at a later time. (it seems I need to test it myself)

Ok tried 1.1.10, it goes a hole bunch further but also crashes.

Recalculating forum statistics... Successful.
Unsuccessful!
This query:

    REPLACE INTO {$to_prefix}settings (variable, value)
    VALUES ("conversion_time", 1252938954),
    ("conversion_from", "e107_to_smf.sql");

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 '{$to_prefix}settings (variable, value)
    VALUES ("conversion_time", 1252938954)' at line 1

And in the screen before that, I saw eval errors but didnt have enough time to copy exact error, lol.

[Norv]

This last error is nothing to worry about. It was my understanding that "Recalculating forum statistics... Successful." is the last step that matters for the conversion, so it succeeded.
The last attempt (hat fails currently) is trying to set some value potentially useful for debugging problems, but not required in any way by the conversion process.

[LiroyvH]

Hallelujah, ur right
It works fine now.

Thnx!