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
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!
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,
^ _ ^
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.
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,
^ _ ^
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.
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!
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,
^ _ ^
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,
^ _ ^
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.
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,
^ _ ^
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. 🙂
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!
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,
^ _ ^
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.
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!
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
Copy link to clipboard
Copied
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,
^ _ ^
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.