Solved

the clone of MySQL without cloning primary key.

Posted on 2013-11-19
24
315 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 42

Accepted Solution

by:
Rob Jurd, EE MVE 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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

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…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

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

19 Experts available now in Live!

Get 1:1 Help Now