Advertisement:

Author Topic: PHPBB2 - problem with MySQL  (Read 104822 times)

Offline MrPrise

  • Semi-Newbie
  • *
  • Posts: 52
Re: PHPBB2 - problem with MySQL
« Reply #20 on: August 24, 2006, 01:45:16 PM »
After the convert finished, it starts to "Recalculating forum statistics..." It takes a very long time, maybe forever?

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #21 on: August 24, 2006, 04:04:33 PM »
Quote
After 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 ;)
« Last Edit: August 24, 2006, 04:15:15 PM by Ferny »
Digital Video & Audio:
www.mundodivx.com

Offline MrPrise

  • Semi-Newbie
  • *
  • Posts: 52
Re: PHPBB2 - problem with MySQL
« Reply #22 on: August 24, 2006, 04:09:02 PM »
Quote
After 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!

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #23 on: August 24, 2006, 04:12:46 PM »
Still this error  :(

Quote
Converting 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:
Code: [Select]
/******************************************************************************/
--- 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:
Code: [Select]
v.vote_id AS ID_POLL
Replace it:
Code: [Select]
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
« Last Edit: August 24, 2006, 04:21:40 PM by Ferny »
Digital Video & Audio:
www.mundodivx.com

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #24 on: August 24, 2006, 04:34:56 PM »
Another fix. While converting posts, if you see an error involving column 'modifiedTime', you should apply this fix:

Find
Code: [Select]
/******************************************************************************/
--- 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:
Code: [Select]
p.post_edit_time AS modifiedTime
Replace it:
Code: [Select]
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

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #25 on: August 24, 2006, 04:40:50 PM »
While you're typing about second bug I have found it too.

Now, it's better, but I have this problem:
Quote
Converting 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

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #26 on: August 24, 2006, 04:45:53 PM »
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.

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #27 on: August 24, 2006, 04:55:27 PM »
Post the code of that section in sql file
Digital Video & Audio:
www.mundodivx.com

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #28 on: August 24, 2006, 04:59:25 PM »
Original code was:
Code: [Select]
/******************************************************************************/
--- 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:
Code: [Select]
/******************************************************************************/
--- 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

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #29 on: August 24, 2006, 05:03:54 PM »
Code: [Select]
FROM {$from_prefix}topics_watch;
Just delete the last ; ;)

This is correct:
Code: [Select]
/******************************************************************************/
--- 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

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #30 on: August 24, 2006, 05:09:59 PM »
Thanks!

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

Code: [Select]
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. :)

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #31 on: August 24, 2006, 05:17:19 PM »
It's still imposible to log-on with Firefox ... :(
& I don't have Private messages in Inbox folder :(

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #32 on: August 24, 2006, 05:45:07 PM »
Try deleting your cookies

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

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #33 on: August 24, 2006, 06:03:29 PM »
OK, now, when I deleted cookie, I can log-on.

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

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #34 on: August 24, 2006, 06:09:00 PM »
& another bug: At membergroups is prefix phpBB

Sample:
Quote
phpBB OK-SG
must be:
Quote
OK-SG

Offline IGSkirk

  • Newbie
  • *
  • Posts: 2
  • Gender: Male
Re: PHPBB2 - problem with MySQL
« Reply #35 on: August 24, 2006, 06:27:27 PM »
Code: [Select]
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', 'someone@hotmail.com', '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?

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #36 on: August 25, 2006, 02:51:20 AM »
& another bug: At membergroups is prefix phpBB

Sample:
Quote
phpBB OK-SG
must be:
Quote
OK-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:
Code: [Select]
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:
Code: [Select]
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

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #37 on: August 25, 2006, 03:10:54 AM »
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?

Offline kitz

  • Jr. Member
  • **
  • Posts: 119
    • kitz.co.uk
Re: PHPBB2 - problem with MySQL
« Reply #38 on: August 25, 2006, 04:48:47 AM »
First of all guys, thanks for the work youve put into this so far.....

However Im still having a problem here


Quote
Converting 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?

Code: [Select]
/******************************************************************************/
--- 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;
---*

Offline pkeffect

  • Semi-Newbie
  • *
  • Posts: 21
  • Gender: Male
Re: PHPBB2 - problem with MySQL
« Reply #39 on: August 25, 2006, 02:25:12 PM »
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.

Quote
Converting 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_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

....