ODBC Server hangs

[mkarl]

Hello! We have the same problem on one of our ColdFusion MX Servers too. On the
Server Windows 2000 Server, Apache 2, MySql 4.0 and ColdFusionMX 6.1 is
running. We use the ODBC socket and the MyODBC driver to connect to the
databases. We have the same configuration on some other servers and they all
work well. Only on one server we always get the error messages 'Error Executing
Database Query'. In some cases the server hangs and we have to restart it. The
error sometimes appears by day when many users are on the application, and
sometimes by night when only some users are on the application. We installed
ColdFusion again, but same problem like before. Good to hear that we are not
alone with this problem. I think this is a bug in CF or in the ODBC-Socket of
CF. Perhaps someone of the macromedia team could answer here and get a
solution. It?s no solution to add a timout to all queries and always display
error pages to the users. Now, CF 7 is out, but I hope that the bugs in CF 6.1
will also bee fixed. Best regards, mkarl

[Eugene]

Agree. This is CF bug and Macromedia must fix it.

---
Eugene Krivenja

[mkarl]

Hello! I wrote an Email to the bug report of macromedia CF with a link to this
topic. This was on wednesday last week. I am very disappointed that no one of
macromedia answerded my via email or in this topic about that problem. I think
the users of ColdFusion MX 6.1 have the right on a correctly working version
without such problems. I hope that someone of the macromedia team will get an
answer to that problem in the next few days. Best regards, mkarl

[gkraeger]

I think many people are having this issue, we have had many people email us asking for help. Macromedia needs to fix this ASAP.

[sdupre]

The ODBC Server in 6.1 and 6.1 Updater 1 is Sequelink 5.3. The hangs
described in this forum thread indicate the server is stuck or down. Some
users mention they're using SQL Server but it's not clear that this is the most
common scenario as a good number of our customers still use desktop databases
such as MS Access. The JDBC MS SQL Server driver is available on all
editions of CFMX so unless you're using NT Authentication, you should be using
this driver. The JDBC driver pack in 6.1 Updater 1 is 3.3 build 31. The
Sequelink piece is 5.4 build 38 I believe (almost the latest driver). 6.1
shipped with the 3.2 JDBC drivers and they had problems so make sure you apply
the updater to get the newer drivers. We do have a newer ODBC Server (5.4)
which also shipped with CF7. It fixes some very specific crashes and we
intend to make this available as a hotfix. It's not an easy component to
upgrade though I believe the instructions are pretty good. This doesn't
mean 5.4 will fix all hangs - unless the hangs are specifically related to the
ODBC Server. We would need to look at stack traces for this or evidence in
the logs. The crashes in the ODBC Server (the ones that would tell you
upgrades are needed) would be logged in /cfusionmx/db/slserver52/logging and in
the Windows event viewer. Many users have problems that point to the ODBC
Server. Some users (maybe 5-10%) may have older drivers that aren't
compatible with the database they're running. (mostly with Desktop databases
such as MS Access - ex: old driver + Access 2000 DB or newer driver with
Access 97 database). This causes subtle problems (sometimes only on
specific queries) like CPU spikes and huge memory footprints (watch swsoc.exe).
If the driver version (vs DB version) is the issue, querying in another tool
(msqry32.exe,etc) with the same ODBC datasource should cause the same problem
(ie the client gets fingered incorrectly - whether it's Sequelink (swsoc.exe),
MSAccess.exe or some other client - the problem is the driver/DB
incompatibility - either upgrade the driver or the Access DB). Other users
have something like MS Access but are hitting it too hard (not caching where
they can, really need SQL Server, etc) causing Access to fall over. For those
users that can't upgrade Access, you can do a few things to help: - check your
queries and optimize indexes. - make sure you have a backup and repair schedule
for the database. (ODBC Admin -> odbcad32.exe allows you to repair an Access
DB or you can do it in the MS Access application). Partially corrupt
databases often cause a lot of problems. - use caching whenever you can
(CFQUERY cachedwithin=CreateTimeSpan(.2 hours.) ....) to minimize hits to the
database for data either infrequently updated or not relevent for a user. (a
hit /visitor counter is a good example of a wasted query - a value of 85000 or
85001 hits/users probably doesn't matter to the end user but might cause fits
on a home page under heavier than normal use. Run it once a day instead and
cache the value with cachedwithin) For MS SQL Server users, the 3.4 version of
the drivers (not shipping even with CF7 since they haven't been tested
thoroughly) does have NT authentication with the JDBC driver. This will get
users off the less stable Sequelink/ODBC Socket server but it only does
authentication of the CFMX Server service user, not with credentials passed in
through username/password in the CFQUERY. This might still work for some
people using NT auth but for most, I would think this doesn't add anything to
get off Sequelink. We have an enhancement logged to allow NT Auth of both the
logged in user (of the CFMX server process) or through CFQUERY passed-in
credentials. One of my to-do's is to publish the 5.4 Server upgrade (it's
about 10MB file) and I'll try to get to it this week. It's important that
users don't just replace this hoping it solves all their problems before they
look at logs, databases, queries, etc. Another aside: 5.4 Sequelink has
Unicode support. The problem is you have to have 2 separate ODBC Servers (one
unicode, one non-unicode Windows service) to get this to work. The older
Sequelink 5.2 and 5.3 versions used NON-unicode for default (both 6.1 and 6.1
Updater 1) so upgrading the existing server keeps you on NON-unicode. It's
not impossible to setup 2 ODBC Servers but the maintenance of the separate
datasources and knowing exactly which CFQUERY needs which datasource can be a
pain. This is another reason to use JDBC if you're in this situation of having
NVARCHAR, NCHAR, NTEXT columns in newer applications or you simply are being
asked to make an existing application (data) unicode-compatible. I'll try to
get this Technote out this week. It should appear in the CF hotfix section.
Stephen Dupre Macromedia QA

[gkraeger]

Thank you for the help, I will suggest this to those who have asked us for help.

[bdicer]

The same exact thing to happened to our server this past Saturday night. All
50+ of our websites we inaccessible when the problem occured. I haven't seen a
hotfix for this issue yet, I wish the would release one soon.

[sdupre]

The hotfix is located here:
[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=e917887[/url] If this
doesn't resolve your problem(s), we have to look at each case individually.
Look at my earlier (long) post to see if any of the symptoms are similar.
Regards, Stephen Dupre Macromedia QA

[Eugene]

> The hotfix is located here:

> [url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=e917887[/url] If

this

> doesn't resolve your problem(s), we have to look at each case

individually.

> Look at my earlier (long) post to see if any of the symptoms are similar.
> Regards, Stephen Dupre Macromedia QA

Thank you.
I will try and post the results.

---
Eugene Krivenja

[Eugene]

Hello Stephen,

today I installed this hotfix and found one error in
cf_root\db\slserver52\cfg\swandm.ini file.
All paths in this file are preset to D:\CFusionMX\
I replaced it to C:\CFusionMX\ (I have installed CF by default) and now all
work fine.
I will test few days and will inform you about results.

---
Eugene Krivenja

> The hotfix is located here:
> [url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=e917887[/url] If

this

> doesn't resolve your problem(s), we have to look at each case

individually.

> Look at my earlier (long) post to see if any of the symptoms are similar.
> Regards, Stephen Dupre Macromedia QA
>

[winkelmann]

Hello Eugene;
Did you proof the version of the SQLServer JDBC Driver and the
jdbc.sequelink.SequeLinkDriver,
before you installed the hotfix.
We have two machines with CF MX 6.1. On one of them, the last updater is
installed.
This machines has the necessary driver versions.
The hotfix made no problems (with the changes in swandm.ini).

On the other machine the updater isn't installed. Here are older driver
versions!
So I did't install the update on this machine, because of
the kb-article:
[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=e917887[/url]

Is the 5.4 SequeLink driver version (driverpack version 3.3 build 31 or higher)
really needed for installing the update?

regards frank

[Eugene]

Hello, frank

Before install SequeLink 5.4 server, I checked driverpack version, how
recomended in following kb-article.
I had version 3.3 build 31.

Did you install all previous updates?
Check it:
[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_17883[/url]

---
Eugene Krivenja

> Did you proof the version of the SQLServer JDBC Driver and the
> jdbc.sequelink.SequeLinkDriver,
> before you installed the hotfix.
> We have two machines with CF MX 6.1. On one of them, the last updater is
> installed.
> This machines has the necessary driver versions.
> The hotfix made no problems (with the changes in swandm.ini).
>
> On the other machine the updater isn't installed. Here are older driver
> versions!
> So I did't install the update on this machine, because of
> the kb-article:
> [url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=e917887[/url]
>
> Is the 5.4 SequeLink driver version (driverpack version 3.3 build 31 or

higher)

> really needed for installing the update?
>
> regards frank
>

[winkelmann]

Hello Stephen;
can you tell me, with which hotfix/update the driverpack version 3.3 build
31comes?
This Version is necessary for installing the hotfix SequeLink 5.4 server.
I didn't install the last updater for CFMX 6.1 on our production system.
Is the driverpack version 3.3 build 31only with the latest updater available?

regards frank