custom_dc: Lost connection to MySQL server during query

[bkosuru]

trafficstars

Created MySQL in GCP as described in https://github.com/datacommonsorg/website/tree/master/custom_dc#setup-google-cloud-sql Also, specified the flags

net_read_timeout 300 net_write_timeout 300 wait_timeout 120 connect_timeout 300 interactive_timeout 300 max_connections 1000

Datacommons is able to connect but I am getting the error when I try to load the sample data - 'Lost connection to MySQL server during query'

I0313 12:24:40.040244 140711787871104 filehandler.py:118] Using GCS project: myproject Using GCS project: myproject I0313 12:24:40.870187 140711787871104 runner.py:108] Using Cloud SQL settings from env. Using Cloud SQL settings from env. I0313 12:24:40.873133 140711787871104 db.py:317] Connecting to Cloud MySQL: myproject:us-central1:dc-graph (datacommons) Connecting to Cloud MySQL: myproject:us-central1:dc-graph (datacommons) Traceback (most recent call last): File "", line 198, in _run_module_as_main File "", line 88, in _run_code File "/workspace/import/simple/stats/main.py", line 82, in app.run(main) File "/usr/local/lib/python3.11/site-packages/absl/app.py", line 308, in run _run_main(main, args) File "/usr/local/lib/python3.11/site-packages/absl/app.py", line 254, in _run_main sys.exit(main(argv)) ^^^^^^^^^^ File "/workspace/import/simple/stats/main.py", line 78, in main _run() File "/workspace/import/simple/stats/main.py", line 64, in _run Runner(config_file=FLAGS.config_file, File "/workspace/import/simple/stats/runner.py", line 118, in init self.db = create_db(_get_db_config()) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/workspace/import/simple/stats/db.py", line 373, in create_db return SqlDb(config) ^^^^^^^^^^^^^ File "/workspace/import/simple/stats/db.py", line 195, in init self.engine = create_db_engine(config) ^^^^^^^^^^^^^^^^^^^^^^^^ File "/workspace/import/simple/stats/db.py", line 362, in create_db_engine return CloudSqlDbEngine(db_params) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/workspace/import/simple/stats/db.py", line 319, in init self.connection: Connection = connector.connect( ^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/google/cloud/sql/connector/connector.py", line 163, in connect return connect_task.result() ^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/concurrent/futures/_base.py", line 456, in result return self.__get_result() ^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/concurrent/futures/_base.py", line 401, in __get_result raise self._exception File "/usr/local/lib/python3.11/site-packages/google/cloud/sql/connector/connector.py", line 279, in connect_async return await self._loop.run_in_executor(None, connect_partial) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/concurrent/futures/thread.py", line 58, in run result = self.fn(*self.args, **self.kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/google/cloud/sql/connector/pymysql.py", line 62, in connect conn.connect(sock) File "/usr/local/lib/python3.11/site-packages/pymysql/connections.py", line 692, in connect self.autocommit(self.autocommit_mode) File "/usr/local/lib/python3.11/site-packages/pymysql/connections.py", line 442, in autocommit self._send_autocommit_mode() File "/usr/local/lib/python3.11/site-packages/pymysql/connections.py", line 463, in _send_autocommit_mode self._read_ok_packet() File "/usr/local/lib/python3.11/site-packages/pymysql/connections.py", line 448, in _read_ok_packet pkt = self._read_packet() ^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/pymysql/connections.py", line 739, in _read_packet packet_header = self._read_bytes(4) ^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/pymysql/connections.py", line 795, in _read_bytes raise err.OperationalError( pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

[keyurva]

Hi @bkosuru - can you tell us a bit more about your import and how long after you initiate the load process do you see this error?

Note that when loading data, we open a single connection to insert data into the database. With that in mind, a couple of things to try:

• try loading a smaller dataset first
• assuming that succeeds, bump up the wait_timeout and try with a larger dataset.

[bkosuru]

Hi @keyurva, I am only trying to load the sample data provided for custom_dc. It is a very small dataset. The error happens right away within a second. Do I need to set any other flags or change current settings? Thanks!

[keyurva]

Actually, looking at the logs closely, it seems like it was never able to connect to the database. Can you ensure your connection parameters are correct?

[bkosuru]

Sometimes it connects and it created 2 tables - triples and observations.

I0311 16:47:51.725041 139931865762688 db.py:317] Connecting to Cloud MySQL: myproject:us-central1:dc-graph (datacommons) Connecting to Cloud MySQL: myproject:us-central1:dc-graph (datacommons) I0311 16:47:52.873222 139931865762688 db.py:321] Connected to Cloud MySQL: myproject:us-central1:dc-graph (datacommons) Connected to Cloud MySQL: myproject:us-central1:dc-graph (datacommons)

[keyurva]

It seems to be a DB connectivity issue. Based on a quick search for the error ("Lost connection to MySQL server during query"), you may need to tweak certain timeouts.