News:

Wondering if this will always be free?  See why free is better.

Main Menu

DB issue after converting

Started by HunterP, April 05, 2011, 12:52:14 AM

Previous topic - Next topic

HunterP


Hi there,

At this point, my issue isn't really phpBB related anymore, so I hope it's correct to post it here.

Yesterday I converted a phpBB3 board to SMF2, but the only way to keep the script working, is to comment two lines in the SQL-file.

The board is currently up and running, but all Guest postings were put in id_member 1 named 'Anonymous'. I don't see any sense in this, so I renamed this into "Gast (phpBB)" and removed this account, knowing that these messages are pre-SMF.

The old tables are still present, in the same database. Now I'd really like to loop all messages (via PHP??) and put back the poster names.

Old tabel: phpbb3_posts (column: post_id)
New tabel: smf_messages (column: id_msg)

post_id en id_msg are the same in both tables.

Now I'd like to loop the post_id's and see where the poster_id = 1, being the guest account in phpBB? To be sure, I want some additional checks, like whether the poster_name in smf_messages is 'Gast (phpBB)' and if post_username in phpbb3_posts is NOT NULL. If all tests are OK, I'd like to copy post_username from phpbb3_posts into poster_name in smf_messages.

I hope someone can help me out :)

HunterP


Someone, not familiar with SMF nor phpBB came with this suggestion :

update smf_messages
set poster_name = phpbb3_posts.post_username
from smf_messages, phpbb3_posts
where smf_messages.poster_name = 'Gast (phpBB)' and smf_message.id_msg in (select post_id from phpbb3_posts where poster_id = 1 and post_username is not null)

Haven't tried it yet, wanted to wait for comment from my SMF-friends first :)

ziycon

Before running the update try running the below query to make sure your not updating unwanted records. Please bare in mind depending on the size of your database this could take a bit off time and you might be better off outputting it to a dump file.
SELECT *
FROM smf_messages, phpbb3_posts
WHERE smf_messages.poster_name = 'Gast (phpBB)' AND smf_message.id_msg IN (
SELECT post_id
FROM phpbb3_posts
WHERE poster_id = 1 AND post_username <> ''
);

HunterP


Unfortunately, my shared hosting doesn't allow such (long) queries. How can I chop this in smaller pieces?

HunterP

Quote from: HunterP on April 05, 2011, 09:38:11 AM
Unfortunately, my shared hosting doesn't allow such (long) queries. How can I chop this in smaller pieces?

Ok, I tried a LIMIT 1000 and I also saw rows which doesn't contain a post_username, so that doesn't seem correct.
I've checked one row manually and this field is really empty, it doesn't contain any whitespace.

ziycon

Can you try this below query now and see if its returning the correct data you want to change?
SELECT *
FROM smf_messages, phpbb3_posts
WHERE smf_messages.poster_name = 'Gast (phpBB)' AND smf_message.id_msg IN (
SELECT post_id
FROM phpbb3_posts
WHERE poster_id = 1 AND ISNULL(post_username)
) LIMIT 1000;


HunterP

Quote from: ziycon on April 05, 2011, 10:05:18 AM
Can you try this below query now and see if its returning the correct data you want to change?

Not able to run that query (timeout). Even when the LIMIT is 10 :(

ziycon

Try that, I had a slight typo in the previous query.
SELECT *
FROM smf_messages, phpbb3_posts
WHERE smf_messages.poster_name = 'Gast (phpBB)' AND smf_messages.id_msg IN (
SELECT post_id
FROM phpbb3_posts
WHERE poster_id = 1 AND ISNULL(post_username)
) LIMIT 1000;

HunterP


If the typo was "smf_messages", I already corrected that, cause without correction, it didn't run at all :)

ziycon

Strange, its working for me on a test table I set-up with an SMF installation, can you try this query?
SELECT *
FROM smf_messages, phpbb3_posts
WHERE smf_messages.poster_name = 'Gast (phpBB)' AND smf_messages.id_msg IN (
SELECT post_id
FROM phpbb3_posts
WHERE poster_id = 1 AND ISNULL(post_username) LIMIT 10
);

HunterP

Quote from: ziycon on April 05, 2011, 10:46:12 AM
Strange, its working for me on a test table I set-up with an SMF installation, can you try this query?

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

ziycon

Ok, the previous queries I sent you wont do what you want, thats what happens when you throw a query together. I'm going to take a step back for a second and try and simplify it. Can you run the below query which should output the common post ID and the smf username and the pp username based on the fields you gave above?
SELECT sm.id_msg AS MSG_ID,sm.poster_name AS SMF_User, pp.post_username AS PP_User FROM smf_messages sm, phpbb3_posts pp
WHERE sm.id_msg IN (
SELECT post_id
FROM phpbb3_posts
WHERE poster_id = 1 AND post_username IS NOT NULL
) GROUP BY sm.id_msg

HunterP

#12
Quote from: ziycon on April 05, 2011, 11:16:14 AM
Ok, the previous queries I sent you wont do what you want, thats what happens when you throw a query together. I'm going to take a step back for a second and try and simplify it. Can you run the below query which should output the common post ID and the smf username and the pp username based on the fields you gave above?

Timeout. I think I need very simple queries, otherwise my server doesn't accept them :(

ziycon

How are you running the queries, phpmyadmin?

HunterP


ziycon

Ok I've tested this query through command line and phpmyadmin and its working grand for me, let me know how you get on. Can you let me know if you get any errors or anything in your logs please?
SELECT sm.id_msg AS MSG_ID,sm.poster_name AS SMF_User, pp.post_username AS PP_User FROM smf_messages sm, phpbb3_posts pp
WHERE sm.id_msg IN (
SELECT post_id
FROM phpbb3_posts
WHERE poster_id = 1 AND post_username IS NOT NULL
) GROUP BY sm.id_msg LIMIT 10

HunterP

Quote from: ziycon on April 05, 2011, 01:47:42 PM
Ok I've tested this query through command line and phpmyadmin and its working grand for me, let me know how you get on. Can you let me know if you get any errors or anything in your logs please?

I'm running 2 SMF boards now, one on a dedicated server on which I can do almost anything, but the other is shared hosting, and that's the one I've converted from phpBB. Also your last query takes too much time and gives me a blank screen, meaning that it took to much time and the server has cut it off :(

ziycon

Seems odd to me that it can't even return 10 records and due to the fact that its not giving out about the field names I would hazard a guess that its something to do with the connection between phpmyadmin and your database.

If you change the limit to 1 does it return anything?

HunterP

Quote from: ziycon on April 05, 2011, 02:01:35 PM
Seems odd to me that it can't even return 10 records and due to the fact that its not giving out about the field names I would hazard a guess that its something to do with the connection between phpmyadmin and your database.

If you change the limit to 1 does it return anything?

But if the matches are being found at the end of the query, it has already been searching for a while. If I limit it to 1, and the only match is at the end of the database, limit 1 doesn't much, does it?

Just a thought....

Your first query worked, but also took to long, so had to be limited. That worked, but also returned rows where the post_username was ''. So I'm just guessing that no rows are being returned before the time-out limit is reached.

ziycon

Ok, lets try another approach, I've attached a quick php script that will run the query that your having problems on and will out put the time in seconds that it takes to execute the query. If you could put your database details in the top of the file in the places located in the file and put the file onto your server and go to the url and post what is returned to screen please?

We can use the timing against other servers to see roughly how long its taking. Its limited to 10 results.

Just so your aware, the reason I'm going to all the effort of seeing all the records first is to 1) make sure you are modifying what you require and 2) for cases like this, as its taking long to run a select query and it times out, if this was an update statement it could leave your data in an invalid state if it failed or timed out in the middle of an update statement.

Advertisement: