Thrift connection error

Hi,

I’m trying to understand what might have caused these errors inside my docker. I restarted my docker several times and even created a new one, but I’m still getting the same error messages.

docker exec -it my_docker /omnisci/bin/omnisql -p HyperInteractive

bad lexical cast: source type value could not be interpreted as target

Not connected to any database. See \h for help.
omnisql> \t

Thrift error: No more data to read.

Thrift connection error: No more data to read.

Retrying connection

Thrift error: No more data to read.

Thrift connection error: No more data to read.

Retrying connection

The last thing I worked on was loading this table to the docker and performed queries (and it’s been working fine all time before that!). So I’m also wondering if this table, or loading a large-ish table had caused the crashes above.

CREATE TABLE IF NOT EXISTS yellowtaxiJan13 (
c1 SMALLINT,
vendor_id TEXT ENCODING DICT,
pickup_datetime TIMESTAMP(0),
dropoff_datetime TIMESTAMP(0),
passenger_count SMALLINT,
trip_distance FLOAT,
pickup_longitude FLOAT,
pickup_latitude FLOAT,
rate_code SMALLINT,
store_and_fwd_flag TEXT ENCODING DICT,
dropoff_longitude FLOAT,
dropoff_latitude FLOAT,
payment_type TEXT ENCODING DICT,
fare_amount FLOAT,
surcharge FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
total_amount FLOAT,
trip_id INT)
WITH (FRAGMENT_SIZE=75000000, max_chunk_size = 3221225472);

COPY yellowtaxiJan13 FROM '/omnisci-storage/yellowtaxi_jan2013.csv' WITH (header='true');

Hi @bycxgto,

the table you created and the 14776617 records (so we are talking about a small number of records) you loaded into that table

created a similar table and loaded the datafile of NYC

omnisql> CREATE TABLE IF NOT EXISTS yellowtaxiJan13 (
..> vendor_id TEXT ENCODING DICT,
..> pickup_datetime TIMESTAMP(0),
..> dropoff_datetime TIMESTAMP(0),
..> passenger_count SMALLINT,
..> trip_distance FLOAT,
..> pickup_longitude FLOAT,
..> pickup_latitude FLOAT,
..> rate_code SMALLINT,
..> store_and_fwd_flag TEXT ENCODING DICT,
..> dropoff_longitude FLOAT,
..> dropoff_latitude FLOAT,
..> payment_type TEXT ENCODING DICT,
..> fare_amount FLOAT,
..> dk float,
..> mta_tax FLOAT,
..> tip_amount FLOAT,
..> tolls_amount FLOAT,
..> surcharge FLOAT,
..> total_amount FLOAT)
..> WITH (FRAGMENT_SIZE=75000000, max_chunk_size = 3221225472);
omnisql> copy yellowtaxiJan13 from '/omnisci-storage/yellow_tripdata_2013-01.csv.gz' with (header='true');
Result
Loaded: 14776616 recs, Rejected: 1 recs in 35.563000 secs
omnisql> SELECT vendor_id, count(*) FROM yellowtaxiJan13 GROUP BY vendor_id;
vendor_id|EXPR$1
CMT|7450899
VTS|7325716
NULL|1
3 rows returned.
Execution time: 24 ms, Total time: 25 ms
omnisql> SELECT
..>   passenger_count,
..>   avg(total_amount)
..> FROM
..>   yellowtaxiJan13
..> GROUP BY
..>   passenger_count;
passenger_count|EXPR$1
0|33.69415650884789
1|13.84340060893641
2|14.47719056930937
3|13.96324844975188
4|14.07942752818585
5|14.03647910991885
6|13.92471244034411
9|4
208|3.700000047683716
255|9.5
NULL|NULL
11 rows returned.
Execution time: 289 ms, Total time: 290 ms
omnisql> SELECT passenger_count, avg(total_amount) FROM yellowtaxiJan13 GROUP BY passenger_count;
passenger_count|EXPR$1
0|33.69415650884789
1|13.84339602515389
2|14.47719056930937
3|13.96324761291078
4|14.07942663848081
5|14.03647802296942
6|13.92471244034411
9|4
208|3.700000047683716
255|9.5
NULL|NULL
11 rows returned.
Execution time: 25 ms, Total time: 26 ms

and so on…

restarted the docker, and gotthe same error :thinking:

it’s obiviously a bug related to the max chunk size you specified

to recover the database you have to follow those steps

  • Install sqlite3 package

  • Stop your docker

  • go to /var/lib/omnisci/data/mapd_catalog directory

  • run this command: sudo sqlite3 omnisci and set thce hunk max size for the table to the default value

      SQLite version 3.11.0 2016-02-15 17:29:24
      Enter ".help" for usage hints.
      sqlite> select tableid,max_chunk_size  from mapd_tables where name='yellowtaxiJan13';
      4|3221225472
      sqlite> update mapd_tables set max_chunk_size=1073741824 where tableid=4;
      sqlite> .quit
    
  • restart your docker

now the database would be accessible.

10x for idscovering this bug; our engineers will dig into it :wink:

Best regards.

If you don’t mind to lose the database and all your data you can delete the contents of the directory /var/lib/omnisci/data/ , but I think we all lover data and we don’t like to loose it, even the one we are using for testing purpose.

just a question… have you choose the 3GB value or the system assigned automatically to the table?

Hi @candido.dessanti,

Thanks for looking this up.

I got an error Loader truncated due to reject count when I left all WITH settings as default. Then I updated my schema to include the extra columns, set the max chunk size to 3GB and able to load the table afterwards (the rest is history…). I wouldn’t think this is a large table at all. On hindsight I probably didn’t have to change the chunk size at all but glad my little experiment had made some discovery :smile:

I ran your sqlite command but I couldn’t update the table. Perhaps the crash had changed the behaviours in database
sqlite> update mapd_tables set max_chunk_size=1073741824 where tableid=4;
Error: attempt to write a readonly database

drwxr--r--   2 root atr   61 Nov 19 13:53 mapd_catalogs
drwxr-sr-x 112 root atr 4096 Nov 19 22:17 mapd_data
drwxr-sr-x   2 root atr 8192 Nov 20 16:12 mapd_log
-rw-r--r--   1 root atr    2 Nov 20 16:11 omnisci_server_pid.lck
-rw-r--r-- 1 root atr 114688 Nov 19 13:53 omnisci
-rw-r--r-- 1 root atr 53248 Nov 12 12:29 omnisci_system_catalog

I created a new docker and mapped it to a new /mnt/folder. Looks like it solves the issues and been working fine. I wonder if there is a way where I can execute scripts e.g. create tables and run queries without using the interactive mode?

Hi @bycxgto,

Thanks for the feedback about the reason you changed the max chunk size; It’s unlikely that changing the that parameter helped loading the table.

To be able to update the catalog you need the right OS privileges, so you just need to issue the sqlite3 command sudoing the command

sudo sqlite3 omnisci

If you want to run sql command (dml or ddl) you can run omnisql in several ways

cat script.sql | omnisql -p YOUR_DATABASE_PASSWORD DATABASE_NAME 

omnisql -p YOUR_DATABASE_PASSWORD DATABASE_NAME <script.sql

echo "select * from Whatever limit 10" | omnisql -p YOUR_DATABASE_PASSWORD DATABASE_NAME 

The omniswl will execute the command and will exit giving you back the control of the shell you are on

Anyway, our backend engineers identified the problem, so it will be corrected in the forthcoming releases.

Thanks again for reporting on this bug

1 Like

got it, yes I ran it without sudo as I didn’t have the permissions, and as you’d expect it didn’t work.

How should I run these commands for docker, like create tables or queries for databases hosted inside a container? I tried the below but wasn’t able to create the tables

echo -e "CREATE TABLE IF NOT EXISTS tweets (tweet_id BIGINT NOT NULL, tweet_time TIMESTAMP NOT NULL ENCODING FIXED(32),lat FLOAT, lon FLOAT, sender_id BIGINT NOT NULL, sender_name TEXT NOT NULL ENCODING DICT, location TEXT ENCODING  DICT, source TEXT ENCODING DICT, reply_to_user_id BIGINT, reply_to_tweet_id BIGINT, lang TEXT ENCODING  DICT, followers INT, followees INT, tweet_count INT, join_time TIMESTAMP ENCODING  FIXED(32), tweet_text TEXT, state TEXT ENCODING  DICT, county TEXT ENCODING DICT, place_name TEXT, state_abbr TEXT ENCODING DICT, county_state TEXT ENCODING DICT, origin TEXT ENCODING DICT, phone_numbers bigint);" | docker exec mydocker /omnisci/bin/omnisql -p HyperInteractive omnisci
User admin connected to database omnisci
User admin disconnected from database omnisci


docker exec -it mydocker /bin/bash
echo "CREATE TABLE IF NOT EXISTS tweets (tweet_id BIGINT NOT NULL, tweet_time TIMESTAMP NOT NULL ENCODING FIXED(32));"
CREATE TABLE IF NOT EXISTS tweets (tweet_id BIGINT NOT NULL, tweet_time TIMESTAMP NOT NULL ENCODING FIXED(32));

Thanks!

Hi @bycxgto,

to create a table in a not-interactive way inside a docker you can to run the command using an addional bash (or another shell).

docker exec -it omnisciserver bash -c 'echo "create table test_docker (id integer not null);" | /omnisci/bin/omnisql -p HyperInteractive'
User admin connected to database omnisci
User admin disconnected from database omnisci
mapd@zion16:~$ docker exec -it omnisciserver bash -c 'echo "show create table test_docker;" | /omnisci/bin/omnisql -p HyperInteractive'
User admin connected to database omnisci
Result
CREATE TABLE test_docker (
  id INTEGER NOT NULL);
User admin disconnected from database omnisci
mapd@zion16:~$

Does it helps?

Regards