Inspiring

Answered

Sporadic issue with ODBC on localhost

Forum|Forum|7 years ago
November 8, 2018
3 replies
1537 views

I'm brand-spanking new to MySQL, using ColdFusion, and I'm having sporadic issues with queries on localhost. Sometimes they work, and sometimes they throw an error. If I reload the page executing a query five times, it might display the page correctly only one or two of those times. The execution times for the pages range from 5 to 15 seconds, whether there's an error or not. Works the same in two different browsers. The behavior is the same, whether I pass a username/pw to the database or not. Pages with no queries execute in the normal millisecond range.

MySQL Server 8.0.13 Windows 64 bit ODBC datasource (AptMaps2), which is referenced from within Coldfusion administrator, where I created a CF DSN with a different name (AptMaps). ColdFusion2018

You can see from the error text, even if the page sends back an error, the variable gets passed properly.

I have no understanding why, but I suspect there is a problem in how I've configured my DSN. Thoughts?

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver]TCP/IP error, connection refused.

The error occurred in C:/ColdFusion2018/cfusion/wwwroot/pages/map.cfm: line 45

43 : SELECT *
44 : FROM Maps
45 : WHERE MID = #url.id#
46 : ORDER BY MID
47 :

SQLSTATE 08S01
DATASOURCE AptMaps
VENDORERRORCODE 2306

SQL SELECT *
FROM Maps
WHERE MID = 110
ORDER BY MID

This topic has been closed for replies.

Correct answer John_Allred

The memo field from Access, is in MySQL, now:

Type: TEXT

Charset: utf8mb4

Display Size: 1073741823

Precision: 403

Scale: 0

Access allows any amount of text content in the MEMO field type.

My guess on the Display Size above is the number of characters allowed. If I must set a finite size, around a thousand would do me. I smell a bottleneck here.

And a bottleneck, indeed, it was. I reset the formerly MEMO field from TEXT to VARCHAR(1500). Everything works like a charm, now. God only knows how many CPU cycles were churned to sift through those humongous fields!

I still don't know much, but I know more than I did.

Thank you so much for talking me through this. Exactly what I needed!

Charlie Arehart

Adobe Expert

Jack, take odbc out of the equation. I appreciate that since you moved from access, it may have seemed reasonable. You want to get away from odbc now that you can. Use jdbc instead. No, cf doesn't include a mysql jdbc driver, but it's easy to add, per the cf docs or just Google:

Setup coldfusion mysql jdbc

And leave odbc in the your rear view mirror of the 90s where it belongs. :-)

/Charlie (troubleshooter, carehart. org)

John_AllredAuthor

Inspiring

Still dog paddling, Charlie. Using JDBC, will I still have a JDBC dsn which I call from within CF Admin? I'll investigate your supplied link later, but just curious. I used what seemed to be a circuitous route, bc I couldn't get anything to work from within MySQL. Plus, I STILL haven't figured out where my file is even stored or what it's named. Access was SO much simpler for me.

Thanks!

WolfShade

Brainiac

You shouldn't access the file, directly. So, no need to know where it is stored. MySQL (as other proper dbs) is much more complex and robust than Access. Honestly, Access really should never be used for a website/app, anyway. Especially if more than one user will be using it.

V/r,

^ _ ^

WolfShade

Brainiac

SQLSTATE 08S01 implies dropped connections, I believe. There should be a setting in advanced DSN for keeping connections live. Give that a shot.

HTH,

^ _ ^

John_AllredAuthor

Inspiring

I have used mostly Access dbs over the years. I have unique primary keys on the tables that need them, but I couldn't say whether my database is indexed, as you asked above.

And here is a possible source of the issue: I created an ODBC datasource through the Windows ustility. Then, I created a dsn in Coldfusion using an ODBC Socket, based on the first datasource. I fiddled with MySQL for a few days without luck in creating a datasource, so this was the hack that finally worked. All that said, the Windows datasource does not provide a timeout setting. The CF datasource does, and it is set to maintain connections for 20 minutes. I boosted to 30 min, doubled the Login Timeout to 60 secs.

I changed all SELECT * to SELECT [specified fields]. No real change to the previous results.

WolfShade

Brainiac

I wasn't completely paying attention.

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver]TCP/IP error, connection refused.

You say that you created an ODBC connection, but this says JDBC. There has to be something, here. I'm Googling it, now.

.. and I'm not finding anything relative, except the same question you posted on SO. LOL!

V/r,

^ _ ^

WolfShade

Brainiac

There could be a number of things going on, here. The first question I have is: How much data are you expecting to be returned? If the answer is "millions of records with images stored as BLOBs," then the page is timing out.

Are you indexing the database?

Is there potential for lag/latency? Something that could bottleneck the network?

One thing that I would suggest is NOT using SELECT * as that forces the database to reference an internal system table to get the column names. It also adds to CPU processing on the db server, and unless you actually USE every column in the table, you're sending information that you don't need/use, using more bandwidth than necessary.

Also, it could be CF Server is having an issue, not the db server.

HTH,

^ _ ^

John_AllredAuthor

Inspiring

Thanks for a quick reply!

I'll try naming the fields in my queries, rather than using the asterisk. The db is incredibly simple and small. Only one table with 98 records consisting of 7 columns. Only one is a Memo field, as originally created in Access. Now that I think of it, could the memo field have caused some issue during the conversion to MySQL? When the queries work, they pull records based on contents of the memo field the same as from any other field.

John_AllredAuthorCorrect answer

Inspiring

The memo field from Access, is in MySQL, now:

Type: TEXT

Charset: utf8mb4

Display Size: 1073741823

Precision: 403

Scale: 0

Access allows any amount of text content in the MEMO field type.

My guess on the Display Size above is the number of characters allowed. If I must set a finite size, around a thousand would do me. I smell a bottleneck here.

And a bottleneck, indeed, it was. I reset the formerly MEMO field from TEXT to VARCHAR(1500). Everything works like a charm, now. God only knows how many CPU cycles were churned to sift through those humongous fields!

I still don't know much, but I know more than I did.

Thank you so much for talking me through this. Exactly what I needed!

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded