Unable to connect to CloudSQL instance using IP address if root password was set when "Only allow SSL connections" was enabled.

[GoogleCodeExporter]

What steps will reproduce the problem?

Assuming that the instance has a IP associated with it and the appropriate 
network authorization (for connecting from) is in place.

1. Go to the "Access Control" page for the CloudSQL instance in the management 
UI.
2. Check "Only allow SSL connections" on the instance.
3. Set the root password.
4. Restart the instance.
5. Un-check the "Only allow SSL connections" on the instance.
6. Users not able to connect to the instance using the IP address with user = 
root.

Original issue reported on code.google.com by adityam...@google.com on 10 Apr 2014 at 6:36

[GoogleCodeExporter]

Note: step 4 and 5 are switched.

So the steps to reproduce this are:

1. Go to the "Access Control" page for the CloudSQL instance in the management 
UI.
2. Check "Only allow SSL connections" on the instance.
3. Set the root password.
4. Un-check the "Only allow SSL connections" on the instance.
5. Restart the instance.
6. Users not able to connect to the instance using the IP address with user = 
root.

Original comment by razv...@google.com on 10 Apr 2014 at 6:53

[GoogleCodeExporter]

Original comment by razv...@google.com on 10 Apr 2014 at 8:51

• Changed state: PendingRelease

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

The fix for this will hit production sometime this week. Sorry for the pain. :-(

The window that shows the when a new client certificate is generated suggests 
names for the files that need to be downloaded and how to run the mysql client 
using those.

Example: http://imgur.com/wNRTrSb

Are you looking for something more than that?

Original comment by razv...@google.com on 22 Apr 2014 at 9:44

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

If you create a user with 'REQUIRE X509' the mysqld will accept connections for 
that user only using SSL certs. Note that you will require to restart the 
instance to make the new certs available to mysqld.

Reference: http://dev.mysql.com/doc/refman/5.5/en/grant.html

Original comment by razv...@google.com on 22 Apr 2014 at 9:59

[GoogleCodeExporter]

Here we go again ...

I've spent the last eight hours trying to figure out why my GCE VM instance
will not connect to the Cloud SQL database.

I've read all the documentation on the web I could find.

I CAN connect via ssh command line with:

mysql --host=CLOUDSQLiP --user=root --password

phpMyAdmin will NOT.  ... and I've tried every configuration change I could
find.

All I get is:

#2003 Cannot log in to the MySQL server

Am I fighting some bug again?

Thanks!!

Original comment by g...@vitalelement.com on 23 Apr 2014 at 4:41

[GoogleCodeExporter]

This bug will not prevent phpMyAdmin to work if the 'mysql --host=CLOUDSQLiP 
--user=root --password' from the same machine works. Can check using 'SELECT 
host,user,ssl_type FROM mysql.user' that the ssl_type is empty for the root@%?

Original comment by razv...@google.com on 23 Apr 2014 at 4:52

[GoogleCodeExporter]

Shows this when logging into Cloud SQL via GCE instance:

mysql> SELECT user,host,ssl_type FROM mysql.user;

+------+-----------+----------+

| user | host      | ssl_type |

+------+-----------+----------+

| root | localhost |          |

| root | 127.0.0.1 |          |

| root | ::1       |          |

|      | localhost |          |

| root | %         |          |

+------+-----------+----------+

5 rows in set (0.01 sec)

mysql>

Original comment by g...@vitalelement.com on 23 Apr 2014 at 6:42

[GoogleCodeExporter]

One more question: what does the "SHOW GRANTS FOR root@'%'" says?

Original comment by razv...@google.com on 23 Apr 2014 at 6:55

[GoogleCodeExporter]

mysql> SHOW GRANTS FOR 'root'@'%';
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------+
| Grants for root@%

                                           |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY
TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE
VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT,
TRIGGER, CREATE TABLESPACE ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD
'*LMNORDDO948598KLJLKIO9847049899879849898IUEO' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------+
1 row in set (0.00 sec)

mysql>

Original comment by g...@vitalelement.com on 23 Apr 2014 at 7:00

[GoogleCodeExporter]

Maybe Google Cloud SQL is too much of a Rube Goldberg machine at this point
and simply running MySQL from the local Google Compute Engine VM is really
the only way.    ??

Original comment by g...@vitalelement.com on 23 Apr 2014 at 7:06

[GoogleCodeExporter]

We're finding that by default GCE's / Google Compute Engine's have everything 
turned off.  It was the case for httpd being able to send mail and other stuff.

In the case of being able to connect your GCE to your Google Cloud SQL, you'll 
need to run this as SU at the command line:

setsebool httpd_can_network_connect_db=1

Not sure if that will survive a server reboot, but it will solve circumstances 
where you can connect to your remote Cloud SQL database via the command line, 
but are NOT able to connect via phpmyadmin.  If you've followed all the 
instructions regarding connecting to Cloud SQL via GCE instance, but when 
trying from a properly configured phpmyadmin install on your GCE, you still get 
errors like:

mysql_connect(): Can't connect to remote MySQL server

and

#2003 Cannot log in to the MySQL server

Do this and you'll likely be golden.

setsebool httpd_can_network_connect_db=1

Original comment by g...@vitalelement.com on 24 Apr 2014 at 12:46

[GoogleCodeExporter]

Note: the fix for the original issue is in prod now.

Original comment by razv...@google.com on 25 Apr 2014 at 3:01

• Changed state: Fixed

[GoogleCodeExporter]

Great!  So now users can turn on/off 'force SSL' all day long in the Cloud
SQL console?

Original comment by g...@vitalelement.com on 25 Apr 2014 at 3:03

[GoogleCodeExporter]

Yes, turning on/off the forcing of SSL should work properly now but note that a 
restart is still required after a change.

Original comment by razv...@google.com on 25 Apr 2014 at 3:20

[GoogleCodeExporter]

I'm not sure if this is resolved. I just experience this same issue.

Original comment by esha...@netpremacy.com on 22 Jul 2014 at 3:15

[GoogleCodeExporter]

Were you able to reproduce this using a new instance? If you set the root 
password while the 'Force SSL' was on before the fixed then the mysql.user 
table have the 'REQUIRED X509' flipped for root@'%'. You will have to manually 
update remove that.

Original comment by razv...@google.com on 22 Jul 2014 at 3:39