[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1291
  • Last Modified:

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?
0
Tel
Asked:
Tel
  • 6
  • 4
  • 3
  • +1
1 Solution
 
SqueebeeCommented:
There is not really a limit, but MyODBC can sometimes throw errors, you may want to add code to retry on error. Are you opening a connection once and reusing it?

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
0
 
TelAuthor Commented:
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?
0
 
SqueebeeCommented:
That is not a good thing, and if you are getting a connection per row I suggest looking at your code.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
ZontarCommented:
Do dbexpress and your MySQL server allow you to create persistent connections? Using those might help.
0
 
TelAuthor Commented:
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!
0
 
SqueebeeCommented:
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.
0
 
jcondeCommented:
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
0
 
SqueebeeCommented:
"Which I'm currently working on"

Umm, jconde: Is your first name Jorge and are you based out of Mexico?
0
 
jcondeCommented:
Squeebee,  you seem to have gotten most of my personal info :)
0
 
SqueebeeCommented:
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 ;)
0
 
jcondeCommented:
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 ?
0
 
SqueebeeCommented:
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 ;)
0
 
TelAuthor Commented:
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?

0
 
jcondeCommented:
Wow ... 20 connections sounds like A LOT to me !!! .... at the most you need only 3 that I can think of ! ... one for TCustomConnection, One for the TDataSet retrieval stuff (what actually gets printed on the screen) and another for Insert/Delete/Update records ... And that's assuming you're using mysql_use_result (instead of mysql_store_result) and that the component is fully multi-threaded.

max_connections is ok at 100.  The reason I don't think max_connections has anything to do with your problem is because mysql would return a "Too many connections" error if you had surpassed the max_connections limit.

Mysteriously, if you search google for "mysql delphi invalid username password" you will find that most of the results have something to do with SQLExpress!

From a console window, try calling "SHOW FULL PROCESSLIST" when the invalid username/password error appear ... that will let you know exactly what's going on from the mysqld side point of view.

If you're not using any of the DBAware components for all of this, what I would do is use the mysql api functions directly for inserting data into mysql.

http://www.fichtner.net/delphi/mysql.delphi.phtml

that will probably be the fastest, most safest way of doing things.

The basic code would look something similar to:

uses mysql;

procedure insert_or_update_mysql;
var
  mysql: PMYSQL;
  Query: String;

begin
  mysql := mysql_init(mysql);
  if (assigned(mysql)) then
  if (mysql_real_connect(mysql, 'hostname', 'username', 'password', 'database', 3306, nil, 0) <> nil) then
  //if mysql_real_connect is not supported, use mysql_connect followed by a mysql_select_db('database') call
  begin
     While (you still have queries left to) do
     begin
        Query := GetNextQuery;  //GetNextQuery returns the next Insert/Update/Delete query that needs to be executed
        if (mysql_query(mysql, PChar(Query)) <> 0) then
          showmessage('Error executing query: ' + Query);
     end;

    mysql_close(mysql);
  end;
end;

The above would not only be faster, but would consume a lot less memory and will for sure not display errors such as invalid username / passwd unless you explicitly make it do so :)

After you're done with the above, you may use SQLExpress to view your database's contents, allow inline insert/update/delete, etc ...

Getting more specific, I would create a TList or TStringList, put all of your queries there and replace my code with a "for" loop instead of a "while".  GetNextQuery, would then be replaced by YourStringList.Strings[Index] (assuming you use a TStringList).
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now