DB issue after converting

[HunterP]

Thanks for your effort, but again I get a timeout.

[ziycon]

Lets see if we can speed things up then, the SMF table already has indexes setup, can you run the below query to see if there are any indexes on your phpbb table?

Code Select Expand

SHOW INDEX FROM phpbb3_posts;

[HunterP]

Lets see if we can speed things up then, the SMF table already has indexes setup, can you run the below query to see if there are any indexes on your phpbb table?

Code Select Expand

SHOW INDEX FROM phpbb3_posts;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
phpbb3_posts	0	PRIMARY	1	post_id	A	91773	NULL	NULL		BTREE
phpbb3_posts	1	forum_id	1	forum_id	A	79	NULL	NULL		BTREE
phpbb3_posts	1	topic_id	1	topic_id	A	7647	NULL	NULL		BTREE
phpbb3_posts	1	poster_ip	1	poster_ip	A	7647	NULL	NULL		BTREE
phpbb3_posts	1	poster_id	1	poster_id	A	1349	NULL	NULL		BTREE
phpbb3_posts	1	post_approved	1	post_approved	A	1	NULL	NULL		BTREE
phpbb3_posts	1	tid_post_time	1	topic_id	A	7647	NULL	NULL		BTREE
phpbb3_posts	1	tid_post_time	2	post_time	A	91773	NULL	NULL		BTREE
phpbb3_posts	1	post_username	1	post_username	A	997	NULL	NULL		BTREE

[ziycon]

Can you ask you host to increase the timeout on mysql, you may need to explain that some queries are timing out due to the amount if data being used/queried?

Also no harm to tell them this is just a temporary change so you can update records.

[HunterP]

Ok, I've mailed them.

How about this, I'm still learning SQL so I'm not sure if this is correct :

SELECT smf_messages.id_msg
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)
);

In stead of selecting * I select smf_messages.id_msg

Should this return anything? I get zero rows.

[ziycon]

What that query says, if post_username is null then return, you need to replace isnull(post_username) with something like

Code Select Expand

post_username <> ''
Does that make any difference?

[HunterP]

What that query says, if post_username is null then return, you need to replace isnull(post_username) with something like

Code Select Expand

post_username <> ''
Does that make any difference?

Yes, again a timeout

[ziycon]

Going to have to wait till your host gets back regarding mysql timeout time.

[HunterP]

I've tried the first query which was recommended to me, but for a backup table :

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

But that results in this error :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from smf_messages_bak, phpbb3_posts
where smf_messages_bak.poster_name = 'Gast ' at line 3

Any suggestions? I just wanted to see what would happen as I can't do any harm on this backup table.

[ziycon]

Try the below query.

Code Select Expand

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

[HunterP]

Try the below query.

Code Select Expand

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

The same 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from smf_messages_bak, phpbb3_posts
where smf_messages_bak.poster_name = "Gast ' at line 3

I'm beginning to think that this host really sucks...

[ziycon]

Sorry, just spotted it there, you can't have a FROM clause in an update statement.

UPDATE smf_messages_bak, phpbb3_posts
SET smf_messages_bak.poster_name = phpbb3_posts.post_username
WHERE smf_messages_bak.poster_name = 'Gast (phpBB)' AND smf_messages_bak.id_msg IN (SELECT post_id FROM phpbb3_posts WHERE poster_id = 1 AND post_username IS NOT NULL)

[HunterP]

Sorry, just spotted it there, you can't have a FROM clause in an update statement.

UPDATE smf_messages_bak, phpbb3_posts
SET smf_messages_bak.poster_name = phpbb3_posts.post_username
WHERE smf_messages_bak.poster_name = 'Gast (phpBB)' AND smf_messages_bak.id_msg IN (SELECT post_id FROM phpbb3_posts WHERE poster_id = 1 AND post_username IS NOT NULL)

Ok thanks, but once again...........timed out

[HunterP]

Ok. I've ex/imported to my own hoster and it seems to run a bit better.
The first query still doesn't work (timeout), but the second does :

Try that, I had a slight typo in the previous query.

Code Select Expand

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;

Returns no records, even without LIMIT.

Code Select Expand

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

Also a timeout on this server, even with a LIMIT of 10. I assume that this query is too big, and returns nothing (at least not untill the timeout)

Code Select Expand

SELECT smf_messages.id_msg
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 post_username <> ''
);

Times out, but when I limit it to 10, 100 or 1000 it returns the number of the limitation. Also when 10000 which takes a little while, 10000 rows are returned.

Think we are more or less back where we started with this query :

Code Select Expand

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 post_username <> ''
) LIMIT 100;

This returns rows with and without post_username. How is this possible?

[HunterP]

Code Select Expand

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 post_username <> ''
) LIMIT 100;

This returns rows with and without post_username. How is this possible?

Ok, now I notice that even the poster_id IS NOT 1 for these rows. These rows don't seem to match the WHERE conditions (poster_id = 1 AND post_username <> '') so how can they be returned?

[ziycon]

So many queries have been posted regarding this topic so lets start from where we are now, you have a different database with your data imported into it or the current one?
See what results you get with this query, if it times out run the second query as it will only return one row:

SELECT smf_messages.id_msg AS ID, smf_messages.poster_name AS SMF_Name, phpbb3_posts.post_username AS PP_Name
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 post_username <> ''
) LIMIT 10;

SELECT smf_messages.id_msg AS ID
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 post_username <> ''
) LIMIT 10;

Can you post your results for these queries, it might be better to PM me them if you don't want to post them publicly on the topic.

[HunterP]

you have a different database with your data imported into it or the current one?

I'm running on a different server and am using a separate database for those two tables.

See what results you get with this query

ID	SMF_Name	PP_Name
73	Gast (phpBB)
73	Gast (phpBB)
73	Gast (phpBB)
73	Gast (phpBB)
73	Gast (phpBB)
73	Gast (phpBB)
73	Gast (phpBB)
73	Gast (phpBB)
73	Gast (phpBB)
73	Gast (phpBB)

[ziycon]

Ok so where getting somewhere, try this query now. And post your results please?

Code Select Expand

SELECT smf_messages.id_msg AS ID, smf_messages.poster_name AS SMF_Name, phpbb3_posts.post_username AS PP_Name
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 post_username <> ''
) GROUP BY smf_messages.id_msg LIMIT 10;

[HunterP]

Ok so where getting somewhere, try this query now. And post your results please?

Time-out

[ziycon]

After taking a step back and thinking about what was trying to be done this query should fix everything.

Code Select Expand

UPDATE smf_messages sm, phpbb3_posts pp
SET sm.poster_name = pp.post_username
WHERE sm.id_msg = pp.post_id AND pp.poster_id = 1 AND pp.post_username <> '';
The original query was searching through the whole phpbb3_posts table every single time for each record in the smf_messages table which is why the timeouts occurred, so that was roughly 20, 000 x 24,000 records, turns out the query was returning roughly 480,000,000 records with the original query

Hope all runs smoothly from here.