I recently migrated the forum from Access to MySQL. It all works perfectly execept for the search function that always return this error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[MySQL][ODBC 3.51 Driver][mysqld-5.0.92-enterprise-gpl-log]Query execution was interrupted
/forum/search.asp, line 337
where the line is:
set rsCount = my_Conn.Execute(strSql1 & strSql2 & strSql3)
The SQL Query generated is:
SELECT COUNT(DISTINCT T.TOPIC_ID) AS PAGECOUNT FROM ((((FORUM_FORUM F LEFT JOIN FORUM_TOPICS T ON F.FORUM_ID = T.FORUM_ID) LEFT JOIN FORUM_REPLY R ON T.TOPIC_ID = R.TOPIC_ID) LEFT JOIN FORUM_MEMBERS M ON T.T_AUTHOR = M.MEMBER_ID) LEFT JOIN FORUM_CATEGORY C ON T.CAT_ID = C.CAT_ID) LEFT JOIN FORUM_MEMBERS MEMBERS_1 ON T.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID WHERE ((R.R_MESSAGE LIKE '%gibert%' OR T.T_SUBJECT LIKE '%gibert%' OR T.T_MESSAGE LIKE '%gibert%')) AND F.F_TYPE = 0
and running exactly the same query on phpMyAdmin directly on the server, it works like expected.
I have tried different queries with the same result.
I have also tried to extend the connection timeout from the connection string, but without results:
Thank you for your answer. Actually it WAS a query timeout error: the query lasted about 100 seconds. I have resolved the issue: during the migration from access to MySQL, all tables indexes were lost. I rebuild manually all the indexes and the query performance has improved significally: no more errors!
We always recommend creating the table structure first and then importing the data. Access has no relevant indexes and both MySQL and SQL Server are much more dependent on indexing than Access is. Without much indexing info on Access, upgrading tools won't create that many indexes and performance will suffer heavily from that.
Query execution was interrupted
Posted - 13 August 2012 : 06:53:48
