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.

Solution 1

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))

Solution 2

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

Solution 3

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

Solution 4

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.

View more solutions

Comments

[user3858193]

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;

[Benjamin Crouzier]

YES. You can also execute a ABORT; or ROLLBACK command. But autocommit is so usefull.