Solved

Error "invalid username / password" running lots of queries

Posted on 2003-11-17
14
1,276 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
ID: 9765675
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
ID: 9765799
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
ID: 9766056
That is not a good thing, and if you are getting a connection per row I suggest looking at your code.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 11

Expert Comment

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

Author Comment

by:Tel
ID: 9768219
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
ID: 9768412
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
ID: 9769316
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
 
LVL 17

Expert Comment

by:Squeebee
ID: 9773054
"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
ID: 9773241
Squeebee,  you seem to have gotten most of my personal info :)
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9773552
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
ID: 9773687
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
ID: 9776338
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
ID: 9778298
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
ID: 9780645
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

786 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