Solved

Error "invalid username / password" running lots of queries

Posted on 2003-11-17
14
1,268 Views
Last Modified: 2008-02-07
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
Comment
Question by:Tel
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 17

Expert Comment

by:Squeebee
Comment Utility
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
 
LVL 1

Author Comment

by:Tel
Comment Utility
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
 
LVL 17

Expert Comment

by:Squeebee
Comment Utility
That is not a good thing, and if you are getting a connection per row I suggest looking at your code.
0
 
LVL 11

Expert Comment

by:Zontar
Comment Utility
Do dbexpress and your MySQL server allow you to create persistent connections? Using those might help.
0
 
LVL 1

Author Comment

by:Tel
Comment Utility
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
 
LVL 17

Expert Comment

by:Squeebee
Comment Utility
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
 
LVL 7

Expert Comment

by:jconde
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 17

Expert Comment

by:Squeebee
Comment Utility
"Which I'm currently working on"

Umm, jconde: Is your first name Jorge and are you based out of Mexico?
0
 
LVL 7

Expert Comment

by:jconde
Comment Utility
Squeebee,  you seem to have gotten most of my personal info :)
0
 
LVL 17

Expert Comment

by:Squeebee
Comment Utility
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
 
LVL 7

Expert Comment

by:jconde
Comment Utility
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
 
LVL 17

Expert Comment

by:Squeebee
Comment Utility
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
 
LVL 1

Author Comment

by:Tel
Comment Utility
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
 
LVL 7

Accepted Solution

by:
jconde earned 350 total points
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now