Solved

the clone of MySQL without cloning primary key.

Posted on 2013-11-19
24
327 Views
Last Modified: 2013-11-26
Dear all,

I want to clone a table and store it in antoher database but not data inside by:


CREATE TABLE <database1>.AB_Audit LIKE <database2>.AB;

but this one will also clone the primary key to <database1>.AB_Audit too.

Anyway to not cloning the primary key too as when I do this on <database1>.AB_Audit:

ALTER TABLE AB_Audit 
ADD DBAuditID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
ADD TriggerAction VARCHAR( 10 ) NULL AFTER DBAuditID,
ADD Action CHAR( 10 ) NULL AFTER TriggerAction ,
ADD ActionDate DATETIME NULL AFTER Action ,
ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;

Open in new window


it will say duplicate primary key detected.

Any suggestion ?
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
  • 16
  • 4
  • 3
  • +1
24 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
ID: 39661137
really need to know how the existing primary key is defined, but assuming is it an auto increment field [ID], then:

-- remove the auto increment first
ALTER TABLE AB_Audit MODIFY id INT NOT NULL;

-- then remove that existing primary key
ALTER TABLE AB_Audit DROP PRIMARY KEY;

-- now add the new stuff as shown in question
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39661858
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39663279
Create table and then drop the PK column:

CREATE TABLE <database1>.AB_Audit LIKE <database2>.AB;
ALTER TABLE <database1>.AB_Audit DROP COLUMN <pk_colum_name>here>;

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:marrowyung
ID: 39664808
Zberteoc,

what I mean is any automated way to do this ! your solution is good but I have to do it manually.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39664827
drop the the primary key column is good? I don't think so

how will you trace all changes made to a record if the primary key value is not present?
i.e. how will you produce an "audit report" of any source record?

all you will have without that vital reference is a pile of useless data.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39664900
"how will you trace all changes made to a record if the primary key value is not present?
i.e. how will you produce an "audit report" of any source record?

basically is one of the developer of that system told me this. she understand the system but you are right too !I dont understnad this !

this statement:

ADD DBAuditID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,

Open in new window


will gives error if the existing primary key is still there.

so probably do this:

ADD DBAuditID BIGINT UNSIGNED NOT NULL,

Open in new window


right ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39664919
did you even try what I proposed? It's quite simple, remove the auto incrment, drop the primary key, add the new stuff.

-- remove the auto increment first
ALTER TABLE AB_Audit MODIFY id INT NOT NULL;

-- then remove that existing primary key
ALTER TABLE AB_Audit DROP PRIMARY KEY;

-- now add the new stuff as shown in question
You MUST retain the [id] data, otherwise you are not in a position to "audit" the source table.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39665085
ok.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39668090
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39670884
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39673844
This is the existing talbe design and we have a primary key here:

CREATE TABLE `YYYY` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `BLOB_DATA` blob,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `SCHED_NAME` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  CONSTRAINT `QRTZ_BLOB_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `QRTZ_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39673848
so the thing is there are no automated way to drop the primary key and index.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39673934
I read this :
stackoverflow.com/questions/3798524/mysql-dropping-all-indexes-from-table?s=ca15d66a-5c5f-459f-80f5-58430b24626e#new-answer

can the SHOW INDEX FROM <table thing> plugable to the cursor and let we loop that one by one ?
0
 
LVL 43

Accepted Solution

by:
Rob earned 300 total points
ID: 39674424
Rather than the SHOW INDEX, you can just add another loop to find these indexes in the INFORMATION_SCHEMA.  The code below loops through the tables and for each table loops through the table constraints.  You could at that point execute a prepared statement OR run the ALTER table DROP etc

DROP PROCEDURE IF EXISTS `create_auditrigger`;
DELIMITER $$

CREATE PROCEDURE `create_auditrigger`(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 tablelist VARCHAR(50) DEFAULT ""; 
DECLARE myindex VARCHAR(50) DEFAULT "";
DECLARE fullexcutecmd VARCHAR(200)  ;
DECLARE mytables VARCHAR(255);
/*Declare and populate the cursor with a SELECT statement */  	
	
DECLARE AAA CURSOR FOR 	
SELECT DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.COLUMNS	
    WHERE TABLE_SCHEMA=databaseName;   	
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 AAA;	



get_tabeslist: LOOP	

	FETCH AAA INTO tablelist;	

	SELECT tablelist;
	BLOCK: BEGIN
	DECLARE finished1  INT DEFAULT 10;

	DECLARE BBB CURSOR FOR
	SELECT COLUMN_NAME
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	WHERE TABLE_NAME=tablelist AND CONSTRAINT_NAME='PRIMARY';
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished1 = 1;

	OPEN BBB;
	get_indexes: LOOP
		FETCH BBB INTO myindex;
		SELECT myindex;
		IF finished1 = 1 then
			leave get_indexes;
		END IF;
	END LOOP get_indexes;
	CLOSE BBB;
	END BLOCK;
	IF finished = 1 THEN 	
		LEAVE  get_tabeslist;	
	END IF;	

END LOOP  get_tabeslist;	
close AAA;                             
/*select mytables;*/
END

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39675054
but one thing, how can I drop all constraint for each table I clone ? as the audit table do not need constraint and that make the whole solution much easier, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39675060
but one thing:

"can the SHOW INDEX FROM <table thing> plugable to the cursor and let we loop that one by one ?"

so this mean yes?
0
 
LVL 43

Expert Comment

by:Rob
ID: 39676059
I couldn't get it to work, however I'll try something else
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39676545
ok, let me know if you find something ! we should try the most simple one !

right now we will take care on how to detect the column has change the lenght of that field and add field, too.

any idea? still the not exists stataement ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39676662
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39676672
this question is expanding in scope.

the clone of a MySQL table without cloning primary key is achieved by goes something like this
CREATE TABLE <database1>.AB_Audit LIKE <database2>.AB;

-- remove any auto increment (if it exists) on the existing primary key first 
ALTER TABLE <database1>.AB_Audit MODIFY id INT NOT NULL; 

-- then remove that existing primary key
ALTER TABLE <database1>.AB_Audit DROP PRIMARY KEY;

-- then add wanted extras
ALTER TABLE AB_Audit 
ADD DBAuditID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
ADD TriggerAction VARCHAR( 10 ) NULL AFTER DBAuditID,
ADD Action CHAR( 10 ) NULL AFTER TriggerAction ,
ADD ActionDate DATETIME NULL AFTER Action ,
ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;

Open in new window

As has been noted in another question when you use LIKE it will be an exact copy with indexes, so the scripts you develop for this "cloning" need to take these basic steps into account.

In my example above ID is assumed as the existing primary key - but you have to determine this in you script. Also if there is an auto-increment that has to be removed - but your script must determine if an auto-increment actually does exist.

On top of all that now you are also trying to introduce the difficult and complex piece of detecting changes in schema.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39676978
"As has been noted in another question when you use LIKE it will be an exact copy with indexes, so the scripts you develop for this "cloning" need to take these basic steps into account.:"

Nice, after seeing the example.

I just found the primary key index can't be drop or it prompt an error,  I will just ignore it or whatelse I can do ?

but one thing, we put that in a SP and how can we execute "ALTER TABLE <database1>.AB_Audit MODIFY id INT NOT NULL; " inside the SP ? it seem it must using concat ?

I can call another SP inside a SP using this:

  call Create_BEFORE_DELETE_TRIGGER (databaseName, current_tablelist);

Open in new window


but how about your example ?

"but your script must determine if an auto-increment actually does exist.
"

this is anothe one that need to programm and hard to do it. any suggestion?

"On top of all that now you are also trying to introduce the difficult and complex piece of detecting changes in schema. "

I think so but no choice if I want to do better, what else you can suggest to simplify that ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39677187
0
 
LVL 43

Expert Comment

by:Rob
ID: 39679304
I agree with PortletPaul that you should close this question as it has already been answered and open a new one (if you haven't already) regarding the dynamic dropping of indexes when cloning a table.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39679719
"ALTER TABLE AB_Audit DROP PRIMARY KEY;"

one thing I can tell you all is, true delete the primary key index, the MysQL will complain that these key is necessary and can't be drop.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…

733 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