PHPBB2 - problem with MySQL

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

Previous topic - Next topic

rejetto

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

Kret

#1
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

Person

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 :(

H

Do you have any phpbb mods installed?
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Person

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.

Artyemy

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

Person

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 :(

Artyemy

Yes. I think too. It's only one difference. You have original PHPBB. And I have some mods installed in my PHPBB.

Ferny

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

Person

Cool. I can't wait for it ;)

Thanks for help :)

Ferny

I get the same errors as rejetto. I'll use his fixed sql file to begin my tests.
Digital Video & Audio:
www.mundodivx.com

kylratix

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.

QuoteConverting 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:
/******************************************************************************/
--- 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;
---*

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



Person

Quote from: Ferny 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.

This are differences: [133 - line number, * - original file, # - rejetto's sql file]

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)

MrPrise

Thank you for the fix. I was looking for that! ;-)
Maybe the SMF team should upgrade the script at the download section.

MrPrise

The converter script removes the first character of the signatures.

Ferny

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):
* 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:
/******************************************************************************/
--- 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:

/******************************************************************************/
--- 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

SBGamesCone

Thanks Ferny, that is a welcome modification.

Ferny

Quote from: MrPrise on August 24, 2006, 12:13:02 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
$row['avatar'] = substr('gallery/' . $row['avatar'], 1, 255);

Find the following and change the 1 to 0
$row['signature'] = substr($row['signature'], 1, 65534);


Ferny
Digital Video & Audio:
www.mundodivx.com

Person

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

:(

MrPrise

#19
Quote from: Ferny on August 24, 2006, 12:59:58 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.

Advertisement: