• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Sporadic issue with ODBC on localhost

Contributor ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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

Views

1.0K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Contributor , Nov 08, 2018 Nov 08, 2018

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!

Votes

Translate

Translate
LEGEND ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

I'm not familiar with Access, so don't know the MEMO datatype.  But, yeah, I guess if you converted the Access db into a MySQL db, then there might be an issue.  Can't rule it out.  What kind of datatype did it wind up being in MySQL?

But, then again, per my second post, the SQLSTATE indicates a connection issue.  With a db so small, the MEMO may or may not be an issue.  But definitely try keeping connections alive.

V/r,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

Glad you got it working.  TEXT is nice, especially when you don't know that the content is going to be limited to x amount, but VARCHAR() is easily searchable.  You could probably keep it TEXT, and use CAST during searches, but that's a lot of overhead, from what I understand.

Anyhoo..  it's working, and that's the best part.

V/r,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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

HTH,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

Jack, adding to what Wolf did:  right, you do not "name the file". you name the DB (and the mysql server info). More in a moment.

Your original comments here indicated you had already setup a simple mysql database. All you need to point to the DB in CF (in the "mysql5" type of DSN I refer to above) is the database name (which you picked when you set it up in mysql), the server name (localhost if mysql is on your same machine as CF, or the IP or name if it's not), the port (3306 is default), and then your mysql username (may be root by default) and password (whichever has access to the created db.

Let us know if that gets you going.


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Nov 09, 2018 Nov 09, 2018

Copy link to clipboard

Copied

Sometimes, I feel really dense, and this is one of them. I may be giving the wrong impression of my goal, or I may just be misunderstanding what is plainly understandable to others.

I have a MySQL database that I have created on my local pc. It is running under localhost. I need to get this database up to my ISP to replace the old Access db that has powered the site. My CF code will run just fine on the ISP with the same DSN, but it just needs to be the MySQL db, which only exists on my local pc at present.

I've been given instructions for using PHP tools. I've never touched PHP, and I wouldn't know where to find MyPHPAdmin, I think it is.

Can I copy and FTP a db file? Must I do a "dump" and somehow process that file on the ISP? I've read about "replicating," which sounds promising, but the text indicates I would not be able to modify the data on the ISP server. I'm lost, and I know this is probably really simple.

Thanks!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 09, 2018 Nov 09, 2018

Copy link to clipboard

Copied

First, you'll need to export your current MySQL file from your local PC. You can do that from the command line with mysqldump:

mysqldump -u YourUser -p YourDatabaseName > wantedsqlfile.sql

If mysqldump isn't in your path, you might have to launch it from the bin subdirectory of your mysql directory, which might be something like this more or less:

c:\mysql\bin

Then, you can transfer it to your other MySQL database server however you like. Once you've done that, you can load it using the mysql command-line program, like this:

mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Here's a page with detailed instructions on all of this.

https://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/#more-184

Dave Watts, Fig Leaf Software

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 09, 2018 Nov 09, 2018

Copy link to clipboard

Copied

LATEST

Jack_Allred2  wrote

I've never touched PHP, and I wouldn't know where to find MyPHPAdmin, I think it is.

You don't need to know PHP in order to use the administrative panel called MyPHPAdmin that your host service provider uses.  There should be a link, somewhere, once you log on to your account on the service provider website.  That's your control panel (or at least it is _a_ control panel within some section of your account.)


Once there, you should find a link for database stuff.  In there should be a way for you to import the database that you exported using Dave's instructions.

HTH,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 08, 2018 Nov 08, 2018

Copy link to clipboard

Copied

I wish I could edit my comment to be more explicit. I was on my phone when I wrote, and it turns out that the google search terms I offered did not prove as helpful as I had hoped they would.

I found a short blog post that does talk through what one needs to do to get mysql and CF working:

http://www.nodans.com/index.cfm/2015/5/18/ColdFusion-11--MySQL-no-Suitable-Driver 

More could be written, but that should get most people going with it (if they try to use the CF-provided "mysql 5" driver and get that error). The problem is that for licensing reasons Adobe stopped providing the needed JDBC driver, but following those simple steps in that post will get it working--even with MySQL 8.


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation