Error in create function syntax developed with MySql not working in MariaDB on PhpMyAdmin SQL console

Question

I have the following create function block for SQL

/**
 * tos_get_duration.sql
 *
 * Given time unit in seconds, it converts to more readable format
 *
 * Eg. SELECT TOS_GET_DURATION(72) => 00d 0:1:12
 */

DROP FUNCTION IF EXISTS TOS_GET_DURATION;
DELIMITER $$
CREATE FUNCTION TOS_GET_DURATION(seconds INT) 
  RETURNS VARCHAR(16) 
BEGIN
  RETURN CONCAT(LPAD(FLOOR(HOUR(SEC_TO_TIME(seconds)) / 24), 2, 0), ' days ',TIME_FORMAT(SEC_TO_TIME(seconds % (24 * 3600)), '%H:%i:%s'));
END; 
$$
DELIMITER;

it is taken from timeonsite analytics reporting query page.

It used to be working well in MySql but I recently switched to MariaDB and tried executed this function; this function basically converts seconds to more human readable string format. I get following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER' at line 1

Server version: 10.5.16-MariaDB - MariaDB Server

Kindly help me find the issue and suggest me common "create function" format I can use in both MySql and MariaDB databases.

webblover · Answer

Thanks for your valuable comments. I've upvoted each one of the comments because they are quite valuable. Based on your suggestions, I've created a common fiddle for both and share it here. The point is, we can remove BEGIN/END statements and remove DELIMITER keywords as well.

The common code works in both MySQL and MariaDB except that MySQL latest versions expect DETERMINISTIC keyword to work.

MariaDB https://dbfiddle.uk/PK8F8ng4

DROP FUNCTION IF EXISTS TOS_GET_DURATION;
CREATE FUNCTION TOS_GET_DURATION(seconds INT) 
  RETURNS VARCHAR(16)
  RETURN CONCAT(LPAD(FLOOR(HOUR(SEC_TO_TIME(seconds)) / 24), 2, 0), ' days ',TIME_FORMAT(SEC_TO_TIME(seconds % (24 * 3600)), '%H:%i:%s'));

SELECT TOS_GET_DURATION(860000);

MySQL https://www.db-fiddle.com/f/qsDtYVw7GrkJW5suNsrfd1/9

DROP FUNCTION IF EXISTS TOS_GET_DURATION;
CREATE FUNCTION TOS_GET_DURATION(seconds INT) 
  RETURNS VARCHAR(16) DETERMINISTIC
  RETURN CONCAT(LPAD(FLOOR(HOUR(SEC_TO_TIME(seconds)) / 24), 2, 0), ' days ',TIME_FORMAT(SEC_TO_TIME(seconds % (24 * 3600)), '%H:%i:%s'));

SELECT TOS_GET_DURATION(860000);

The output:

09 days 22:53:20

I also agree to Salmon's point,

can't do that mysql and mariadb are diverging and have diverged to the point where mariadb can't be considered a drop in replacement for mysql.

But the point is, we have numerous applications that were built as part of LAMP stack. So, we need common code formats like this to make easy switch for the time being. We'll keep it open for additional suggestions from others. This issue is first identified when I worked on timeonsite.js when trying to run query complex sub-queries to analyze user engagement in web applications.

Finding top 10 sessions for user engagement in application:

Line 2 uses user-defined function TOS_GET_DURATION in MariaDB

SELECT 
  TOS_GET_DURATION(max_timeonsite) as 'Session Duration', 
  tos_session_key 
FROM 
  (
    SELECT 
      MAX(timeonsite) AS max_timeonsite, 
      tos_session_key 
    FROM 
      tos 
    GROUP BY 
      tos_session_key 
    ORDER BY 
      max_timeonsite DESC 
    LIMIT 
      10
  ) timeonsite_large_sessions;

Query Output

Error in create function syntax developed with MySql not working in MariaDB on PhpMyAdmin SQL console

1 Answers