Unable to change thread_stack size MySQL Windows

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:

  1. there is no .cnf file anywhere;
  2. there is a single my.ini file in the hidden C:\ProgramData\MySQL\MySQL Server 8.0\ but not in the Program Files\MySQL\MySQL Server 8.0\;
  3. there is no thread_stack value in that .ini file;
  4. 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\.
  5. To execute mysqld commands in command prompt I had to create a blank data folder in the Program Files subfolder:
    Unable to change thread_stack size MySQL Windows
  6. On executing the above command, there are two findings:

Unable to change thread_stack size MySQL Windows

Unable to change thread_stack size MySQL Windows

So, here’s is what I did to increase it 4x:

  1. I added a thread_stack=1120K line under [mysqld] in the my.ini file using notepad as administrator. (286720*4/1024 = 1120).

  2. (on suggestion of a user but did nothing) added semicolons(initially not present) around the url after datadir in the same my.ini.

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:

Unable to change thread_stack size MySQL Windows

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%';

Unable to change thread_stack size MySQL Windows

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.

Method 1

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply