Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2121
  • Last Modified:

"Single execution returned more than 100 result sets" MYSQL error message

Dear all,

I am running this SP for 2x DB and I get this error:

Single execution returned more than 100 result sets

Open in new window


will there be any problem? or just let it be ?

The SP is :

CREATE PROCEDURE `create_auditriggers` (IN databaseName VARCHAR(40))  	
BEGIN  
DECLARE a, b, finished  INT DEFAULT 10;
DECLARE temptable VARCHAR(50);	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT ;
DECLARE current_tablelist VARCHAR(50) DEFAULT ""; 
DECLARE fullexcutecmd VARCHAR(5000);
DECLARE fullexcutecmd2 VARCHAR(5000) ;

/*Declare and populate the cursor with a SELECT statement */  	
	
DECLARE tablename CURSOR FOR 	
      SELECT  DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA=databaseName and table_type<> 'view';
    
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;     
&#9;

OPEN tablename;&#9;

&#9;get_tabeslist: LOOP&#9;
 
   FETCH tablename INTO current_tablelist;&#9;
         IF finished = 1 THEN &#9;
          LEAVE  get_tabeslist;&#9;
          END IF;&#9;
  
SET @fullexcutecmd =CONCAT( 'CREATE TABLE IF NOT EXISTS Audit_info.',databaseName,'_DBAudit_', current_tablelist,' LIKE ',databaseName,'.',current_tablelist,';'); 
 
  PREPARE stmt FROM @fullexcutecmd;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  

SET @fullexcutecmd = CONCAT( 'ALTER TABLE Audit_info.',databaseName,'_DBAudit_', current_tablelist,' ADD TriggerAction VARCHAR( 10 ) NULL,
ADD ActionDone CHAR( 10 ) NULL AFTER TriggerAction ,
ADD ActionDate DATETIME NULL AFTER ActionDone , 
ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;');   
 
 PREPARE stmt FROM @fullexcutecmd;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;   
      
     call Create_BEFORE_DELETE_TRIGGER (databaseName, current_tablelist);
    call Create_AFTER_INSERT_TRIGGER (databaseName, current_tablelist);
    call Create_AFTER_UPDATE_TRIGGER  (databaseName, current_tablelist); 


  END LOOP  get_tabeslist;&#9;
close tablename;                             

END $$
DELIMITER ; 

Open in new window


all these other SP:

     call Create_BEFORE_DELETE_TRIGGER (databaseName, current_tablelist);
    call Create_AFTER_INSERT_TRIGGER (databaseName, current_tablelist);
    call Create_AFTER_UPDATE_TRIGGER  (databaseName, current_tablelist); 

Open in new window


only creaet script for me to run it in another script UI to finish my job, any idea?
0
marrowyung
Asked:
marrowyung
  • 3
1 Solution
 
InsoftserviceCommented:
Please check the o/p of
 SELECT  DISTINCT TABLE_NAME &#9;
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA=databaseName and table_type<> 'view';

It normally occurs when you are inserting or using it in where condition and data used for the same is not a single value but multiple
0
 
marrowyungAuthor Commented:
"
It normally occurs when you are inserting or using it in where condition and data used for the same is not a single value but multiple "


so it is ok and don't present any problems? it seems that MySQL don't allow to return result set more than 100 and just drop out after reaching the 101 result set and after?
0
 
marrowyungAuthor Commented:
What I did using this is:

DECLARE tablename CURSOR FOR ;
      SELECT  DISTINCT TABLE_NAME ;
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA=databaseName and table_type<> 'view';

Open in new window


and then loop out the vaule for cursor one by one, is it the idea you are talking about "?
0
 
dbbishopCommented:
I'm not a MySQL developer, but it appears that something is returning data that MySQL is interpreting as a recordset. I would start by determining if the statement:
     EXECUTE stmt;
is returning any data. Start by making @fullexcutecmd an executable statement:

    DECLARE fullexcutecmd VARCHAR(5000);
    SET @fullexcutecmd = CONCAT( 'ALTER TABLE Audit_info.','TestSchema','_DBAudit_', 'testTable',' ADD TriggerAction VARCHAR( 10 ) NULL,
    ADD ActionDone CHAR( 10 ) NULL AFTER TriggerAction ,
    ADD ActionDate DATETIME NULL AFTER ActionDone ,
    ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;');  

Then execute your PREPARE and EXECUTE statements:
    PREPARE stmt FROM @fullexcutecmd;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

If nothing looks 'fishy' there, try other statements (your CALL statements, etc.) This may take some debugging to figure out.

It also appears that the @ may or may not be required preceding the variable name. Again, I'm not a MySQL developer, so I am not familiar with the exact syntax.
Hope that helps.

p.s. I assume MySQL gives you something similar to a command window where you can directly execute commands and statements without building a procedure.
0
 
marrowyungAuthor Commented:
"If nothing looks 'fishy' there, try other statements (your CALL statements, etc.) This may take some debugging to figure out.
"

I tried all method, it is not.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now