How can I do a safe transaction?
I'm working on a samp game server with pawno script language, And I'm using a mysql plugin for it.
What I want is simply:
DELETE FROM mytable1 WHERE aid = '2' and bid = '8';
INSERT INTO mytable1 (aid,bid) VALUES ('6','3');
UPDATE mytable1 SET cid = '5' WHERE aid = '1' and bid = '1';
If any of them fails, the transaction should be terminated.
I used transactions as:
START TRANSACTION;
QUERY;
QUERY;
COMMIT;
but it didn't work. I tried to add set autocommit=0; at the beginning and it didn't work.
There was no 'delete' action but still 'insert' and 'update' where processed.
I can't use a stored procedure because either the plugin is not stable on it or I did something wrong, because I get syntax errors.
Lastly, I need to send my query as inline format.
Probably plugin is not stable for transactions too..
Is there a way to do that safe action?
mysql
edited Dec 29 '16 at 14:51
Eran
23914
asked Sep 16 '16 at 16:14
Ajna SarutAjna Sarut
1
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'm working on a samp game server with pawno script language, And I'm using a mysql plugin for it.
What I want is simply:
DELETE FROM mytable1 WHERE aid = '2' and bid = '8';
INSERT INTO mytable1 (aid,bid) VALUES ('6','3');
UPDATE mytable1 SET cid = '5' WHERE aid = '1' and bid = '1';
If any of them fails, the transaction should be terminated.
I used transactions as:
START TRANSACTION;
QUERY;
QUERY;
COMMIT;
but it didn't work. I tried to add set autocommit=0; at the beginning and it didn't work.
There was no 'delete' action but still 'insert' and 'update' where processed.
I can't use a stored procedure because either the plugin is not stable on it or I did something wrong, because I get syntax errors.
Lastly, I need to send my query as inline format.
Probably plugin is not stable for transactions too..
Is there a way to do that safe action?
mysql
edited Dec 29 '16 at 14:51
Eran
23914
asked Sep 16 '16 at 16:14
Ajna SarutAjna Sarut
1
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.
Please provideSHOW CREATE TABLE.
– Rick James
Sep 17 '16 at 22:55
5.0.1-- Ouch. That is a decade old, and it is a pre-release version. Upgrade!
– Rick James
Sep 17 '16 at 22:56
add a comment |
I'm working on a samp game server with pawno script language, And I'm using a mysql plugin for it.
What I want is simply:
DELETE FROM mytable1 WHERE aid = '2' and bid = '8';
INSERT INTO mytable1 (aid,bid) VALUES ('6','3');
UPDATE mytable1 SET cid = '5' WHERE aid = '1' and bid = '1';
If any of them fails, the transaction should be terminated.
I used transactions as:
START TRANSACTION;
QUERY;
QUERY;
COMMIT;
but it didn't work. I tried to add set autocommit=0; at the beginning and it didn't work.
There was no 'delete' action but still 'insert' and 'update' where processed.
I can't use a stored procedure because either the plugin is not stable on it or I did something wrong, because I get syntax errors.
Lastly, I need to send my query as inline format.
Probably plugin is not stable for transactions too..
Is there a way to do that safe action?
mysql
edited Dec 29 '16 at 14:51
Eran
23914
asked Sep 16 '16 at 16:14
Ajna SarutAjna Sarut
1
I'm working on a samp game server with pawno script language, And I'm using a mysql plugin for it.
What I want is simply:
DELETE FROM mytable1 WHERE aid = '2' and bid = '8';
INSERT INTO mytable1 (aid,bid) VALUES ('6','3');
UPDATE mytable1 SET cid = '5' WHERE aid = '1' and bid = '1';
If any of them fails, the transaction should be terminated.
I used transactions as:
START TRANSACTION;
QUERY;
QUERY;
COMMIT;
but it didn't work. I tried to add set autocommit=0; at the beginning and it didn't work.
There was no 'delete' action but still 'insert' and 'update' where processed.
I can't use a stored procedure because either the plugin is not stable on it or I did something wrong, because I get syntax errors.
Lastly, I need to send my query as inline format.
Probably plugin is not stable for transactions too..
Is there a way to do that safe action?
mysql
mysql
edited Dec 29 '16 at 14:51
Eran
23914
asked Sep 16 '16 at 16:14
Ajna SarutAjna Sarut
1
edited Dec 29 '16 at 14:51
Eran
23914
asked Sep 16 '16 at 16:14
Ajna SarutAjna Sarut
1
edited Dec 29 '16 at 14:51
Eran
23914
edited Dec 29 '16 at 14:51
Eran
23914
edited Dec 29 '16 at 14:51
Eran
23914
23914
asked Sep 16 '16 at 16:14
Ajna SarutAjna Sarut
1
asked Sep 16 '16 at 16:14
Ajna SarutAjna Sarut
1
asked Sep 16 '16 at 16:14
Ajna SarutAjna Sarut
1
1
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.
Please provideSHOW CREATE TABLE.
– Rick James
Sep 17 '16 at 22:55
5.0.1-- Ouch. That is a decade old, and it is a pre-release version. Upgrade!
– Rick James
Sep 17 '16 at 22:56
add a comment |
Please provideSHOW CREATE TABLE.
– Rick James
Sep 17 '16 at 22:55
5.0.1-- Ouch. That is a decade old, and it is a pre-release version. Upgrade!
– Rick James
Sep 17 '16 at 22:56
Please provide
SHOW CREATE TABLE.– Rick James
Sep 17 '16 at 22:55
Please provide
SHOW CREATE TABLE.– Rick James
Sep 17 '16 at 22:55
5.0.1 -- Ouch. That is a decade old, and it is a pre-release version. Upgrade!– Rick James
Sep 17 '16 at 22:56
5.0.1 -- Ouch. That is a decade old, and it is a pre-release version. Upgrade!– Rick James
Sep 17 '16 at 22:56
add a comment |
1 Answer
1
active
oldest
votes
Which store engine do you use? If you are using MyISAM, it is not supported transactions. In this topic it is described: http://forums.mysql.com/read.php?21,68686,69229#msg-69229 On the other hand some user said that 5+ versions have auto commit mode by default. May be you make table design change for MyISAM. Don't delete rows in multi user system because of a lot of index operation start for delete. Alternatively use another column for delete state.
answered Sep 16 '16 at 18:23
mkysoftmkysoft
1011
Im using innodb, mysql version 5.0.1. Can you give me one example for my needs which includes rollback and commit on it. Also right now, im thinking to do this like, check data with 3 select in one query and get counts. If all counts > 0 then use the transaction. If i cant use rollback thats the only way to do i believe.
– Ajna Sarut
Sep 17 '16 at 2:57
I think this plugin run command one by one, so transactions not working. This plugin can run stored procedure? May be you create stored procedure for this: stackoverflow.com/questions/18817148/…
– mkysoft
Sep 17 '16 at 7:51
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%2f149905%2fhow-can-i-do-a-safe-transaction%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
Which store engine do you use? If you are using MyISAM, it is not supported transactions. In this topic it is described: http://forums.mysql.com/read.php?21,68686,69229#msg-69229 On the other hand some user said that 5+ versions have auto commit mode by default. May be you make table design change for MyISAM. Don't delete rows in multi user system because of a lot of index operation start for delete. Alternatively use another column for delete state.
answered Sep 16 '16 at 18:23
mkysoftmkysoft
1011
Im using innodb, mysql version 5.0.1. Can you give me one example for my needs which includes rollback and commit on it. Also right now, im thinking to do this like, check data with 3 select in one query and get counts. If all counts > 0 then use the transaction. If i cant use rollback thats the only way to do i believe.
– Ajna Sarut
Sep 17 '16 at 2:57
I think this plugin run command one by one, so transactions not working. This plugin can run stored procedure? May be you create stored procedure for this: stackoverflow.com/questions/18817148/…
– mkysoft
Sep 17 '16 at 7:51
add a comment |
Which store engine do you use? If you are using MyISAM, it is not supported transactions. In this topic it is described: http://forums.mysql.com/read.php?21,68686,69229#msg-69229 On the other hand some user said that 5+ versions have auto commit mode by default. May be you make table design change for MyISAM. Don't delete rows in multi user system because of a lot of index operation start for delete. Alternatively use another column for delete state.
answered Sep 16 '16 at 18:23
mkysoftmkysoft
1011
Im using innodb, mysql version 5.0.1. Can you give me one example for my needs which includes rollback and commit on it. Also right now, im thinking to do this like, check data with 3 select in one query and get counts. If all counts > 0 then use the transaction. If i cant use rollback thats the only way to do i believe.
– Ajna Sarut
Sep 17 '16 at 2:57
I think this plugin run command one by one, so transactions not working. This plugin can run stored procedure? May be you create stored procedure for this: stackoverflow.com/questions/18817148/…
– mkysoft
Sep 17 '16 at 7:51
add a comment |
Which store engine do you use? If you are using MyISAM, it is not supported transactions. In this topic it is described: http://forums.mysql.com/read.php?21,68686,69229#msg-69229 On the other hand some user said that 5+ versions have auto commit mode by default. May be you make table design change for MyISAM. Don't delete rows in multi user system because of a lot of index operation start for delete. Alternatively use another column for delete state.
answered Sep 16 '16 at 18:23
mkysoftmkysoft
1011
Which store engine do you use? If you are using MyISAM, it is not supported transactions. In this topic it is described: http://forums.mysql.com/read.php?21,68686,69229#msg-69229 On the other hand some user said that 5+ versions have auto commit mode by default. May be you make table design change for MyISAM. Don't delete rows in multi user system because of a lot of index operation start for delete. Alternatively use another column for delete state.
answered Sep 16 '16 at 18:23
mkysoftmkysoft
1011
answered Sep 16 '16 at 18:23
mkysoftmkysoft
1011
answered Sep 16 '16 at 18:23
mkysoftmkysoft
1011
answered Sep 16 '16 at 18:23
mkysoftmkysoft
1011
1011
Im using innodb, mysql version 5.0.1. Can you give me one example for my needs which includes rollback and commit on it. Also right now, im thinking to do this like, check data with 3 select in one query and get counts. If all counts > 0 then use the transaction. If i cant use rollback thats the only way to do i believe.
– Ajna Sarut
Sep 17 '16 at 2:57
I think this plugin run command one by one, so transactions not working. This plugin can run stored procedure? May be you create stored procedure for this: stackoverflow.com/questions/18817148/…
– mkysoft
Sep 17 '16 at 7:51
add a comment |
Im using innodb, mysql version 5.0.1. Can you give me one example for my needs which includes rollback and commit on it. Also right now, im thinking to do this like, check data with 3 select in one query and get counts. If all counts > 0 then use the transaction. If i cant use rollback thats the only way to do i believe.
– Ajna Sarut
Sep 17 '16 at 2:57
I think this plugin run command one by one, so transactions not working. This plugin can run stored procedure? May be you create stored procedure for this: stackoverflow.com/questions/18817148/…
– mkysoft
Sep 17 '16 at 7:51
Im using innodb, mysql version 5.0.1. Can you give me one example for my needs which includes rollback and commit on it. Also right now, im thinking to do this like, check data with 3 select in one query and get counts. If all counts > 0 then use the transaction. If i cant use rollback thats the only way to do i believe.
– Ajna Sarut
Sep 17 '16 at 2:57
Im using innodb, mysql version 5.0.1. Can you give me one example for my needs which includes rollback and commit on it. Also right now, im thinking to do this like, check data with 3 select in one query and get counts. If all counts > 0 then use the transaction. If i cant use rollback thats the only way to do i believe.
– Ajna Sarut
Sep 17 '16 at 2:57
I think this plugin run command one by one, so transactions not working. This plugin can run stored procedure? May be you create stored procedure for this: stackoverflow.com/questions/18817148/…
– mkysoft
Sep 17 '16 at 7:51
I think this plugin run command one by one, so transactions not working. This plugin can run stored procedure? May be you create stored procedure for this: stackoverflow.com/questions/18817148/…
– mkysoft
Sep 17 '16 at 7:51
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%2f149905%2fhow-can-i-do-a-safe-transaction%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
Please provide
SHOW CREATE TABLE.– Rick James
Sep 17 '16 at 22:55
5.0.1-- Ouch. That is a decade old, and it is a pre-release version. Upgrade!– Rick James
Sep 17 '16 at 22:56