• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2386
  • 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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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