empty table after shutdown one of cluster nodes

aleksss55

Hello. I have a cluster from 3 nodes. There is a table mytable. There are some records in the table. This records are displayed correctly with select * from mytable;
I shutdown one of cluster nodes and table mytable becomes empty. Why is that?

Nimmi_gupta

Is this k-safe cluster?
Run the below query to check segment_expression
select * from projections where projection_name ilike '%mytable%';
Can you also export the table definition
select export_objects('', 'mytable');

aleksss55

Yes, I have k-safe cluster.
Below is the output of the command select * from projections where projection_name ilike '%my%';

test=> select * from projections where projection_name ilike '%my%';
-[ RECORD 1 ]----------------+-------------------------------------------------------------------------------------
projection_schema_id         | 45035996273704980
projection_schema            | public
projection_id                | 45035996273840608
projection_name              | my_b1
projection_basename          | my
owner_id                     | 45035996273704962
owner_name                   | mydba
anchor_table_id              | 45035996273840594
anchor_table_name            | my
node_id                      | 0
node_name                    |
is_prejoin                   | f
created_epoch                | 17
create_type                  | DELAYED CREATION
verified_fault_tolerance     | 1
is_up_to_date                | t
has_statistics               | f
is_segmented                 | t
segment_expression           | hash(my.id, my.first_name)
segment_range                | implicit range: v_test_node0008[33.3%] v_test_node0009[33.3%] v_test_node0007[33.3%]
is_super_projection          | t
is_key_constraint_projection | f
has_expressions              | f
is_aggregate_projection      | f
aggregate_type               |
is_shared                    | f
-[ RECORD 2 ]----------------+-------------------------------------------------------------------------------------
projection_schema_id         | 45035996273704980
projection_schema            | public
projection_id                | 45035996273840596
projection_name              | my_b0
projection_basename          | my
owner_id                     | 45035996273704962
owner_name                   | mydba
anchor_table_id              | 45035996273840594
anchor_table_name            | my
node_id                      | 0
node_name                    |
is_prejoin                   | f
created_epoch                | 17
create_type                  | DELAYED CREATION
verified_fault_tolerance     | 1
is_up_to_date                | t
has_statistics               | f
is_segmented                 | t
segment_expression           | hash(my.id, my.first_name)
segment_range                | implicit range: v_test_node0007[33.3%] v_test_node0008[33.3%] v_test_node0009[33.3%]
is_super_projection          | t
is_key_constraint_projection | f
has_expressions              | f
is_aggregate_projection      | f
aggregate_type               |
is_shared                    | f

But when I run select * from my;
I get empty table
test=> select * from my;
(No rows)

marcothesane

When your query, initially, showed 'some' rows:
Were you logged in with VSQL, and had you inserted the rows in the same transaction?
If you insert a bunch of rows into a table using VSQL, and you don't commit before logging off, these rows will disappear.
If you commit, however, the new rows will immediately become visible to everyone - and I can-t imagine a situation where they would go away just because of a database restart ...

marcothesane

Let me demo what I explain above:

I start VSQL, create a table, then I insert 3 rows. A SELECT * FROM that table will return 3 rows.
Then, I log off without committing.
When I log in again , and again SELECT * FROM that table, the table is empty.
```SQL
$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

sbx=> create table ins (id int, name varchar(32));
CREATE TABLE
sbx=> insert into ins select 1,'arthur' union all select 2,'ford' union all select 3,'trillian';

OUTPUT

  3

(1 row)

sbx=> select * from ins;
id | name
----+----------
1 | arthur
2 | ford
3 | trillian
(3 rows)

sbx=> \q
[email protected]:~/1/Vertica/supp$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

sbx=> select * from ins;
id | name
----+------
(0 rows)
```

Nimmi_gupta

@aleksss55, follow the suggestion provided by marcothesane. If it's still have a problem can you log a case with support. In the case provide detail information with example like how you inserting the data to the table? Are you using copy, insert commit etc..