Solved

the clone of MySQL without cloning primary key.

Posted on 2013-11-19
24
329 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 49

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 27

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
Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 
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 49

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 49

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: 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 49

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

Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

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

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…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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