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
533 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

757 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

21 Experts available now in Live!

Get 1:1 Help Now