how to increase max_allowed_packet size without restart the server

Question

Hi i am using codeigniter framework. I am running one query and request data from mysql. I have lot of data. It is running but gives the error as below.

A PHP Error was encountered Severity: Warning

Message: Error while sending QUERY packet. PID=182364

Filename: mysqli/mysqli_driver.php

Line Number: 305

I have asked my service provider to increase max_allowed_packet size. Previously I've 245M. they increased it 500M. But there are problems with restarting the server. So, i want to increase it without restarting the server. Is it possible?. If it is possible please suggest how to set and where to set.

You can use `ini_set( string $varname , string $newvalue )` function to override the `php.ini` settings in your php file or you can define you changes in `.htaccess` file. [Click here to see](https://stackoverflow.com/a/8812260/3453169) — Manav, Jun 30 '18 at 11:12
But why you want to add this in function.?.... simply define this in starting of you php file. — Manav, Jun 30 '18 at 11:17
I added it in starting of my php file. But is is not working. — Mythili, Jun 30 '18 at 11:22
I am using codeigniter framework. I added my function in controller, so,please tell me where i can add the line which you are posted? — Mythili, Jun 30 '18 at 11:26
it is saying #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation — Mythili, Jun 30 '18 at 11:41
Any other solution please not with SET GLOBAL max_allowed_packet=1073741824; Because i don't have permissions to change.I tried with ini_set also. It did not work for me. — Mythili, Jul 02 '18 at 12:16

score 2 · Answer 1 · answered Jun 30 '18 at 11:21

2

max_allowed_packet This server variable can be set globally by running a query.

However, if you do not change it in the my.ini/my.cnf file, the value will reset when the server restarts, even if you set it globally.

To change the setting for everyone until the server restarts:

SET GLOBAL max_allowed_packet=1073741824;

answered Jun 30 '18 at 11:21

AbdulAhmad Matin

1,047
1
18
27

I am using codeigniter framework. I added my function in controller, so,please tell me where i can add above line? – Mythili Jun 30 '18 at 11:25
is your project in local server or online server? – AbdulAhmad Matin Jun 30 '18 at 11:27
It is online server, Yes i can access cpanel – Mythili Jun 30 '18 at 11:29
Is it effect any another tables? It is online so, My clients are currently working now. – Mythili Jun 30 '18 at 11:33
1

it does effect to all mysql not only one table. – AbdulAhmad Matin Jun 30 '18 at 11:34
it is saying #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation – Mythili Jun 30 '18 at 11:41
your user don't have permission you must have all privilege of super user? – AbdulAhmad Matin Jun 30 '18 at 11:44

how to increase max_allowed_packet size without restart the server

1 Answers1