Tel
asked on
Error "invalid username / password" running lots of queries
Hi
I have an application written in Delphi7 that connects to MySQL 3.23.53 via dbexpress. I have a text file that i am trying to import and have a routine that gets this data and inserts it (using insert into and parameters). This works fine, but after about 1000-2000 updates i get the message "invalid username / password" trying to run a query (or anything).
I have split the file into blocks of 1000 rows and it works fine (if i wait for a minute before running the next 1000).
Is there a max queries per minute or am i way off the mark?
I have an application written in Delphi7 that connects to MySQL 3.23.53 via dbexpress. I have a text file that i am trying to import and have a routine that gets this data and inserts it (using insert into and parameters). This works fine, but after about 1000-2000 updates i get the message "invalid username / password" trying to run a query (or anything).
I have split the file into blocks of 1000 rows and it works fine (if i wait for a minute before running the next 1000).
Is there a max queries per minute or am i way off the mark?
ASKER
that would have been handy 2 weeks ago! but i am also getting the same problem running my application now, just from doing a few select statements.
if you don't think its from the MySQL side, then it must be dbexpress thats causing it. I have only 1 connection - but looking at the variables MySql is telling me i have 2500 connections - is this a problem?
if you don't think its from the MySQL side, then it must be dbexpress thats causing it. I have only 1 connection - but looking at the variables MySql is telling me i have 2500 connections - is this a problem?
That is not a good thing, and if you are getting a connection per row I suggest looking at your code.
Do dbexpress and your MySQL server allow you to create persistent connections? Using those might help.
ASKER
i have found that when i connect to the database, it returns saying i can have only 1 statement per connection - hense i am running out rather rapidly. Is there something in mysql that says how many statements i can have per connection?
Any help much appreciated!
Any help much appreciated!
Do you run the server yourself? This would not be on the MySQL side as MySQL limits queries per hour. I think you need to look at your Delphi implementation.
That looks more like a dbexpress mysql driver bug ...
The truth is the mysql driver for dbexpress really s...s. I think your best shot one of the following:
-Use ZeosDBO - Freeware / opensource / gpl TDataSet descendant that supports mysql (never used it).
-TMySQLComponents - Pretty good but comercial
-DACMySQL - Haven't used it much but I don't like the idea of having "direct access" to mysql. This can cause a bunch of conflicts as the protocol changes. Note for those not familiar with this component: "direct access" means it doesn't use any client libraries provided by MySQL ... this can cause incompatibility issues in the long run. This component, although faster (because it makes no dll calls) than many other solutions is not free also.
-Wait until Connector/VCL comes out ... which I'm currently working on :)
BTW, I think there's an open-source mysql driver for DBExpress which is much better than the one supplied with Delphi ... at least it supports mysql 4.0, but from experience and by having studdying the dbexpress code, I wouldn't really trust the mysql driver with it. Unlike the rest of the supported db drivers, mysql doesn't support cursors which makes things a little complicated as the DBExpress framework heavily relies on them. It has to emulate cursors in MySQL which after all its not a good idea. BTW, the cursors issue will change in 5.0
The truth is the mysql driver for dbexpress really s...s. I think your best shot one of the following:
-Use ZeosDBO - Freeware / opensource / gpl TDataSet descendant that supports mysql (never used it).
-TMySQLComponents - Pretty good but comercial
-DACMySQL - Haven't used it much but I don't like the idea of having "direct access" to mysql. This can cause a bunch of conflicts as the protocol changes. Note for those not familiar with this component: "direct access" means it doesn't use any client libraries provided by MySQL ... this can cause incompatibility issues in the long run. This component, although faster (because it makes no dll calls) than many other solutions is not free also.
-Wait until Connector/VCL comes out ... which I'm currently working on :)
BTW, I think there's an open-source mysql driver for DBExpress which is much better than the one supplied with Delphi ... at least it supports mysql 4.0, but from experience and by having studdying the dbexpress code, I wouldn't really trust the mysql driver with it. Unlike the rest of the supported db drivers, mysql doesn't support cursors which makes things a little complicated as the DBExpress framework heavily relies on them. It has to emulate cursors in MySQL which after all its not a good idea. BTW, the cursors issue will change in 5.0
"Which I'm currently working on"
Umm, jconde: Is your first name Jorge and are you based out of Mexico?
Umm, jconde: Is your first name Jorge and are you based out of Mexico?
Squeebee, you seem to have gotten most of my personal info :)
I make it a habit to lurk the mySQL General List, the MySQL employee signatures cause me to remember names. I am glad to see a MySQL employee on here, it means I can retire soon ;)
hahaha,
No, please don't retire ... You know your stuff really well and just because I'm one of the developers, it doesn't mean I know everything !!! ... analogy: Mechanics know how to build & fix cars. Is it imperative for them to know how to drive them ?
EE has given me a lot (since 1998) and the reason I'm here is to give something back.
BTW, after looking at your profile, its a shame we didn't meet in SF .. I was scheduled for a couple of talks there but because of medical reasons I was unable to make it :(
...maybe Orlando ?
No, please don't retire ... You know your stuff really well and just because I'm one of the developers, it doesn't mean I know everything !!! ... analogy: Mechanics know how to build & fix cars. Is it imperative for them to know how to drive them ?
EE has given me a lot (since 1998) and the reason I'm here is to give something back.
BTW, after looking at your profile, its a shame we didn't meet in SF .. I was scheduled for a couple of talks there but because of medical reasons I was unable to make it :(
...maybe Orlando ?
I hope we can meet in Orlando, I have a talk ready to go as long as it gets approved. You know, I actually recall hearing about you being sick and not making it. A did meet one user from Mexico City, Alejandro Querejeta of the Mexican government did a presentation in SF, ever meet him? Really nice guy.
Anyway, I have been looking to take some time away from EE, as I find sometimes the experts get to aggressive, and miss the MySQL general list, but I want to hit 150,000 in this topic first ;)
Anyway, I have been looking to take some time away from EE, as I find sometimes the experts get to aggressive, and miss the MySQL general list, but I want to hit 150,000 in this topic first ;)
ASKER
1 more question before i dish out the points
After going through all the code, it seems dbExpress clones the connection, so for each form open i am getting around 20 connections per PC (threads open=the same * per user). With max_connections set @ 100 - is this why i might recieve an error? if so how do i change that?
After going through all the code, it seems dbExpress clones the connection, so for each form open i am getting around 20 connections per PC (threads open=the same * per user). With max_connections set @ 100 - is this why i might recieve an error? if so how do i change that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Anyway, that being said, what I can tell you is that as long as you are not processing the text file other than to form INSERT statements, you may want to look at the LOAD DATA command, as I can insert 3 million rows in 8 seconds using LOAD DATA.
http://www.mysql.com/doc/en/LOAD_DATA.html