0

I am trying to deploy a python code with google cloud funtions and scheduler that writes a simple table to a google postgresql cloud database.

  1. I created a postgre sql database
  2. Added a Cloud SQL client role to the App Engine default service account
  3. Created a Cloud Pub/Sup topic
  4. Created a scheduler job.

So far so good. Then I created the following function main.py :

from sqlalchemy import create_engine
import pandas as pd
from google.cloud.sql.connector import Connector
import sqlalchemy

connector = Connector()

def getconn():
    conn = connector.connect(
        INSTANCE_CONNECTION_NAME,
        "pg8000",
        user=DB_USER,
        password=DB_PASS,
        db=DB_NAME
    )
    return conn

pool = sqlalchemy.create_engine(
    "postgresql+pg8000://",
    creator=getconn,
)

def testdf(event,context):

    df=pd.DataFrame({"a" : [1,2,3,4,5],
    "b" : [1,2,3,4,5]})

    df.to_sql("test",
          con = pool,
          if_exists ="replace",
          schema = 'myschema')

And the requirements.txt contains:
pandas
sqlalchemy
pg8000
cloud-sql-python-connector[pg8000]

When I test the function it always timeout. No error, just these logs: enter image description here

I cant figure it out why. I have tried several code snippets from:
https://colab.research.google.com/github/GoogleCloudPlatform/cloud-sql-python-connector/blob/main/samples/notebooks/postgres_python_connector.ipynb#scrollTo=UzHaM-6TXO8h

and from

https://codelabs.developers.google.com/codelabs/connecting-to-cloud-sql-with-cloud-functions#2

I think the adjustments of the permissions and roles cause the timeout. Any ideas?

Thx

dkantor
  • 162
  • 6
  • can you refer to this [thread](https://stackoverflow.com/q/71507095/15774176) and [this](https://stackoverflow.com/q/64100523/15774176) is it helpful? – Divyani Yadav Aug 22 '22 at 12:09
  • I've already tried these methods, without success. – dkantor Aug 22 '22 at 13:40
  • can you share the error that it returns and what you see in logs? – Divyani Yadav Aug 23 '22 at 07:09
  • 1
    i've updated the original post. – dkantor Aug 23 '22 at 07:33
  • can you explain "adjustments of the permissions and roles cause the timeout"..why you think so, it will be helpful in troubleshooting. – Divyani Yadav Aug 24 '22 at 11:35
  • Everything is fine when I run a function without clousql connection. The script works on a local environment. So that is the reason why I think the timeout is connected to the permissions and roles. Another interesting thing: On the IAM page I added a Cloud SQL Client role to the App engine default service account, but on the Functions page under permissions it doesn't show the Cloud SQL Client role of the App engine default service account. My database and function are placed in the same project. – dkantor Aug 24 '22 at 15:32
  • can you tell the type of IP address you assigned to your Cloud SQL instance ? your cloud function is gen1 or gen2 ? and have you tried using [Cloud SQL Auth proxy](https://cloud.google.com/sql/docs/mysql/sql-proxy) ? – Divyani Yadav Aug 26 '22 at 08:11

0 Answers0