phpbb => smf problem with topics and duplicated entries

[scorpz]

SMF Version: SMF 1.1 RC2
Hello, i am trying to convert from phpbb 2.0.18 to SMF 1.1 RC2 and i get this error.

I tried to use the search function on forum because i saw thread where somebody said if you get this error use search inhere because many had this problem, but search was disabled.

And yes i downloaded new convert.php and phpbb_to_smf.sql files that were announced in forums for RC2 converters.

Can somebody please tell me what to do because i dont have a clue?

Code Select Expand

Converting topics... Unsuccessful!
Caused the error:

    Duplicate entry '9485-11' for key 3



And i get 1 more error, but i suspect this really isnt a problem because it is connected to the avatars:

Code Select Expand


Converting members...
Warning: copy(/home/content/g/r/o/grobin/html/forum/images/avatars/1917136911439d88b790816.jpg): failed to open stream: No such file or directory in /home/content/g/r/o/grobin/html/convert.php(1627) : eval()'d code on line 35

Warning: copy(/home/content/g/r/o/grobin/html/forum/images/avatars/4420423844141330c23eb.jpg): failed to open stream: No such file or directory in /home/content/g/r/o/grobin/html/convert.php(1627) : eval()'d code on line 35
Successful.


Thank you for your help.

[Oldiesmann]

Delete any duplicate entries from your phpBB topics and messages tables. I think those errors are related to topics that have been moved.

The other one is an avatar-related issue. Basically it means that SMF couldn't copy the user's avatars over because the specified files didn't exist.

[JayBachatero]

Did you use the convert.php in this topic?
http://www.simplemachines.org/community/index.php?topic=65677.0

[etdwh]

Hi all

Im also facing this issue.
With the debug turned on

Code Select Expand

INSERT INTO `[mydbname]`.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... and the long list of values...until the end, there is an error:

Code Select Expand

Caused the error:

    Duplicate entry '19056-15' for key 3

Jay,

I did use the convert.php from http://www.simplemachines.org/community/index.php?topic=65677.0

Oldiesmann might be right, as there are several moved topics. How do i check?

Thanks
-Eugene

[PaulW21781]

This is actually quite a big annoyance for me if I have to manually go round & remove all shadow topics from my database...

Code Select Expand

Converting topic notifications... Unsuccessful!
This query:
INSERT INTO `corsab_dev`.smf_log_notify

(ID_MEMBER, ID_TOPIC)

VALUES ('395', '3578'),

('662', '3583'),

('226', '3560'),

('919', '3588'),

('919', '3500'),

('919', '2991'),

('919', '3590'),

('54', '2342'),

('54', '3590'),

('908', '3591'),

('793', '3592'),

('900', '3593'),

('513', '3563'),

('345', '3598'),

('919', '3600'),

('583', '3588'),

('583', '3590'),

('226', '3609'),

('915', '3603'),

('583', '3597'),

('226', '3603'),

('921', '3835'),

('919', '3613'),

('226', '3612'),

('226', '3615'),

('921', '3618'),

('395', '3619'),

('226', '3796'),

('189', '3622'),

('751', '3612'),

('155', '3616'),

('226', '3619'),

('670', '4082'),

('712', '3633'),

('919', '3485'),

('226', '3633'),

('716', '3601'),

('155', '3601'),

('155', '3635'),

('714', '3641'),

('395', '3640'),

('919', '3649'),

('226', '3621'),

('226', '3640'),

('832', '3645'),

('919', '3650'),

('583', '3641'),

('919', '3652'),

('222', '3661'),

('41', '3662'),

('495', '3663'),

('810', '3666'),

('54', '3653'),

('908', '3669'),

('226', '3669'),

('695', '3673'),

('155', '3673'),

('155', '3668'),

('110', '3679'),

('345', '3686'),

('908', '3687'),

('714', '3691'),

('189', '3692'),

('891', '3693'),

('495', '3695'),

('954', '3698'),

('192', '3699'),

('527', '3836'),

('732', '3700'),

('921', '3701'),

('915', '3666'),

('406', '3704'),

('888', '3705'),

('54', '3686'),

('155', '3681'),

('492', '3711'),

('492', '3252'),

('492', '3024'),

('492', '2930'),

('958', '3714'),

('583', '3714'),

('958', '926'),

('226', '3714'),

('226', '3713'),

('919', '3719'),

('919', '3720'),

('226', '3721'),

('751', '3718'),

('919', '3723'),

('919', '3724'),

('155', '3718'),

('226', '3725'),

('166', '3728'),

('492', '3729'),

('751', '3592'),

('166', '3425'),

('166', '2466'),

('962', '3730'),

('166', '3714'),

('166', '3618'),

('953', '3731'),

('166', '3731'),

('166', '3732'),

('965', '3494'),

('966', '3738'),

('362', '3954'),

('953', '3714'),

('226', '3704'),

('226', '3523'),

('226', '3742'),

('395', '3743'),

('662', '3749'),

('737', '3750'),

('226', '3750'),

('395', '3742'),

('395', '3751'),

('41', '3752'),

('41', '3753'),

('395', '3753'),

('974', '3755'),

('959', '3763'),

('874', '3764'),

('891', '1601'),

('934', '1758'),

('226', '3754'),

('820', '3611'),

('395', '3754'),

('979', '3768'),

('41', '3770'),

('155', '3611'),

('155', '3753'),

('971', '3773'),

('583', '3774'),

('962', '3775'),

('226', '3772'),

('793', '3742'),

('395', '3772'),

('958', '3773'),

('891', '3714'),

('583', '3778'),

('874', '3773'),

('423', '3782'),

('41', '3780'),

('583', '3784'),

('974', '2973'),

('583', '3785'),

('966', '4147'),

('662', '3787'),

('155', '3786'),

('395', '3786'),

('226', '3786'),

('551', '3786'),

('551', '3787'),

('551', '2466'),

('551', '3749'),

('551', '3732'),

('891', '3794'),

('395', '3803'),

('919', '3938'),

('981', '3761'),

('981', '3754'),

('395', '3807'),

('359', '3811'),

('993', '3812'),

('891', '3804'),

('804', '3814'),

('226', '3795'),

('994', '3820'),

('994', '3818'),

('345', '3822'),

('793', '3823'),

('226', '3823'),

('226', '3696'),

('364', '3825'),

('670', '3790'),

('359', '3836'),

('155', '3771'),

('155', '3790'),

('155', '3751'),

('155', '3806'),

('155', '3830'),

('155', '3821'),

('981', '3840'),

('981', '3842'),

('226', '3836'),

('155', '3824'),

('155', '3846'),

('981', '3818'),

('981', '3772'),

('54', '3828'),

('919', '3848'),

('919', '3849'),

('166', '3917'),

('583', '3866'),

('891', '3850'),

('891', '3736'),

('1007', '3870'),

('226', '3818'),

('226', '3871'),

('54', '3860'),

('662', '3872'),

('226', '3860'),

('583', '3478'),

('551', '3876'),

('155', '3857'),

('919', '3757'),

('714', '3877'),

('793', '3878'),

('226', '3881'),

('226', '3880'),

('810', '3885'),

('958', '3886'),

('919', '3888'),

('751', '3889'),

('908', '3920'),

('395', '3889'),

('1019', '3898'),

('793', '3889'),

('395', '3901'),

('793', '1458'),

('793', '3908'),

('1024', '3912'),

('921', '3913'),

('166', '3886'),

('226', '3674'),

('527', '3772'),

('927', '3931'),

('583', '3857'),

('919', '3936'),

('583', '3915'),

('583', '3937'),

('1024', '3941'),

('925', '3942'),

('411', '3845'),

('411', '3937'),

('495', '3912'),

('751', '3915'),

('1041', '3782'),

('155', '3921'),

('395', '3935'),

('345', '4544'),

('793', '3977'),

('856', '3977'),

('1041', '3687'),

('583', '3962'),

('155', '3987'),

('411', '3963'),

('411', '2958'),

('751', '4003'),

('345', '3995'),

('751', '3976'),

('1019', '4022'),

('192', '4023'),

('1019', '3862'),

('793', '4019'),

('793', '4024'),

('793', '3953'),

('793', '4026'),

('793', '4026'),

('793', '4015'),

('166', '4043'),

('810', '3974'),

('921', '4036'),

('981', '3898'),

('166', '4025'),

('166', '3929'),

('166', '4031'),

('395', '4073'),

('1074', '4046'),

('1074', '4048'),

('874', '4057'),

('751', '4060'),

('1019', '2958'),

('908', '4068'),

('908', '4069'),

('921', '4081'),

('948', '3674'),

('1100', '4104'),

('395', '4114'),

('362', '4364'),

('1019', '4116'),

('77', '4124'),

('226', '4126'),

('733', '4131'),

('810', '4137'),

('362', '4142'),

('500', '4206'),

('395', '4198'),

('1135', '4183'),

('971', '4155'),

('6', '4066'),

('925', '4125'),

('226', '4160'),

('362', '4140'),

('362', '4110'),

('362', '3915'),

('759', '4164'),

('551', '4167'),

('810', '4308'),

('1135', '4172'),

('1133', '4147'),

('1133', '4174'),

('1133', '4171'),

('395', '4165'),

('1139', '4181'),

('759', '4183'),

('395', '4182'),

('166', '3915'),

('974', '4189'),

('1135', '4190'),

('690', '4197'),

('1135', '4199'),

('1135', '3641'),

('690', '4204'),

('1149', '4202'),

('974', '4194'),

('888', '4210'),

('54', '4187'),

('54', '5422'),

('191', '4206'),

('191', '3641'),

('751', '4217'),

('810', '4238'),

('392', '3559'),

('925', '4165'),

('362', '4217'),

('1133', '4192'),

('923', '4230'),

('226', '4233'),

('1160', '4234'),

('810', '4237'),

('1172', '4127'),

('395', '4239'),

('395', '2717'),

('751', '4146'),

('1163', '4241'),

('925', '4146'),

('1135', '4248'),

('155', '4248'),

('1118', '4252'),

('872', '4327'),

('925', '4254'),

('395', '4257'),

('1019', '4251'),

('751', '4257'),

('395', '4267'),

('395', '4263'),

('1135', '4265'),

('927', '4266'),

('191', '4261'),

('155', '4272'),

('583', '4137'),

('362', '4280'),

('191', '4284'),

('926', '4287'),

('926', '4288'),

('1183', '4290'),

('315', '4306'),

('73', '4310'),

('395', '4311'),

('751', '4289'),

('1191', '4318'),

('1028', '4321'),

('1175', '4330'),

('395', '4322'),

('820', '4344'),

('362', '4346'),

('191', '4305'),

('91', '4875'),

('395', '4348'),

('166', '4280'),

('166', '4337'),

('166', '4350'),

('395', '4356'),

('362', '4357'),

('362', '4358'),

('1019', '4360'),

('1135', '4361'),

('921', '4362'),

('1172', '4268'),

('1172', '3333'),

('1172', '4365'),

('1201', '4368'),

('1172', '4372'),

('54', '4381'),

('551', '4385'),

('191', '4467'),

('54', '4390'),

('1213', '4392'),

('226', '4395'),

('360', '4396'),

('670', '4400'),

('874', '4401'),

('793', '4416'),

('921', '4422'),

('551', '4419'),

('551', '4388'),

('505', '4379'),

('226', '4253'),

('1019', '4463'),

('54', '4419'),

('583', '4394'),

('166', '4214'),

('166', '4443'),

('166', '4101'),

('166', '3556'),

('166', '3953'),

('166', '3804'),

('166', '3761'),

('166', '2611'),

('166', '4436'),

('395', '4384'),

('191', '4394'),

('1234', '4448'),

('166', '4426'),

('166', '4448'),

('1234', '4411'),

('395', '4456'),

('395', '4454'),

('716', '4458'),

('921', '4460'),

('921', '4424'),

('915', '4462'),

('395', '4434'),

('191', '4458'),

('926', '4465'),

('226', '4473'),

('1252', '4487'),

('395', '4713'),

('773', '4489'),

('1019', '4476'),

('716', '4495'),

('810', '4500'),

('919', '4506'),

('166', '4423'),

('395', '4514'),

('874', '4343'),

('1268', '4518'),

('919', '4515'),

('1268', '3978'),

('191', '4518'),

('835', '4524'),

('915', '4527'),

('1277', '4534'),

('395', '3967'),

('1276', '4535'),

('1274', '4545'),

('1274', '4546'),

('670', '4436'),

('583', '4460'),

('583', '3967'),

('583', '4552'),

('583', '4214'),

('583', '4282'),

('583', '1646'),

('896', '4554'),

('1019', '4573'),

('359', '1646'),

('1291', '4575'),

('395', '4575'),

('925', '4587'),

('1263', '4588'),

('166', '1646'),

('166', '4572'),

('166', '4576'),

('166', '4598'),

('793', '4599'),

('1299', '4526'),

('921', '4208'),

('41', '4607'),

('41', '4608'),

('192', '4611'),

('1019', '4553'),

('583', '4612'),

('722', '4981'),

('226', '4616'),

('971', '4617'),

('395', '4619'),

('751', '4620'),

('925', '4626'),

('226', '4574'),

('874', '4634'),

('733', '4640'),

('222', '4641'),

('192', '4632'),

('192', '4591'),

('166', '4662'),

('395', '4663'),

('859', '4423'),

('859', '4669'),

('1019', '4646'),

('166', '4670'),

('166', '4665'),

('583', '4637'),

('583', '4673'),

('583', '4646'),

('583', '4670'),

('583', '4647'),

('583', '4680'),

('583', '4663');
Caused the error:

Duplicate entry '793-4026-0' for key 1

As you can see... there are quite a few 

[JayBachatero]

etdwh go to your database and check in the topics table for duplicate entries.  (the phpBB table not SMF).

Paul that is not really needed as is just keeps tracks of the topics people have marked for notificatons.

If you want send me a dump of the database to [email protected] and I will set up a test phpbb and try the conversion out.

[PaulW21781]

Thanks for the offer JayBachatero, but I managed to sort it out in the end...

Easiest way to find the duplicates was by running this SQL Command

Code Select Expand

select topic_id, user_id, count(*) as n
from phpbb_topics_watch
group by topic_id, user_id
having  n > 1;

This will list all the duplicates from phpMyAdmin, then its a case of searching & deleting each duplicate entry so only 1 remains for each, re-run the query to ensure there all gone & then run the conversion script from the start again (clicking Try Again just throws up more)

Imported the databse fine now

[JayBachatero]

Glad to hear you got it working Paul.  I thought it was a converter problem.

[PaulW21781]

nope the converter ran perfectly, it threw up the usual errors about the avatar locations, but other than that, it worked fine!

im purely testing SMF as a full replacement for phpBB on my site, which is due a re-launch when we atain full club status...

[JayBachatero]

Oh ok good.  I will take a look at this later on in the week to see if I see anything in relation to the avatars.

[etdwh]

I think i have solved the problem, partially 

It was caused by the moved topics. I noticed inside the phpbb2_to_smf.sql, there is a WHERE clause commented out

Code Select Expand

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

Notice the
/* // !!! WHERE t.topic_moved_id = 0 */

I just removed the comments, and was able to convert the topics !!
Just wondering, any reason the line was commented out ?

However, i failed when i tried to convert posts. Maybe i should empty all tables and try again.

Code Select Expand

INSERT INTO `[mydbname]`.smf_messages

(ID_MSG, ID_TOPIC, posterTime, ID_MEMBER, posterEmail, subject, posterName, smileysEnabled, modifiedTime, posterIP, body, ID_BOARD)
lists several values

Code Select Expand

Caused the error:

    Duplicate entry '115464' for key 1

Simple select shows that there is only 1 post with post_id = 115464. Where else should i look? Did adding the where clause in the topic conversion cause the problem ?

[JayBachatero]

Ummm not sure as to why it's commented out.  If you want you can send me a databse dump and I'll take a look at it.

[etdwh]

After more trying, by clearing out all the smf tables, and then converting again..more success 

But i do have the problem of duplicate topic notifications as Paul. Either i fix the data as Paul suggested, or just modify the phpbb2_to_smf.sql to ignore topic watches. Maybe mod it to ignore duplicates..

Erm, on another note, DO NOT clear all data from all SMF tables...just some tables will do. If not, you will end up "themeless" like me, i guess coz i deleted all settings and theme info. Oh well, at least the conversion process is getting better and almost perfect. I will try the conversion again with a fresh install

[JayBachatero]

etdwh can you create the database dump using create_backup.php.  The one you sent me is not working.
http://www.simplemachines.org/community/index.php?topic=18350.msg168327#msg168327

[Borat]

Thanks for the offer JayBachatero, but I managed to sort it out in the end...

Easiest way to find the duplicates was by running this SQL Command

Code Select Expand

select topic_id, user_id, count(*) as n
from phpbb_topics_watch
group by topic_id, user_id
having  n > 1;

This will list all the duplicates from phpMyAdmin, then its a case of searching & deleting each duplicate entry so only 1 remains for each, re-run the query to ensure there all gone & then run the conversion script from the start again (clicking Try Again just throws up more)

Imported the databse fine now

Could you explain how I would go about running that command? I am not really a phpBB / SMF guru. Just a newbie trying to learn. I have a similar error but can't find any duplicate entries by manually examining the the topic IDs.

[JayBachatero]

You run that query in phpMyAdmin.
What is phpMyAdmin?

[NicolaUK]

Thanks for the offer JayBachatero, but I managed to sort it out in the end...

Easiest way to find the duplicates was by running this SQL Command

Code Select Expand

select topic_id, user_id, count(*) as n
from phpbb_topics_watch
group by topic_id, user_id
having  n > 1;

This will list all the duplicates from phpMyAdmin, then its a case of searching & deleting each duplicate entry so only 1 remains for each, re-run the query to ensure there all gone & then run the conversion script from the start again (clicking Try Again just throws up more)

Imported the databse fine now

I'm also getting the duplicate entry error whilst converting - when I run the query I get

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0174 sec)SQL query:SELECT topic_id, user_id, count( * ) AS n
FROM phpbb_topics_watch
GROUP BY topic_id, user_id
HAVING n >1
LIMIT 0 , 30;

Can anyone advise please?

Thanks!

[NicolaUK]

And to add, even if I delete the so called duplicate entry, when I try again it says the next entry is a dupe and so on and so on

[JayBachatero]

Yea it is very annoying having to delete this duplicate queries.  I had to convert a board and had to do it manually.

[NicolaUK]

Just to add I've managed to get it to work by installing SMF 1.05, converting (worked like a dream) then upgrading to 1.1

Just have some minor issues now - bottoms of posts not being shown, PMs not always displaying - will have a search around for any similar reports