Error 50240: Invalid buffer position

e596297

Hi! I'm using vertica-odbc-6.0.1-0.x86_64.linux.tar.gz with php-5.3.26 thru pdo_odbc on Debian GNU/Linux 6.0.6 (squeeze). Connect string is "odbc:odbc:Driver={verticadriver};Server=myserver.somewhere.net;Port=5433;ResultBufferSize=16776960;Database=analytic;". $ cat /etc/odbc.ini [ODBC] UNICODE = UTF-8 [ODBC Data Sources] verticadsn = Vertica ODBC DSN [verticadsn] Description = Vertica ODBC DSN Driver = verticadriver Database = Servername = vertica-storage.lc Port = 5433 Username = Password = [Default] Description = Vertica ODBC DSN Driver = verticadriver Database = Servername = vertica-storage.lc Port = 5433 Username = Password = $ cat /etc/odbcinst.ini [verticadriver] Description = Vertica ODBC driver Driver = /opt/vertica-odbc/lib/libverticaodbc.so Driver64 = /opt/vertica-odbc/lib64/libverticaodbc.so [ODBC] Threading = 1 $ cat vertica.ini [Driver] DriverManagerEncoding=UTF-16 ODBCInstLib=/usr/lib64/libodbcinst.so.1 ErrorMessagesPath=/opt/vertica-odbc/lib64 LogLevel=4 LogPath=/var/log/vertica/client While INSERTing pseudorandom data to vertica get this error (from time to time): [resent] SQLSTATE[HY000]: General error: 50240 [Vertica][Support] (50240) Invalid buffer position. (SQLExecute[50240] at /usr/src/php5/source/php5-5.3.26/ext/pdo_odbc/odbc_stmt.c:254) What does it mean? How can I fix this?

[Deleted User]

Hi, Hm... Do you have an example of a string that causes this? I don't know the cause of this error; someone else who does may step in. But a common issue with pseudorandom data is that the data occasionally includes some metacharacter or other symbol with special meaning, and it uses that symbol incorrectly somehow. Adam

e596297

In 'pseudorandom' term I mean data meet the constraints of vertica table. Table DDL is:

  create table search (    id char(32) NOT NULL,    ts timestamp NOT NULL,    aid integer NOT NULL,    said varchar(50) NOT NULL,    v_ip integer NOT NULL,    v_ua varchar(255) NOT NULL,    v_w varchar(255),    v_ref varchar(255),    a_ip integer,    status varchar(50) NOT NULL,    s_type varchar(50) NOT NULL,    srv_id varchar(50),    finish integer NOT NULL,    primary key (id)  );  

Data is:

  select * from search;                 id                |      ts      | aid  | said | v_ip | v_ua | v_w  | v_ref | a_ip | status | s_type | srv_id | finish   ----------------------------------+---------------------+------+------+------------+------------+-------------+-------------+--------+--------+-------------+-----------+---------------   033ecd303d35100a12b18b8d312f264b | 2013-06-05 14:38:11 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        232867   3ed920c1114e2f5251e9e1957b0aeaa6 | 2013-06-11 13:28:29 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        216291   8930a08087bee50247beb2f2bb5d675e | 2013-06-11 13:27:06 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        199695   12345                            | 2013-06-05 18:14:17 |  111 | 222  |        333 | visitor_ua | visitor_kw  | visitor_ref |    444 | status | simple      | 555       |           666   210eb8ed66fcdbd099e7aca98be709c7 | 2013-06-05 14:39:52 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        223430   62a65e571e60097b8f5265623b21fe96 | 2013-06-11 13:25:10 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        215747   f3dc87df26ee072e343136cf4b92d9be | 2013-06-11 13:27:17 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        240814   8545cb4804c8ae47e2a08ef1614ea7b4 | 2013-06-11 13:27:59 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        195070   1234                             | 2013-06-05 18:14:17 |  111 | 222  |        333 | v_ua | v_w  | v_ref |    444 | status | simple      | 555       |           666   1234                             | 2013-06-05 18:14:17 |  111 | 222  |        333 | v_ua | v_w  | v_ref |    444 | status | simple      | 555       |           666   39f34a0f4f54190542ceee033a452c2c | 2013-06-05 14:41:46 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        221858   7d59ef182b7cad929d1a410bb2e16708 | 2013-06-05 14:42:13 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        304684   2567d1f4d280f8804afbfa77d0f4eb5f | 2013-06-05 15:04:31 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        536431   6030c8ffa37ed12888ccc2ca6dc80916 | 2013-06-05 14:47:34 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        210661   655dbf92582aba7fb8d25b5ba1520ebe | 2013-06-05 15:06:37 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        194939   b6bdcacbe36b477a37591d62301e601e | 2013-06-05 14:44:54 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        245774   e75b102fc6ce1d64dc5da30abbd9433b | 2013-06-05 14:45:11 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        211220   39f34a0f4f54190542ceee033a452c2c | 2013-06-05 14:41:46 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        221858   39f34a0f4f54190542ceee033a452c2c | 2013-06-05 14:41:46 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        221858   39f34a0f4f54190542ceee033a452c2c | 2013-06-05 14:41:46 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        221858   4b84b8fd7197f11e68305c983f4c0f6c | 2013-06-11 13:15:45 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        196624   e9b3a2692ad8b01c3da3d38cfc148ce7 | 2013-06-11 13:16:14 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        218964   f9fdd0f8ad7acea2c08f3a848e3cc585 | 2013-06-11 13:15:28 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        555611   7721def6bf3548442f4eafa9e69ba329 | 2013-06-11 13:21:10 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        270971   07c3cfea52d4cee41dadd9ac31b7dbea | 2013-06-11 13:26:44 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        207747   1cb2c6ded94e7803a468dd6e4479c17d | 2013-06-11 13:26:56 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        498116   0cf64d1d33b05fb04d5a4394836141b4 | 2013-06-12 12:25:59 | 1696 | 123  |  134744072 | PHPUnit    | interesting | localhost   |      0 | 200    | simple      | 0         |        270683  

Good: most of this data were inserted with php. Bad: much more (than data lines were inserted) error messages were earned due insert. Strange: vsql client insert everything without errors. Here is an example:

  analytic=> INSERT INTO search (id, ts, aid, said, v_ip, v_ua, v_w, v_ref, a_ip, status, s_type, srv_id, finish) VALUES ('12345', '2013-06-12 05:14:17', '111', '222', '333', 'v_ua', 'v_w', 'v_ref', '444', 'status', 'simple', '555', '666' );   OUTPUT   --------        1  (1 row)  

e596297

Maybe I don't understand you... Did you need data or code snippet?

[Deleted User]

Looking at your next comment, I think my idea is not relevant to what you're seeing. (I'm thinking of things like invalid backslash-escape sequences, invalid Unicode multi-byte sequences, etc. Looks like you're just using basic A-Z characters.) I'm not sure what's causing the errors that you're seeing. Hopefully someone else will have some idea...

[Deleted User]

Hm, I don't have an immediate answer... One point, though -- INSERT from ODBC actually uses a totally different mechanism than from vsql; ODBC INSERTs are often part of a batch, so it uses a process that's heavily tuned for multiple consecutive INSERTs. The closest simple analog from vsql would be "COPY search FROM STDIN;". If you use COPY, be sure to check the COPY exceptions and rejections files, as these indicate things that would show up as errors in ODBC.

e596297

Any other suggestions?

e596297

Is there any way to use COPY syntax in php-odbc?

[Deleted User]

I believe that COPY LOCAL works fine with ODBC. I don't know offhand if there is a way to use COPY FROM LOCAL STDIN; there may be. But COPY FROM LOCAL '[some local file]' should cause the driver to open and load that local file.

[Deleted User]

1) check your unixodbc installation. Vertica needs version 2.2.14 (with some restrictions) and above. 2) use isql (a unixodbc utility much like the vsql client) to run the same insert command and check if you get the same error. This will bypass pdo_odbc and test the driver directly.

e596297

1. my unixodbc version is 2.2.14. 2. isql makes insert of same data without any errors.

[Deleted User]

Good, that means the queries themselves ran fine via the driver. Is there any difference between the queries you issued in isql and the queries executed in your php script? For one thing, I noticed that you're using strings for integer valued columns in your query, which seems odd. Would it be possible for you to post your php script here?

Wils_Gomes

I am having the same issue, any update about this issue?

[Deleted User]

Have you tried "copy ... from local '[your data filename]'"?
If vsql/isql works fine, it may have something to do with the way pdo_odbc processing the ODBC calls. Can you show an example case that causes this?

Wils_Gomes

Hi Xuan,

In my case the problem is when performing a simple SELECT using the named parameter as you can see the complete description about the issue in this thread:

https://community.vertica.com/vertica/topics/sqlstate_hy000_general_error_50240_vertica_support_50240_invalid_buffer_position

This does not happen if I use the odbc_functions instead of PDO.

tkx
Wils

[Deleted User]

This is a pdo_odbc limitation/bug. By looking at the ODBC trace files, I see that when pdo_odbc binds parameter, it always sets the BufferLength argument of the SQLBindParameter() function to 0. This is wrong when the input is a character type, and as a result there is a buffer overrun.