How to fix commands out of sync error in MySQL upon executing a big .sql file?
I have to create a database, tables and insert data into all of them. At the end, there should be a stored procedure created and invoked. This should all be done in one .sql file that can be imported to any MySQL database. The issue i'm facing is that after creating the procedure and trying to invoke it as well, upon importing i'm welcomed with error :
#2014 - Commands out of sync; you can't run this command now
I have done some research and played around with my code, and reached the conclusion that it all runs smoothly until the very last line, where I am invoking this procedure. If I delete that line and import my .sql file then all is grand - however, it is essential that the procedure is actually invoked at the end.
At this point I am not even sure if it's to do with phpmyadmin trying to bulk the statements which is why it goes out of sync or what, due to the sheer volume of the statements (there is about 150), as after a certain number of queries phpmyadmin doesn't even detail the single queries that are being run just returns '#1 row affected' or something like that at the end, which is weird because I have multiple joins and others which should be output independently (however im digressing).
I will not paste all of my .sql code in this, I have tested it and know the error is due to the fact that the procedure is being invoked at the end, just not sure how to solve it yet. This is the end of the .sql file:
drop procedure if exists GetStockItems;
delimiter //
create procedure db.GetStockItems(IN param_quantity int)
begin
select * from Stock s where s.quantity <= param_quantity;
end
//
delimiter ;
call db.GetStockItems(55);
drop procedure GetStockItems;
The procedure should return items with less than of a certain quantity from the database, however trying to invoke from the big imported file results in an error. If I call the procedure on the already created database then it works, but that's not the desired flow.
mysql phpmyadmin
edited Dec 28 '18 at 13:59
GMB
4,500519
asked Dec 27 '18 at 15:52
peterxz
102110
add a comment |
I have to create a database, tables and insert data into all of them. At the end, there should be a stored procedure created and invoked. This should all be done in one .sql file that can be imported to any MySQL database. The issue i'm facing is that after creating the procedure and trying to invoke it as well, upon importing i'm welcomed with error :
#2014 - Commands out of sync; you can't run this command now
I have done some research and played around with my code, and reached the conclusion that it all runs smoothly until the very last line, where I am invoking this procedure. If I delete that line and import my .sql file then all is grand - however, it is essential that the procedure is actually invoked at the end.
At this point I am not even sure if it's to do with phpmyadmin trying to bulk the statements which is why it goes out of sync or what, due to the sheer volume of the statements (there is about 150), as after a certain number of queries phpmyadmin doesn't even detail the single queries that are being run just returns '#1 row affected' or something like that at the end, which is weird because I have multiple joins and others which should be output independently (however im digressing).
I will not paste all of my .sql code in this, I have tested it and know the error is due to the fact that the procedure is being invoked at the end, just not sure how to solve it yet. This is the end of the .sql file:
drop procedure if exists GetStockItems;
delimiter //
create procedure db.GetStockItems(IN param_quantity int)
begin
select * from Stock s where s.quantity <= param_quantity;
end
//
delimiter ;
call db.GetStockItems(55);
drop procedure GetStockItems;
The procedure should return items with less than of a certain quantity from the database, however trying to invoke from the big imported file results in an error. If I call the procedure on the already created database then it works, but that's not the desired flow.
mysql phpmyadmin
edited Dec 28 '18 at 13:59
GMB
4,500519
asked Dec 27 '18 at 15:52
peterxz
102110
Have you tried this? stackoverflow.com/questions/8891179/…
– kmoser
Dec 27 '18 at 17:32
yes and none of the solutions worked from the thread. reading selected columns into variables would work if there was only 1 row in the dataset, but of course it rarely would be that (which then results in another error because it tries reading multiple values into the same variables).
– peterxz
Dec 28 '18 at 14:23
So you're saying you tried this directly from the MySQL command prompt (i.e. not using phpMyAdmin) and you still had the same problem?
– kmoser
Dec 28 '18 at 18:01
correct, using the command prompt and jamming the whole thing in there still resulted in the error.
– peterxz
Dec 29 '18 at 14:48
add a comment |
I have to create a database, tables and insert data into all of them. At the end, there should be a stored procedure created and invoked. This should all be done in one .sql file that can be imported to any MySQL database. The issue i'm facing is that after creating the procedure and trying to invoke it as well, upon importing i'm welcomed with error :
#2014 - Commands out of sync; you can't run this command now
I have done some research and played around with my code, and reached the conclusion that it all runs smoothly until the very last line, where I am invoking this procedure. If I delete that line and import my .sql file then all is grand - however, it is essential that the procedure is actually invoked at the end.
At this point I am not even sure if it's to do with phpmyadmin trying to bulk the statements which is why it goes out of sync or what, due to the sheer volume of the statements (there is about 150), as after a certain number of queries phpmyadmin doesn't even detail the single queries that are being run just returns '#1 row affected' or something like that at the end, which is weird because I have multiple joins and others which should be output independently (however im digressing).
I will not paste all of my .sql code in this, I have tested it and know the error is due to the fact that the procedure is being invoked at the end, just not sure how to solve it yet. This is the end of the .sql file:
drop procedure if exists GetStockItems;
delimiter //
create procedure db.GetStockItems(IN param_quantity int)
begin
select * from Stock s where s.quantity <= param_quantity;
end
//
delimiter ;
call db.GetStockItems(55);
drop procedure GetStockItems;
The procedure should return items with less than of a certain quantity from the database, however trying to invoke from the big imported file results in an error. If I call the procedure on the already created database then it works, but that's not the desired flow.
mysql phpmyadmin
edited Dec 28 '18 at 13:59
GMB
4,500519
asked Dec 27 '18 at 15:52
peterxz
102110
I have to create a database, tables and insert data into all of them. At the end, there should be a stored procedure created and invoked. This should all be done in one .sql file that can be imported to any MySQL database. The issue i'm facing is that after creating the procedure and trying to invoke it as well, upon importing i'm welcomed with error :
#2014 - Commands out of sync; you can't run this command now
I have done some research and played around with my code, and reached the conclusion that it all runs smoothly until the very last line, where I am invoking this procedure. If I delete that line and import my .sql file then all is grand - however, it is essential that the procedure is actually invoked at the end.
At this point I am not even sure if it's to do with phpmyadmin trying to bulk the statements which is why it goes out of sync or what, due to the sheer volume of the statements (there is about 150), as after a certain number of queries phpmyadmin doesn't even detail the single queries that are being run just returns '#1 row affected' or something like that at the end, which is weird because I have multiple joins and others which should be output independently (however im digressing).
I will not paste all of my .sql code in this, I have tested it and know the error is due to the fact that the procedure is being invoked at the end, just not sure how to solve it yet. This is the end of the .sql file:
drop procedure if exists GetStockItems;
delimiter //
create procedure db.GetStockItems(IN param_quantity int)
begin
select * from Stock s where s.quantity <= param_quantity;
end
//
delimiter ;
call db.GetStockItems(55);
drop procedure GetStockItems;
The procedure should return items with less than of a certain quantity from the database, however trying to invoke from the big imported file results in an error. If I call the procedure on the already created database then it works, but that's not the desired flow.
mysql phpmyadmin
mysql phpmyadmin
edited Dec 28 '18 at 13:59
GMB
4,500519
asked Dec 27 '18 at 15:52
peterxz
102110
edited Dec 28 '18 at 13:59
GMB
4,500519
asked Dec 27 '18 at 15:52
peterxz
102110
edited Dec 28 '18 at 13:59
GMB
4,500519
edited Dec 28 '18 at 13:59
GMB
4,500519
edited Dec 28 '18 at 13:59
GMB
4,500519
4,500519
asked Dec 27 '18 at 15:52
peterxz
102110
asked Dec 27 '18 at 15:52
peterxz
102110
asked Dec 27 '18 at 15:52
peterxz
102110
102110
Have you tried this? stackoverflow.com/questions/8891179/…
– kmoser
Dec 27 '18 at 17:32
yes and none of the solutions worked from the thread. reading selected columns into variables would work if there was only 1 row in the dataset, but of course it rarely would be that (which then results in another error because it tries reading multiple values into the same variables).
– peterxz
Dec 28 '18 at 14:23
So you're saying you tried this directly from the MySQL command prompt (i.e. not using phpMyAdmin) and you still had the same problem?
– kmoser
Dec 28 '18 at 18:01
correct, using the command prompt and jamming the whole thing in there still resulted in the error.
– peterxz
Dec 29 '18 at 14:48
add a comment |
Have you tried this? stackoverflow.com/questions/8891179/…
– kmoser
Dec 27 '18 at 17:32
yes and none of the solutions worked from the thread. reading selected columns into variables would work if there was only 1 row in the dataset, but of course it rarely would be that (which then results in another error because it tries reading multiple values into the same variables).
– peterxz
Dec 28 '18 at 14:23
So you're saying you tried this directly from the MySQL command prompt (i.e. not using phpMyAdmin) and you still had the same problem?
– kmoser
Dec 28 '18 at 18:01
correct, using the command prompt and jamming the whole thing in there still resulted in the error.
– peterxz
Dec 29 '18 at 14:48
Have you tried this? stackoverflow.com/questions/8891179/…
– kmoser
Dec 27 '18 at 17:32
Have you tried this? stackoverflow.com/questions/8891179/…
– kmoser
Dec 27 '18 at 17:32
yes and none of the solutions worked from the thread. reading selected columns into variables would work if there was only 1 row in the dataset, but of course it rarely would be that (which then results in another error because it tries reading multiple values into the same variables).
– peterxz
Dec 28 '18 at 14:23
yes and none of the solutions worked from the thread. reading selected columns into variables would work if there was only 1 row in the dataset, but of course it rarely would be that (which then results in another error because it tries reading multiple values into the same variables).
– peterxz
Dec 28 '18 at 14:23
So you're saying you tried this directly from the MySQL command prompt (i.e. not using phpMyAdmin) and you still had the same problem?
– kmoser
Dec 28 '18 at 18:01
So you're saying you tried this directly from the MySQL command prompt (i.e. not using phpMyAdmin) and you still had the same problem?
– kmoser
Dec 28 '18 at 18:01
correct, using the command prompt and jamming the whole thing in there still resulted in the error.
– peterxz
Dec 29 '18 at 14:48
correct, using the command prompt and jamming the whole thing in there still resulted in the error.
– peterxz
Dec 29 '18 at 14:48
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53947612%2fhow-to-fix-commands-out-of-sync-error-in-mysql-upon-executing-a-big-sql-file%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53947612%2fhow-to-fix-commands-out-of-sync-error-in-mysql-upon-executing-a-big-sql-file%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
Have you tried this? stackoverflow.com/questions/8891179/…
– kmoser
Dec 27 '18 at 17:32
yes and none of the solutions worked from the thread. reading selected columns into variables would work if there was only 1 row in the dataset, but of course it rarely would be that (which then results in another error because it tries reading multiple values into the same variables).
– peterxz
Dec 28 '18 at 14:23
So you're saying you tried this directly from the MySQL command prompt (i.e. not using phpMyAdmin) and you still had the same problem?
– kmoser
Dec 28 '18 at 18:01
correct, using the command prompt and jamming the whole thing in there still resulted in the error.
– peterxz
Dec 29 '18 at 14:48