Occassional Error during COPY statement - using Python/Pyodbc

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.

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

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.

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)

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

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

Thank Sam for INSTANT answer
Have You tried Vertica 7?

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

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

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)

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.

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