| T O P I C R E V I E W |
| sanmag |
Posted - 13 August 2012 : 06:53:48
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:
'strDBType = "sqlserver"
'strDBType = "access"
strDBType = "mysql"
'## Make sure to uncomment one of the strConnString lines and edit it so that it points to where your database is!
strConnString = "Driver={MySQL ODBC 3.51 Driver}; Server=xx.xx.xx.xx; uid=user; pwd=pwd; database=myDB; option=16387; port=3306; default command timeout=800; connection timeout=800;"
Is there anyone that can help me?
Thank you!
|
| 3 L A T E S T R E P L I E S (Newest First) |
| ruirib |
Posted - 14 August 2012 : 11:08:48
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. |
| sanmag |
Posted - 14 August 2012 : 10:38:53
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!
Thank you! |
| ruirib |
Posted - 13 August 2012 : 09:24:39
It looks like a timeout error to me, even if the error message is MySQL at its best.
The connection timeout will have no effect on that, since it won't affect query execution timeouts. It only affects connection establishment timeouts.
To fix the problem you will have to rewrite the code to use a Command object and use the CommandTimeout property, to increase the timeout value. |
