The concat of string to create trigger, the length of string TEXT varible can store seems not enought

Dear all,

right now I am running a SP of MYSQL and the code is:

CREATE PROCEDURE `create_auditriggers_forALL` (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 FieldAdded_statement TEXT  ;
DECLARE Final_FieldAdded_statement TEXT DEFAULT "";
DECLARE FieldRemoved_statement TEXT  ;
DECLARE Final_FieldRemoved_statement TEXT DEFAULT "";
DECLARE BEFORE_DELETE TEXT  ;
DECLARE Final_BEFORE_DELETE TEXT DEFAULT "";
DECLARE AFTER_INSERT TEXT  ;
DECLARE Final_AFTER_INSERT TEXT DEFAULT ""  ;
DECLARE AFTER_UPDATE TEXT  ;
DECLARE Final_AFTER_UPDATE TEXT DEFAULT "";
DECLARE Check_Each_PrimaryKey_Autoincrement TEXT  ;
DECLARE Final_Check_Each_PrimaryKey_Autoincrement TEXT DEFAULT "";
DECLARE Check_index TEXT  ;
DECLARE Final_Check_index TEXT DEFAULT "";
DECLARE TableExistsinAuditDB int (1) DEFAULT '0' ;  
DECLARE TableHasFieldChanged int (1) DEFAULT '1' ;  
/* for the detection of drop of field */
DECLARE Add_Field_statement TEXT DEFAULT "";
DECLARE Combine_DropField_statement TEXT DEFAULT "";
DECLARE TableHasFielddropped int (1) DEFAULT '1' ;  
DECLARE TableRenameCommand TEXT  ; 

/*Declare and populate the cursor with a SELECT statement */  	
 
/*  SET @fullexcutecmd =CONCAT(@beginWith,'%'); 
 select @fullexcutecmd; */
 /* scan for all tables  */
 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;     
/*Specify what to do when no more records found, notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures*/  	

OPEN tablename;	

	get_tabeslist: LOOP	
 
   FETCH tablename INTO current_tablelist;	
         IF finished = 1 THEN 	
          LEAVE  get_tabeslist;	
          END IF;	
  
  /* check if the related audit table in the audit database existed or not */
    
  SELECT count(*) INTO TableExistsinAuditDB     
FROM INFORMATION_SCHEMA.TABLES             
WHERE TABLE_SCHEMA='Audit_info'                        
AND TABLE_NAME = CONCAT(databaseName,'_DBAudit_', current_tablelist);   

/* check if the current table has field dropped */
Select count(DISTINCT COLUMN_NAME) INTO TableHasFielddropped
 FROM INFORMATION_SCHEMA.COLUMNS 
-- WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = current_tablelist and COLUMN_NAME
WHERE TABLE_SCHEMA = 'Audit_info' AND TABLE_NAME = CONCAT(databaseName,'_DBAudit_',current_tablelist) and COLUMN_NAME
NOT IN
(
Select DISTINCT COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = current_tablelist
-- WHERE TABLE_SCHEMA = 'Audit_info' AND TABLE_NAME = CONCAT(databaseName,'_DBAudit_',current_tablelist)
)
AND COLUMN_NAME != "TriggerAction" AND COLUMN_NAME != "ActionDone" AND COLUMN_NAME != "ActionDate" AND COLUMN_NAME != "ActionBy";
 
  if TableHasFielddropped >= 1 then   -- if field droppped
  /* this is the trouble statement */
   SET @TableRenameCommand = CONCAT( 'RENAME TABLE Audit_info.',databaseName,'_DBAudit_',current_tablelist ,' to Audit_info.SchemaChanged',
   databaseName,'_DBAudit_',current_tablelist, DATE_FORMAT(NOW(), '%Y_%m_%d_%H_%i_%S') );   

         PREPARE stmt FROM @TableRenameCommand;
         EXECUTE stmt;
         DEALLOCATE PREPARE stmt;    
     
     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; 
  
 select Function_Create_BEFORE_DELETE_TRIGGER (databaseName, current_tablelist) into BEFORE_DELETE;

 set Final_BEFORE_DELETE= CONCAT(Final_BEFORE_DELETE, BEFORE_DELETE);
   
  select Function_Create_AFTER_INSERT_TRIGGER (databaseName, current_tablelist) into AFTER_INSERT;
  
   set Final_AFTER_INSERT= CONCAT(Final_AFTER_INSERT, AFTER_INSERT);
   
  select Function_Create_AFTER_UPDATE_TRIGGER (databaseName, current_tablelist) into AFTER_UPDATE;
  
   set Final_AFTER_UPDATE= CONCAT(Final_AFTER_UPDATE, AFTER_UPDATE);
  
    select Function_Drop_PrimaryKey_autoincrement (databaseName, current_tablelist) into Check_Each_PrimaryKey_Autoincrement;

   set Final_Check_Each_PrimaryKey_Autoincrement = CONCAT(Final_Check_Each_PrimaryKey_Autoincrement , Check_Each_PrimaryKey_Autoincrement);

    select Function_Drop_index (databaseName, current_tablelist) into Check_index;

   set Final_Check_index  = CONCAT(Final_Check_index, Check_index);
   
   set Final_FieldRemoved_statement = CONCAT( Final_FieldRemoved_statement, ' ', Final_Check_Each_PrimaryKey_Autoincrement ,' ' , Final_Check_index,' use ',databaseName,'; ',
   Final_BEFORE_DELETE,' ', Final_AFTER_INSERT,' ', Final_AFTER_UPDATE
   );
      
      set Final_BEFORE_DELETE = ' '; set Final_AFTER_INSERT = '' ;
      set Final_AFTER_UPDATE= ' ' ;  set Final_Check_Each_PrimaryKey_Autoincrement= ' ' ;
       set Final_Check_index = ' ' ;
      
  END IF; 
  
    select Function_CheckFieldAdded (databaseName, current_tablelist) into FieldAdded_statement;
    set Final_FieldAdded_statement =  CONCAT(Final_FieldAdded_statement, FieldAdded_statement);
    
    if TableExistsinAuditDB < 1 then   /* Check if the Audit DB already has that table */
    
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;   
    
/* create the audit database's table for column compare purpose */
      
select Function_Create_BEFORE_DELETE_TRIGGER (databaseName, current_tablelist) into BEFORE_DELETE;

 set Final_BEFORE_DELETE= CONCAT(Final_BEFORE_DELETE, BEFORE_DELETE);
   
  select Function_Create_AFTER_INSERT_TRIGGER (databaseName, current_tablelist) into AFTER_INSERT;
  
   set Final_AFTER_INSERT= CONCAT(Final_AFTER_INSERT, AFTER_INSERT);
   
  select Function_Create_AFTER_UPDATE_TRIGGER (databaseName, current_tablelist) into AFTER_UPDATE;
  
   set Final_AFTER_UPDATE= CONCAT(Final_AFTER_UPDATE, AFTER_UPDATE);
  
    select Function_Drop_PrimaryKey_autoincrement (databaseName, current_tablelist) into Check_Each_PrimaryKey_Autoincrement;

   set Final_Check_Each_PrimaryKey_Autoincrement = CONCAT(Final_Check_Each_PrimaryKey_Autoincrement , Check_Each_PrimaryKey_Autoincrement);

    select Function_Drop_index (databaseName, current_tablelist) into Check_index;

   set Final_Check_index  = CONCAT(Final_Check_index, Check_index);

   END IF;  /* If the Audit DB already has that table */
   
  END LOOP  get_tabeslist;	
  
  set Final_FieldRemoved_statement = CONCAT('use Audit_info ; ',Final_FieldRemoved_statement);
  set Final_FieldAdded_statement= CONCAT('use Audit_info ; ',Final_FieldAdded_statement);
   set Final_BEFORE_DELETE= CONCAT('use ',databaseName,'; ',Final_BEFORE_DELETE);
     set Final_AFTER_INSERT= CONCAT('use ',databaseName,'; ',Final_AFTER_INSERT);
      set Final_AFTER_UPDATE= CONCAT('use ',databaseName,'; ',Final_AFTER_UPDATE);
      set Final_Check_Each_PrimaryKey_Autoincrement = CONCAT('use Audit_info ; ', Final_Check_Each_PrimaryKey_Autoincrement );
          set Final_Check_index  = CONCAT('use Audit_info ; ', Final_Check_index );
       
 select Final_FieldRemoved_statement as "Step 0: This is the total field remove statement(s), please copy and paste to a MySQL console and run this script"; 
select Final_FieldAdded_statement as "Step 0: This is the total field add statement(s), please copy and paste to a MySQL console and run this script"; 
select Final_BEFORE_DELETE as "Step 1: This is the  total before delete trigger statement(s), please copy and paste to a MySQL console and run this script"; 
select Final_AFTER_INSERT as "Step 2: This is the total after insert trigger statement(s), please copy and paste to a MySQL console and run this script"; 
select Final_AFTER_UPDATE as "Step 3: This is the total after update trigger statement(s), please copy and paste to a MySQL console and run this script"; 
select Final_Check_Each_PrimaryKey_Autoincrement  as "Step 4: This is the total drop primary key and auto increment statement(s), please copy and paste to a MySQL console and run this script"; 
  select Final_Check_index   as "Steps 5: This is the script to drop all index in Audit table "; 
  
close tablename;                             

END $$
DELIMITER ; 

Select allOpen in new window

but TODAY can't see why the Trigger statement it created seems not complete, it only happend today and I didn't change anything !!!!!! it works many weeks before.

the only problem I can see is, the variable use to print out the final CREATE TRIGGER statement can't hold all string, and the result of the CREATE TRIGGER statement is:

DROP TRIGGER IF EXISTS PACKAGE_ELEMENT_AFTER_INSERT_TRIGGER;  CREATE TRIGGER PACKAGE_ELEMENT_AFTER_INSERT_TRIGGER AFTER INSERT ON PCCWSP.PACKAGE_ELEMENT FOR EACH ROW 
BEGIN 
INSERT INTO Audit_info.PCCWSP_DBAudit_PACKAGE_ELEMENT SET 
TriggerAction="AFTER",
ActionDone="INSERT",
ActionDate=now(),
ActionBy=USER() , PACKAGE_ID=NEW.PACKAGE_ID, ELEMENT_ID=NEW.ELEMENT_ID, PRODUCT_FAMILY=NEW.PRODUCT_FAMILY, 
PRODUCT_TYPE=NEW.PRODUCT_TYPE, VENDOR_TYPE=NEW.VENDOR_TYPE, VENDOR_ID=NEW.VENDOR_

Select allOpen in new window

you can that last field for the trigger is  VENDOR_ID=NEW.VENDOR_ but not VENDOR_ID=NEW.VENDOR_ID.

Any thing I need to change to fix this ?