Re: [google-cloud-sql-discuss] Trying to Connect to database in PHP using PDO and Cloud SQL Proxy

Vadim, 

1.so you were able to access the socket using fuse in PHP/PDO? Can you share your sample code?

2. Can you try it on centos 7 64 bit please?

3. We should probably provide some example systemd/upstart configs for running the proxy as a service. --> that will be great if you can share it

On Tuesday, March 15, 2016 at 2:18:29 PM UTC-4, Vadim Berezniker wrote:

Depends on how you are starting it. If you are starting it manually, killing the PID is probably the best way.

We should probably provide some example systemd/upstart configs for running the proxy as a service.

On Tue, Mar 15, 2016 at 5:17 AM <wob...@yblew.com> wrote:

To add to my previous post what is the right way to stop the socket? just kill the pid?

On Tuesday, March 15, 2016 at 6:21:14 AM UTC-4, wob...@yblew.com wrote:

I would like to help you get FUSE working on your setup, it is a nicer experience than listing instances on the command line. If you are able to get things to work with the '-instances' flag, I suggest replacing the '-instances' flag with '-fuse' and trying again (without changing your PHP app). What does the proxy write out when your PHP server tries to access the /cloudsql directory when '-fuse' is passed to the proxy?

I get "no file or directory" error. Is as if php/pdo need to see the file there. Question, I'm using CentOs 7. besides "yum install fuse", is there any packages I'm missing?

You don't have to create a user that has a hostname of `cloudsqlproxy~%`, but it is suggested that you create that user and use the user when using the proxy. It will decrease the chance of accidentally opening up your database to access that you didn't mean to. I can be more specific about an example of accidental misconfiguration if you want. 

can you give me an example :) I like to learn:)

I will do more tests and update you

On Tuesday, March 15, 2016 at 3:21:10 AM UTC-4, Kevin Malachowski wrote:

On Mon, Mar 14, 2016 at 11:42 PM, <wob...@yblew.com> wrote:

To your question, yes I'm replacing the ips and ports

1. So to confirm, using fuse with PDO is not advised right? When I set it up with fuse, I see a "README" file in my /cloudsql directory with error (no such file or directory) but when I set it up with "-instances" I see <your-project-id>:<the-region>:<your-instance-name> file in /cloudsql directory with a successful connection
   

I don't know if we can say that, yet. A teammate tried to reproduce your issue using FUSE with PDO (right, Vadim?), so it doesn't appear to be a specific issue with FUSE. The fact that you only see the README is not a bad thing; FUSE allows the proxy to create a file in that directory right when something else tries to access a file in that directory. Any file you try to access should appear to be there (try running `ls /cloudsql/asdfasdfasdfas`, it should show up as a symbolic link (but of course if you try to connect to that socket things won't work, as it isn't a correctly formatted name)). Therefore it is suspect to me that the error you got was 'no such file or directory', as (as far as I can remember) that should never happen unless something is misconfigured somehow.

Note that passing the '-instances' will cause the socket file to exist in the /cloudsql directory but doesn't necessarily mean that the connection works; you could put many things on the commandline and the proxy will open up a socket in that directory. It's just that when you try to connect to that unix socket things won't work.

I would like to help you get FUSE working on your setup, it is a nicer experience than listing instances on the command line. If you are able to get things to work with the '-instances' flag, I suggest replacing the '-instances' flag with '-fuse' and trying again (without changing your PHP app). What does the proxy write out when your PHP server tries to access the /cloudsql directory when '-fuse' is passed to the proxy?

 

1. Let's say I want access to multiple instances how will I achieve that?
   

'-instances' takes a comma-separated list of instances to connect to. And if you get -fuse to work then you will be able to connect to any number of instances just by attempting to connect to files in the /cloudsql directory.

1. When you say "Note that you don't specify the hostname when connecting to a Cloud SQL instance (using the proxy or not using the proxy), it is inferred by the database itself when a new connection comes in." what do you mean? Just a reminder that I'm running the proxy on a on premise server to connect to my instance in google cloud sql.
   

Sorry to confuse you, I was just providing extra info in case you didn't understand what a MySQL user's hostname meant (I don't know how much you know about MySQL, so I wanted to educate if I could!).

You don't have to create a user that has a hostname of `cloudsqlproxy~%`, but it is suggested that you create that user and use the user when using the proxy. It will decrease the chance of accidentally opening up your database to access that you didn't mean to. I can be more specific about an example of accidental misconfiguration if you want. 

1. How stable is the proxy  sql. Does it hang sometimes? Can I expect to run this on production?
   

If the proxy hangs, please file an issue on the github with specifics about how and when you encountered the issue. We have a prober that continually uses the proxy and that is very stable, but there's always interesting things that can go on in other people's setups that are hard to predict. As always, feel free to post on this forum or even email me directly with information that you don't want to be public.

1. When I create name_of_user@'cloudsqlproxy~% in mysql, I don't need to assign any privileges to the database I need right?
   

There are no special permissions that you need to give that user when using the proxy.

1. When I create name_of_user@'cloudsqlproxy~% is that the username with no password I use to connect to my db in my php code?
   

Yep! Only users with hostname of 'cloudsqlproxy~%' will always be safe without a password (this is why it's good to create such a user when using the proxy). A user with that hostname will not be accessible to entities connecting to the database that don't use the proxy.

Just a suggestion. I think the common name of the ssl certificate on the cloud sql instance should be a name with no colon; set of alphanumeric ASCII characters (a-z, A-Z, 0-9). Let me tell you why. When I tried to first access the db with PHP/PDO and ssl certificates, it failed peer validation which makes sense because you guys use CN and IP that does not match. If the CN was without colon maybe I can map the ip and the CN in my hosts file so that when peer validation is done, it can check my hosts file before it touches the DNS and pass. This would have been a quick fix instead of people moving to AWS RDS or waiting for PHP to implement CLIENT_SSL_DONT_VERIFY_SERVER_CERT. (https://bugs.php.net/bug.php?id=71003) 

+Easwar to comment on PHP verification of SSL certs, he knows more than I do. The friction of SSL certificate setup is the reason that we developed the Proxy: using it gives you all of the safety of SSL certs without having to go through the trouble of setting up SSL certificates. Ideally you'll be able to use the proxy instead of having to manually set up SSL certs.

Thanks your for help

On Monday, March 14, 2016 at 7:37:04 PM UTC-4, Kevin Malachowski wrote:

Just a quick question: are you actually swapping in your project id, the region of your instance, and the instances name? I can't tell if you're copy-pasting and manually redacting your project/region/instance (I agree, you shouldn't be posting this info on this public forum) or if you're literally trying to connect to  "<your-project-id>:<the-region>:<your-instance-name>".

Answers to your questions inline:

On Mon, Mar 14, 2016 at 4:05 PM, <wob...@yblew.com> wrote:

Kevin, I think it worked. Will do more testing and update you

2016/03/14 18:56:37 Got a connection for "<your-project-id>:<the-region>:<your-instance-name>"

2016/03/14 18:56:37 from "<your-project-id>:<the-region>:<your-instance-name>" via IP:3307: read tcp IP->IP:3307: use of closed network connection

But two things: 

1. What is really the purpose of CREATE USER name_of_user@'cloudsqlproxy~%';  in your documentation. I'm still a bit confused about it? I should be able to login using regular mysql username and password from php right Or I need to have that?

If you are using the proxy, you should create a user with a hostname 'cloudsqlproxy~%'; this user will only be available for connections that come in through the proxy. It is not strictly required but it is definitely suggested because it is safe to create this user without a password regardless of the firewall configuration of your instance.

Note that you don't specify the hostname when connecting to a Cloud SQL instance (using the proxy or not using the proxy), it is inferred by the database itself when a new connection comes in.

 

2.  How do I silent the print out from proxy and how can I keep the command running even after server reboot?

The proxy prints out its logs on standard error. You can redirect this output to a file named 'proxy.log' by doing something like this:

./cloud_sql_proxy    YOUR_OTHER_ARGUMENTS_HERE    2>proxy.log   &

(the '&' causes the process to run in the background)

On unix-like systems, you can cause the output to be ignored by redirecting it to the file located at '/dev/null'. I suggest writing the output to a file or somehow hooking into your system's logging infrastructure, though. It will be easier to debug problems you may encounter in the future when you have access to the logs that the proxy writes.

As for keeping the proxy up after a reboot, you can configure your system to run the proxy when the server starts up. Every system is different, so I can't give specific advice unless you give more info about your specific server. For example, can you find other services that run on startup that you can model after? I find that searching the internet for your operating system along with something along the lines of "run program on startup" yields good results.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/AsVdpcRF5gA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/6330a422-365f-4069-b2ff-f764e2b09371%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/99cf2b0d-8e18-4535-a479-31bc20979646%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/08f0e3c7-f9f5-439f-a174-ccad77c2f260%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.