[SMF Converter] e107 - 0.7.7

Started by JayBachatero, January 10, 2007, 01:26:50 PM

Previous topic - Next topic

JayBachatero

Did you run the same query that you posted on the e107 table?  Can't see a reason for dup ids.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

Angpeu

SELECT user_id, COUNT(user_id) AS count
FROM e107_user
GROUP BY user_id
HAVING count > 1


=> No results => No Dup

SELECT
    u.user_id AS ID_MEMBER, SUBSTRING(u.user_name, 1, 80) AS memberName,
    u.user_join AS dateRegistered, u.user_forums AS posts,
    IF (u.user_admin = 1, 1, 0) AS ID_GROUP, u.user_lastvisit AS lastLogin,
    SUBSTRING(u.user_name, 1, 255) AS realName,
    SUBSTRING(u.user_password, 1, 64) AS passwd,
    SUBSTRING(u.user_email, 1, 255) AS emailAddress, 0 AS gender,
    ue.user_birthday AS birthdate,
    SUBSTRING(REPLACE(ue.user_homepage, 'http://', ''), 1, 255) AS websiteTitle,
    SUBSTRING(ue.user_homepage, 1, 255) AS websiteUrl,
    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 hideEmail,
    SUBSTRING(u.user_signature, 1, 65534) AS signature,
    IF(SUBSTRING(u.user_timezone, 1, 1) = '+', SUBSTRING(u.user_timezone, 2), u.user_timezone) AS timeOffset,
    SUBSTRING(u.user_image, 1, 255) AS avatar,
    SUBSTRING(u.user_customtitle, 1, 255) AS usertitle,
    SUBSTRING(u.user_ip, 1, 255) AS memberIP,
    SUBSTRING(u.user_ip, 1, 255) AS memberIP2, '' AS lngfile, '' AS buddy_list,
    '' AS pm_ignore_list, '' AS messageLabels, '' AS personalText, '' AS YIM,
    '' AS timeFormat, '' AS secretQuestion, '' AS secretAnswer,
    '' AS validation_code, '' AS additionalGroups, '' AS smileySet,
    '' AS passwordSalt, SUBSTRING(u.user_ip, 1, 255) AS memberIP2
FROM e107_user AS u
    LEFT JOIN e107_user_extended AS ue ON (u.user_id = user_extended_id)
WHERE u.user_id > 0;


=> Query from e107_to_smf.sql => Returns 1742 row and no dups.

SELECT id_member, COUNT(id_member) AS count
FROM smf_members
GROUP BY id_member
HAVING count > 1


=> Query to smf_members table with droped PRIMARY.
Return 121 row with coun = 2.

I think problem with convert procedure.

JayBachatero

Ok try this one.  The left join was missing the table alas.  Maybe that was the cause for the error.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

Angpeu

line 89 add "," on end
line 90 remove "," on end

No errors. All rows convert Rights. Victory !!!

next trouble

Converting categories... Unsuccessful!
This query:
INSERT INTO `smf`.smf_categories
(ID_CAT, name, catOrder)
VALUES ('18446744073709551118', 'Общие', '1'),
('18446744073709551121', 'РЎРµС,СЊ', '2'),
('18446744073709551127', 'РљРѕРјРїСЊСЋС,еры', '3'),
('18446744073709551130', 'Развлечения', '4');
Caused the error:

Duplicate entry '255' for key 1

JayBachatero

I wonder why the heck to they insists in not using regular id's for the categories table.  I have to find out what is the value that they use.  I thought they started it at 500.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

JayBachatero

Not sure how to take on this.  I need to dig deeper and find out what is the correct forum_id that they use for categories.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

Angpeu

i have normal id's 1,2,4,5 for categories table and remove -499 from sql query.
converts OK.

Next my BIG problem this sql works too many time and not compleate.

SELECT
    t.thread_id AS ID_TOPIC, t.thread_s AS isSticky,
    t.thread_forum_id AS ID_BOARD, t.thread_id AS ID_FIRST_MSG,
    IFNULL(tl.thread_id, t.thread_id) AS ID_LAST_MSG,
    IFNULL(us.user_id, 0) AS ID_MEMBER_STARTED,
    IFNULL(ul.user_id, IFNULL(us.user_id, 0)) AS ID_MEMBER_UPDATED,
    IFNULL(p.poll_id, 0) AS ID_POLL, COUNT(*) AS numReplies, t.thread_views AS numViews,
    IF(t.thread_active = 1, 0, 1) AS locked
FROM `onet-local`.e107_forum_t AS t
    LEFT JOIN `onet-local`.e107_user AS us ON (us.user_id = SUBSTRING_INDEX(t.thread_user, '.', 1))
    LEFT JOIN `onet-local`.e107_forum_t AS tl ON (tl.thread_parent = t.thread_id AND tl.thread_datestamp = t.thread_lastpost)
    LEFT JOIN `onet-local`.e107_user AS ul ON (ul.user_id = SUBSTRING_INDEX(tl.thread_user, '.', 1))
    LEFT JOIN `onet-local`.e107_forum_t AS m ON (tl.thread_parent = t.thread_id)
    LEFT JOIN `onet-local`.e107_polls AS p ON (p.poll_datestamp = t.thread_id)
WHERE t.thread_parent = 0
GROUP BY t.thread_id
LIMIT 0, 500

its run more 3 hours and mysql use 100% CPU on Athlon64 3200. ;(

Angpeu

my e107_forum_t table have 135541 rows, may be its too many for 5 LEFT JOIN's?

JayBachatero

Ok limited the query to just 200 results for topics.  Also I put a check to see if the forum_id is greater than 500 and what to do with it.  Try this one.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

Angpeu

this query not complete on my DB with LIMIT 1  :(
i do now why :(

SELECT
t.thread_id AS ID_TOPIC, t.thread_s AS isSticky,
t.thread_forum_id AS ID_BOARD, t.thread_id AS ID_FIRST_MSG,
IFNULL(tl.thread_id, t.thread_id) AS ID_LAST_MSG,
IFNULL(us.user_id, 0) AS ID_MEMBER_STARTED,
IFNULL(ul.user_id, IFNULL(us.user_id, 0)) AS ID_MEMBER_UPDATED,
IFNULL(p.poll_id, 0) AS ID_POLL, COUNT(*) AS numReplies, t.thread_views AS numViews,
CASE WHEN t.thread_active = 1 THEN 0 ELSE 1 END AS locked
FROM `onet-local`.e107_forum_t AS t
LEFT JOIN `onet-local`.e107_user AS us ON (us.user_id = SUBSTRING_INDEX(t.thread_user, '.', 1))
LEFT JOIN `onet-local`.e107_forum_t AS tl ON (tl.thread_parent = t.thread_id AND tl.thread_datestamp = t.thread_lastpost)
LEFT JOIN `onet-local`.e107_user AS ul ON (ul.user_id = SUBSTRING_INDEX(tl.thread_user, '.', 1))
LEFT JOIN `onet-local`.e107_forum_t AS m ON (tl.thread_parent = t.thread_id)
LEFT JOIN `onet-local`.e107_polls AS p ON (p.poll_datestamp = t.thread_id)
WHERE t.thread_parent = 0
GROUP BY t.thread_id
LIMIT 1


JayBachatero

What error are you getting?  It's just over loading the server?
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

Angpeu

No errors
MySql show State: Copying to tmp table and just over loading server.
i kill him for stop.

JayBachatero

Might be a problem with index.  Might have some missing index causing MySQL to choke.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

JayBachatero

How is this query?

SELECT
t.thread_id AS ID_TOPIC, t.thread_s AS isSticky,
t.thread_forum_id AS ID_BOARD, t.thread_id AS ID_FIRST_MSG,
IFNULL(tl.thread_id, t.thread_id) AS ID_LAST_MSG,
IFNULL(us.user_id, 0) AS ID_MEMBER_STARTED,
IFNULL(ul.user_id, IFNULL(us.user_id, 0)) AS ID_MEMBER_UPDATED,
IFNULL(p.poll_id, 0) AS ID_POLL, COUNT(*) AS numReplies, t.thread_views AS numViews,
CASE WHEN t.thread_active = 1 THEN 0 ELSE 1 END AS locked
FROM `onet-local`.e107_forum_t AS t
LEFT JOIN `onet-local`.e107_user AS us ON (us.user_id = SUBSTRING_INDEX(t.thread_user, '.', 1))
LEFT JOIN `onet-local`.e107_forum_t AS tl ON (tl.thread_parent = t.thread_id AND tl.thread_datestamp = t.thread_lastpost)
LEFT JOIN `onet-local`.e107_user AS ul ON (ul.user_id = SUBSTRING_INDEX(tl.thread_user, '.', 1))
LEFT JOIN `onet-local`.e107_polls AS p ON (p.poll_datestamp = t.thread_id)
WHERE t.thread_parent = 0
GROUP BY t.thread_id
LIMIT 1
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

Angpeu


JayBachatero

Ok try this file.  Not sure why that table was being joined 3 times.  The last one didn't seem to have a purpose.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

Angpeu

Next Problem:
Converting posts (this may take some time)... Unsuccessful!
This query:
INSERT INTO `smf`.smf_messages
...................
Caused the error:
Duplicate entry '69422' for key 1

JayBachatero

Ok lets set ignore to true to see how many "duplicate" posts are there.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

Angpeu

Converting topics... Unsuccessful!
This query:
INSERT INTO `smf`.smf_topics
(ID_TOPIC, isSticky, ID_BOARD, ID_FIRST_MSG, ID_LAST_MSG, ID_MEMBER_STARTED, ID_MEMBER_UPDATED, ID_POLL, numReplies, numViews, locked)
VALUES ('95039', '0', '11', '95039', '95324', '77', '77', '0', '1', '51', '0'),
.........................................
Caused the error:
Duplicate entry '95039' for key 1


JayBachatero

Weird.  It worked before so it should be working now.  Converting topics comes before converting posts.  Try truncating the topics table.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

Advertisement: