0

I am working on my local set up of a web application and I have to fetch all the items in a table, but when I do select * from my_table, it gives me this errorERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes.

I have tried many options, some of which include:

-Setting max_allowed_packet in my \etc\mysql\mysql.conf.d\mysqld.cnf and \etc\mysql\mysql.conf.d\mysqldump.cnf(I am not using dump, but to be on safe side), to 1G then 2G etc.

-Also tried using set global net_buffer_length=1000000; and set global max_allowed_packet=1000000000; in mysql in terminal

I got a bit of success when I used mysql --max_allowed_packet=100M -u root - it gives all the data but the problem is it works only in the terminal, but I have to get data in my web application where it will not work.

The interesting thing is: I tried using limit, so when I use select * from my_table limit 200, it gives expected result(but 200 rows only). Total rows are approx 350, So the error is in data of higher rows than 200. To checkout size of row result I used following query: select sum(row_size) from ( select char_length(id)+ char_length(code)+ char_length(name)+ char_length(url) +char_length(group)+ char_length(template)+ char_length(html) + char_length(association) + char_length(status) + char_length(created_at)+ char_length(updated_at) + char_length(html_generation_date) as row_size from myTable limit 311,1) as tbl1; Which gives me: 43095 while for limit 150,1 above query gives: 94428. So row size doesn't seem to be a problem.

I tried solutions given on MySQL Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes and https://serverfault.com/questions/102564/error-2020-got-packet-bigger-than-max-allowed-packet-bytes-when-dumping-table.

Basically, I want to do something like this: myTable::model()->findAll(); in my PHP Yii app which is equivalent to select * from my_table and afterwards I can use the data to populate my list.

Table Schema is: +----------------------+--------------+------+-----+-------------------+-----------------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | code | varchar(150) | NO | | NULL | | | name | varchar(150) | NO | | NULL | | | url | varchar(255) | NO | | NULL | | | group | int(11) | NO | MUL | NULL | | | template | int(11) | NO | | NULL | | | html | longtext | YES | | NULL | | | association | varchar(100) | NO | | NULL | | | status | tinyint(1) | NO | | NULL | | | created_at | timestamp | NO | | CURRENT_TIMESTAMP | | | updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | html_generation_date | timestamp | YES | | NULL | |

And the mysql version is: 14.14 Distribution: 5.7.26. DB engine is InnoDB.

Any help will be highly appreciated.

Harish Kumar Saini
  • 362
  • 3
  • 20
  • *"i got a bit of success when I used mysql --max_allowed_packet=100M -u root - it gives all the data but the problem is it works only in the terminal, but I have to get data in my web application where it will not work."* This web application code is missing and it ideally should be posted as well. – Raymond Nijland Jun 12 '19 at 13:45
  • But most likely you need to use `SET SESSION max_allowed_packet = 1000000000;` as separated query in the application code.. As `SET GLOBAL` always require `SUPER` privilege – Raymond Nijland Jun 12 '19 at 13:51
  • Lets put the web application aside, I want to configure MySQL server and client on localhost to handle large enough packet size say up to 1G without passing it in MySQL client command parameter. – Harish Kumar Saini Jun 13 '19 at 04:24
  • SET GLOBAL applies only to _subsequent_ logins. – Rick James Jun 14 '19 at 06:56
  • SET GLOBAL also doesn't work out, still getting the error. I think I will have to change the config files, which somehow are not working for now. – Harish Kumar Saini Jun 14 '19 at 10:20
  • I have tried changing max_packet_size already, the effect shows in SHOW VARIABLES. But still, no effect; seems the value is being ignored. – Harish Kumar Saini Jun 14 '19 at 10:23

0 Answers0