Member Avatar for sjgriffiths

hello
I am inserting records into a MYSQL table via a loop

Records are being inserted, but every now and then the program bombs out with the below message

There is nothing wrong with the query line though (If i restart the job, this record will be inserted without issue)

Help is much appreciated

Unable to execute query INSERT INTO `shmosis`.`shmosis` (companyname, Addressline1, Addressline2, Addressline3, Addressline4, Postcode,BicName, ContactName, ContactTelephone, lastcontacted,established,txtDesc,Email,MobileNumber,StaffNumber,Title,Forename,Surname,FaxNumber,BicSubCodeNew,Ref ) VALUES ("Robertson Northern","Buckie High School","West Cathcart Street","","Aberdeen","AB56 1QB","Trade and Building > Sites > Buildings","Mr Skene","01542839198","2009-11-11 16:46:37","Under","construction site","","","20-49","Mr","","Skene","","10401","40090923181621407045")
Traceback (most recent call last):
File "./", line 263, in <module>
db.queryNoReturn(dbquery)
File "./", line 74, in queryNoReturn
self.disconnect()
File "./", line 64, in disconnect
self._conn.close()
_mysql_exceptions.ProgrammingError: closing a closed connection

  • 3 Contributors
  • 6 Replies
  • 198 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by woooee

Dani AI

Generated

Intermittent _mysql_exceptions.ProgrammingError: “closing a closed connection” during a looped INSERT. Since the same INSERT runs fine from the MySQL console and succeeds after a job restart (as reported by ), a SQL-level problem like a unique-key violation is unlikely — that was suggested earlier by . The traceback and ’s comment point toward the connection/cleanup code as the real culprit.

Likely causes: the disconnect/close path is being invoked more than once (for example in both an exception handler and a finally/cleanup block); the server or network drops the connection partway through the job (MySQL wait_timeout/network blips) and a subsequent client-side close raises ProgrammingError; or a shared connection is being closed from another thread.

Actionable, practical fixes:

  • Centralize and harden disconnect logic so the connection is cleared exactly once. Wrap close in a small “safe disconnect” wrapper that catches the driver’s ProgrammingError/InterfaceError and sets the connection attribute to None.
  • Keep a single open connection for the insert loop instead of opening/closing per row. Commit in batches (for example every 100–1000 rows) rather than committing each insert. This reduces load and timing windows that expose races/timeouts.
  • Add reconnection/retry logic: on OperationalError/InterfaceError attempt to re-open the connection and retry the failed statement once.
  • If the app is multithreaded, ensure each thread uses its own connection or use a connection pool.
  • Add concise logging around connect/close/exception paths and inspect MySQL server logs and the server’s wait_timeout setting for signs of server-side disconnects.

These steps address the common root causes indicated by the traceback and the replies in the thread; implementing them should stop the intermittent “closing a closed connection” failures.

Recommended Answers

All 6 Replies

Member Avatar for baki100

try running the insert query in the mysql console and see what happens

Member Avatar for sjgriffiths

It works fine, this is what i am saying (Is this a timeout issue)?

Thanks

Member Avatar for baki100

what happens if that record you have on top gets inserted twice.

Member Avatar for woooee

File "./", line 64, in disconnect
self._conn.close()
_mysql_exceptions.ProgrammingError: closing a closed connection

Why are you closing a connection (twice) for a db that you are adding records to. How about some code. Also, you do not have to commit after every add, so wait until after you have added x records, which may also be part of the problem. Also, it could be a log file that is full. The workaround is to add some number of records, then close the db, open it again, add some more records, etc. but that is only if all else fails. Please post any solutions you find as this is the kind of thing that can waste hours trying to track down.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.