Delphi - ZEOS MySQL - access denied

Question

I am trying to connect to my mysql database server (version 5.6) on the web, from Delphi 7 application...

With Mysql Workbench, it works OK, i can run select queries with my test user (the same user that not working in Delphi 7)...

In Delphi, where using TZEosConn 7.1.4-stable , typed correct hostname for DB server, correct username and password... (parameter loginprompt = false) When I change "connected" to true, everything working OK...

But when i am trying to execute a simple query:

zq.SQL.Text:='SELECT field FROM database.TABLE where id='+inttostr(x)+';'; zq.ExecSQL;

where zq is TZQuery, and x is integer,

I got this error: **EZSQLException .. SELECT Command denied for @my_internet_hostname **

When i am logged in MySQL Workbench, i see the same user with wildcard session, like mydbuser@% , the same select works fine

So i think, the problem is in ZEOS component, it adding automatically my internet provider hostname...

On the mysql server i have wildcard hostnames allowed ( simply - '%' - so all hosts can access my MYSql DB )

Can someone suggest me, what is the problem with my ZEOS component???

PS: Sorry for my english and formatting

you can have a look at my answer here. Don't read too far only the part "permisson for a table" . The other part is not related to that.[permission](http://stackoverflow.com/a/27553279/1322642). Maybe it helps. — moskito-x, Feb 24 '16 at 01:55
@moskito-x thanks for an answer, i think there is no problem with table permissions, because from my local PC (same hosname IP address, but different application) - the same thing working on mysql workbench, and unfortunately, the same thing not working in Delphi with Zeos components... Problem is, as i said, zeos component is adding "wrong" hostname after username - i.e. user@11-22-33-44.provider.com and the correct username must be user@% — Fero, Feb 24 '16 at 02:03
there is no user@% . You always get the host where the connection comes from. — moskito-x, Feb 24 '16 at 02:13
This is the picture from MySQL WorkBench, where everything works fine: http://i67.tinypic.com/11l0bkh.png — Fero, Feb 24 '16 at 02:25
Remeber : your connection is OK . Only the user has no permission to the **SELECT** Command — moskito-x, Feb 24 '16 at 02:25
User has granted everything, except GRANT permission... And on a mysql server side, wildcard hosts are allowed — Fero, Feb 24 '16 at 02:30
look at the database on server look for the table you want access. and show me a screenshot of the permission. — moskito-x, Feb 24 '16 at 02:33
I have empty table Information_schema.TABLE_PRIVILEGES - can it be a problem? — Fero, Feb 24 '16 at 02:49
just for a moment add a user user@11-22-33-44.provider.com to the server. Give him for that table select, update, alter table permission. and try again. — moskito-x, Feb 24 '16 at 02:51
I added a hostname to allowed hosts on the server, not helped... then I recreated the test user, flushed privileges, and everything working fine now.. weird... But thanks for idea, now , my problem is solved... — Fero, Feb 24 '16 at 03:06

ffert2907 · Answer 1 · 2016-02-24T09:22:23.347

Does your MySQL server is from internet ?

first of all : does your internet provider allows you to direct access to MySQL Database with a pipe/socket connexion ? Usually internet provider don't allow this because of security matters. (They can open a special IP/Port for this if you ask them).
Second : did you have the grant option ? this can cause problem with ZEOS component.

GRANT USAGE ON . TO 'root'@'%'

GRANT ALL PRIVILEGES ON mydb.* TO 'root'@'%' WITH GRANT OPTION

If i remember well, After i change privilege like this works. it was with Local network mysql Server (with EasyPHP and WAMP tested).

I would also advise you to connect with a DSN string : check below the way to connect :

http://synopse.info/forum/viewtopic.php?id=1547

var
  dsn : string;
begin
  dsn := 'zdbc:mysql://192.168.2.60:3306/world?username=root;password=dev';

U can use it for your ZEOSConnexion initialize, and also be careful what is the Charset used : iso-8859-1, iso-8859-15, utf-8, ansi. Because Delphi 7 using ANSI, and internet servers use often others like UTF-8.

anyway i see an error in your code :

zq.SQL.Text:='SELECT field FROM database.TABLE where id='+inttostr(x)+';';
zq.ExecSQL;

ExecSQL is used to UPDATE, INSERT, DELETE commands, usually for a select command that return records it's more convenient to use open command :

zq.Close;
zq.SQL.Text:='SELECT field FROM database.TABLE where id='+inttostr(x)+';';
zq.Open;

I give many path to investigate, i hope it helps.

score 0 · Answer 2 · answered Feb 24 '16 at 10:54

i think that you must add some line in a properties of your TZConnection component:

before opening connection with your TZConnection component try this:

function openConnection(BD, User, Host, Pass: string; DBConnection: TZConnection): boolean;
begin
    Result:= false;
    if DBConnection = nil then exit;

    DBConnection.HostName := Host; // Host name to connect
    DBConnection.Protocol := 'mysql-5';
    DBConnection.Port     := 3306; //default port
    DBConnection.Database := DB; // your database name
    DBConnection.User     := User;
    DBConnection.Password := Pass;
    DBConnection.Properties.clear;
    DBConnection.Properties.Add('CLIENT_MULTI_STATEMENTS=1');
    DBConnection.Properties.Add('controls_cp=GET_ACP');

    try
        DBConnection.Connect;
        Result:= DBConnection.Connected;    
    except
        On e:Exception do
        begin
            Result:= false;
            ShowMessage(e.Message);
        end;
    end;
end;

Delphi - ZEOS MySQL - access denied

2 Answers2