Member Avatar for Dani

I was consistently receiving MySQL "Packets out of order" error messages when attempting to connect to MySQL, mostly on my AJAX pages, but I couldn't attribute it to anything or reproduce it in any way. I did notice, however, that when switching MySQL persistent connections from on to off, the problem went away.

Lots of Google searches later, I found lots of articles and forum posts saying that it was an issue with the my.cnf configuration setting, and to tweak max_connections, etc. Then our sysadmin found an article saying it was an issue with the version of MySQL + PHP we were using together. There apparently are lots of causes for this, and the fact that it wasn't reproducible made it all the more difficult to debug.

Well, I finally figured out what the cause was, and so I'd like to share it with you, at least in our case.

On Persistent Database Connections in the PHP manual there is a warning blurb with a red background that says that you should never use persistent connections if your script uses transactions.

Well, that was it! We do, indeed, use transactions. I've made an adjustment to no longer use persistent connections when using transactions, and the problem miraculously solved itself!

  • 4 Contributors
  • 5 Replies
  • 1K Views
  • 9 Months Discussion Span
  • Latest Post Latest Post by Haseeb_12
wwwalker commented: Why would you need persistent connection anyway unless there were a high volume of transactions? +3
Member Avatar for AndreRet

Very interesting read, never happened to me before but I heard about this type of issue when a server gets updated to run specific versions of PHP whilst your version gets outdated.

Glad you could solve this, must have been daunting at the time!

Member Avatar for Dani

Why would you need persistent connection anyway unless there were a high volume of transactions?

I foolishly had persistent connections enabled for all database connections here at DaniWeb, although I am using transactions when members make posts, etc. since it touches a few different denormalized tables simultaneously. A lot of AJAX requests were failing due to this MySQL Packets out of order error showing up in the error log, but it was never anything I could reproduce. I now have persistent connections disabled for all HTTP POST requests and the problem completely went away.

Member Avatar for wwwalker

If PHP locks the database connection in a persistent connection and the packets from the transaction are incomplete then the packets would be out of order. Hence asynchronous PHP connection would be OK for transactions and not have the locks that persistent connection would create.

Edited by wwwalker
Member Avatar for Dani

Sorry, I’m not quite sure I understand what you’re saying?

Member Avatar for Haseeb_12

Thanks for sharing this insight! Disabling persistent connections when using transactions is a great tip and saved me a lot of debugging time.

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.