A little background.
I want to use FOSS Matomo analytics on my website and I installed it with partial success. Automated system check in Matomo gave me couple issues to solve and one of them is changing max_allowed_packet size of MySQL database that is assigned during installation to Matomo.
I tried couple different approaches that I was able to find on my own, but to no effect. I know jack about databases.
1 attempt - MyPHPAdmin, after logging with appropriate user account I wanted to change the parameter, but it turns out I don’t have admin privileges. Same goes for inputting SQL queries. My hosting provider do not provide options to change this.
2 attempt - adding max_allowed_packet with desired value to ‘php.ini’ that I found in my_client_username/settings directory. This don’t work because I don’t have privileges to write to this dir.
Is it possible to set max_allowed_packet for a specific database without having admin privileges?
This would need a command line flag on mysqld server or a my.cnf config file change (depending on how you’re running the db).
99.99% of the time when you’re using the DB for small random point changes and point lookups, the actual value doesn’t really matter and whatever small default is set will work fine.
It determines max amount of data that the server would read over TCP before beginning to parse the statement or query… and/or how much data you can put and manipulate in a single row of a table.
You can still return many rows, and you can still issue many separate statements.
If you’re not issuing large queries or statements, as in: many megabytes long SELECT... WHERE foo IN (..giant list..) type of queries with unnaturally long IN clauses or storing large many megabytes blobs in table fields, the defaults are probably fine and you don’t need to worry.
One place where people sometimes legitimately stumble on this is when doing bulk inserts, such as when restoring a logical backup.
Various backup utilities will often generate multi row inserts that sometimes looks like INSERT INTO table VALUES ((a1,b1,c1,d1),(a2,b2,c2,d2),(a3....
You end up with a series of these large statements that might be split at 1000rows per statement or something like that. If this is tripping up at the packet size, the solution is to split the insert into fewer rows per statement… (usually configurable with the restore utility) or to put data files alongside the server and instruct mysql to read the file using LOAD DATA statements.
It’s this some kind of managed PHP web hosting environment? You’re not running your own on a VPS?
Thanks for explanation.
I’m not running VPS no. I’m too much of a smoothbrain and don’t want to rely on my own server and security skills.
The only PHP related tools that hosting provides is link to MyPHPAdmin interface. I tried to locate my.cnf via SFTP but it doesn’t exists in directories to which I have write permission.
I thought that max_allowed_packet is not that important otherwise the Matomo installer would bitch about it harder.
The database will be used for collecting analytics (mostly traffic) and I honestly don’t know if this can trip the 16MB limit. I guess in extreme cases yes and that’s why Matomo have a check for it.