Solved

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

Posted on 2013-11-25
6
1,728 Views
Last Modified: 2013-12-10
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
Comment
Question by:marrowyung
  • 3
6 Comments
 
LVL 15

Accepted Solution

by:
Insoftservice earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 15

Expert Comment

by:dbbishop
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now