cannot conncet to server 1

[madvalpreston]

Hi

This is a following on thread from how to set up ODBC I created a few days agao.

I have Mysql on a computer. I am trying to connect to it from another computer on the network through ODBC.

This is the setup on the server.

connection Name : localhost
Connectio type : Standard TCP/IP
Hostname : localhost
Username : SA
Port :3306

I have loaded the ODBC connector on the computer and when I try to conncet with

localhost, port 3306, user SA and password, it does not show any databases and also gives me an error message

"Connection failed cant connect to Mysql server as local host 10061"

Could someone please help me out, I have gone through many web pages from google but can find a solution as yet.. Our databse is no good if we cant share it....

Thanks

 

[vacunita]

I have Mysql on a computer. I am trying to connect to it from another computer on the network through ODBC.

If your MYSQL server is not on the same computer you are connecting from, then the host cannot be localhost. Localhost as the name implies is the local machine, not some other machine in the network.

You would need to specify an IP address, and make sure the ports are opened and the connections are allowed in the firewall.




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[madvalpreston]

Hi Vacunita

Thanks for the reply.

1.Does the IP address have to be the IP of the machine the Mysql is on? I did try this and I got the same error message. I put the IP in the same place as I put localhost name.
2. Also I have opened the port on the Mysql machine for 3306, does this need to be done on the machine(s) I am trying to connect from. I did this on one machine but got same error message also.

Any further advice please would be grateful...

Thanks

 

[madvalpreston]

Hi

I now have my 2 laptops joined together and on Mysql workbench I can see that in my server administration I have mysqld@127.0.0.1

I can ping 127.0.0.1 from the other laptop, but when i try and run the ODBC I get the same message. I am putting in

TCP/IP server : 127.0.0.1 Port : 3306

I have also added the programs and the ports to the firewall.

I have exhausted the web for solutions. Has anyone got any more ideas please, this is urgent as we need to link to the database through ODBC.

Thanks

 

[whosrdaddy]

127.0.0.1 is the loopback address of you own computer.
localhost is in fact an alias for 127.0.0.1

run ipconfig in a command line on the laptop where you have MySQL running. Use that address to connect from the other laptop.

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

please read these pages:

http://www.w3schools.com/tcpip/tcpip_intro.asp

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[vacunita]

I could have sworn I answered again to this.

Anyway Whosr is right. if MYSQL is not running on the PC you are connecting from, then the address cannot be 127.0.0.1.

You need an actual IP address for the machine that holds the MYSQL server.

You also need to give permissions to the user so it can connect from that specific address.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[madvalpreston]

Hi

Thanks for the replies. I have tried with the IP from the machine but I still get the same error message.

That is whay I have posted again to see if anyone had anymore idea I could try.

Vacunita I am not sure how you mean give pernissions to the user for the machine. I am logging in through the ODBC with the SA and password.... The machines are networked and both have shared C drives.... Could you speicify abit more please

Thanks

 

[vacunita]

Using the Workbench, and assuming you ave already setup a Server Instance to manage under Server Administration, double click the instance to open it, and then click on the Users and Privileges in the Security section.

You should have the Users listed in the Server Access Management tab.

Click on the Scheme Privileges tab.
From there Clicking on the User will show you its Privileges, and its locations from which it has said privileges. If you have nothing configured for the user, you can click below on the Add Entry button to create new Privileges for it.


You either need to set the IP address of the computer you are connecting from, or set is a % sign so it can connect from anywhere. Then just mark the check boxes of the actions you want the user to be able to perform.

Once that is done, you can go back to your ODBC setup.

If you still get an error, post it here so we may see.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[madvalpreston]

Hi Vacunita

Ok I set all what you said up, the user SA had nothing set up. So I have set it all up and given both machine static IP addresses.

It now as Host % schema % and I have granted all privalges.

When I now try and connect I get a new error message

Connection Failed [HY000][Mysql][ODBC 5.1 driver]Host 'kir-wks-007' is not allowed to connect to Mysql server.

Its a new one but not sure how to cure it, any ideas please, many thsnks

 

[vacunita]

You need to set the Host to be allowed to actually connect to MYSQL.

From the Server Access Management Tab, Click on "Add Account" On the right side, type in the user with any password it has and set the "Limit connectivity..." option to again "%".

Hit apply, and try to connect again.




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[madvalpreston]

Hi Vacunita

You are a star, I put % with the sa login in the Limit Connectivity and it now conncects perfectly.

I assume now if I do the same on the computers at work I will get the same result. I will give this a go tomorrow.

Many thsnks to yourself and others for your patience and brilliant advice.

Thanks

 

[vacunita]

Should work the same yes. Glad its working now.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[madvalpreston]

Hi Vacunita

I have run into problems now with the server on the works machines. I made the changes as per my laptops but now I am getting this error

queryerror executing 'access denied;you need (at least one of) the PROCESS privileges for this operation SHOW PROCESSLIST SQL error:1227

How do I get the process or privileges working again in workbench. This happens when I try and go into Server Administration.

Thanks

 

[vacunita]

It Seems the user you are using to connect through the Workbench does not have the appropriate privileges. In Particular the Process privilege.

Usually for administrative connections through the Workbench you should be using an Admin user like root, or some other user with elevated privileges so the user may engage in administrative tasks.

You'll need a user like root to grant the privilege to the user.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[madvalpreston]

Hi

The problem is I cannot get into the Server administration to do anything. I did have a user with SA and this usually loaded ok.

Any ideas how I can get into the server admin in the workbench.

I tried going in without services started , then started the server but it then cant add in any users. Also in this mode the user I had SA is not there.

Any ideas how I can fix this please....

Thanks

 

[vacunita]

You need an admin user, there's no other way around this.

Does your mysql installation have a root user? If so try using that if you know its password.




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[madvalpreston]

Hi

Sorry I am really confused here. I open the workbench and then click on the server admin, that is when i get the error message, before it just opened up.....

I am certain it was SA user and I beleive there was root. But how can I get the privileges back so i can open it..

Thanks

 

[vacunita]

Can you click on the Manage Server Instances option?

Whichever server instance you have configured make sure its using a user with adequate privileges.

You can also modify the Connections to make use of a root user.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[madvalpreston]

Hi

I have opened up the Manage server instances and not sure where to go from there.
It shows my user in the connection: but other than that I cannot see anything else to do with privileges.

Also not sure how to set it so it uses Root. Could you clarify.

Thanks