MySQL Function Error

MySQLmysql-5.1

In an inherited database I have two tables of current interest, records and logins. login tells me the location of the user and where and when his login occured (in and out times included).

The fun part is that there is no correlating mark between the login session and the applications ran. To solve the issue I'm creating a new table login_apps and am trying to use a function to combine the two tables.

So far I have:

drop function if exists ltop;
delimiter $$

create function ltop(id int, u varchar(10), s datetime, e datetime)
    returns text
    language sql
begin
    declare pid varchar(40);
    declare pname varchar(63);
    declare my_return varchar(100);
    declare cur1 cursor for (select usageProgramID 
                                from records 
                                where usageUser=u
                                    and usageWhen>=s 
                                    and usageWhen<=e
                                    and usageProgramID!="");
    open cur1;
    read_loop: loop
        fetch cur1 into pid;
        set pname = (select programName from Programs where programID=pid);
        insert into login_apps(`sid`, `programName`) values(id, pname);
    end loop read_loop;

    close cur1;

    return "finished";
end;$$
delimiter ;

It enters into the system without issue and the insert is working as expected on a test. The issue I'm coming up with is that when it's run as:

select ltop("265548", "user", "2013-02-21 13:54:27", "2013-02-21 14:32:18");

I get the error: 

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

Shouldn't the data be returned be "finished"?

Secondly, I'm not really sure what would be considered the best way to run this on every value of logins any pointers for going that route?

Best Answer

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

That error is not about your RETURN; it's this:

fetch cur1 into pid;

When the cursor runs out of data, this error is thrown. This could be because the SELECT query declared in the cursor didn't find any rows, or because it did find rows but as you iterated through it, you read past the last row. If nothing happened when the cursor ran out of data, how would you ever break out of read_loop?

You need to trap the error with a handler, which will prevent execution of the procedure from terminating, and set a variable you can use to break out of the loop.

At the top, with the other variables:

DECLARE done TINYINT DEFAULT 0;

Then, after declaring but before opening the cursor:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Inside your loop:

fetch cur1 into pid;
IF done = 1 THEN LEAVE read_loop; END IF;

This should solve your immediate problem.

Other observations:

Your function modifies the database, so at the top, you should have this:

returns text
MODIFIES SQL DATA
language sql

...although, really, this would be more appropriately written as a stored procedure rather than a function. MySQL supports non-deterministic stored functions that modify the database, you there are potential issues with the binary log used for point-in-time recovery or replication that can pop up.

To process all of the data rather than one value at a time, you could use nested cursors -- the outer cursor fetching whatever data you're feeding into this function and the inner cursor -- inside an additional BEGIN/END block doing the logic you've got, above with values fetched in the outer cursor. This requires resetting of the done value back to 0 after each iteration of the inner block so that the other block doesn't stop prematurely.

The inner subquery is unnecessary, since the cursor could be declared with...

SELECT p.programName
  FROM records r JOIN Programs p ON p.programID = r.usageProgramID
 WHERE ...

...but, in fact, this entire function seems like it may be unnecessary.

In SQL, it's typically best if you you tell the database what needs to be done ("declarative"), as opposed to how to do it ("procedural").

Thinking too procedurally is an SQL antipattern.

I don't know your column names in the 'login' table, so you'll need to correct those, but consider the following query. As far as I can tell, it does exactly what the function does, for all logins and all matching records.

INSERT INTO login_apps(`sid`, `programName`)
SELECT l.session_id, p.programName
  FROM logins l
  JOIN records r ON r.usageUser = l.user_name 
                AND r.usageWhen >= l.start_time 
                AND r.usageWhen <= l.end_time
                AND r.usageProgramID != ""
  JOIN Programs p ON p.programID = r.usageProgramID;

Done.

For future maintenance of the "login_apps" table, a trigger on the "records" table could look up the related login and write the new entries into "login_apps" every time "records" has an insert or an update... or when the stop time is logged on the "login" table, an update trigger could read "records" and insert into "login_apps".

Final thought, it would probably be better design if you stored the program ID rather the program name in the login_apps table... unless the "records" table actually has an auto_increment primary key, in which case, that would be the value you should really store in login_apps.

Related Question