Problems using MySQL functions and procedures in Uniform Server.

[andre]

These problems originally occurred using Uniform Server 4.3. I updated to version 5.1 and the problems still existed.

 

I created a procedure in MySQL to retrieve data from a table. When I called the procedure, I got the following error message;

 

Error Code : 1436

Thread stack overrun: 6436 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.

 

I also created a function to multiply two numbers. When used, I received the following message;

 

Error Code : 1436

Thread stack overrun: 9124 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.

 

Checking the MySQL docs, I saw that the default size for the thread_stack default size is 196608, incremental in 1024 byte blocks to 4294967295. I have no idea where to access mysqld in Uniform Server.

 

Thinking that the database (uses INNODB tables) itself may have upset something, I created a new database with no tables and re-created the function. The error repeated.

 

I have used multiple computers and repeated the error messages every time. These computers were running Win XP, one with SP3 and one with SP2. I have also downloaded and tried more than one copy of Uniform Server, same errors each time.

 

In desperation, I tried a copy of XAMPP. Everything worked first time. Using XAMPP is not an option.

 

Can anyone help me to get the procedures and functions operating please?

[Ric]

Can you post some example code the SQL and PHP code for this will do:

I also created a function to multiply two numbers. When used, I received the following message;

Just that I would like to reproduce the errors you obtained.

 

All the best

Ric

[andre]

Can you post some example code the SQL and PHP code for this will do:

 

Just that I would like to reproduce the errors you obtained.

 

All the best

Ric

 

Thanks Ric,

 

No PHP code. All MySQL. Was originally using SQLyog as interface, but went to the CLI to try and isolate the problem. Sample code -

 

 

Creating Functions,

####################################################################

# Function lineTotal()

#

# Takes two parameters, one integer, one decimal

# and returns the product of the two numbers rounded to

# two decimal places.

####################################################################

DELIMITER $$

DROP FUNCTION IF EXISTS lineTotal$$

CREATE FUNCTION lineTotal(quantity INT,price DECIMAL(8,2))

RETURNS DECIMAL(10,2)

BEGIN

RETURN ROUND((quantity * price),2);

END$$

 

DELIMITER ;

####################################################################

# Function shorten()

#

# Takes two parameters, one string, one integer

# and returns part of the string to a maximum

# of 15 characters places.

####################################################################

DELIMITER $$

DROP FUNCTION IF EXISTS shorten$$

CREATE FUNCTION shorten(s VARCHAR(255),n INT)

RETURNS VARCHAR(255)

BEGIN

IF ISNULL(s) THEN

RETURN '';

ELSEIF n < 15 THEN

RETURN LEFT(s,n);

ELSE

IF CHAR_LENGTH(s) <= n THEN

RETURN s;

ELSE

RETURN CONCAT(LEFT(s, n-10),' ... ', RIGHT(s,5));

END IF;

END IF;

END$$

 

DELIMITER ;

 

To test the functions,

 

SELECT shorten('abcdefghijklmnopqrstuvwxyz', 16);

SELECT lineTotal(3,2.3);

 

I have now had a similar error with triggers too.

 

Much appreciative of any advice you can give

 

Andre

[Ric]

Hi Andre

 

Thanks for posting your test code:

I can confirm it fails on: Uniform Server 4.3, 5.0-Nano and 5.1-Nano

 

Error: #1436 - Thread stack overrun: 9128 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.

 

Solution is to edit MySQL configuration file my.cnf (note the extension is not displayed hence you will see file my)

 

File location:

Uniform Server 4.4-Mona

UniServer\udrive\usr\local\mysql\my.cnf

 

Uniform Server 5.0-Nano

Uniform Server 5.1-Nano

UniServer\usr\local\mysql\my.cnf

 

Comment out lines (or delete) and add new lines as shown:

# port=3306
# #socket=MySQL
# skip-locking
# key_buffer = 16K
# max_allowed_packet = 1M
# table_cache = 4
# sort_buffer = 64K
# read_buffer_size = 128K
# read_rnd_buffer_size = 256K
# net_buffer_length = 2K
# thread_stack = 128K
# server-id	= 1

port=3306
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
server-id	= 1

 

Note: The commented out section is for a small MySQL configuration

New lines are for a medium MySQL configuration.

 

It looks like MySQL functions and procedures require at least the medium configuration.

 

All the best

Ric

[andre]

Hi Ric,

 

Worked beautifully. All procedures, functions and triggers running properly now.

 

Quick question, which will show my ignorance. There was one line in the small MySQL configuration, that you had commented out;

 

# thread_stack = 128K

 

Looking at the MySQL documentation, the default size of the thread_stack is 192K. If I had just changed that line to update the size, would it have caused trouble elsewhere? Having a bit of 'what-if' mentality, I tried it and everything seemed to work properly, but I have no idea if it made a mess of something else.

 

Many thanks, you have saved the last few remaining strands of hair,

 

Andre

[Ric]

Hi Andre,

Many thanks for that bit of information.

 

I discovered this has been a known issue for some time!(One of many references I found http://bugs.mysql.com/bug.php?id=45445 )

 

Even the new version (MySQL 5.1.39) of small.ini contains thread_stack = 128K

As you rightly point out the default size for thread_stack is 192K looks as if the config files were never updated.

 

Bottom line, next versions of US will include the correct value.

 

“If I had just changed that line to update the size, would it have caused trouble elsewhere?”

 

Changing only that line will be safe, other existing values keep PC resources low. However if your server is under a high load you will need to changes values accordingly.

 

Again thanks for the feedback

 

All the best

Ric