Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-25
6
Medium Priority
?
2,061 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 15

Accepted Solution

by:
Insoftservice earned 1500 total points
ID: 39686874
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
ID: 39689104
"
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
ID: 39689106
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
ID: 39691203
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
ID: 39692344
"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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
What we learned in Webroot's webinar on multi-vector protection.
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…

715 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