11

I can't execute MySQL statement when using delimiter (default ';'). I mean, when I run query like:

select * from mdw.dim_date dd limit 10;
select * from mdw.dim_order do limit 5;

I've got such error:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from mdw.dim_order do limit 5' at line 2

I don't want to execute this per Alt+X (I am using Dbeaver) as I want to put query into pentaho. I need to execute around 50 deletes, so I don't want to divide it on 50 SQL scripts.

I am using MySQL (version: 5.6.36-82.0).

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
user14261502
  • 121
  • 1
  • 1
  • 5
  • Most database APIs only allow you to execute one query at a time. – Barmar Sep 11 '20 at 16:01
  • So not clear what you are actually asking here. You say you want to delete soemthing but you show us a SELECT that fails? – RiggsFolly Sep 11 '20 at 16:04
  • Hi Riggs. Selects are just for example what kind of error is this. I have around 50 delete statements like this: DELETE from mpanel.alias where alias_created < (SELECT CAST(DATE_SUB(NOW(),INTERVAL 2 YEAR) AS DATE)); with different databases, tables and columns, but error is still the same. – user14261502 Sep 11 '20 at 16:18

3 Answers3

47

You need to click on "Execute Script" option on DBeaver

enter image description here

If you click the play button, It will show you that error.

Omar Alvarado
  • 1,304
  • 2
  • 12
  • 16
  • Thanks for reply, but the point is that there is no such option in Pentaho (as I mentioned in post - I need to execute it in Pentaho on "Execute SQL script" where I get same error). – user14261502 Sep 11 '20 at 16:13
  • I think it should be something similar, look at the documentation of pentaho, it requires precisely that lines must be separated by semicolon., maybe is just to choose the right option, or maybe you have a selected option like "Execute as a single statement" https://help.pentaho.com/Documentation/8.2/Products/Data_Integration/Transformation_Step_Reference/Execute_SQL_Script – Omar Alvarado Sep 11 '20 at 16:19
  • No, any option is selected (but I tried everything - just to be sure). My query is around 50 deletes, all of them finished with semicolon. – user14261502 Sep 11 '20 at 16:27
  • Not sure, but maybe is possible to putthe instruction "DELIMITER ; " at the beginning of the script to force it, Just trying to give some ideas. – Omar Alvarado Sep 11 '20 at 16:30
  • Yeah, tried this as well. I also tried with different character ('$' and '$$') just to make sure, but didn't help. Anyway thanks for your time. – user14261502 Sep 11 '20 at 16:33
28

If you is using Dbeaver you can select you script and press ALT+X

wrong and correct way below

enter image description here

Consule
  • 1,059
  • 12
  • 12
0

there must be no spaces before delimiter keyword (dbeaver bug I think)

Onoffrei Constantin
  • 9
  • 2