All we need is an easy explanation of the problem, so here it is.
I got the following error on MySQL 8.0 Windows on large recursive procedure:
Error Code: 1436. Thread stack overrun: 127944 bytes used of a 286720 byte stack, and 160000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.
After doing hours of research on how to increase stack size.
Here are my findings:
- there is no
- there is a single
my.inifile in the hidden
C:\ProgramData\MySQL\MySQL Server 8.0\but not in the
Program Files\MySQL\MySQL Server 8.0\;
- there is no
thread_stackvalue in that
- there is no data folder in
C:\Program Files\MySQL\MySQL Server 8.0\but there is a data folder in
C:\ProgramData\MySQL\MySQL Server 8.0\.
- To execute
mysqldcommands in command prompt I had to create a blank
datafolder in the Program Files subfolder:
- On executing the above command, there are two findings:
So, here’s is what I did to increase it 4x:
I added a
my.inifile using notepad as administrator. (286720*4/1024 = 1120).
(on suggestion of a user but did nothing) added semicolons(initially not present) around the url after
datadirin the same
Now after I again ran the
verbose --help command it still showed me the same stack size
So, I copied the ‘my.ini’ file from ProgramData into the
C:\Windows\ folder as the mysqld statement searches that directory for config files. (as mentioned in the image)
Now when I ran the command again:
And here the size has increased. But, the main thing is after I use
mysql -u root -p, connect with the server and use
SHOW VARIABLES LIKE '%thread_stack%';
Here there is no change in the size.
Here I am stuck, how to do this?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I finally figured this out.
Before you start changing the my.ini file make sure you have a copy of a working my.ini file as a Backup.
The problem lies with the text editing. If you open my.ini in Notepad it will save the file as my.ini.txt even though the .txt extension might not show up. As a result MySQL does not recognize the file as a configuration file and does not load it on startup.
So, add the following single line: thread_stack=512K (or your preferred size) under the [mysqld] section of the my.ini file. Don’t include — before thread_stack.
Then save the file as my.ini and make sure "All Files (.)" is displayed in the Save dialogue. This should prevent it from being saved as a .txt file and will be recognized by Windows as a configuration file.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂