MantisBT - TestLink
View Issue Details
0008111TestLinkDatabase MySQLpublic2017-10-16 19:002018-04-15 10:36
tjosephine 
fman 
normalminoralways
closedfixed 
LinuxMySQL server5.6.21
1.9.16 (2016 Q4) 
1.9.17 (2018 Q1) 
MySQL
Chrome v.58
5.6.14
READY FOR TESTING
0008111: Not able to create the "UDFStringHTMLTags" function when upgrading my MySQL database
We upgraded TestLink to last fix package version and were not able to run successfully the db_udf_update.sql located in folder
\install\sql\alter_tables\1.9.17\mysql\DB.1.9.17\step1

Our MySQL database version is 5.6.21 hosted on a linux server.

Error message (see attachment):
ErrorCode:1418:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you "might" want to use the less safe log_bin_trust_function_creations variable)

1. What is the impact to not have this function defined in our MySQL database?
2. is it critical for TestLink?

Thanks
This error was generated when upgrading our MySQL database 5.6.21 on Linux server.
No error on Windows machine using xampp and MySQL 5.6.16.
The function "UDFStripHTMLTags" was created successfully.
No tags attached.
png ErrorCode_1418.png (69,266) 2017-10-16 19:00
http://mantis.testlink.org/file_download.php?file_id=4831&type=bug
png

png Screen Shot 2017-10-21 at 16.14.42.png (43,202) 2017-10-21 14:15
http://mantis.testlink.org/file_download.php?file_id=4833&type=bug
png
Issue History
2017-10-16 19:00tjosephineNew Issue
2017-10-16 19:00tjosephineFile Added: ErrorCode_1418.png
2017-10-18 20:44fmanQA Team - Task Workflow Status => TBD
2017-10-18 20:44fmanView Statusprivate => public
2017-10-18 20:44fmanNote Added: 0026945
2017-10-19 17:15tjosephineNote Added: 0026953
2017-10-21 14:13fmanNote Added: 0026955
2017-10-21 14:15fmanFile Added: Screen Shot 2017-10-21 at 16.14.42.png
2017-10-21 14:15fmanNote View State: 0026955: public
2017-10-21 14:16fmanNote Edited: 0026955bug_revision_view_page.php?bugnote_id=26955#r5385
2017-10-21 14:16fmanAssigned To => fman
2017-10-21 14:16fmanStatusnew => feedback
2017-10-23 13:20tjosephineNote Added: 0026964
2017-10-23 13:20tjosephineStatusfeedback => assigned
2017-10-23 17:15fmanQA Team - Task Workflow StatusTBD => READY FOR TESTING
2017-10-23 17:15fmanStatusassigned => resolved
2017-10-23 17:15fmanFixed in Version => 1.9.17 (2018 Q1)
2017-10-23 17:15fmanResolutionopen => fixed
2018-04-15 10:36fmanNote Added: 0027365
2018-04-15 10:36fmanStatusresolved => closed

Notes
(0026945)
fman   
2017-10-18 20:44   
function is needed for new searh feature
(0026953)
tjosephine   
2017-10-19 17:15   
Fixed by adding in the declaration DETERMINISTIC, READS SQL DATA

USE `YOUR_TL_DBNAME`; /* Replace before run */
DROP function IF EXISTS `UDFStripHTMLTags`;

DELIMITER $$
USE `YOUR_TL_DBNAME`$$ /* Replace before run */
CREATE FUNCTION `UDFStripHTMLTags`(Dirty varchar(4000)) RETURNS varchar(4000) CHARSET utf8
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE iStart, iEnd, iLength int;
   WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
        SET iLength = ( iEnd - iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, '');
          END;
        END IF;
      END;
    END WHILE;
RETURN Dirty;
END$$

DELIMITER ;

How to test that the new function `UDFStripHTMLTags` is working as expected?
(0026955)
fman   
2017-10-21 14:13   
(edited on: 2017-10-21 14:16)
you need to use the new search feature => SEE attached image

(0026964)
tjosephine   
2017-10-23 13:20   
Working fine! Thanks
(0027365)
fman   
2018-04-15 10:36   
released