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
Solved

create MySQL table if not exists, ignore the talbe if it exists but still needs checking.

Posted on 2014-01-05
8
473 Views
Last Modified: 2014-01-08
right now I have the script to only create table if it doesn't exist:

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 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 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;	
  /* create the audit database's 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;  


  END LOOP  get_tabeslist;	
close tablename;                             

END $$
DELIMITER ; 
    

Open in new window


but it seems that if I execute this SP more than one time, and it see the same table which was created, will give error message and stop.

Anyway to fix it ?
0
Comment
Question by:marrowyung
  • 5
  • 2
8 Comments
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 39758499
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39758594
I need more specific answer.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39758645
for example I can do this to BYPASS it by:


     SELECT  DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA='PCCWSP' and table_type <> 'view' and TABLE_NAME NOT IN ( SELECT  DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA='Audit_info' and table_type<> 'view');

Open in new window


as the condition of the cursor, but the result don't allow me to check all table name everytime but only the new one.

So I have to use that first one.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39759427
Maybe you can wrap the creation in an IF statement.  Although, it is not jumping out at me why the IF NOT EXISTS is not working as expected.  It may be helpful to SELECT the @fullexcutecmd variable and see what the CREATE TABLE looks like after the CONCAT to make sure it is nothing odd there.

Anyway, you can wrap the below lines in an IF:

DECLARE auditExistsFlag BIT(1);  
SET auditExistsFlag = 0;

SELECT 1 INTO auditExistsFlag
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='Audit_info'
AND TABLE_NAME = CONCAT(databaseName,'_DBAudit_', current_tablelist)
;

IF auditExistsFlag = 0 THEN
  PREPARE stmt FROM @fullexcutecmd;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  
END IF;

Open in new window


I hope that helps!
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39761511
"Although, it is not jumping out at me why the IF NOT EXISTS is not working as expected"

exaction what I am thing, that's why I try to add the IF NOT EXISTS there but it don't works !!

"It may be helpful to SELECT the @fullexcutecmd variable and see what the CREATE TABLE looks like after the CONCAT to make sure it is nothing odd there.
"

this done at the beginning of this SP developement to troubleshoot everything ! MySQL is rubblish ! a lot of thing surprising DBA !

"SELECT 1 INTO auditExistsFlag"

why do this at the beginning and this variable never change to 1. And what is the main point of :

SELECT 1 INTO auditExistsFlag
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='Audit_info'
AND TABLE_NAME = CONCAT(databaseName,'_DBAudit_', current_tablelist)

Open in new window

?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39761525
I think the IF should be:

SELECT count(*) INTO auditExistsFlag
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='Audit_info'
AND TABLE_NAME = CONCAT(databaseName,'_DBAudit_', current_tablelist)
;

Open in new window


agree?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39761607
COUNT(*) should work, but there is either one table or zero; therefore, the way I have it should also.  If it is not changing to 1, then it is not recognizing the existence of the audit table, which is why you are getting the error.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39764615
thanks, I get it done already based on your suggsetion but I do this:

DECLARE TableExistsinAuditDB int (1) DEFAULT '0' ;
..
.
.
  SELECT count(*) INTO TableExistsinAuditDB    
FROM INFORMATION_SCHEMA.TABLES            
WHERE TABLE_SCHEMA='Audit_info'                        
AND TABLE_NAME = CONCAT(databaseName,'_DBAudit_', current_tablelist);  

if TableExistsinAuditDB < 1 then

.
.
.
.

END IF;
0

Featured Post

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.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

792 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