Advertisement:

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

Offline rejetto

  • Semi-Newbie
  • *
  • Posts: 25
PHPBB2 - problem with MySQL
« on: August 22, 2006, 08:29:55 PM »
SMF Version: SMF 1.1 RC3
was importing my phpbb2 with MySQL 4.1.20 and encountered in some SQL syntax errors.
the errors are in the form SUBSTRING(a AS b,c,d) AS b
to fix the problem i had to remove the first "AS b"
you can get the fixed sql file here: http://www.sendspace.com/file/q0ly0r

Offline Kret

  • Semi-Newbie
  • *
  • Posts: 42
    • Sabaki Games & Internet Services
Re: PHPBB2 - problem with MySQL
« Reply #1 on: August 23, 2006, 03:52:08 AM »
nice fix rejetto
php-nuke have same problem, u dont mind to fix that one as well  ;D
ppl have problems: http://www.simplemachines.org/community/index.php?topic=35853.msg693352#new
« Last Edit: August 23, 2006, 03:55:40 AM by Kret »

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #2 on: August 23, 2006, 06:20:00 AM »
Converting...
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'),

...

Caused the error:

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



It's problem, that I can't insert "" as integer :(

Offline H

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 21,660
  • Gender: Male
Re: PHPBB2 - problem with MySQL
« Reply #3 on: August 23, 2006, 12:53:40 PM »
Do you have any phpbb mods installed?
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #4 on: August 23, 2006, 01:34:26 PM »
No, just original version phpBB 2.0.21. & I have used fixed version of converter which is posted at top of post.
This fixed version has minor changes in SQL code.

Offline Artyemy

  • Newbie
  • *
  • Posts: 4
Re: PHPBB2 - problem with MySQL
« Reply #5 on: August 23, 2006, 03:01:50 PM »
Hello.
Sorry for my english. I am russian.
I have problem with MySQL script too. Here is what gives me while runing this script:

Converting ranks... Successful.
Converting groups... Successful.
Converting members... Successful.
Converting additional member groups... Successful.
Converting categories... Successful.
Converting boards... Successful.
Converting topics... Unsuccessful!
This query:
INSERT INTO `forum`.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', '12', '12', '2', '2', '5', '', '1', '0', '56'),
('4', '0', '13', '686', '4', '15', '15', '', '0', '35', '355'),
('5', '0', '14', '515', '6', '15', '15', '', '0', '21', '203'),
('6', '0', '19', '24', '8', '3', '15', '', '0', '3', '71'),
('7', '0', '26', '583', '4', '3', '14', '', '0', '5', '77'),
('8', '0', '35', '35', '2', '2', '16', '', '1', '0', '138'),
('10', '0', '54', '561', '2', '11', '16', '', '0', '87', '658'),
('11', '0', '56', '56', '3', '3', '14', '', '1', '0', '37'),
('14', '0', '63', '189', '2', '3', '17', '', '0', '32', '105'),
('15', '0', '76', '254', '20', '3', '15', '', '0', '7', '85'),
('16', '0', '85', '90', '3', '2', '18', '', '0', '3', '49'),
('17', '0', '123', '140', '2', '11', '17', '', '0', '5', '16'),
('18', '0', '166', '693', '15', '23', '13', '', '0', '37', '226'),
('19', '0', '167', '172', '15', '11', '14', '', '0', '1', '20'),
('20', '0', '178', '240', '15', '15', '11', '', '0', '10', '57'),
('21', '0', '179', '292', '15', '15', '8', '', '0', '13', '79'),
('22', '0', '216', '216', '2', '2', '17', '', '0', '0', '5'),
('24', '0', '227', '350', '3', '11', '16', '1', '0', '36', '216'),
('25', '0', '234', '253', '3', '11', '17', '', '0', '1', '8'),
('26', '0', '274', '352', '15', '11', '9', '', '0', '13', '63'),
('27', '0', '276', '652', '26', '23', '12', '', '0', '25', '159'),
('28', '0', '286', '567', '15', '15', '7', '', '0', '46', '237'),
('29', '0', '340', '610', '15', '23', '16', '', '0', '42', '291'),
('30', '0', '347', '462', '11', '11', '11', '', '0', '13', '78'),
('31', '0', '374', '408', '11', '2', '17', '', '0', '1', '10'),
('34', '0', '410', '492', '2', '15', '8', '', '0', '8', '40'),
('35', '0', '419', '699', '11', '2', '6', '', '0', '14', '63'),
('37', '0', '452', '605', '3', '11', '17', '', '0', '6', '23'),
('38', '0', '469', '697', '11', '2', '11', '', '0', '13', '66'),
('39', '0', '474', '659', '26', '26', '11', '', '0', '8', '59'),
('41', '0', '498', '600', '3', '3', '10', '', '0', '22', '114'),
('42', '0', '509', '579', '26', '26', '10', '', '0', '8', '44'),
('43', '0', '512', '638', '2', '3', '16', '', '0', '15', '107'),
('44', '0', '523', '545', '2', '3', '17', '', '0', '1', '8'),
('45', '0', '537', '546', '2', '3', '17', '', '0', '2', '7'),
('46', '0', '584', '648', '3', '2', '17', '', '0', '8', '22'),
('47', '0', '599', '603', '2', '11', '17', '', '0', '1', '6'),
('48', '0', '616', '645', '23', '2', '16', '', '0', '15', '104'),
('49', '0', '640', '649', '3', '2', '17', '', '0', '1', '7'),
('50', '0', '667', '671', '3', '2', '17', '', '0', '1', '3'),
('51', '0', '670', '683', '2', '11', '13', '', '0', '6', '24'),
('52', '0', '675', '685', '11', '15', '15', '', '0', '3', '23'),
('53', '0', '695', '696', '23', '3', '13', '', '0', '1', '6'),
('54', '0', '698', '698', '2', '2', '5', '', '0', '0', '1');
Caused the error:
Incorrect integer value: '' for column 'ID_POLL' at row 1

SMF version 1.1RC3
PHPBB version 2.0.21
MySQL version 5.0

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #6 on: August 23, 2006, 03:17:28 PM »
I think that you have the same problem.

When this problem will be resolved, I would like to know how can I mod it to set source charset for phpBB2 data, because I have problem with importing backup SQL script ... some Slovenian special chars won't work :(

Offline Artyemy

  • Newbie
  • *
  • Posts: 4
Re: PHPBB2 - problem with MySQL
« Reply #7 on: August 23, 2006, 03:33:03 PM »
Yes. I think too. It's only one difference. You have original PHPBB. And I have some mods installed in my PHPBB.

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #8 on: August 23, 2006, 05:40:48 PM »
Hi 8)

I wanted to move my old phpbb to SMF and during some tests I had some problems like this during phpbb 2.0.21 to SMF 1.1-RC2 conversion, so I made some modifications to the .sql file to fix them. Also I added the "converting smilies" function.

Now it works fine on MySQL 5.0.24 and SMF 1.1-RC2, but I've not tested it with the new 1.1-RC3 version. I have seen that there's a new version of phpbb converter (updated for 1.1-RC3), but it fails during conversion, so I can do the same changes to the new converter and post it. Give me a couple of days (maybe one) and I'll post the new .sql file and comment the changes I've made so that the responsible ones are informed.

Ferny
Digital Video & Audio:
www.mundodivx.com

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #9 on: August 23, 2006, 05:49:27 PM »
Cool. I can't wait for it ;)

Thanks for help :)

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #10 on: August 23, 2006, 06:12:56 PM »
I get the same errors as rejetto. I'll use his fixed sql file to begin my tests.
Digital Video & Audio:
www.mundodivx.com

Offline kylratix

  • Newbie
  • *
  • Posts: 5
Re: PHPBB2 - problem with MySQL - another fix
« Reply #11 on: August 23, 2006, 07:32:12 PM »
This one is running phpBB 2.0.19 and it has a bunch of duplicate entries in the phpbb_topics_watch table. UNIQUE for the win. Modifying rejetto's sql file in the first post with a GROUP BY statement will solve this. If you run into before updating, you'll have to truncate the corresponding smf table first to remove problem keys.

Quote
Converting topic notifications... Unsuccessful!
This query:

    INSERT INTO `smf`.smf_log_notify
    (ID_MEMBER, ID_TOPIC)
    VALUES ('3', '2'),
    ('2', '3'),
    ('3', '4'),
    ('10', '10'),
[ed. ....]
    ('12', '360'),
    ('16', '360'),
    ('12', '361');

Caused the error:

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

New 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;
---*

/******************************************************************************/


Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #12 on: August 24, 2006, 02:46:09 AM »
I get the same errors as rejetto. I'll use his fixed sql file to begin my tests.

This are differences: [133 - line number, * - original file, # - rejetto's sql file]
Code: [Select]
133  
* SUBSTRING(u.user_email AS emailAddress, 1, 255) AS emailAddress, 
# SUBSTRING(u.user_email, 1, 255) AS emailAddress,
135  
* SUBSTRING(u.user_msnm AS MSN, 1, 255) AS MSN, 
# SUBSTRING(u.user_msnm, 1, 255) AS MSN,
136  
* SUBSTRING(u.user_aim AS AIM, 1, 16) AS AIM, 
# SUBSTRING(u.user_aim, 1, 16) AS AIM,
367  
* SUBSTRING(uf.username AS fromName, 1, 255) AS fromName, 
# SUBSTRING(uf.username, 1, 255) AS fromName,

+ rejetto's sql file has one blank line at end (nothing special)

Offline MrPrise

  • Semi-Newbie
  • *
  • Posts: 52
Re: PHPBB2 - problem with MySQL
« Reply #13 on: August 24, 2006, 08:26:51 AM »
Thank you for the fix. I was looking for that! ;-)
Maybe the SMF team should upgrade the script at the download section.

Offline MrPrise

  • Semi-Newbie
  • *
  • Posts: 52
Re: PHPBB2 - problem with MySQL
« Reply #14 on: August 24, 2006, 12:13:02 PM »
The converter script removes the first character of the signatures.

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #15 on: August 24, 2006, 12:42:45 PM »
I downloaded phpBB2 to SMF 1.1-RC3 converter from http://www.simplemachines.org/download/?converters It contains two files, convert.php and phpbb2_to_smf.sql



File convert.php is not correct, it has some bugs. I've used the file which is attached at first post here, which has this bugs solved:
http://www.simplemachines.org/community/index.php?topic=65677.0 It works fine, but I noticed that there're a lot of empty lines (all even lines are empty), so I deleted them and I've attached the corrected file here: http://ultrashare.net/hosting/dl/be9a33aad3

Also I updated the following line at the beginning (changed RC2 to RC3):
Code: [Select]
* Software Version:           SMF 1.1 RC3                                     *
I think the converter should be updated with this new file.



File phpbb2_to_smf.sql works fine if we apply rejetto's modifications. I've not applied kylratix's modification, but It can be added. This sql file has solved the errors I've experimented on RC2 converter, so only it's necessary to apply this modifications.

But if somebody likes to import smilies, should do the following change in sql file:

After:
Code: [Select]
/******************************************************************************/
--- Converting groups...
/******************************************************************************/

---* {$to_prefix}membergroups
SELECT
SUBSTRING(CONCAT('phpBB ', group_name), 1, 255) AS groupName,
-1 AS minPosts, '' AS stars, '' AS onlineColor
FROM {$from_prefix}groups
WHERE group_single_user = 0;
---*

Add:
Code: [Select]
/******************************************************************************/
--- Converting smileys...
/******************************************************************************/

---# Copying over smilies directory...
---{
function copy_smilies($source, $dest)
{
if (!is_dir($source) || !($dir = opendir($source)))
return;

while ($file = readdir($dir))
{
if ($file == '.' || $file == '..')
continue;

// If we have a directory create it on the destination and copy contents into it!
if (is_dir($source . '/' . $file))
{
@mkdir($dest . '/' . $file, 0777);
copy_dir($source . '/' . $file, $dest . '/' . $file);
}
else
copy($source . '/' . $file, $dest . '/' . $file);
}
closedir($dir);
}

// Find the path for phpBB smileys.
$request = convert_query("
SELECT config_value
FROM {$from_prefix}config
WHERE config_name = 'smilies_path'
LIMIT 1");
list ($phpbb_smilies_path) = mysql_fetch_row($request);
mysql_free_result($request);

// Find the path for SMF smileys.
$request = convert_query("
SELECT value
FROM {$to_prefix}settings
WHERE variable = 'smileys_dir'
LIMIT 1");
list ($smf_smileys_dir) = mysql_fetch_row($request);
mysql_free_result($request);

$phpbb_smilies_path = $_POST['path_from'] . '/' . $phpbb_smilies_path;
copy_smilies($phpbb_smilies_path, $smf_smileys_dir . "/default");
---}
---#

---* {$to_prefix}smileys
SELECT code AS code, smile_url AS filename, emoticon AS description
FROM {$from_prefix}smilies;
---*

It adds all phpbb smilies to smf default set


Ferny
Digital Video & Audio:
www.mundodivx.com

Offline SBGamesCone

  • Semi-Newbie
  • *
  • Posts: 16
  • Gender: Male
    • Snackbar Games
Re: PHPBB2 - problem with MySQL
« Reply #16 on: August 24, 2006, 12:54:38 PM »
Thanks Ferny, that is a welcome modification.

Offline Ferny

  • Semi-Newbie
  • *
  • Posts: 40
  • Gender: Male
    • MundoDivX
Re: PHPBB2 - problem with MySQL
« Reply #17 on: August 24, 2006, 12:59:58 PM »
The converter script removes the first character of the signatures.

Not only there but also in the avatar field of members table, making avatars not to work (only if avatars are type 3, those which are in phpBB's gallery)

The following changes in sql file should fix it:

Find the following and change the 1 to 0
Code: [Select]
$row['avatar'] = substr('gallery/' . $row['avatar'], 1, 255);
Find the following and change the 1 to 0
Code: [Select]
$row['signature'] = substr($row['signature'], 1, 65534);

Ferny
Digital Video & Audio:
www.mundodivx.com

Offline Person

  • Semi-Newbie
  • *
  • Posts: 34
Re: PHPBB2 - problem with MySQL
« Reply #18 on: August 24, 2006, 01:26:08 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

 :(

Offline MrPrise

  • Semi-Newbie
  • *
  • Posts: 52
Re: PHPBB2 - problem with MySQL
« Reply #19 on: August 24, 2006, 01:40:31 PM »
Not only there but also in the avatar field of members table, making avatars not to work (only if avatars are type 3, those which are in phpBB's gallery)

The following changes in sql file should fix it
Thanks for the fix. I have a new problem. The convert doesn't try to convert birthdays.
I have PHPBB2 Plus and that stored the date as int( 8 ), SMF stored as date. Basically the fix is the date() PHP func, but I dont see where to put that, yet.

EDIT: the PHPBB2 without plus doesn't have birthday field, so this is not bug.
« Last Edit: August 24, 2006, 01:56:49 PM by MrPrise »