Occassional Error during COPY statement - using Python/Pyodbc

Sam_2

Hi - I get an error every once in a while during a copy statement : [HY000] server sent data ("D" message) without prior row description ("T" message)\n (0) (SQLExecDirectW). what is exactly going on here? I'm using python/pyodbc to feed info to vertica. On most occassion the feed does work. It's once in a blue moon I get this...but this translates to once every two -three days, during a course of possibly 1000 loads. From what I'm finding on google - i get many hits for the client side running out of memory for Postgress Copy. Is this same for Vertica? What can be possible solutions besides allocating more memory to my process, Thanks

[Deleted User]

Hi Sam, thanks for reporting your problem! We're glad to hear it's occasional but we'll definitely look into what could be causing this.

Sam_2

Thanks Danielle. --- the other item could be some sort of packet loss when I look at the error message carefully. Simple copy stmt if you need it which is executing when i get the error mesg: COPY equity_price_list (ticker,price,currency as 'USD',update_time as '2013-04-17 10:28:47', source as '/data/reports/POS2\20130416_rms_reference_prices.csv') FROM LOCAL '/data/reports/POS2\20130416_rms_reference_prices.csv' DELIMITER ',' DIRECT thanks

[Deleted User]

Hi Sam, thanks for the details. I wouldn't expect ordinary packet loss to cause this particular problem because we use TCP for our client/server protocol; TCP will detect and re-send ordinary dropped packets transparently to the client application. (Dropped packets would certainly affect performance, though; and could result in a disconnect if enough packets are dropped.) If you have some sort of fancy firewall or load balancer, though, I could see that possibly causing this sort of issue if the device is buggy? On a separate note, are you running the latest version of the client ODBC drivers? We have had (and have fixed) bugs in the past that would intermittently cause this error when loading data.

Sam_2

Hi, I'm using "Vertica 6.0 ODBC 3.5_64.DLL" on windows...I'll check if there's a newer one and install that. good point on the TCP. No - we have no firewall or load balancer in terms of the client talking to the Vertica DB server. Here's our Vertica version btw: dbadmin=> select version(); version ------------------------------------ Vertica Analytic Database v6.0.1-0 (1 row)

Josip

any update on this?
I am experiencing same problems with Vertica Analytic Database v6.1.3-0
Do I lose data when this happens ?

Thanks,
Josip

Sam_2

Josip - from my experience - yes, the load should simply fail. I simply catch the Exception and retry.

Josip

Thank Sam for INSTANT answer
Have You tried Vertica 7?

Sam_2

No. When we moved to vertica 6.0, we had frequent "oddities" on using vertica. Things stabilized on 6.0.1. We'll wait for a bit before exploring vertica 7

Josip

Hi Sam,
How did You  cought that Error?
Is there special type or I can do it by  comparing error messages.
Could You just put your code snippet?

Thanks,
Josip

------------------------------------------------------>
This is my idea:
------------------------------------------------------>

COPY_ERROR =  '[HY000] server sent data ("D" message) without prior row    description ("T" message)\n (0) (SQLExecDirectW)'


try:
   cursor().execute(copy_query)
except Exception, e:
   error_msg = str(e)
   if(error_msg == COPY_ERROR):
      retry_copy()

Sam_2

Seems about right. I basically have a more generic logic to retry the file 3 times (sleep for couple of secs in between) regardless of the actual message and then move on. I don't think I've ever hit 3 chokes...so I've never had to investigate a bad file or anything.

Yeah...caught it the old fashion way just like you're doing:

 except Exception, e:
                log.error("Error processing file %s. Exception %s",price_file, e)

Tomasz_Rakowski

We have experienced the same error. We're executing a "copy" query which results in "server send data ("D" message) without prior row description ("T" message)" exception. The bad part is that the data is actually inserted, at least partially, while we assumed it is not. Our script entered an infinite loop trying to insert the same file due to this error and duplicated data for that specific statement. 

Sam_2

I'm kind of surprised you get a partial insert - that is a bit weird. What version of Vertica you using?