Bug #97365 Server does not start after changing max_allowed_packet parameter
Submitted: 24 Oct 2019 15:25 Modified: 24 Oct 2019 17:05
Reporter: Rajneesh Gupta Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:8.0.18 OS:Windows (10)
Assigned to: CPU Architecture:x86

[24 Oct 2019 15:25] Rajneesh Gupta
I am running the community server 8.0.18 (from the command line and not the service) and I need to set the max_allowed_packet value to higher than the default. 

However,the server hangs on start with max_allowed_packet=16M, both when
- change is made in the ini file, or
- the value is passed as a command line parameter,

Full logging is on but nothing even gets written to the log. The last entry in the log is:

2019-10-24T14:47:56.916731Z	   10 Connect	root@localhost on  using SSL/TLS
2019-10-24T14:47:56.916973Z	   10 Query	shutdown
2019-10-24T14:47:56.917224Z	   10 Query	

At this point, the only thing possible is to reboot, uninstall and then install again.

How to repeat:
1. Install MySQL with advance logging and turn on all logs and without setting up a service
2. Start server by running mysqld : starts ok
3. Stop server by running mysqladmin -u root -p shutdown : shutsdown ok
4. change the value in the my.ini
5. Start server - server hangs
[24 Oct 2019 15:42] Rajneesh Gupta
the conf file with the change in place

Attachment: my.ini (application/octet-stream, text), 17.02 KiB.

[24 Oct 2019 16:44] MySQL Verification Team
Server start

Attachment: 97365.png (image/png, text), 88.42 KiB.

[24 Oct 2019 16:45] MySQL Verification Team
Thank you for the bug report. I couldn't repeat (see screenshot).
[24 Oct 2019 16:50] Rajneesh Gupta

It seemed strange that the server would not start after a legal change - so I just saved the conf (my.ini) file without making any changes. And the got the same result
[24 Oct 2019 16:51] MySQL Verification Team
New  max_allowed_packet value

Attachment: 97365_1.png (image/png, text), 75.73 KiB.

[24 Oct 2019 16:52] MySQL Verification Team
New  max_allowed_packet value

Attachment: 97365_1.png (image/png, text), 75.73 KiB.

[24 Oct 2019 17:04] Rajneesh Gupta
server start - attempting shutdown

Attachment: mysqld.png (image/png, text), 38.70 KiB.

[24 Oct 2019 17:05] Rajneesh Gupta
Attached screen shot of the server hanging on the console. The process starts - can see it in the windows process list. But does not accept any connections
[24 Oct 2019 17:10] Rajneesh Gupta
After new install

Attachment: mysqld.png (image/png, text), 73.18 KiB.

[25 Oct 2019 8:08] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Just like our Miguel, I attempted this on my local machine and confirmed that no issues while changing option values etc. We checked the screenshot uploaded at [24 Oct 17:10] - looking at the error "mysqld: Can not perform keyring migration : Invalid --keyring-migration-source option. " - it seems to me like you tried to shutdown but using server binary instead of client i.e "mysqld -uroot -p shutdown" <-- this invoked keyring migration mode of MySQL server. You should have used "mysql -uroot -p shutdown".

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.
[8 Apr 2020 5:06] Tinman Namnit
This bug report is old but I wanted to comment anyway since it took me a while to find the answer and it may help others. It is an encoding issue with the ini file. See the link below or pasted comment for the explanation and fix. 

I found the ini file from mySQL has 3 invalid ANSI characters. The following line in the file is the culprit "The line # range from 1 to 2^32 − 1. “Unique” means that each ID must be different.". It has 3 Unicode characters. This is causing notepad to append the byte order mark to the text file.

Remove this comment and use Save As in Notepad. Under "Save As" change encoding to ANSI. If you don't remove the comment and then save as ANSI, Notepad will automatically replace the characters with place holders.