marrowyung
asked on
"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:
will there be any problem? or just let it be ?
The SP is :
all these other SP:
only creaet script for me to run it in another script UI to finish my job, any idea?
I am running this SP for 2x DB and I get this error:
Single execution returned more than 100 result setswill 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;
	
OPEN tablename;	
	get_tabeslist: LOOP	
FETCH tablename INTO current_tablelist;	
IF finished = 1 THEN 	
LEAVE get_tabeslist;	
END IF;	
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;	
close tablename;
END $$
DELIMITER ; 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); only creaet script for me to run it in another script UI to finish my job, any idea?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What I did using this is:
and then loop out the vaule for cursor one by one, is it the idea you are talking about "?
DECLARE tablename CURSOR FOR ;
SELECT DISTINCT TABLE_NAME ;
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=databaseName and table_type<> 'view';and then loop out the vaule for cursor one by one, is it the idea you are talking about "?
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',
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.
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',
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.
ASKER
"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.
"
I tried all method, it is not.
ASKER
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?