Select command denied to user@IP

CodedK
CodedK used Ask the Experts™
on
Hi.
It all began when i ported a project from Delphi 7 to Delphi 2007.
I try to do a select to a remote database and i get :
select command denied to user: 'username@xx.xx.xx.xx' for table 'myusers'
I have the old compiled executable that works fine !

If i open the original folder and recompile it with Delphi 7. The same problem arises.



I downloaded HeidiSQL. (A free MySQL front-end) http://www.heidisql.com/
and i got : select command denied to user: 'username@xx.xx.xx.xx' for table 'myusers' again !
And for all the other tables.


I closed and reopened HeidiSQL and in the left pane i could see my tables.
in the tabs above i could see a data tab. I clicked it and it showed all my data in the current table. I looked in the bottom panel and copy-paste the select command to the Query tab.
>> Check my uploaded picture <<

The selected SELECT worked !
I tried another SELECT. From that point on everything works fine.
HeidiSQL is working.


I don't know what i should do to make it work for my application !
(That has been working for 3 years now) ...
This is a crazy error. I have all the permissions for this user. I created it. I own the server.

Please help.
data.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ferruccio AccalaiSenior developer, analyst and customer assistance

Commented:
Even if the error says that, you could have another kind of error that is not reported (a not found table should raise again 'select denied for user...'

Check the sql command that raises the error for quoted strings or something else.
Check the IP Whitelist in MySQL Manager / PHPMyAdmin.
CodedKSenior Software Engineer

Author

Commented:
There is no command i can execute!
Everything returns that error.

Everything works with my old executable. It doesn't make any sense.
Everything works with the downloaded Heidi that didn't work in the beginning without any change!

@Sanket_1985
What IP Whitelist ?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

IP Whitelist and Blacklist are the lists from where Connections are allowed / disallowed.

There should be an option in PHP MY Admin to control the connections.

Please check this thing in Users list.
Try putting a TSQLMonitor component into your project and tracing the handshaking that goes on.

There are settings in MySQL for allowing a user to access only from certain hosts, or from any host.  Have any of those setting changed?  

If you load the MySQL GUI front-end onto the pc that's running the program and login as that user, are you able to interrogate the data from there?  

What happens if you login as Root?  

If you run the program from Localhost (the same pc as the server, using 127.0.0.1 as the host connection string, not the domain name), are you then able to access the data?  

Those are the troubleshooting techniques I would use, in the order that I thought of them.
CodedKSenior Software Engineer

Author

Commented:
@Sanket and MoorHouseLondon

IP Whitelist doesn't make sense since i can connect and execute queries with my old exe.

I don't have a copy of the database to go local.
It is on a remote host. Its the database of a website.


Some queries that do work

SHOW DATABASES; <-OK
SHOW VARIABLES; <-OK
USE DataBase_name <-OK
-------------------------------------

SHOW TABLE STATUS FROM Database_name <- ERROR


>> If you load the MySQL GUI front-end onto the pc that's running theprogram and login as that user, are you able to interrogate the datafrom there?  If you load the MySQL GUI front-end onto the pc that's running the program and login as that user, are you able to interrogate the data from there?  

I cant understand. Can you elaborate on that ?

Download link for GUI Tools, easier to use than the command prompt:-

http://downloads.mysql.com/archives.php?p=MySQLGUITools

This is assuming you are not using the very latest MySQL version.
CodedKSenior Software Engineer

Author

Commented:
MoorHouseLondon i've downloaded GUI Tools.
What can i do with this?
I've never used it and i'm lost. Is this going to help somehow ?
Install it then login to the MySQL server by specifying the host and password.  On the left, click on User Administration and check the user details for the user the program logs in as. In particular check the Schema Privileges tab on the right for the Schemata you are using to see whether the user has the correct Assigned Privileges.  

Also make sure that when clicking on the User that the list of Hosts that the User can connect from includes the Host needed.  
Sorry that last para should read:-

Also make sure that when clicking on the User that the list of Hosts that the User can connect from includes the correct client IP or domain address.  
CodedKSenior Software Engineer

Author

Commented:
Ok, please start from the beginning

I have those executables inside the folder :
MySQLGrtCppShell
MySQLGrtShell
MySQLWorkbench

I run all of this but i cant see the options you said.  :/
CodedKSenior Software Engineer

Author

Commented:
After staring for a long time this application i cant find how to connect to a remote server. :/

Image1.jpg
Grrr, they seem to have completely changed the interface.  Try this link

http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-gui-tools-5.0-r17-win32.msi/from/pick
CodedKSenior Software Engineer

Author

Commented:
Damn ...
It all worked till i tried to connect.
It said that it supports MySQL 4 and above...
I've got 3.3 on this server.
CodedKSenior Software Engineer

Author

Commented:
Is there any other way to find what may causing this ?

When i searched the net i found only that someone had an error in a table name or something like that.
This is not the case here.

I've got all the user rights
There is no problem with the queries.
The same program with the same source code works. When i recompile it i cant do any select.

Please post any suggestion that could help.
There might be an earlier version you could download which will work with 3.3, look in the archives...
CodedKSenior Software Engineer

Author

Commented:
The oldest release is the one i downloaded first...
Looking again at this thread I think a possibility is that a DLL or other file required to interface between Delphi and MySQL has been updated to a later version.  This later version exhibits the problem you are seeing.  By recompiling your program in Delphi 7 you are still using the more up to date library file.  

I have had some weird problems with interfacing Delphi to MySQL and the way that I managed to troubleshoot them was to download tools off sysinternals website which traced what calls from which DLL were being used to produce an error message.  That will then narrow down which files have changed since D7 in your case, and which ones you should therefore look at with a view to somehow reverting or somehow otherwise produce a work-around for.
CodedKSenior Software Engineer

Author

Commented:
With Delphi 2007 and the new AnyDAC i can access all my other remote databases.
The problem (i think) is the fact that it has MySQL 3.23.58.

But...
AnyDAC from the version v1.6.0 (22.09.06) stated that ->
added MySQL: 4.1.12 and pre 3.23.57 compatibility
Using UniDAC components i can connect without any problems.
Using AnyDAC
Third-party components add that extra layer of complexity to things, particularly with regard to upgrading.
CodedKSenior Software Engineer

Author

Commented:
There is no support where i could ask something or to report this bug. Any way thanks a lot for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial