Error 50240: Invalid buffer position
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?
+2Hi, 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
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 | 270683Good: 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) Maybe I don't understand you... Did you need data or code snippet?
+2Looking 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...
+2Hm, 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.
Any other suggestions?
Is there any way to use COPY syntax in php-odbc?
+2I 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.
+21) 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.
1. my unixodbc version is 2.2.14. 2. isql makes insert of same data without any errors.
+2Good, 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?
+1I am having the same issue, any update about this issue?
+2Have 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?
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?
+1Hi 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
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
+2This 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.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
