News:

Wondering if this will always be free?  See why free is better.

Main Menu

PHPBB2 - problem with MySQL

Started by rejetto, August 22, 2006, 08:29:55 PM

Previous topic - Next topic

MrPrise

After the convert finished, it starts to "Recalculating forum statistics..." It takes a very long time, maybe forever?

Ferny

#21
QuoteAfter the convert finished, it starts to "Recalculating forum statistics..." It takes a very long time, maybe forever?

It's a bug, see my post about convert.php above ;)
Digital Video & Audio:
www.mundodivx.com

MrPrise

Quote from: Ferny on August 24, 2006, 04:04:33 PM
QuoteAfter the convert finished, it starts to "Recalculating forum statistics..." It takes a very long time, maybe forever?

It's a bug, see my post about convert.php above ;-)
Ahh. Now, it is ok. Thank you!

Ferny

#23
Quote from: Person on August 24, 2006, 01:26:08 PM
Still this error  :(

QuoteConverting topics... Unsuccessful!
This query:

    INSERT INTO `acentermedia_main`.smf_topics
    (ID_TOPIC, isSticky, ID_FIRST_MSG, ID_LAST_MSG, ID_MEMBER_STARTED, ID_MEMBER_UPDATED, ID_BOARD, ID_POLL, locked, numReplies, numViews)
    VALUES ('3', '0', '3', '32', '2', '2', '2', '', '0', '1', '370'),
    ('4', '0', '4', '118', '2', '2', '1', '', '0', '10', '1318'),
    ('5', '0', '6', '81', '3', '2', '2', '', '0', '27', '2207'),
    ('6', '0', '14', '2822', '3', '27', '2', '', '0', '34', '5462'),
    ('7', '0', '19', '139', '4', '4', '6', '', '0', '2', '483'),
    ('8', '0', '23', '328', '3', '3', '2', '', '0', '6', '815'),
    ('9', '0', '24', '102', '3', '4', '7', '', '0', '3', '48'),
    ('10', '0', '42', '46', '2', '4', '6', '', '0', '1', '292'),
    ('12', '1', '59', '2774', '3', '4', '2', '', '0', '9', '1237'),
    ('13', '0', '87', '280', '4', '4', '3', '', '1', '83', '5912'),
   

Caused the error:

    Incorrect integer value: '' for column 'ID_POLL' at row 1

:(

This should fix it

Find in sql file:
/******************************************************************************/
--- Converting topics...
/******************************************************************************/

TRUNCATE {$to_prefix}topics;
TRUNCATE {$to_prefix}log_topics;
TRUNCATE {$to_prefix}log_boards;
TRUNCATE {$to_prefix}log_mark_read;

---* {$to_prefix}topics
SELECT
t.topic_id AS ID_TOPIC, t.topic_type = 1 AS isSticky,
t.topic_first_post_id AS ID_FIRST_MSG, t.topic_last_post_id AS ID_LAST_MSG,
t.topic_poster AS ID_MEMBER_STARTED, p.poster_id AS ID_MEMBER_UPDATED,
t.forum_id AS ID_BOARD, v.vote_id AS ID_POLL, t.topic_status = 1 AS locked,
t.topic_replies AS numReplies, t.topic_views AS numViews
FROM {$from_prefix}topics AS t
LEFT JOIN {$from_prefix}posts AS p ON (p.post_id = t.topic_last_post_id)
LEFT JOIN {$from_prefix}vote_desc AS v ON (v.topic_id = t.topic_id)
/* // !!! WHERE t.topic_moved_id = 0 */
GROUP BY t.topic_id
HAVING ID_FIRST_MSG != 0
AND ID_LAST_MSG != 0;
---*


Find:
v.vote_id AS ID_POLL

Replace it:
IF(v.vote_id <> 0, v.vote_id, 0) AS ID_POLL

I got this error converting one of my forums and it was solved. In other forum was not necessary. I recommend to do this change to be sure that the conversion doesn't fail
Digital Video & Audio:
www.mundodivx.com

Ferny

Another fix. While converting posts, if you see an error involving column 'modifiedTime', you should apply this fix:

Find
/******************************************************************************/
--- Converting posts (this may take some time)...
/******************************************************************************/

TRUNCATE {$to_prefix}messages;

---* {$to_prefix}messages 200
---{
$row['body'] = preg_replace('~\[size=([789]|[012]\d)\]~is', '[size=$1px]', $row['body']);
---}
SELECT
p.post_id AS ID_MSG, p.topic_id AS ID_TOPIC, p.post_time AS posterTime,
p.poster_id AS ID_MEMBER,
SUBSTRING(IFNULL(u.user_email, ''), 1, 255) AS posterEmail,
SUBSTRING(IF(IFNULL(pt.post_subject, '') = '', t.topic_title, pt.post_subject), 1, 255) AS subject,
SUBSTRING(IF(IFNULL(p.post_username, '') = '', u.username, p.post_username), 1, 255) AS posterName,
p.enable_smilies AS smileysEnabled, p.post_edit_time AS modifiedTime,
CONCAT_WS('.', CONV(SUBSTRING(p.poster_ip, 1, 2), 16, 10), CONV(SUBSTRING(p.poster_ip, 3, 2), 16, 10), CONV(SUBSTRING(p.poster_ip, 5, 2), 16, 10), CONV(SUBSTRING(p.poster_ip, 7, 2), 16, 10)) AS posterIP,
SUBSTRING(REPLACE(REPLACE(IF(pt.bbcode_uid = '', pt.post_text, REPLACE(REPLACE(REPLACE(pt.post_text, CONCAT(':u:', pt.bbcode_uid), ''), CONCAT(':1:', pt.bbcode_uid), ''), CONCAT(':', pt.bbcode_uid), '')), '\n', '<br />'), '"', '"'), 1, 65534) AS body,
p.forum_id AS ID_BOARD, '' AS modifiedName, 'xx' AS icon
FROM ({$from_prefix}posts AS p, {$from_prefix}posts_text AS pt, {$from_prefix}topics AS t)
LEFT JOIN {$from_prefix}users AS u ON (u.user_id = p.poster_id)
WHERE pt.post_id = p.post_id
AND t.topic_id = p.topic_id;
---*


Find:
p.post_edit_time AS modifiedTime

Replace it:
IF(p.post_edit_time > 0, p.post_edit_time, 0) AS modifiedTime

Also I got this error in one forum but not in other...
Digital Video & Audio:
www.mundodivx.com

Person

While you're typing about second bug I have found it too.

Now, it's better, but I have this problem:
QuoteConverting topic notifications... Unsuccessful!
This query:

    GROUP BY ID_MEMBER, ID_TOPIC
    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 'GROUP BY ID_MEMBER, ID_TOPIC
    LIMIT 0, 500' at line 1

Hmmm, I think that I saw solution somewhere on this forum ...

Thanks

Person

Without this fix http://www.simplemachines.org/community/index.php?topic=107868.msg695181#msg695181 ... I have the same error.
When I changed code, I get error, which I have posted one post back.

Ferny

Post the code of that section in sql file
Digital Video & Audio:
www.mundodivx.com

Person

Original code was:
/******************************************************************************/
--- Converting topic notifications...
/******************************************************************************/

TRUNCATE {$to_prefix}log_notify;

---* {$to_prefix}log_notify
SELECT user_id AS ID_MEMBER, topic_id AS ID_TOPIC
FROM {$from_prefix}topics_watch;
---*

This makes error: http://www.simplemachines.org/community/index.php?topic=107868.msg695181#msg695181

This is "fixed" code:
/******************************************************************************/
--- Converting topic notifications...
/******************************************************************************/

TRUNCATE {$to_prefix}log_notify;

---* {$to_prefix}log_notify
SELECT user_id AS ID_MEMBER, topic_id AS ID_TOPIC
FROM {$from_prefix}topics_watch;
GROUP BY ID_MEMBER, ID_TOPIC;
---*


And this makes this: http://www.simplemachines.org/community/index.php?topic=107868.msg696826#msg696826

Ferny

FROM {$from_prefix}topics_watch;

Just delete the last ; ;)

This is correct:
/******************************************************************************/
--- Converting topic notifications...
/******************************************************************************/

TRUNCATE {$to_prefix}log_notify;

---* {$to_prefix}log_notify
SELECT user_id AS ID_MEMBER, topic_id AS ID_TOPIC
FROM {$from_prefix}topics_watch
GROUP BY ID_MEMBER, ID_TOPIC;
---*
Digital Video & Audio:
www.mundodivx.com

Person

Thanks!

Now, converting is OK, but I can't log myself on :(

An Error Has Occurred!
You were unable to login. Please check your cookie settings.


Any idea?

EDITED: I think that this is bug in Firefox ... I have just try IE and it's works. :)

Person

It's still imposible to log-on with Firefox ... :(
& I don't have Private messages in Inbox folder :(

Ferny

Try deleting your cookies

I still have not do a complete conversion, maybe appear some bugs more
Digital Video & Audio:
www.mundodivx.com

Person

OK, now, when I deleted cookie, I can log-on.

Bug is in Private messages in Inbox & Labels ...

Person

& another bug: At membergroups is prefix phpBB

Sample:
QuotephpBB OK-SG
must be:
QuoteOK-SG

IGSkirk


Converting posts (this may take some time)... Unsuccessful!
This query:

    INSERT INTO `if_smf`.smf_messages
    (ID_MSG, ID_TOPIC, posterTime, ID_MEMBER, posterEmail, subject, posterName, smileysEnabled, modifiedTime, posterIP, body, ID_BOARD, modifiedName, icon)
    VALUES ('379072', '1949', '1126528814', '5863', '[email protected]', 'Ratchet &amp; Clank vending machine', 'Demolition', '1', '', '70.56.201.95', 'U get sasha
    <br />*inserts clank*', '6', '', 'xx'),

    [...snip...]

Caused the error:

    Duplicate entry '379072' for key 1


First off - thanks to Ferny and everyone for the updates & mods to the scripts - I've incorporated all those so far and it runs MORE smoothly - but I'm still getting a hiccup when importing POSTS (see above).

I checked the source phpbb_posts table and there has never yet been any duplicates of the post_id it's reporting (I have 800K posts, so forgive me that I haven't scanned the entire table visually). 

When I check the SMF posts table, there is already a post been inserted of this key.  It's as if the script is trying to insert the same post TWICE?

I've gotten this maybe 15-20 times so far, after about 50% of the posts.  My work-around to-date (over the last 24 hrs - and I've been running the script nearly all of that 24 hrs) has been to delete the source post from phpbb_posts (which isn't necessarily cool, since it's not a duplicate) - but now that I'm in the 450-475K range, I've gotten about 10 of these errors now, and so I'm starting to get concerned.

Any thoughts from anyone?

Ferny

Quote from: Person on August 24, 2006, 06:09:00 PM
& another bug: At membergroups is prefix phpBB

Sample:
QuotephpBB OK-SG
must be:
QuoteOK-SG

It's not a bug, the converter automatically adds the phpbb prefix to all phpbb ranks. If you don't want it, do this change.

Find:
SELECT
SUBSTRING(CONCAT('phpBB ', rank_title), 1, 255) AS groupName,
rank_image AS stars, IF(rank_special = 0, rank_min, -1) AS minPosts,
'' AS onlineColor
FROM {$from_prefix}ranks
ORDER BY rank_min;


Replace it:
SELECT
SUBSTRING(rank_title, 1, 255) AS groupName,
rank_image AS stars, IF(rank_special = 0, rank_min, -1) AS minPosts,
'' AS onlineColor
FROM {$from_prefix}ranks
ORDER BY rank_min;





To IGSkirk: I don't know why this occurs (I converted a forum with 110K posts without this error) but I'll review the code.
Digital Video & Audio:
www.mundodivx.com

Person

OK, this is now OK.

Problems:
* Private messages - inbox, labels (scrip create one with only number)

I have one questions. How can I force convertion script to use UTF8 charset?

kitz

First of all guys, thanks for the work youve put into this so far.....

However Im still having a problem here


QuoteConverting topic notifications... Unsuccessful!
This query:
INSERT INTO `forum`.smf_log_notify
(ID_MEMBER, ID_TOPIC)
VALUES ('2', '44'),
('3', '44'),
('6', '11'),
('6', '13'),

//snip

Caused the error:

Duplicate entry '2-44-0' for key 1

This is my code...  am I missing something blindingly obvious?

/******************************************************************************/
--- Converting topic notifications...
/******************************************************************************/

TRUNCATE {$to_prefix}log_notify;

---* {$to_prefix}log_notify
SELECT user_id AS ID_MEMBER, topic_id AS ID_TOPIC
FROM {$from_prefix}topics_watch
GROUP BY ID_MEMBER, ID_TOPIC;
---*

pkeffect

SMF 1.1RC3

Some of it worked.

Trying to convert phpBB from 2.0.18. Most of it went well even though I had a sub-category mod installed. It converted all to the main index.

No biggie.

Here's the failure.

QuoteConverting members... Unsuccessful!
This query:

    SELECT
    u.user_id AS ID_MEMBER, SUBSTRING(u.username, 1, 80) AS memberName,
    SUBSTRING(u.username, 1, 255) AS realName,
    SUBSTRING(u.user_password, 1, 64) AS passwd, u.user_lastvisit AS lastLogin,
    u.user_regdate AS dateRegistered,
    SUBSTRING(u.user_from, 1, 255) AS location,
    u.user_posts AS posts, IF(u.user_level = 1, 1, mg.ID [nofollow]_GROUP) AS ID_GROUP,
    u.user_new_privmsg AS instantMessages,
    SUBSTRING(u.user_email AS emailAddress, 1, 255) AS emailAddress,
    u.user_unread_privmsg AS unreadMessages,
    SUBSTRING(u.user_msnm AS MSN, 1, 255) AS MSN,
    SUBSTRING(u.user_aim AS AIM, 1, 16) AS AIM,
    SUBSTRING(u.user_icq, 1, 255) AS ICQ,
    SUBSTRING(u.user_yim, 1, 32) AS YIM,
    SUBSTRING(u.user_website, 1, 255) AS websiteTitle,
    SUBSTRING(u.user_website, 1, 255) AS websiteUrl,
    u.user_allow_viewonline AS showOnline, u.user_timezone AS timeOffset,
    IF(u.user_viewemail = 1, 0, 1) AS hideEmail, u.user_avatar AS avatar,
    REPLACE(u.user_sig, '\n', '<br />') AS signature,
    u.user_sig_bbcode_uid AS signature_uid, u.user_avatar_type,
    u.user_notify_pm AS pm_email_notify, u.user_active AS is_activated,
    '' AS lngfile, '' AS buddy_list, '' AS pm_ignore_list, '' AS messageLabels,
    '' AS personalText, '' AS timeFormat, '' AS usertitle, '' AS memberIP,
    '' AS secretQuestion, '' AS secretAnswer, '' AS validation_code,
    '' AS additionalGroups, '' AS smileySet, '' AS passwordSalt,
    '' AS memberIP2
    FROM `herbtalk_info_-_phpbb`.phpbb_users AS u
    LEFT JOIN `herbtalk_info_-_phpbb`.phpbb_ranks AS r ON (r.rank_id = u.user_rank AND r.rank_special = 1)
    LEFT JOIN `herbtalk_info_-_pmf`.smf_membergroups AS mg ON (BINARY mg.groupName = CONCAT('phpBB ', r.rank_title))
    WHERE u.user_id != -1
    GROUP BY u.user_id
    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 'AS emailAddress, 1, 255) AS emailAddress,
    u.user_unread_privmsg

....

Advertisement: