What are the CREATE TABLE commands for those tables?
> On 16 Dec 2022, at 09:39, Айсина Роза Мунеровна <[email protected]>
> wrote:
>
> Hola!
>
> We've discovered some strange behaviour in Ignite cluster and now we are
> trying to understand how to recover from this state.
>
> So we have 5 node cluster with persistence and all caches either replicated
> or partitioned with affinity key.
> All caches are created via DDL with CREATE TABLE IF NOT EXISTS statements in
> one regular job (once per day).
>
> The problem is that we hit Query execution is too long warning.
> After some debug we found out that some tables have missed indexes and
> affinity keys.
> More precisely - corrupted tables have indexes not by exact column name but
> for _KEY column.
> And no affinity key at all.
>
> select
> TABLE_NAME,
> INDEX_NAME,
> COLUMNS
> from SYS.INDEXES
> where TABLE_NAME = ‘PRODUCTFEATURES’ — broken table
> or TABLE_NAME = ‘USERFEATURESDISCOUNT’ — healthy table
> ;
>
> Result:
> +--------------------+------------+----------------------------+
> |TABLE_NAME |INDEX_NAME |COLUMNS |
> +--------------------+------------+----------------------------+
> |USERFEATURESDISCOUNT|_key_PK_hash|"USER_ID" ASC, "USER_ID" ASC|
> |USERFEATURESDISCOUNT|__SCAN_ |null |
> |USERFEATURESDISCOUNT|_key_PK |"USER_ID" ASC |
> |USERFEATURESDISCOUNT|AFFINITY_KEY|"USER_ID" ASC |
> |PRODUCTFEATURES |_key_PK_hash|"_KEY" ASC |
> |PRODUCTFEATURES |__SCAN_ |null |
> |PRODUCTFEATURES |_key_PK |"_KEY" ASC |
> +--------------------+------------+----------------------------+
>
>
> Query execution even with simplest statements with filters on primary and
> affinity keys takes ~20min in best case.
> We have 8 tables, 5 out 8 are corrupted.
>
> So the questions are:
> 1. What can probably cause such state?
> 2. Is there any way to recover without full delete-refill tables? I see that
> index can be created via CREATE INDEX, but affinity key can be created only
> via CREATE TABLE statement?
>
> Thanks in advance!
> --
>
> Роза Айсина
> Старший разработчик ПО
> СберМаркет | Доставка из любимых магазинов
>
> Email: [email protected] <mailto:[email protected]>
> Mob:
> Web: sbermarket.ru <https://sbermarket.ru/>
> App: iOS
> <https://apps.apple.com/ru/app/%D1%81%D0%B1%D0%B5%D1%80%D0%BC%D0%B0%D1%80%D0%BA%D0%B5%D1%82-%D0%B4%D0%BE%D1%81%D1%82%D0%B0%D0%B2%D0%BA%D0%B0-%D0%BF%D1%80%D0%BE%D0%B4%D1%83%D0%BA%D1%82%D0%BE%D0%B2/id1166642457>
> и Android
> <https://play.google.com/store/apps/details?id=ru.instamart&hl=en&gl=ru>
>
>
>
>
>
> УВЕДОМЛЕНИЕ О КОНФИДЕНЦИАЛЬНОСТИ: это электронное сообщение и любые
> документы, приложенные к нему, содержат конфиденциальную информацию.
> Настоящим уведомляем Вас о том, что, если это сообщение не предназначено Вам,
> использование, копирование, распространение информации, содержащейся в
> настоящем сообщении, а также осуществление любых действий на основе этой
> информации, строго запрещено. Если Вы получили это сообщение по ошибке,
> пожалуйста, сообщите об этом отправителю по электронной почте и удалите это
> сообщение.
> CONFIDENTIALITY NOTICE: This email and any files attached to it are
> confidential. If you are not the intended recipient you are notified that
> using, copying, distributing or taking any action in reliance on the contents
> of this information is strictly prohibited. If you have received this email
> in error please notify the sender and delete this email.
>
