phpBB to SMF - The database value you're trying to insert does not exist: modifi

[stephanhughson]

Hi,

Thanks for the converter tool.

I'm stuck on this page:

site.../convert.php?step=1&substep=19&start=15600
Converting posts...
The database value you're trying to insert does not exist: modified_name

Does anyone know what I can try please?

This is going from phpBB 3.0.12 to SMF 2.0.8.

The forum actually looks quite populated with posts, it just doesn't quite complete.

Help would be very very appreciated.

Thanks for your help.

[stephanhughson]

Sorry to bump this up... I'd really like to move to SMF but am stuck with this existing phpBB forum which I need to migrate.

Can you suggest anything that I could try please? I'm happy to get stuck in and give it a go!

[margarett]

Im sorry, I missed this.

Can you tell me which converter are you using?

[stephanhughson]

Ah, good point. It's the Phpbb 3 to SMF 2-0 coverter from http://download.simplemachines.org/?converters;software=phpbb

I'm coming from a newly upgraded phpBB, version 3.0.12.

I wonder if the phpBB database is a bit damaged somehow... Is there a way to run the converter in debug mode? Thanks for your help.

[margarett]

Can you try this improved converter from dougiefresh?
http://www.simplemachines.org/community/index.php?topic=508551.0

I don't think it handles your issue (it's unclear why it happens, apparently you have a post with "messed" edit_user) but it's worth to try

[stephanhughson]

Thanks for the support. I think I'm making progress.

The downloadable SQL file from dougiefresh seemed to be for a slightly different version of SMF, so I merged in the changes manually and am making progress.

It's getting stuck on users now I think, most have been imported but it gave me:

Converting...
Converting ranks... Successful.
Converting groups... Successful.
Converting members...Duplicate entry '13143' for key 'PRIMARY'

I've had a look through the database but can't find anything much about "13143". I'm wondering if it's a user with invalid, or duplicated details.

Do you have any thoughts on that? :-S. Your help is really appreciated!

[margarett]

Hummm no, it is supposed to be for SMF2 the same way...

That error means that in your phpBB table there should be duplicate user ID 13143. You can use the "ignore" trick, but try to check first that table and check if the duplicate is confirmed

[Hudzon]

I have having very similar issues, dougiefresh's modified file calls for the old style of naming AS groupName where version we have now needs AS group_name this is for all the AS fields

I am having an issue now with the ban list part looking for an email field that it can't find no mater how I name it.

[stephanhughson]

Hi again,

I've gone back to the original SQL script without any modifications, to keep things simple. Back to the original error now :-)

So for this bit, I'd be really very grateful for help and can probably supply a few more details...


/convert.php?step=1&substep=19&start=15600
Converting posts...
The database value you're trying to insert does not exist: modified_name


The bit of the code which is related to modified_name is...


SELECT
        p.post [nofollow]_id AS id_msg, p.topic_id AS id_topic, p.forum [nofollow]_id AS id_board,
        p.post [nofollow]_time AS poster_time, p.poster_id AS id_member, p.post [nofollow]_subject AS subject,
        IFNULL(m.username, 'Guest') AS poster_name,
        IFNULL(m.user_email, 'Unknown') AS poster_email,
        IFNULL(p.poster_ip, '0.0.0.0') AS poster_ip,
        p.enable_smilies AS smileys_enabled, p.post [nofollow]_edit_time AS modified_time,
        CASE p.post [nofollow]_edit_user WHEN 0 THEN 'Guest' ELSE m2.username END AS modified_name,
        p.post [nofollow]_text AS body
FROM {$from_prefix}posts AS p
        LEFT JOIN {$from_prefix}users AS m ON (m.user_id = p.poster_id)
        LEFT JOIN {$from_prefix}users AS m2 ON (m2.user_id = p.post [nofollow]_edit_user);
---*



Is there anything I can change on the "END AS modified_name" bit to handle a failure and let it continue?

Thanks again.

[stephanhughson]

Actually, I have an idea...

I'd like to switch the forum I'm helping with to SMF but the converter keeps getting stuck and actually there are a few different errors now. It probably can't be easily fixed on here, I need to work with someone in real-time, or semi-real-time. I've noticed I can make it skip over by editing the URL at the top and increasing the number a little, so it skips over (not right, but just to test).

Maybe we can take the script, add in the improvements from dougiefresh, add in a few more fixes for the issues I've seen, then contribute it back to the project?

I'd rather stick with SMF as I've used it before, I don't want to go commercial as although they can convert me (at a cost), I'm then stuck with them... This is for a friend's website so ongoing fees may be an issue.

I see there is a "charter member" option at http://www.simplemachines.org/charter/ but am not sure if it would be covered. I don't mind contributing to the project a little, I've been using SMF since it came out. What's the best way to proceed?

Thanks :-)

[Illori]

being patient or hiring your own developer to help seems to be the only options.

charter membership does not cover converting as the team does not have many members that know how the process works and those that do seem to be pretty busy.

[stephanhughson]

Thanks. Ok, I'm keen to get something going. Preferably by someone in the SMF team. If you're reading this and can help, please get in touch with me (PM).
Unfortunately I don't have heaps of money going spare :-) but if I can help the project a little then we all win.

Hudzon, are you also still stuck? Do you want to join in? We can split the cost and get our forums converted.

[margarett]

I have converted several phpBB forums with minor issues, it's really strange you got that many problems.

That being said, converters are really tricky I'm always amazed by the amount of variations of what's supposed to be the same database schema. And usually, SMF is really not to blame (although it's our converter that fails )

So:

The downloadable SQL file from dougiefresh seemed to be for a slightly different version of SMF,

It shouldn't be, really. It's for 2.0.x although I've never used it... I'll nudge him so that he can check

The database value you're trying to insert does not exist: modified_name

This certainly relates to a non-default value in phpBB's tables or a condition that wasn't really covered when the converter was developed. I would have to find the matching post and see what's its content...

Converting members...Duplicate entry '13143' for key 'PRIMARY'[/code]
This you can easily "fix" by editing the .sql file. Find:

Code Select Expand


---* {$to_prefix}members
---{

Add after:

Code Select Expand

$ignore = true;

[stephanhughson]

Thanks margarett. I've had a go at fixing one of the problems:

Converting posts...
The database value you're trying to insert does not exist: modified_name


I opened up phpMyAdmin and got the SQL out of the converter, to run manually.

SELECT
        p.post [nofollow]_id AS id_msg, p.topic_id AS id_topic, p.forum [nofollow]_id AS id_board,
        p.post [nofollow]_time AS poster_time, p.poster_id AS id_member, p.post [nofollow]_subject AS subject,
        IFNULL(m.username, 'Guest') AS poster_name,
        IFNULL(m.user_email, 'Unknown') AS poster_email,
        IFNULL(p.poster_ip, '0.0.0.0') AS poster_ip,
        p.enable_smilies AS smileys_enabled, p.post [nofollow]_edit_time AS modified_time,
        CASE p.post [nofollow]_edit_user WHEN 0 THEN 'Guest' ELSE m2.username END AS modified_name,
        p.post [nofollow]_text AS body
FROM phpbb_posts AS p
        LEFT JOIN phpbb_users AS m ON (m.user_id = p.poster_id)
        LEFT JOIN phpbb_users AS m2 ON (m2.user_id = p.post [nofollow]_edit_user)
ORDER BY modified_name -- added...


The query came up with 16 posts where "modified_name" was set to null and poster_name was set to guest with poster_email set to unknown.

So I think that could be the problem with that error. I've attached a photo of the result in phpMyAdmin (nothing massively sensitive in there... a few IPs...)


Does this take me 75% of the way? I wonder if you could give me a quick edit for the query above from the converter to handle the NULL entries please? I think I'm on the right track... maybe...

Thanks 


Edit...

Actually maybe after the three IFNULL lines, I have to put IFNULL(m2.username, 'Unknown') AS modified_name,

Just trying now... onto the next error, maybe!


Edit 2...

No luck, still stuck... Feel like I'm near the solution though. Can you guys/girls give me a hand please?

[margarett]

It does

Let me get to the computer, I'll fix the query

[stephanhughson]

Hi, sorry to "bump" this up... just wondering if you've been busy or just forgot about the post? Sorry / thanks!

[Mstcool]

Hi, sorry to "bump" this up... just wondering if you've been busy or just forgot about the post? Sorry / thanks!

You don't have to apologize for bumping a topic. You're allowed to do that. :p

[margarett]

Hi, sorry to "bump" this up... just wondering if you've been busy or just forgot about the post? Sorry / thanks!

Both. I've been extremely busy...

It's actually easier if you go to your posts database and get rid of NULL fields.
Backup your phpBB's database and run this query:

Code Select Expand

UPDATE phpbb_posts
SET post_edit_user = '0'
WHERE post_edit_user IS NULL;

Then run the converter again

I'm always amazed by the amount of variations of what's supposed to be the same database schema. And usually, SMF is really not to blame

[stephanhughson]

Thanks everyone for your help. and patience. There's more to come I think... but we're getting there nicely.

I do think it's the database and data... not SMF :-).

I tried the query above (thanks), but it said that there were no rows where post_edit_user was NULL, which is a bit weird but is fine for now.

So for now, I've just deleted some of the rows which were messed up just with queries like this:

DELETE FROM `my_database_phpbb`.`phpbb_posts` WHERE `phpbb_posts`.`post_id` = xxxxx;


It turns out I've got exactly the same problem with some of the private messages. So same...

DELETE FROM `my_database_phpbb`.`phpbb_privmsgs` WHERE `phpbb_privmsgs`.`msg_id` = xxxxx;


After listing them with:

SELECT pm.msg_id AS id_pm, pm.author [nofollow]_id AS id_member_from, pm.message_time AS msgtime, SUBSTRING( uf.username, 1, 255 ) AS from_name, SUBSTRING( pm.message_subject, 1, 255 ) AS subject, SUBSTRING( REPLACE( IF( pm.bbcode_uid = '', pm.message_text, REPLACE( REPLACE( pm.message_text, CONCAT( ':1:', pm.bbcode_uid ) , '' ) , CONCAT( ':', pm.bbcode_uid ) , '' ) ) , '\n', '<br />' ) , 1, 65534 ) AS body
FROM phpbb_privmsgs AS pm
LEFT JOIN phpbb_users AS uf ON ( uf.user_id = pm.author [nofollow]_id )
ORDER BY `from_name` ASC
LIMIT 0 , 30

Just posting an update in case anyone else has the same trouble in future. Thanks again :-). I think I'm ok for the moment.

[Faile]

If someone finds this post and wonders, here's the solution ( it's a bad SQL CASE statement that causes the problem )

FIND:

Code Select Expand


SELECT
        p.post_id AS id_msg, p.topic_id AS id_topic, p.forum_id AS id_board,
        p.post_time AS poster_time, p.poster_id AS id_member, p.post_subject AS subject,
        IFNULL(m.username, 'Guest') AS poster_name,
        IFNULL(m.user_email, 'Unknown') AS poster_email,
        IFNULL(p.poster_ip, '0.0.0.0') AS poster_ip,
        p.enable_smilies AS smileys_enabled, p.post_edit_time AS modified_time,
        CASE p.post_edit_user WHEN 0 THEN 'Guest' ELSE m2.username END AS modified_name,
        p.post_text AS body
FROM {$from_prefix}posts AS p
        LEFT JOIN {$from_prefix}users AS m ON (m.user_id = p.poster_id)
        LEFT JOIN {$from_prefix}users AS m2 ON (m2.user_id = p.post_edit_user);


CHANGE TO:

Code Select Expand


SELECT
        p.post_id AS id_msg, p.topic_id AS id_topic, p.forum_id AS id_board,
        p.post_time AS poster_time, p.poster_id AS id_member, p.post_subject AS subject,
        IFNULL(m.username, 'Guest') AS poster_name,
        IFNULL(m.user_email, 'Unknown') AS poster_email,
        IFNULL(p.poster_ip, '0.0.0.0') AS poster_ip,
        p.enable_smilies AS smileys_enabled,
        p.post_edit_time AS modified_time,
        p.post_text AS body,
        (
            CASE
                WHEN p.post_edit_user = 0 THEN 'Guest'
                WHEN m2.username IS NULL THEN 'Guest'
                ELSE m2.username
            END
        ) AS modified_name
FROM {$from_prefix}posts AS p
        LEFT JOIN {$from_prefix}users AS m ON (m.user_id = p.poster_id)
        LEFT JOIN {$from_prefix}users AS m2 ON (m2.user_id = p.post_edit_user);