mysqli::real_connect(): MySQL server has gone away

[eranhazout]

Hi guys

Since upgrading from CodeIgniter 2.2 to 3.1, I keep receiving this error.
I know that many of you consider this as an ideal PHP problem or behavior when doing some heavy work before executing SQL statement, but I see that I'm not the only one with this problem.
If you google this problem, you will see that most of the problems are related to CodeIgniter.
As I'm working on a huge project built on Codeigniter, I don't think that using a recoonect() function before any SQL query should be the solution.
I think we should improve the MySQL driver to ping/reconnect automatically before executing SQL query.
I move to pconnect for now and it seems like a better solution for now.
Do any of you guys have a good solution for this problem?

btw, I'm not complaining. Codeigniter is AWESOME!!!

[narfbg]

I think we should improve the MySQL driver to ping/reconnect automatically before executing SQL query.

An additional function call prior to every single query sounds really bad to me.

Do any of you guys have a good solution for this problem?

Please note that this is a bug tracker, not a support ticketing system or a forum board.

[eranhazout]

I know, I consider it as a bug because I have this problem only since I moved from 2.2 to 3.1.
I'm testing a new configuration,
pconnect = FALSE
ini_set('mysqli.reconnect',1)

[narfbg]

If that works, yes - it's an acceptable solution, just a single call when the driver is loaded. But it only applied to libmysql.

The setting is ignored if you're using MySQL ND (native driver), and for good reasons; see https://bugs.php.net/bug.php?id=52561

Not sure what to do if the problem exists for mysqlnd ...

[eranhazout]

I hope :)
My QA team is already testing this solution right now.
I will update soon.

[eranhazout]

Hi to all of you who's run into "MySQL server has gone away" problem.
Since PHP7 mysqli.reconnect option is off by default so if you have long running scripts they can lose connection to mysql.
The only option to change it is by modifying your main php.ini file: "mysqli.reconnect On" and restart PHP 7.
So this is not a CodeIgniter bug (sorry for the mistake ;)
But I still think we can improve the mysqli driver to reconnect automatically on connection error.

[narfbg]

When I said this:

The setting is ignored if you're using MySQL ND (native driver), and for good reasons; see https://bugs.php.net/bug.php?id=52561

I meant the last (as of right now) comment in that bug report; i.e.:

It is not implemented because reconnect creates a new connection which has new state and the state of the old connection is gone - session variables, transaction. In this context automatic reconnect is considered dangerous and is disabled since years in libmysql. mysqlnd doesn't provide this for the very same reason.

I understand how that may seem harmless to some people, but broken transactions are a horrible thing and "session variables" includes things like character set, sql_mode, ssl verification - all things that CI enforced and would be lost during an automatic reconnect, most of them having security implications.

I'd suggest looking for another solution.