Bug #11405 ERROR : MySQL server has gone away

• Submitted: 17 Jun 2005 5:01
• Modified: 18 Jun 2005 3:37
• Reporter: Rahul Chaudhari
• Status: Can't repeat
• Category: MySQL Server: User-defined functions ( UDF )
• Severity: S2 (Serious)
• Version: 5.0.6
• OS: Windows (Windows 2000 service pack 4)
• CPU Architecture: Any

[17 Jun 2005 5:01] Rahul Chaudhari

Description:
Hi All

     i had created one function for dat diffrence in time !!!

these function gives me different between time and dates

the function executes properly but at the time of call it closed the service for mysql, function hangs up and after some say few seconds services are colsed with the error...

ERROR 2006 (HY000): MySQL server has gone away

How to repeat:
here is the function :--------------

mysql> DELIMITER $$
mysql>
mysql> DROP FUNCTION IF EXISTS `usermgm`.`Date_Diff_In_Time`$$
Query OK, 0 rows affected (0.28 sec)

mysql> CREATE FUNCTION `usermgm`.`Date_Diff_In_Time`(
    ->   p_MaxDate datetime,
    ->   p_MinDate datetime
    ->  ) RETURNS varchar(100)
    -> BEGIN
    ->  declare v_DiffInHours           INT;
    ->  declare v_DiffInMinutes         INT;
    ->  declare v_DiffInSeconds         INT;
    ->  declare v_FinalDiffInHours  VARCHAR(11);
    ->  declare v_FinalDiffInMinutes  VARCHAR(2);
    ->  declare v_FinalDiffInSeconds  VARCHAR(2);
    ->  declare v_FinalDIff     VARCHAR(15);
    ->
    ->  /*--declare Time_Exception  EXCEPTION;
    ->  declare Time_Exception handler for  v_DiffInHours < 0 set @a = 'CHECK CLOCK TIME ON KIOSK';
    ->   DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement*/
    ->
    ->   set v_DiffInHours   = (p_MaxDate-p_MinDate) * 24;
    ->
    ->   set v_DiffInMinutes  = v_DiffInHours * 60;
    ->   set v_DiffInSeconds  = v_DiffInMinutes * 60;
    ->
    ->   set v_FinalDiffInHours = FLOOR(v_DiffInHours);
    ->
    ->   set v_FinalDiffInMinutes = FLOOR(MOD(v_DiffInMinutes,60));
    ->   set v_FinalDiffInSeconds = TRUNC(MOD(v_DiffInSeconds,60));
    ->
    ->   IF LENGTH(v_FinalDiffInHours)  = 1 THEN
    ->    set v_FinalDiffInHours = concat('0',v_FinalDiffInHours);
    ->   END IF;
    ->
    ->   IF LENGTH(v_FinalDiffInMinutes) = 1 THEN
    ->    set v_FinalDiffInMinutes = concat('0',v_FinalDiffInMinutes);
    ->   END IF;
    ->
    ->   IF LENGTH(v_FinalDiffInSeconds) = 1 THEN
    ->    set v_FinalDiffInSeconds = concat('0',v_FinalDiffInSeconds);
    ->   END IF;
    ->
    ->   set v_FinalDIff = concat(v_FinalDiffInHours,':',v_FinalDiffInMinutes,':',v_FinalDiffInSeconds);
    ->
    ->   IF v_FinalDIff = '::' THEN
    ->     set v_FinalDIff ='';
    ->   END IF;
    ->   RETURN v_FinalDIff;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

mysql> select Date_Diff_In_Time(str_to_date('01-01-2005','%m-%d-%Y %T'),str_to_date('01-10-2005','%m-%d-%Y %T'));
ERROR 2006 (HY000): MySQL server has gone away

[18 Jun 2005 3:37] MySQL Verification Team

I was unable to repeat this issue:

ERROR 1305 (42000): FUNCTION test.TRUNC does not exist

in your sample:

    ->   set v_FinalDiffInMinutes = FLOOR(MOD(v_DiffInMinutes,60));
    ->   set v_FinalDiffInSeconds = TRUNC(MOD(v_DiffInSeconds,60));
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

I assume you meant:

TRUNCATE(X,D)

Returns the number X, truncated to D decimals. If D is 0, the result has no decimal point or fractional part. D can be negative to truncate (make zero) D digits left of the decimal point of the value X.

mysql> SELECT TRUNCATE(1.223,1);
        -> 1.2