General error: 2006 MySQL server has gone away. All settings fine

Question

I have 2 linux servers. 1 is running apache2 with an PHP application and the other one is running mysql 5.7. (so a remote connection to DB)

PHP applications tried to create a BIGBLOB from a file (90MB) and store it into SQL.

But i'm getting this errors: (This is happening only on a BIGGER BLOB) PHP:

[PDOException] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

MYSQL:

2022-03-24T14:22:41.443626Z 268701 [Note] Aborted connection 268701 to db: 'bug' user: 'user' host: 'subdomain.hostname.com' (Got an error reading communication packets)

I searched the hole internet. I did the following updating: max_allowed_packet to 1GB, i checked: wait_timeout and interactive_timeout they are fine (28880 seconds) I added SWAP memory. Nothing is working. Any ideas?

P.S: Seems connetion is getting cut somewhere after 30 seconds. but i can't tell for sure and why.

This are the MySQL settings:

mysql> show global variables like "%timeout%";
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 120      |
| net_write_timeout           | 120      |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+

 max_allowed_packet                                   | 1073741824           |

RAM:

              total        used        free      shared  buff/cache   available
Mem:           7976        1056         256           1        6663        6630
Swap:         10239           8       10231

Answer 1

"Gone away" is usually caused by some really long query that exceeds some setting. However, this smells like a different problem. The default size for reading a row is only a few MB. Furthermore, I think there is a hard limit of 16MB. That is, raising the "..._size" won't suffice.

What will you be doing with a 90MB BLOB? Consider leaving it in a file, and put meta information in the database. Even for MB-sized jpegs, that is more practical and more efficient than shoveling into a database.

Discuss the BLOB further, plus provide SHOW CREATE TABLE and some of the queries that you plan to use. Then I may have further advice.

Answer 2

I facing the same situation, My "my.ini" file are also correct but my query is not running only show " SQLSTATE[HY000] [2006] MySQL server has gone away ". How to solve on local server.