Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

empty table after shutdown one of cluster nodes

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?

Tagged:

Answers

  • 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');

  • 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)

  • 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 ...

  • 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)
    ```

  • @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..

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.