search

codenotary / immudb

immudb - immutable database based on zero trust, SQL/Key-Value/Document model, tamperproof, data change history
https://immudb.io
Other
8.52k stars 337 forks source link

Getting Error: indexed key of unsupported type or exceeded length: can not create index #1922

Open iSRIDHARRAO opened 5 months ago

iSRIDHARRAO commented 5 months ago

Getting indexed key of unsupported type or exceeded length: can not create index using columns '[userid iudxid time api]'. Max key length is 512 error while creating an index in immudb:1.9DOM.2 container

Used the following python snippet

from immudb import ImmudbClient
client = ImmudbClient("{0}:3322".format(config['immudb_host']))
client.sqlExec("auditingtable(id VARCHAR[128] NOT NULL, userRole VARCHAR[64] NOT NULL,userID VARCHAR[128] NOT NULL,iid VARCHAR[250] NOT NULL,api VARCHAR[128] NOT NULL,method VARCHAR[32] NOT NULL,time INTEGER NOT NULL,iudxID VARCHAR[256] NOT NULL,PRIMARY KEY id)")
client.sqlExec("auditingtable(userID, iudxID, time, api)")

Earlier we used codenotary/immudb:1.4.1 image and deployed immudb, at that time index got created but now when we are using the recently released image ( immudb:1.9DOM.2 ) we are getting the error

SimoneLazzaris commented 4 months ago

Hi @iSRIDHARRAO thanks for the bug report.

I believe that you are missing some part of the sql statement, since we can't see the create table part.

Anyway, there is a limit of 512 bytes on key length for indexes. So you can't create an index where the sum of the length of its parts is more than 512 bytes, and I believe that you are just above that.

iSRIDHARRAO commented 4 months ago

Hi @SimoneLazzaris , Sorry for missing some part, the missed part I included below

from immudb import ImmudbClient
client = ImmudbClient("{0}:3322".format(config['immudb_host']))
client.sqlExec("CREATE TABLE auditingtable(id VARCHAR[128] NOT NULL, userRole VARCHAR[64] NOT NULL,userID VARCHAR[128] NOT NULL,iid VARCHAR[250] NOT NULL,api VARCHAR[128] NOT NULL,method VARCHAR[32] NOT NULL,time INTEGER NOT NULL,iudxID VARCHAR[256] NOT NULL,PRIMARY KEY id)")
client.sqlExec("CREATE INDEX ON auditingtable(userID, iudxID, time, api)")

I wanted to know that this limit was added recently or was there before in 1.4 too

jeroiraz commented 4 months ago

Hi @SimoneLazzaris , Sorry for missing some part, the missed part I included below

from immudb import ImmudbClient
client = ImmudbClient("{0}:3322".format(config['immudb_host']))
client.sqlExec("CREATE TABLE auditingtable(id VARCHAR[128] NOT NULL, userRole VARCHAR[64] NOT NULL,userID VARCHAR[128] NOT NULL,iid VARCHAR[250] NOT NULL,api VARCHAR[128] NOT NULL,method VARCHAR[32] NOT NULL,time INTEGER NOT NULL,iudxID VARCHAR[256] NOT NULL,PRIMARY KEY id)")
client.sqlExec("CREATE INDEX ON auditingtable(userID, iudxID, time, api)")

I wanted to know that this limit was added recently or was there before in 1.4 too

Hi @iSRIDHARRAO, the max key length limit was there already. The reason to set a limit is due to performance, the longer the keys the worst the performance you will get reading and writing data. It's not externally configurable but the size is currently set in a constant https://github.com/codenotary/immudb/blob/65a25a5b71de2522d93ea0c5f5fc585c9a7a9f69/embedded/sql/engine.go#L95