Can't run syncdb on Google CloudSQL

2
votes

I'm trying to port a django 1.3 application to AppEngine + Google Cloud SQL. But I'm having some problems at connecting to my cloudSQL instance from local development django.

I can connect to CloudSQL just fine using dbshell. It shows the browser authentication and I'm dropped at the prompt.

(ENV)$ SETTINGS_MODE=prod ./manage.py dbshell
Google SQL Client

Type "help" or "?" for help.

Connecting to Google SQL database "my_project:prod1|my_db" on host None.
Using readline for history management.
Loading history file "/Users/user/.googlesql/django_proj#prod1.hist"
sql> SELECT CURRENT_USER();
Execution time: 0.694 seconds
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+

I think it's strange that dbshell actually seem to run as the root user, even though I have no root password on my settings.py file. But still, it works.

It fails when I try to run syncdb. 

(ENV)$ SETTINGS_MODE=prod ./manage.py syncdb
Traceback (most recent call last):
  File "./manage.py", line 11, in <module>
    execute_manager(settings)
  File "/usr/local/google_appengine/lib/django-1.3/django/core/management/__init__.py", line 438, in execute_manager
    utility.execute()
  File "/usr/local/google_appengine/lib/django-1.3/django/core/management/__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/google_appengine/lib/django-1.3/django/core/management/base.py", line 191, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/local/google_appengine/lib/django-1.3/django/core/management/base.py", line 220, in execute
    output = self.handle(*args, **options)
  File "/usr/local/google_appengine/lib/django-1.3/django/core/management/base.py", line 351, in handle
    return self.handle_noargs(**options)
  File "/usr/local/google_appengine/lib/django-1.3/django/core/management/commands/syncdb.py", line 56, in handle_noargs
    cursor = connection.cursor()
  File "/usr/local/google_appengine/lib/django-1.3/django/db/backends/__init__.py", line 252, in cursor
    cursor = util.CursorWrapper(self._cursor(), self)
  File "/usr/local/google_appengine/google/storage/speckle/python/django/backend/base.py", line 275, in _cursor
    self.connection = Connect(**kwargs)
  File "/usr/local/google_appengine/google/storage/speckle/python/django/backend/base.py", line 200, in Connect
    return driver.connect(**kwargs)
  File "/usr/local/google_appengine/google/storage/speckle/python/api/rdbms_googleapi.py", line 183, in __init__
    super(GoogleApiConnection, self).__init__(*args, **kwargs)
  File "/usr/local/google_appengine/google/storage/speckle/python/api/rdbms.py", line 812, in __init__
    self.OpenConnection()
  File "/usr/local/google_appengine/google/storage/speckle/python/api/rdbms.py", line 835, in OpenConnection
    response = self.MakeRequest('OpenConnection', request)
  File "/usr/local/google_appengine/google/storage/speckle/python/api/rdbms.py", line 946, in MakeRequest
    raise _ToDbApiException(response.sql_exception)
google.storage.speckle.python.api.rdbms.OperationalError: (1045, u"Access denied for user 'my_prod_user'@'localhost' (using password: YES)")

In this case you can see that it tries to user my real user, not root. And it fails with Access Denied.

If I try to connect directly to cloud SQL using my_prod_user it works just fine.

$ mysql -h XXX.XXX.XXX.XXX -u my_prod_user -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.36 (Google)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT CURRENT_USER();
+-----------------+
| CURRENT_USER()  |
+-----------------+
| my_prod_user@% |
+-----------------+
1 row in set (0.19 sec)

Have anyone run into this?

2
djangogoogle-cloud-sql

2 Answers

1
votes

My problem was the way I was setting my settings.py file. Seems like that when accessing your cloudSQL database through your local machine you HAVE to use the user root. Also the password locally don't seem to make any difference, makes sens since it probably just uses oAuth tokens to create the connection.

For future reference here's my working settings for accessing CloudSQL from local development machine.

DATABASES = {
  'default': {
    'ENGINE': 'google.appengine.ext.django.backends.rdbms',
    'INSTANCE': 'project-name:instance',
    'NAME': 'my_db',
    'USER': 'root',
   }
 }

And here is the broken one I was using previously. Notice how I have passwords inlined. Silly me.

DATABASES = {
  'default': {
    'ENGINE': 'google.appengine.ext.django.backends.rdbms',
    'INSTANCE': 'project-name:instance',
    'NAME': 'my_db',
    'USER': 'my_prod_user',
    'PASSWORD' : 'myVerySecretPassword',
   }
 }

I suppose Google hates passwords. I'm glad I don't need these hardcoded anymore in my App.

0
votes

With Django==1.10.4, here is the setting I used to connect. Note the HOST and password.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'HOST': 'ip_address',
        'NAME': 'db_name',
        'USER': 'root',
        'PASSWORD': 'password'
    }
}

And the new command is:

SETTINGS_MODE=prod python manage.py migrate