the clone of MySQL without cloning primary key.

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 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
RobOwner (Aidellio)Commented:
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
 
PortletPaulfreelancerCommented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
ZberteocCommented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
Zberteoc,

what I mean is any automated way to do this ! your solution is good but I have to do it manually.
0
 
PortletPaulfreelancerCommented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
"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
 
PortletPaulfreelancerCommented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
so the thing is there are no automated way to drop the primary key and index.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
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
 
RobOwner (Aidellio)Commented:
I couldn't get it to work, however I'll try something else
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
PortletPaulfreelancerCommented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
"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
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
RobOwner (Aidellio)Commented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.