web2py uses deprecated driver to connect to Google Cloud SQL

[Karoly Kantor]

I have been unable to connect to a 2nd generation Google SQL instance for quite a time now. This is painful as 1st generation instances are less capable and declared "legacy" by Google.

I suspect it may have to do with web2py using a deprecated driver "rdbms", e.g. in google_adapters.py. According to Google, this has been long deprecated and "future versions of Cloud SQL will not be supported by the legacy drivers" - a quote from 2015.

Read here:

https://groups.google.com/forum/#!topic/google-cloud-sql-announce/4m3_GPhm444

In fact, I have tried to hack google_adapters.py to use the Google recommended MySQLdb driver instead of the old rdbms. I also had to change some of the args to it. Suddenly, I was able to connect to the 2nd generation SQL instance, only to fail a little later with 

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-prod' at line 1")

This is where my current knowledge stops, I am hoping some insider could help.

Thank you. 

[Karoly Kantor]

In the meantime, I have solved the syntax error above (there was a "-" in the data base name...), and I have successfully connected to a 2nd generation Google SQL instance !!!!

To do this, i had to hack pyDAL. I would appreciate if someone could look at what I have done, and tell me if this is a viable long term solution, or i will be running into some problems later. (I have not fully tested my app on the new DB yet.)

So I have added this to app.yaml:

env_variables:

    CLOUDSQL_USER: root

    CLOUDSQL_PASSWORD: password

    CLOUDSQL_CONNECTION_NAME: myproject:europe-west1:myinstance

    CLOUDSQL_DATABASE: mydbname

    

libraries:

- name: MySQLdb

  version: "latest" 

Then I changed google_adapters.py :

import MySQLdb

class GoogleSQLAdapter(UseDatabaseStoredFile, MySQLAdapter):

    uploads_in_blob = True

    REGEX_URI = re.compile('^(?P<instance>.*)/(?P<db>.*)$')

    def clear_cache(self):

        ndb.get_context().clear_cache()

    def ignore_cache_for(self, entities = None):

        entities = entities or []

        ndb.get_context().set_cache_policy(lambda key: key.kind() not in entities)

    def __init__(self, db, uri='google:sql://realm:domain/database',

                 pool_size=0, folder=None, db_codec='UTF-8',

                 credential_decoder=IDENTITY, driver_args={},

                 adapter_args={}, do_connect=True, after_connection=None):

        self.db = db

        self.dbengine = "mysql"

        self.uri = uri

        self.pool_size = pool_size

        self.db_codec = db_codec

        self._after_connection = after_connection

        if do_connect: self.find_driver(adapter_args, uri)

        self.folder = folder or pjoin('$HOME',THREAD_LOCAL.folder.split(

                os.sep+'applications'+os.sep,1)[1])

        ruri = uri.split("://")[1]

        m = self.REGEX_URI.match(ruri)

        if not m:

            raise SyntaxError("Invalid URI string in SQLDB: %s" % self.uri)

        instance = credential_decoder(m.group('instance'))

        self.dbstring = db = credential_decoder(m.group('db'))

        driver_args['instance'] = instance

        

        #KK's addition to attempt fix 2nd gen problem with missing user name and password:

        driver_args['user'] = os.environ.get('CLOUDSQL_USER')

        driver_args['passwd'] = os.environ.get('CLOUDSQL_PASSWORD') 

        driver_args['unix_socket'] = os.path.join('/cloudsql', os.environ.get('CLOUDSQL_CONNECTION_NAME')) 

        del driver_args['instance']

        #driver_args['database'] = os.environ.get('CLOUDSQL_DATABASE')       

        #END KK

        

        if not 'charset' in driver_args:

            driver_args['charset'] = 'utf8'

        self.createdb = createdb = adapter_args.get('createdb',True)

        if not createdb:

            driver_args['database'] = db

            

        #KK added this for debug:

        print "DEBUG: driver args = " + str(driver_args) 

        print "DEBUG: self.dbstring = " + str(self.dbstring)   

        #END KK    

        

        #KK changed rdbms to MySQLdb:    

        def connector(driver_args=driver_args):

            return MySQLdb.connect(**driver_args)

        self.connector = connector

        if do_connect: self.reconnect()

    def after_connection(self):

        if self.createdb:

            # self.execute('DROP DATABASE %s' % self.dbstring)

            self.execute('CREATE DATABASE IF NOT EXISTS %s' % self.dbstring)

            self.execute('USE %s' % self.dbstring)

        self.execute("SET FOREIGN_KEY_CHECKS=1;")

        self.execute("SET sql_mode='NO_BACKSLASH_ESCAPES';")

    def execute(self, command, *a, **b):

        return self.log_execute(command.decode('utf8'), *a, **b)

    def find_driver(self,adapter_args,uri=None):

        self.adapter_args = adapter_args

        self.driver = "google"