Strange error during insert

[rasmus]

Hi!

I insert some data through the jdbc driver and getting unknown exception(ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: localhost, port: 8123;) 

I understand that it can be error in jdbc-driver, but when I try to do this insert through command line I also get the following error:

addr is not a string at /usr/share/perl/5.20/IO/Socket/IP.pm line 453, <STDIN> line 1.

I do insert like this:

cat data.csv | POST 'http://localhost:8123/?database=test&query=INSERT INTO test(column1, column2,....) FORMAT TabSeparated

The most strange behavior here is that when there are a lot columns into statement then I get error, but when I don't enumerate columns(i.e. do 

 cat data.csv | POST 'http://localhost:8123/?database=test&query=INSERT INTO test FORMAT TabSeparated

) then all is OK.

Maybe there is limit for the query length or something like this(because it seems that when we have not so many columns then all is fine)?

Thanks!

[Oleg A]

Can you provide sample 'create table' and data for reproducing? 

понедельник, 20 февраля 2017 г., 17:00:27 UTC+3 пользователь rasmus написал:

[rasmus]

Sample table with 2500 columns(in create_table.sql). Data with all empty strings in data.csv. And two versions of post with columns and without it. 

In this sample in version with columns data does not inserted and gives error.

But in version without columns all is ok.

понедельник, 20 февраля 2017 г., 20:37:06 UTC+2 пользователь Oleg A написал:

[Oleg A]

Your table created in 'default' database, but you trying to insert into 'test' database.

try create table  with database name:

CREATE TABLE test.test(...

вторник, 21 февраля 2017 г., 0:40:07 UTC+3 пользователь rasmus написал:

[rasmus]

No, I have created table in test database. 

Before create table I use test database.

I provide for you example of sample creation, inserting and etc. 

So maybe somewhere I have missed alias or something else, but this is only example for you and it provide this error. I have tested it.

среда, 22 февраля 2017 г., 18:01:56 UTC+2 пользователь Oleg A написал:

[Alex Zatelepin]

The source of the problem is that the JDBC driver sends the beginning of the INSERT statement in the query string and the URL becomes too big when you include all column names. HTTP server used by ClickHouse limits URL length to 16 Kb and if it exceeds this length, returns HTTP status code 400 which is interpreted as error by the JDBC driver. I think the error when using POST command line tool is of similar origin, just on the sender side.

This problem can be overcome by sending the beginning of the statement along with the data in the POST body, not in the query string. You just need to add a line break after FORMAT TabSeparated and before the data. That would of course require reworking the driver.

Also note a slight mistake in your example - types should not be included in the INSERT statement, only column names (column0, not column0 String).