SQL workbench Error while connecting to Redshift

Question

I am trying to create a table in aws redshift using sqlwork bench. I am able to connect redshift but while executing below script, I am getting connection aborted error. Please let me know if any one has any solution.

CREATE  TABLE actual_report (
  type1 varchar(40),
  Jj_calendar_Order_Month varchar(40),
  Jj_Calendar_Order_Year varchar(40),
  Product_major_Code_description varchar(40),
  Product_minor_Code varchar(40),
  Product_part_number varchar(40),
  Product_Minor_Description varchar(40),
  Order_Quantity decimal(20),
  Order_Item_Unit_Price decimal(10,2),
  country varchar(40))

An error occurred when executing the SQL command: CREATE TABLE actual_report ( type1 varchar(40), Jj_calendar_Order_Month varchar(40), Jj_Calendar_Order_Year varchar(40), Product_major_Code_descripti...

Amazon Invalid operation: current transaction is aborted, commands ignored until end of transaction block; Execution time: 0.22s 1 statement failed.

Adding a semicolon after the statement *might* help. – wildplasser Mar 24 '15 at 00:30 — wildplasser, Mar 24 '15 at 00:30

keypoint · Answer 1 · 2015-07-01T00:04:36.787

52

I'm using SQL Workbench, and by default autocommit is off. I run the SQL command below to turn on autocommit, otherwise the transaction will not be committed to database. Just for your reference.

SET autocommit ON

edited Jul 01 '15 at 00:04

answered Jun 30 '15 at 23:17

keypoint

2,268
4
31
59

4

YES. You can also execute a `ABORT;` or `ROLLBACK` command. But autocommit is so usefull. – Benjamin Crouzier Jul 30 '15 at 15:16

score 30 · Answer 2 · answered Nov 21 '17 at 21:22

30

Set Autocommit to TRUE in SQL Workbench connection window. See attached image:

answered Nov 21 '17 at 21:22

Anuj Sethi

643
1
9
13

score 10 · Answer 3 · answered Jun 04 '15 at 02:07

Unless you start one explicitly, every Redshift query you make is wrapped in a transaction: http://docs.aws.amazon.com/redshift/latest/dg/r_BEGIN.html

To fix this issue when it arises again, enter ROLLBACK; on a line by itself and run it. This should bring your connection back to working order.

score 0 · Accepted Answer · edited Apr 08 '19 at 23:44

0

I got it. Below script worked out for me. I just removed (_).

CREATE  TABLE actual_report (
type1 varchar(40),
JjcalendarOrderMonth varchar(40),
JjCalendarOrderYear varchar(40),
ProductmajorCodedescription varchar(40),
ProductminorCode varchar(40),
Productpartnumber varchar(40),
ProductMinorDescription varchar(40),
OrderQuantity decimal(20),
OrderItemUnitPrice decimal(10,2),
country varchar(40))

edited Apr 08 '19 at 23:44

prayagupa

30,204
14
155
192

answered Mar 23 '15 at 23:50

user3858193

1,320
5
18
50

But I would like to know why Below error comes. Amazon](500310) Invalid operation: current transaction is aborted, commands ignored until end of transaction block; – user3858193 Mar 23 '15 at 23:58

SQL workbench Error while connecting to Redshift

4 Answers4