Rollback any duplicate occured using EXIT HANDLERstored procedure returns ERROR 1305 (42000): FUNCTION does...
Was it really inappropriate to write a pull request for the company I interviewed with?
Are E natural minor and B harmonic minor related?
Can I negotiate a patent idea for a raise, under French law?
Sampling from Gaussian mixture models, when are the sampled data independent?
How do spaceships determine each other's mass in space?
Is there a logarithm base for which the logarithm becomes an identity function?
What do you call someone who likes to pick fights?
Idiom for feeling after taking risk and someone else being rewarded
Help! My Character is too much for her story!
How do we create new idioms and use them in a novel?
Why restrict private health insurance?
Why does Central Limit Theorem break down in my simulation?
What does the Digital Threat scope actually do?
Short scifi story where reproductive organs are converted to produce "materials", pregnant protagonist is "found fit" to be a mother
Is "cogitate" used appropriately in "I cogitate that success relies on hard work"?
How can I portion out frozen cookie dough?
Why is there an extra space when I type "ls" on the Desktop?
What can I do if someone tampers with my SSH public key?
How to make sure I'm assertive enough in contact with subordinates?
If nine coins are tossed, what is the probability that the number of heads is even?
What should I do when a paper is published similar to my PhD thesis without citation?
Are all players supposed to be able to see each others' character sheets?
Has a sovereign Communist government ever run, and conceded loss, on a fair election?
Difference between `nmap local-IP-address` and `nmap localhost`
Rollback any duplicate occured using EXIT HANDLER
stored procedure returns ERROR 1305 (42000): FUNCTION does not existCannot create stored procedureHow long will a temporary MEMORY table persist if I don't drop it (MySQL)Rollback parent procedure on failure in MySQLMultiple SELECT subqueries in an INSERT statement in a stored procedureMySQL Cluster lock wait timeout exceeded when trying to deal with huge amount of queriesmySql while loop ending after a insertDatabase Trees with MySqlCall to default values (MySQL) in a procedureMysql trigger to update if date match and insert if no match all BEFORE INSERT
I have two INT integer types in my table that set as UNIQUE KEY to avoid duplication. I wanted to rollback any duplicate occur in my stored procedure using EXIT HANDLER.
CREATE TABLE curriculum
(
id INT PRIMARY KEY AUTO_INCREMENT,
gradelevel_id INT,
schoolyear_id INT,
CONSTRAINT uc_curriculum UNIQUE (gradelevel_id, schoolyear_id)
)
DELIMITER //
CREATE PROCEDURE insertCurriculum(
IN pIN_gradelevelId INT,
IN pIN_schoolyearId INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT "Duplicate keys found";
RESIGNAL;
END;
INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (pIN_gradelevelId, pIN_schoolyearId);
END //
DELIMITER ;
I inserted manually in my curriculum table for the test.
INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (1, 1);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1`
FIRST TRY:
Calling my first stored procedure that has duplicate it automatically rollback.
call insertCurriculum(1, 1);
call insertCurriculum(2, 2);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1`
SECOND TRY:
Calling my first stored procedure that has no duplicate it allows the first stored procedure to be executed and insert into my table. Even the second stored procedure has duplicate. I wanted to rollback this. How can I achieve this? Any tips will greatly appreciated!
call insertCurriculum(2, 2);
call insertCurriculum(1, 1);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1
2 2 2`
mysql
asked Jun 21 '17 at 4:25
FrancisunoxxFrancisunoxx
1011
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have two INT integer types in my table that set as UNIQUE KEY to avoid duplication. I wanted to rollback any duplicate occur in my stored procedure using EXIT HANDLER.
CREATE TABLE curriculum
(
id INT PRIMARY KEY AUTO_INCREMENT,
gradelevel_id INT,
schoolyear_id INT,
CONSTRAINT uc_curriculum UNIQUE (gradelevel_id, schoolyear_id)
)
DELIMITER //
CREATE PROCEDURE insertCurriculum(
IN pIN_gradelevelId INT,
IN pIN_schoolyearId INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT "Duplicate keys found";
RESIGNAL;
END;
INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (pIN_gradelevelId, pIN_schoolyearId);
END //
DELIMITER ;
I inserted manually in my curriculum table for the test.
INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (1, 1);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1`
FIRST TRY:
Calling my first stored procedure that has duplicate it automatically rollback.
call insertCurriculum(1, 1);
call insertCurriculum(2, 2);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1`
SECOND TRY:
Calling my first stored procedure that has no duplicate it allows the first stored procedure to be executed and insert into my table. Even the second stored procedure has duplicate. I wanted to rollback this. How can I achieve this? Any tips will greatly appreciated!
call insertCurriculum(2, 2);
call insertCurriculum(1, 1);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1
2 2 2`
mysql
asked Jun 21 '17 at 4:25
FrancisunoxxFrancisunoxx
1011
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have two INT integer types in my table that set as UNIQUE KEY to avoid duplication. I wanted to rollback any duplicate occur in my stored procedure using EXIT HANDLER.
CREATE TABLE curriculum
(
id INT PRIMARY KEY AUTO_INCREMENT,
gradelevel_id INT,
schoolyear_id INT,
CONSTRAINT uc_curriculum UNIQUE (gradelevel_id, schoolyear_id)
)
DELIMITER //
CREATE PROCEDURE insertCurriculum(
IN pIN_gradelevelId INT,
IN pIN_schoolyearId INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT "Duplicate keys found";
RESIGNAL;
END;
INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (pIN_gradelevelId, pIN_schoolyearId);
END //
DELIMITER ;
I inserted manually in my curriculum table for the test.
INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (1, 1);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1`
FIRST TRY:
Calling my first stored procedure that has duplicate it automatically rollback.
call insertCurriculum(1, 1);
call insertCurriculum(2, 2);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1`
SECOND TRY:
Calling my first stored procedure that has no duplicate it allows the first stored procedure to be executed and insert into my table. Even the second stored procedure has duplicate. I wanted to rollback this. How can I achieve this? Any tips will greatly appreciated!
call insertCurriculum(2, 2);
call insertCurriculum(1, 1);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1
2 2 2`
mysql
asked Jun 21 '17 at 4:25
FrancisunoxxFrancisunoxx
1011
I have two INT integer types in my table that set as UNIQUE KEY to avoid duplication. I wanted to rollback any duplicate occur in my stored procedure using EXIT HANDLER.
CREATE TABLE curriculum
(
id INT PRIMARY KEY AUTO_INCREMENT,
gradelevel_id INT,
schoolyear_id INT,
CONSTRAINT uc_curriculum UNIQUE (gradelevel_id, schoolyear_id)
)
DELIMITER //
CREATE PROCEDURE insertCurriculum(
IN pIN_gradelevelId INT,
IN pIN_schoolyearId INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT "Duplicate keys found";
RESIGNAL;
END;
INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (pIN_gradelevelId, pIN_schoolyearId);
END //
DELIMITER ;
I inserted manually in my curriculum table for the test.
INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (1, 1);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1`
FIRST TRY:
Calling my first stored procedure that has duplicate it automatically rollback.
call insertCurriculum(1, 1);
call insertCurriculum(2, 2);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1`
SECOND TRY:
Calling my first stored procedure that has no duplicate it allows the first stored procedure to be executed and insert into my table. Even the second stored procedure has duplicate. I wanted to rollback this. How can I achieve this? Any tips will greatly appreciated!
call insertCurriculum(2, 2);
call insertCurriculum(1, 1);
SELECT * FROM curriculum
`id | gradelevel_id | schoolyear_id
1 1 1
2 2 2`
mysql
mysql
asked Jun 21 '17 at 4:25
FrancisunoxxFrancisunoxx
1011
asked Jun 21 '17 at 4:25
FrancisunoxxFrancisunoxx
1011
asked Jun 21 '17 at 4:25
FrancisunoxxFrancisunoxx
1011
asked Jun 21 '17 at 4:25
FrancisunoxxFrancisunoxx
1011
asked Jun 21 '17 at 4:25
FrancisunoxxFrancisunoxx
1011
1011
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Your question is very unclear, but maybe I can still answer it.
Why is it unclear?
What do you mean with duplicates exactly? In your examples there are none. A duplicate relates to rows. You would have a duplicate in your table, if you tried to insert (1, 1) two times.
If you meant with duplicates, that it shouldn't be allowed to have the same value in both columns, then you will have to catch that with a trigger. Create a before insert trigger and have the code something along the line if (new.gradelevel_id = new.schoolyear_id) then signal...; end if;
Anyway...(some more explaining)
In your procedure there won't be any rollback, cause there's nothing to roll back. A rollback statement only makes sense, when your transaction consists of multiple statements. To have multiple statements in a transaction you either have the auto_commit variable set to 0 or you explicitly start a transaction with START TRANSACTION; When you didn't do either, a single statement is also a transaction. When a statement fails, it is undone, you won't see it partially applied on the data. And like I said, the ROLLBACK is meant for the other statements in the transaction that have already executed.
Finally, a hint:
You might also want to have a look at INSERT IGNORE (you have already fulfilled the prerequisite of having a unique index). This way there won't be any errors when duplicates occur and your code keeps running without interruption.
answered Jun 21 '17 at 6:51
tombomtombom
2,34511222
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f176841%2frollback-any-duplicate-occured-using-exit-handler%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your question is very unclear, but maybe I can still answer it.
Why is it unclear?
What do you mean with duplicates exactly? In your examples there are none. A duplicate relates to rows. You would have a duplicate in your table, if you tried to insert (1, 1) two times.
If you meant with duplicates, that it shouldn't be allowed to have the same value in both columns, then you will have to catch that with a trigger. Create a before insert trigger and have the code something along the line if (new.gradelevel_id = new.schoolyear_id) then signal...; end if;
Anyway...(some more explaining)
In your procedure there won't be any rollback, cause there's nothing to roll back. A rollback statement only makes sense, when your transaction consists of multiple statements. To have multiple statements in a transaction you either have the auto_commit variable set to 0 or you explicitly start a transaction with START TRANSACTION; When you didn't do either, a single statement is also a transaction. When a statement fails, it is undone, you won't see it partially applied on the data. And like I said, the ROLLBACK is meant for the other statements in the transaction that have already executed.
Finally, a hint:
You might also want to have a look at INSERT IGNORE (you have already fulfilled the prerequisite of having a unique index). This way there won't be any errors when duplicates occur and your code keeps running without interruption.
answered Jun 21 '17 at 6:51
tombomtombom
2,34511222
add a comment |
Your question is very unclear, but maybe I can still answer it.
Why is it unclear?
What do you mean with duplicates exactly? In your examples there are none. A duplicate relates to rows. You would have a duplicate in your table, if you tried to insert (1, 1) two times.
If you meant with duplicates, that it shouldn't be allowed to have the same value in both columns, then you will have to catch that with a trigger. Create a before insert trigger and have the code something along the line if (new.gradelevel_id = new.schoolyear_id) then signal...; end if;
Anyway...(some more explaining)
In your procedure there won't be any rollback, cause there's nothing to roll back. A rollback statement only makes sense, when your transaction consists of multiple statements. To have multiple statements in a transaction you either have the auto_commit variable set to 0 or you explicitly start a transaction with START TRANSACTION; When you didn't do either, a single statement is also a transaction. When a statement fails, it is undone, you won't see it partially applied on the data. And like I said, the ROLLBACK is meant for the other statements in the transaction that have already executed.
Finally, a hint:
You might also want to have a look at INSERT IGNORE (you have already fulfilled the prerequisite of having a unique index). This way there won't be any errors when duplicates occur and your code keeps running without interruption.
answered Jun 21 '17 at 6:51
tombomtombom
2,34511222
add a comment |
Your question is very unclear, but maybe I can still answer it.
Why is it unclear?
What do you mean with duplicates exactly? In your examples there are none. A duplicate relates to rows. You would have a duplicate in your table, if you tried to insert (1, 1) two times.
If you meant with duplicates, that it shouldn't be allowed to have the same value in both columns, then you will have to catch that with a trigger. Create a before insert trigger and have the code something along the line if (new.gradelevel_id = new.schoolyear_id) then signal...; end if;
Anyway...(some more explaining)
In your procedure there won't be any rollback, cause there's nothing to roll back. A rollback statement only makes sense, when your transaction consists of multiple statements. To have multiple statements in a transaction you either have the auto_commit variable set to 0 or you explicitly start a transaction with START TRANSACTION; When you didn't do either, a single statement is also a transaction. When a statement fails, it is undone, you won't see it partially applied on the data. And like I said, the ROLLBACK is meant for the other statements in the transaction that have already executed.
Finally, a hint:
You might also want to have a look at INSERT IGNORE (you have already fulfilled the prerequisite of having a unique index). This way there won't be any errors when duplicates occur and your code keeps running without interruption.
answered Jun 21 '17 at 6:51
tombomtombom
2,34511222
Your question is very unclear, but maybe I can still answer it.
Why is it unclear?
What do you mean with duplicates exactly? In your examples there are none. A duplicate relates to rows. You would have a duplicate in your table, if you tried to insert (1, 1) two times.
If you meant with duplicates, that it shouldn't be allowed to have the same value in both columns, then you will have to catch that with a trigger. Create a before insert trigger and have the code something along the line if (new.gradelevel_id = new.schoolyear_id) then signal...; end if;
Anyway...(some more explaining)
In your procedure there won't be any rollback, cause there's nothing to roll back. A rollback statement only makes sense, when your transaction consists of multiple statements. To have multiple statements in a transaction you either have the auto_commit variable set to 0 or you explicitly start a transaction with START TRANSACTION; When you didn't do either, a single statement is also a transaction. When a statement fails, it is undone, you won't see it partially applied on the data. And like I said, the ROLLBACK is meant for the other statements in the transaction that have already executed.
Finally, a hint:
You might also want to have a look at INSERT IGNORE (you have already fulfilled the prerequisite of having a unique index). This way there won't be any errors when duplicates occur and your code keeps running without interruption.
answered Jun 21 '17 at 6:51
tombomtombom
2,34511222
answered Jun 21 '17 at 6:51
tombomtombom
2,34511222
answered Jun 21 '17 at 6:51
tombomtombom
2,34511222
answered Jun 21 '17 at 6:51
tombomtombom
2,34511222
2,34511222
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f176841%2frollback-any-duplicate-occured-using-exit-handler%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
