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
546 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
[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
  • 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 39

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 39

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 39

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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