MySQL Bugs: #26670: sql_mode set to null old value

Bug #26670	sql_mode set to null old value
Submitted:	27 Feb 2007 8:42	Modified:	30 Oct 2007 8:23
Reporter:	bocquet denis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	5.0.41, 5.1	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any
Tags:	Backup, mysqldump, sql_node

Description:
--
-- Dumping routines for database 'footx'
--
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`lxquip`@`%`*/ /*!50003 PROCEDURE `spAjouteSportifsNonJoue`(IN idniveau BIGINT)
BEGIN
[...]
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`lequipe`@`%`*/ /*!50003 PROCEDURE `spAttribueRang`(IN idniveau BIGINT,IN idcompet BIGINT)
BEGIN

How to repeat:
when one makes a safeguard with 
 mysqldump $CNX_BKP -q -R \
          --hex-blob \
          --add-drop-table=0 \
          --single-transaction \
          --fields-terminated-by="|" --lines-terminated-by='\n' \
          $DB --tab . -r $DB.proc
an error occurs has the execution
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
that is in the result $DB.proc
@OLD_SQL_MODE is set to NULL (it doesn't exist)

Suggested fix:
set the @OLD_SQL_MODE when execute /*!50003 SET SESSION SQL_MODE=""*/;;

it is not necessary to put 
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 SET SESSION SQL_MODE=""*/;;
between 2 proc but only at the end (i think)
it will be best to put "drop proc if exist" before create proc

sorry the "drop if exist" is put when you don't use add-drop-table=0.
that-'s ok
but the OLD_SQL_node is not set.
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `spAttribueRang` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;

Thank you for the report.

As I understand from your description your request is put before CREATE PROCEDURE line /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; and not reset SESSION SQL_MODE in next line.

Please confirm or refuse if I understood you correctly.

in sql generated for the tables before create table, you put 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
and after you put
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
this isn't the same with the procedure, you have 
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
but @OLD_SQL_MODE isn't known and an error occurs.
so before create proc you have
/*!50003 SET SESSION SQL_MODE=""*/;;
it will be best to have 
/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;

Thank you for the feedback.

I can not repeat it with current development sources. Below is output of mysqldump for database contains only stored procedures. You can see string "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;" before dump of any procedure.

So, please, provide output of mysqldump which can raise error.

$mysqldump --socket=/tmp/mysql50.sock -uroot -R bug26670
-- MySQL dump 10.11
--
-- Host: localhost    Database: bug26670
-- ------------------------------------------------------
-- Server version       5.0.38-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping routines for database 'bug26670'
--
DELIMITER ;;
/*!50003 DROP PROCEDURE IF EXISTS `p1` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `p1`()
begin
select 1;
end */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `p2` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `p2`()
begin
select 1;
end */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `p3` */;;
/*!50003 SET SESSION SQL_MODE="ANSI_QUOTES"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER="root"@"localhost"*/ /*!50003 PROCEDURE "p3"()
begin
select 'foo' as "foo";
end */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2007-03-01 12:11:23

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

"Can't repeat" looks like a more reasonable state.

to repeat : 
1 - you create 2 procedures in database test
2 - you use mysqldump -R test -r test.proc
3 - you reload the proc with mysql < test.proc

and you receive this error:
ERROR 1231 (42000) at line 13: Variable 'sql_mode' can't be set to the value of 'NULL'

--
-- Dumping routines for database 'test'
--
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`sa`@`%`*/ /*!50003 FUNCTION `GEN_ID`(name VARCHAR(15), increment INT) RETURNS int(11)
    MODIFIES SQL DATA
BEGIN
 UPDATE test.sequences SET seq_value=@id:=seq_value+increment WHERE seq_name=name;
     RETURN @id;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`sa`@`%`*/ /*!50003 FUNCTION `GETAPPMODE`(PDATE CHAR(10), PTIME CHAR(10), POFFSET INTEGER) RETURNS char(1)
    READS SQL DATA
BEGIN
...

te pb is here : /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;

Thank you for the feedback.

But you provided only part of mysqldump output. Please provide full test.proc file.

why not

--
-- Dumping routines for database 'test'
--
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`sa`@`%`*/ /*!50003 FUNCTION `ADDSECONDS`(t TIME, s INT) RETURNS time
    DETERMINISTIC
BEGIN
RETURN SEC_TO_TIME(TIME_TO_SEC(t) + s);
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`sa`@`%`*/ /*!50003 FUNCTION `GEN_ID`(name VARCHAR(15), increment INT) RETURNS int(11)
    MODIFIES SQL DATA
BEGIN
 UPDATE test.sequences SET seq_value=@id:=seq_value+increment WHERE seq_name=name;
     RETURN @id;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`sa`@`%`*/ /*!50003 FUNCTION `GETAPPMODE`(PDATE CHAR(10), PTIME CHAR(10), POFFSET INTEGER) RETURNS char(1)
    READS SQL DATA
BEGIN
DECLARE rMode CHAR(1);
DECLARE vCount INT;
DECLARE no_more_rows BOOLEAN;
DECLARE cur1 CURSOR FOR
SELECT TMP.C FROM (
SELECT count(*) C
FROM test.app_data a
WHERE a.app_id = 1
AND a.object = 'MATCH'
AND a.field in ('ID', 'DATE', 'HEURE', 'STATUS')
AND a.rec_id in (select rec_id from test.app_data where field = 'DATE' and data = PDATE)
AND a.rec_id in (select rec_id from test.app_data where field = 'STATUS' and data = '2')
AND a.rec_id in (select rec_id from test.app_data where field = 'HEURE' and
cast(PTIME AS TIME) <= test.ADDSECONDS(cast(data AS TIME), (60 * 120)))
UNION
SELECT count(*) C
FROM test.app_data a
WHERE a.app_id = 1
AND a.object = 'MATCH'
AND a.field in ('ID', 'DATE', 'HEURE', 'STATUS')

AND a.rec_id in (select rec_id from test.app_data where field = 'DATE' and data = PDATE)
AND a.rec_id in (select rec_id from test.app_data where field = 'STATUS' and data = '0')
AND a.rec_id in (select rec_id from test.app_data where field = 'HEURE' and
cast(PTIME AS TIME) >= test.ADDSECONDS(cast(data AS TIME), -(60 * POFFSET)) and
cast(PTIME AS TIME) <= test.ADDSECONDS(cast(data AS TIME), (60 * 120)))
UNION
SELECT count(*) C
FROM test.app_data a
WHERE a.app_id = 1
AND a.object = 'MATCH'
AND a.field in ('ID', 'DATE', 'HEURE', 'STATUS')

AND a.rec_id in (select rec_id from test.app_data where field = 'DATE' and data = PDATE)
AND a.rec_id in (select rec_id from test.app_data where field = 'STATUS' and data = '1' and
cast(PTIME AS TIME) <= test.ADDSECONDS(cast(update_ts AS TIME), (60 * POFFSET)))
) TMP ORDER BY TMP.C ASC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET no_more_rows = TRUE;
SET rMode = 'F';
OPEN cur1;
loop_cur1: LOOP
FETCH cur1 INTO vCount;
IF no_more_rows THEN
CLOSE cur1;
LEAVE loop_cur1;
END IF;
IF(vCount > 0) THEN
SET rMode = 'C';
ELSE
SET rMode = 'F';
END IF;
END LOOP loop_cur1;
    RETURN rMode;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;

 mysql -h0 -P0 -usa -p$SAPASS -Dtest < test.proc
ERROR 1231 (42000) at line 12: Variable 'sql_mode' can't be set to the value of 'NULL'

Thank you for the feedback.

But before string

--
-- Dumping routines for database 'test'
--

output of mysqldump must contain header like:

-- MySQL dump 10.11
--
-- Host: localhost    Database: bug26670
-- ------------------------------------------------------
-- Server version       5.0.38-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Are you sure your version of mysqldump creates dump file without such header? If yes, please, provide output of mysqldump --version and name of package you get it form.

test case

Attachment: bug26670.test (application/octet-stream, text), 390 bytes.

Thank you for the feedback.

Verified as described using attached test case.

it's ok in 5.0.45
regards