Unable to backup table.

[PunshiB]

Unable to backup table.

[AlexAkulov]

Hello!
Please, write output of these commands:
select create_table_query,partition_key,primary_key from system.tables where name='tmp_employee'
select * from system.parts where table='tmp_employee' limit 100

[PunshiB]

Executing select statement together was throwing exception so I fired individually..

1. select create_table_query from system.tables where name='tmp_employee';

┌─create_table_query──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.tmp_employee ( name String,  age UInt32,  joining_date Date,  designation String) ENGINE = MergeTree() PARTITION BY toYYYYMM(joining_date) ORDER BY name SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

2. select partition_key from system.tables where name='tmp_employee';
3. select primary_key from system.tables where name='tmp_employee';

Code: 47. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Unknown identifier: partition_key.
Code: 47. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Unknown identifier: primary_key.

4. select * from system.parts where table='tmp_employee' limit 100

┌─partition─┬─name─────────┬─active─┬─marks─┬─marks_size─┬─rows─┬─bytes─┬───modification_time─┬─────────remove_time─┬─refcount─┬───min_date─┬───max_date─┬─min_block_number─┬─max_block_number─┬─level─┬─primary_key_bytes_in_memory─┬─primary_key_bytes_in_memory_allocated─┬─database─┬─table────────┬─engine────┬─path─────────────────────────────────────────────────────────┐
│ 199401    │ 199401_3_3_0 │      1 │     1 │         64 │    1 │   716 │ 2019-05-01 14:28:13 │ 0000-00-00 00:00:00 │        1 │ 1994-01-01 │ 1994-01-01 │                3 │                3 │     0 │                          14 │                                  4128 │ default  │ tmp_employee │ MergeTree │ /motadata/clickhouse/data/default/tmp_employee/199401_3_3_0/ │
│ 201006    │ 201006_1_1_0 │      1 │     1 │         64 │    2 │   730 │ 2019-05-01 14:28:13 │ 0000-00-00 00:00:00 │        1 │ 2010-06-01 │ 2010-06-10 │                1 │                1 │     0 │                          15 │                                  4128 │ default  │ tmp_employee │ MergeTree │ /motadata/clickhouse/data/default/tmp_employee/201006_1_1_0/ │
│ 201306    │ 201306_2_2_0 │      1 │     1 │         64 │    1 │   711 │ 2019-05-01 14:28:13 │ 0000-00-00 00:00:00 │        1 │ 2013-06-01 │ 2013-06-01 │                2 │                2 │     0 │                          14 │                                  4128 │ default  │ tmp_employee │ MergeTree │ /motadata/clickhouse/data/default/tmp_employee/201306_2_2_0/ │
└───────────┴──────────────┴────────┴───────┴────────────┴──────┴───────┴─────────────────────┴─────────────────────┴──────────┴────────────┴────────────┴──────────────────┴──────────────────┴───────┴─────────────────────────────┴───────────────────────────────────────┴──────────┴──────────────┴───────────┴──────────────────────────────────────────────────────────────┘

3 rows in set. Elapsed: 0.023 sec. 

I figured out the problem there isn't partition_id column in system.parts table but I don't know why , what could be the alternative of partition_id column ?

[AlexAkulov]

What version of ClickHouse do you use?

[PunshiB]

Here is every detail-

Details-
Ubuntu 16.04 LTS
Clickhouse version- 1.1.54370
Clickhouse-backup (latest 26Apr2019)

[AlexAkulov]

This is very old version of ClickHouse. I have found out that clickhouse-backup works correctly since ClickHouse version 19.1.5 or above.
Can do you upgrade your clickhouse?

[PunshiB]

Yes the version is quite old, for some reasons I cannot upgrade the clickhouse atleast for my current project, I was thinking of replacing partition_id with something else ,I am actually analysing columns for that do you have any suggestions which could be a possible replacement for partition_id?

[PunshiB]

I did some modification in sql queries replaced partition_id with partition and accordingly modified ALTER queries but now I am stuck with some other error, is it ok to continue on this or so should I close this thread and start new one?

The error -

2019/05/02 20:46:00 Copy metadata
2019/05/02 20:46:00 can't backup metadata with stat /punshi/clickhouse/metadata: no such file or directory

I should say I have modified the config and I am trying to backup data of remote server. The above address does exist on remote machine for which I am taking backup but not on mine from where I am running the utility.
Thanks

[AlexAkulov]

The error on copy metadata is a bug and it was fixed in v0.3.1
Thank you for report!

[AlexAkulov]

It's possible to use partition instead of partiotion_id for old versions of ClickHouse but it requires code modification. I don't know when I can fix it.

[PunshiB]

Well thanks for all this effort, I have modified the queries and its working partially I am stuck with some other error so I will close this ticket and raise another. If you want I can upload my changes once everything is working..