Hi Roza! >> We have hypothesis that somehow these corrupted caches were created by Key-Value API, not SQL.
Am I correct that you first create a cache "PUBLIC_ProductFeatures", and then create a table over the existing cache by invoking the DDL query? Could you please share some additional info that can help to reproduce your case? At least: 1. Ignite version on which cache and table were created. 2. CacheConfiguration for the cache if it was created before. 3. Classes specified in KEY_TYPE and VALUE_TYPE. 4. The SQL query you run and the "explain" plan for it. >> The more important question - is there any way to rebuild index and add affinity key back? Affinity can be set only during cache creation. Please check the docs if you create a cache before table: https://ignite.apache.org/docs/2.14.0/data-modeling/affinity-collocation#configuring-affinity-key Thanks, Maksim On Mon, Dec 19, 2022 at 3:55 PM Айсина Роза Мунеровна < [email protected]> wrote: > Hi Maksim! > > The problem is that simple SELECT query runs in ~20min - this index does > not work. > > More over, other (not corrupted) tables with affinity key == primary key > have index by concrete column, not *_KEY*, and have specified affinity > key - see my first message with example. > > We have hypothesis that somehow these corrupted caches were created by > Key-Value API, not SQL. Otherwise how specified indexes and affinity keys > were skipped in DDL while creating the caches? > > The more important question - is there any way to rebuild index and add > affinity key back? > > Thanks! > > On 16 Dec 2022, at 4:30 PM, Maksim Timonin <[email protected]> > wrote: > > Hi Roza, > > In this ddl primary key (product_sku) equals the affinity key > (product_sku). In such cases Ignite skips creating an additional index > because _key_PK index already covers primary key. > > Thanks, > Maksim > > On Fri, Dec 16, 2022 at 2:06 PM Айсина Роза Мунеровна < > [email protected]> wrote: > >> Hello Stephen! >> >> This DDL we use: >> >> CREATE TABLE IF NOT EXISTS PUBLIC.ProductFeatures >> ( >> product_sku INT PRIMARY KEY, >> total_cnt_orders_with_sku INT >> ) >> WITH "CACHE_NAME=PUBLIC_ProductFeatures, >> KEY_TYPE=io.sbmt.ProductFeaturesKey, >> VALUE_TYPE=io.sbmt.ProductFeaturesValue, AFFINITY_KEY=product_sku, >> TEMPLATE=PARTITIONED, BACKUPS=1 >> >> And all tables are created similarly. >> >> On 16 Dec 2022, at 1:03 PM, Stephen Darlington < >> [email protected]> wrote: >> >> Внимание: Внешний отправитель! >> Если вы не знаете отправителя - не открывайте вложения, не переходите по >> ссылкам, не пересылайте письмо! >> >> 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] >> Mob: >> Web: 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. >> >> >> >> *--* >> >> *Роза Айсина* >> Старший разработчик ПО >> *СберМаркет* | Доставка из любимых магазинов >> >> >> Email: [email protected] >> Mob: >> Web: 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. >> > > *--* > > *Роза Айсина* > > Старший разработчик ПО > > *СберМаркет* | Доставка из любимых магазинов > > > > Email: [email protected] > > Mob: > > Web: 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. >
