Solved

use system time as part of the Dynamic MySQL statement.,

Posted on 2014-02-13
24
298 Views
Last Modified: 2014-03-06
Rigth now I want to reaname a table using Dynamic SQL statement in MySQL:

  
   SET @TableRenameCommand = CONCAT( 'RENAME TABLE Audit_info.',databaseName,'_DBAudit_',current_tablelist ,' to Audit_info.',databaseName,'_DBAudit_',
     current_tablelist,' cutoff_at: ',now());   

         PREPARE stmt FROM @TableRenameCommand;
         EXECUTE stmt;
         DEALLOCATE PREPARE stmt;     
           

Open in new window


but when execute it, error comes out:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cutoff_at: 2014-02-13 11:20:53' at line 1

Open in new window


I know the error is here:

cutoff_at: ',now());   

Open in new window


how can I add the system timestamp as the part of the dynamic MYSQL statement ?
0
Comment
Question by:marrowyung
  • 14
  • 10
24 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39856267
Hi, you need to format it as a string, so you can concatenate it to the other string literals.
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

For example:
SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%S')

Open in new window

Results in:
20140213101833

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39856306
so it is:

SET @TableRenameCommand = CONCAT( 'RENAME TABLE Audit_info.',databaseName,'_DBAudit_',current_tablelist ,' to Audit_info.',databaseName,'_DBAudit_',
     current_tablelist,' cutoff_at: ',DATE_FORMAT(NOW(), '%Y%m%d%H%i%S') );   

         PREPARE stmt FROM @TableRenameCommand;
         EXECUTE stmt;
         DEALLOCATE PREPARE stmt;  

Open in new window


?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39856322
It should be if you want the system time in format I showed as an example, but you can temporarily change your code to audit it.

SET @TableRenameCommand = CONCAT( 'RENAME TABLE Audit_info.',databaseName,'_DBAudit_',current_tablelist ,' to Audit_info.',databaseName,'_DBAudit_',
     current_tablelist,' cutoff_at: ',DATE_FORMAT(NOW(), '%Y%m%d%H%i%S') );   

SELECT @TableRenameCommand;

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39858091
it still says:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cutoff_at: 20140214' at line 1

Open in new window


any other suggestion ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39858201
today I tested it on MYSQL 5.5 enterprise edition, a very rubblish DB I ever seen, something surprise AGAIN in doing SQL programming:

   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') );   

         PREPARE stmt FROM @TableRenameCommand;
         EXECUTE stmt;
         DEALLOCATE PREPARE stmt;    

Open in new window


this one work but the string is not what I want, the table name it created is :

Audit_info.SchemaChanged_DBAudit_PACKAGE_ELEMENT2014_02_14

Open in new window


but I can't do it as

Audit_info.DBAudit_PACKAGE_ELEMENT)SchemaChanged_2014_02_14

Open in new window


I can't see why I can't add any string in between:

current_tablelist and DATE_FORMAT(NOW(), '%Y_%m_%d').

or it will say syntax error before  DATE_FORMAT(NOW(), '%Y_%m_%d').

Any reason for this?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39858752
Is the ')' a type-o?
I do not see anything obvious why Audit_info.DBAudit_PACKAGE_ELEMENT_SchemaChanged_2014_02_14 would not work, but there is a table name limit.  It should be 64 characters, though.
http://dev.mysql.com/doc/refman/5.5/en/identifiers.html

Also, if you successfully completed the rename previous, the new name may already exist.  Double check that the target name is not in MySQL.  Anyway, I suspect it is just a concatenation issue where it is picking up something it should not, or leaving a space in the wrong place.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39863837
"Is the ')' a type-o?"

what is this mean ?

"Audit_info.DBAudit_PACKAGE_ELEMENT_SchemaChanged_2014_02_14 would not work, but there is a table name limit.  It should be 64 characters, though.
http://dev.mysql.com/doc/refman/5.5/en/identifiers.html"

this is the only thing I can think about too ! but it is not ! I shorten the who characters by removing some, the result is the same !

"Also, if you successfully completed the rename previous, the new name may already exist.Double check that the target name is not in MySQL"

Don't understand what this mean. if the same table already exist, then error message should comes out ! I fix all of them already !

"Anyway, I suspect it is just a concatenation issue where it is picking up something it should not, or leaving a space in the wrong place. "

I use the concat for a lot parts in my programming and only this has problem right now ! just can't see why ! it only appear when I use the now() and dateformat.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39864597
Can you post the entire code block for the procedure?  Need to see the definitions for the variables/parameters you are using as well as the values you are passing it (i.e., how you call the procedure).  It appears there is a bit of a language barrier, so I will try to explain what I mean with examples.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39865052
this is the code:

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


please watch the section /* this is the trouble statement */
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39865866
What results do you get with this?
DELIMITER $$

CREATE PROCEDURE `create_auditriggers_forALL_test` (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'), ';' );   

   SELECT @TableRenameCommand;
 
  END IF; 
   
  END LOOP  get_tabeslist;	

  
close tablename;                             

END $$
DELIMITER ;
CALL `create_auditriggers_forALL_test`('your_db_name');

Open in new window


I ask as I do not get the error message you specified, so want to see if you can at least get to this part using trimmed down procedure code.  You may need to slowly add in the other parts of the code to determine what exactly causes the error.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39865867
P.S. you can RENAME multiple tables at a time, separating them via a comma; therefore, you may not need a CURSOR; however, you will need to examine the remainder of your requirements.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39866513
"you may not need a CURSOR;"

why you say this ? you don't like cursor, I like that very much ! it can make sure I can loop out the next row easily. or how to do it in while loop or other loop ?

executing your version of script do not have problem! but what i experiencing is

if I change this:

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') );   

Open in new window


to this :

SET @TableRenameCommand = CONCAT( 'RENAME TABLE Audit_info.',databaseName,'_DBAudit_',current_tablelist ,' to Audit_info.SchemaChanged',
   databaseName,'_DBAudit_',current_tablelist, ' on ',DATE_FORMAT(NOW(), '%Y_%m_%d_%H_%i_%S') );   

Open in new window


and run the SP again, I will get an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on 2014_02_18_04_43_10' at line 1

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39867091
Yes, you cannot have a space in the name of the table without quoting it.
i.e., `This is a name with spaces`

Moreover, run the query I gave you "as-is," so we can troubleshoot the original issue unless you were trying to use a name with special characters before.  If so, the fix is to add the quoted identifiers to the concatenation.

Cursors have their place but set-based operations generally perform better.  My point simply was you may be able to execute one RENAME of the tables meeting your criteria instead of row-by-row through the INFORMATION_SCHEMA as the cursor will.

Anyway, I hope that helps!
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39867631
no, for concat, it should work and that's why we use concat to combine variable and string together and make it finally become a string, right?


you can see that all of my concat use in this way and that's why right now I use DATE_FORMAT(NOW(), '%Y_%m_%d_%H_%i_%S') with string ( ' on ' ).

"If so, the fix is to add the quoted identifiers to the concatenation.
"
what is that mean ?

\"set-based operations generally perform better"

ok, good, same as MS SQL. But one thing, any good example of what is set-based query/operation.  

so don't even need:

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)
)

Open in new window


to check in advance ? show me your case if your do this on how to use one query to rename that as long as it found out there field dropped.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39867833
Let's solve the first problem.
Try this:
SET @TableRenameCommand = CONCAT( 'RENAME TABLE Audit_info.',databaseName,'_DBAudit_',current_tablelist ,' to `Audit_info.SchemaChanged',
   databaseName,'_DBAudit_',current_tablelist, ' on ',DATE_FORMAT(NOW(), '%Y_%m_%d_%H_%i_%S'), '`;' );   

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39869285
now as it is a select statement is said:

RENAME TABLE Audit_info.PCCWSP_DBAudit_PACKAGE_ELEMENT to `Audit_info.SchemaChangedPCCWSP_DBAudit_PACKAGE_ELEMENT on 2014_02_19_03_00_43`;

Open in new window


so now put into my original script, now it complian other message:

Incorrect table name 'Audit_info.SchemaChangedyyy_DBAudit_PACKAGE_ELEMENT on 2014_02_19_03_02_34'

Open in new window


what it means to you now ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39869288
I can tell you that thsi kind of characters:

'`;'

and

' to `Audit_info.SchemaChanged'

shouldn't be matter, as this kind of character I tried it from time to time and it should be but not with the DATE_FORMAT.

this is where the main problem is.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39869293
I also tried to shorten the lenght by removing DBAudit word and it still complain the smae message.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39873459
It is not a DATE_FORMAT problem.  You should not name your tables with spaces or special characters.  I think the answer to the question on how to insert the system time is complete.  Your current issue is in how you are using it.  I suggest reviewing the entire conversation here again, then thinking through what it is you are trying to do and make sure your code matches without creating unnecessarily complex code.

Good luck!
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39873693
"You should not name your tables with spaces or special characters"

special characters means the date ? 2014_05_16 as part of the name is not allowed?
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39873730
The spaces are an example!

Instead of a table name of "Audit_info.SchemaChangedyyy_DBAudit_PACKAGE_ELEMENT on 2014_02_19_03_02_34", MySQL will try to run a command that has a table name of "'Audit_info.SchemaChangedyyy_DBAudit_PACKAGE_ELEMENT" followed by the words "on" and "2014_02_19_03_02_34" which it cannot recognize as valid syntax.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39875853
yeah, the best answer@!! but if I want to create the file name with data the table rename, what do you suggest ?

I just want to make the whole thing understand by human !

again, I think we should bring back the original problem, why can't insert ' on' or in between  'Audit_info.SchemaChangedyyy_DBAudit_PACKAGE_ELEMENT and 2014_02_19_03_02_34 ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39875901
if you take a look at my example, The spaces are not a problem, I keep using it as part of the Dynamic SQL for a long time.

Again back to my question ! here comes the problem ONLY when any string come before the dataformat !
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39911550
MySQL really has a lot of problem in this area.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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

22 Experts available now in Live!

Get 1:1 Help Now