Solved

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

Posted on 2014-02-18
8
540 Views
Last Modified: 2014-02-20
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 ; 

Open 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_

Open 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 ?
0
Comment
Question by:marrowyung
  • 5
  • 3
8 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 39869631
the database name passed to this SP as parameter only has 155 tables, the produciton DB which will has this SP deploy will have 1000 tables.
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 39871367
I havent' used text since sql2005 so I'm not 100% on it's size limitations, however I know that varchar(max) can go upto 2gb
http://technet.microsoft.com/en-us/library/ms176089.aspx
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39872297
noon, I am talking about MySQL, not MS SQL.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 38

Accepted Solution

by:
Aaron Tomosky earned 500 total points
ID: 39872389
Whoops. For MySQL you probably need mediumtext or longtext
http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39872417
LONGTEXT is the largest one ?
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 39872426
Yes. The link explains all the sizes if you scroll down
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39872450
I just tried that and it seems working for me for that 15x tables !!

then I need to execute it 10 times probably for 1000 tables, if the longtext can't do it again !!

but one thing, which statement tells LONGTEXT is the largest one ? I dont see one related.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39872452
i just read longtext use 4 bytes as the storage unit and it's size is 4GB.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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