Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
Allow a user to create unique constraints of arbitrary length. This will be done on the upper layer, in the server, not in the engine. The server will create the invisible virtual column with a hash over the to-be-unique columns. And a normal BTREE index over this column. On insert or update it'll check the index for hash collisions and, if needed, will retrieve the actual rows to compare the data.
original bug report:
hi guys, i was reading about index... and i have a interesting problem...
i need to check if a file (ok a big row... no problem...) is inside my table...
what i´m thinking....
create table a(b int not null default '',c longblob not null, primary key b)
...
ok no problems....
the problem is... how to know if a file, let´s sai a file of 16MB is inside my table...
first solution is... MD5 and check each row... OK nice work....
but could be a other nicer solution?!
i was thinking something like:alter table a
add index some_index(c) using hash;could this work? since it´s a hash index, i don´t see why should i use a part of c value like c (100) for example...
could check if this is possible? today not... i tryed and it return:
/* SQL Error (1170): Coluna BLOB 'hash_automatico' usada na especificação de chave sem o comprimento da chave */ (in portugues PT_BR)i think that´s all
Attachments
Issue Links
- causes
-
MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Stalled
-
-
-
- Confirmed
-
- includes
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Stalled
-
-
-
- Open
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-35297 Table will encounter rebuild during minor version upgrade if contains hashing index on TEXT type unique key
-
- Confirmed
-
Activity
humm, nice =)
well i think the problem was sending big quanty of bytes in/out database, but... maybe a function to help index could be nice... i think that many guys implement something like
select count(*) from files where some_hash_field=(length || ';' || some_hash_value_calculated_in_a_script)
if count(*)> 0
select * from files where some_hash_field=(length || ';' || some_hash_value_calculated_in_a_script)
well, the first part is realy nice... but if it return >1 well send file is nicer than read many files with same size...
i don´t know if it could be nice, but implementation is close to hash with any binary char field, since it´s a hash not a btree... (i´m wrong?)
well =) let´s see what happen =)
it´s a feature request, don´t think that´s really needed but a 'formal' or a 'recommended' way to check if a 'file' (blob) is in database could be good, at least in documentation...
hi sergey any idea where the patch about this try was saved? i want to see if it's easy to implement but i don't know how to start
No, I don't. Any anyway, if I would like to do it, I would rather start
from scratch, than from some old incomplete patch.
this should be done in each storage engine, or index is a general "feature" of mariadb?
in each storage engine. in particular, MyISAM and Aria almost support this already, and that "attempt" that I was referring to was exactly about making them support it fully.
hummm nice, in first step make myisam and aria blob index possible, innodb and others is a second step, right?
| Field | Original Value | New Value |
|---|---|---|
| Summary | INDEX ON BLOBS | BLOBS - allow index (at least hash index) on blobs |
| Summary | BLOBS - allow index (at least hash index) on blobs | INDEX - BLOBS - allow index (at least hash index) on blobs |
| Fix Version/s | 10.1.0 [ 12200 ] |
| Priority | Trivial [ 5 ] | Major [ 3 ] |
| Labels | gsoc14 |
Hi Sergei,
I am interested in working on this feature as part of GSoC. From Google search it seems that this feature is really important as many people report problem related to having BLOB/TEXT as primary key or creating index for it.
I have good experience with databases but currently I am new to mariadb source code. I would appreciate if you can help me in getting started.
Thanks
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Workflow | defaullt [ 12411 ] | MariaDB v2 [ 42687 ] |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.1.0 [ 12200 ] |
| Labels | gsoc14 | gsoc14 gsoc15 |
| Description |
hi guys, i was reading about index... and i have a interesting problem... i need to check if a file (ok a big row... no problem...) is inside my table... what i´m thinking.... create table a(b int not null default '',c longblob not null, primary key b) ... ok no problems.... the problem is... how to know if a file, let´s sai a file of 16MB is inside my table... first solution is... MD5 and check each row... OK nice work.... but could be a other nicer solution?! i was thinking something like: alter table a add index some_index(c) using hash; could this work? since it´s a hash index, i don´t see why should i use a part of c value like c (100) for example... could check if this is possible? today not... i tryed and it return: /* SQL Error (1170): Coluna BLOB 'hash_automatico' usada na especificação de chave sem o comprimento da chave */ (in portugues PT_BR) i think that´s all |
Allow a user to create unique constraints of arbitrary length. Only in MyISAM (extending this to InnoDB is a separate task). MyISAM supports unique constraints of arbitrary length. They are used in queries like {code:sql} SELECT DISTINCT blob_column FROM some_table; {code} The server executes this query with the help of a temporary MyISAM table. And this table must be able to maintain uniqueness of all _blob_column_ values. *original bug report:* {quote} hi guys, i was reading about index... and i have a interesting problem... i need to check if a file (ok a big row... no problem...) is inside my table... what i´m thinking.... create table a(b int not null default '',c longblob not null, primary key b) ... ok no problems.... the problem is... how to know if a file, let´s sai a file of 16MB is inside my table... first solution is... MD5 and check each row... OK nice work.... but could be a other nicer solution?! i was thinking something like: alter table a add index some_index(c) using hash; could this work? since it´s a hash index, i don´t see why should i use a part of c value like c (100) for example... could check if this is possible? today not... i tryed and it return: /* SQL Error (1170): Coluna BLOB 'hash_automatico' usada na especificação de chave sem o comprimento da chave */ (in portugues PT_BR) i think that´s all {quote} |
| Description |
Allow a user to create unique constraints of arbitrary length. Only in MyISAM (extending this to InnoDB is a separate task). MyISAM supports unique constraints of arbitrary length. They are used in queries like {code:sql} SELECT DISTINCT blob_column FROM some_table; {code} The server executes this query with the help of a temporary MyISAM table. And this table must be able to maintain uniqueness of all _blob_column_ values. *original bug report:* {quote} hi guys, i was reading about index... and i have a interesting problem... i need to check if a file (ok a big row... no problem...) is inside my table... what i´m thinking.... create table a(b int not null default '',c longblob not null, primary key b) ... ok no problems.... the problem is... how to know if a file, let´s sai a file of 16MB is inside my table... first solution is... MD5 and check each row... OK nice work.... but could be a other nicer solution?! i was thinking something like: alter table a add index some_index(c) using hash; could this work? since it´s a hash index, i don´t see why should i use a part of c value like c (100) for example... could check if this is possible? today not... i tryed and it return: /* SQL Error (1170): Coluna BLOB 'hash_automatico' usada na especificação de chave sem o comprimento da chave */ (in portugues PT_BR) i think that´s all {quote} |
Allow a user to create unique constraints of arbitrary length. Only in MyISAM (extending this to InnoDB is a separate task). MyISAM supports unique constraints of arbitrary length. They are used in queries like {code:sql} SELECT DISTINCT blob_column FROM some_table; {code} The server executes this query with the help of a temporary MyISAM table. And this table must be able to maintain uniqueness of all _blob_column_ values. Internally it's implemented as an index over hash values of blobs, with the additional direct value comparison on collisions. So, MyISAM can do it internally, but there is no way for the end user to create such a unique constraint explicitly. The following statement {code:sql} CREATE TABLE some_table (blob_column BLOB, UNIQUE(blob_column)); {code} will fail. This task is about giving the access to this MyISAM feature to the SQL layer, about allowing the end user to create long unique constraints explicitly. *original bug report:* {quote} hi guys, i was reading about index... and i have a interesting problem... i need to check if a file (ok a big row... no problem...) is inside my table... what i´m thinking.... create table a(b int not null default '',c longblob not null, primary key b) ... ok no problems.... the problem is... how to know if a file, let´s sai a file of 16MB is inside my table... first solution is... MD5 and check each row... OK nice work.... but could be a other nicer solution?! i was thinking something like: alter table a add index some_index(c) using hash; could this work? since it´s a hash index, i don´t see why should i use a part of c value like c (100) for example... could check if this is possible? today not... i tryed and it return: /* SQL Error (1170): Coluna BLOB 'hash_automatico' usada na especificação de chave sem o comprimento da chave */ (in portugues PT_BR) i think that´s all {quote} |
| Fix Version/s | 10.2.0 [ 14601 ] | |
| Fix Version/s | 10.1 [ 16100 ] |
| Summary | INDEX - BLOBS - allow index (at least hash index) on blobs | Unique indexes for blobs |
| Workflow | MariaDB v2 [ 42687 ] | MariaDB v3 [ 66291 ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Labels | gsoc14 gsoc15 | gsoc14 gsoc15 gsoc16 |
| Assignee | Sergei Golubchik [ serg ] |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-10178 [ MDEV-10178 ] |
| Epic Link | PT-58 [ 62150 ] |
| Fix Version/s | 10.3 [ 22126 ] | |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Assignee | Sergei Golubchik [ serg ] | Sachin Setiya [ sachin.setiya.007 ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Epic Link | PT-76 [ 68557 ] |
| Assignee | Sachin Setiya [ sachin.setiya.007 ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Golubchik [ serg ] | Sachin Setiya [ sachin.setiya.007 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Sachin Setiya [ sachin.setiya.007 ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Assignee | Sergei Golubchik [ serg ] | Sachin Setiya [ sachin.setiya.007 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Sachin Setiya [ sachin.setiya.007 ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Golubchik [ serg ] | Sachin Setiya [ sachin.setiya.007 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Component/s | Data Definition - Create Table [ 14503 ] | |
| Fix Version/s | 10.4.3 [ 23230 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Description |
Allow a user to create unique constraints of arbitrary length. Only in MyISAM (extending this to InnoDB is a separate task). MyISAM supports unique constraints of arbitrary length. They are used in queries like {code:sql} SELECT DISTINCT blob_column FROM some_table; {code} The server executes this query with the help of a temporary MyISAM table. And this table must be able to maintain uniqueness of all _blob_column_ values. Internally it's implemented as an index over hash values of blobs, with the additional direct value comparison on collisions. So, MyISAM can do it internally, but there is no way for the end user to create such a unique constraint explicitly. The following statement {code:sql} CREATE TABLE some_table (blob_column BLOB, UNIQUE(blob_column)); {code} will fail. This task is about giving the access to this MyISAM feature to the SQL layer, about allowing the end user to create long unique constraints explicitly. *original bug report:* {quote} hi guys, i was reading about index... and i have a interesting problem... i need to check if a file (ok a big row... no problem...) is inside my table... what i´m thinking.... create table a(b int not null default '',c longblob not null, primary key b) ... ok no problems.... the problem is... how to know if a file, let´s sai a file of 16MB is inside my table... first solution is... MD5 and check each row... OK nice work.... but could be a other nicer solution?! i was thinking something like: alter table a add index some_index(c) using hash; could this work? since it´s a hash index, i don´t see why should i use a part of c value like c (100) for example... could check if this is possible? today not... i tryed and it return: /* SQL Error (1170): Coluna BLOB 'hash_automatico' usada na especificação de chave sem o comprimento da chave */ (in portugues PT_BR) i think that´s all {quote} |
Allow a user to create unique constraints of arbitrary length. This will be done on the upper layer, in the server, not in the engine. The server will create the invisible virtual column with a hash over the to-be-unique columns. And a normal BTREE index over this column. On insert or update it'll check the index for hash collisions and, if needed, will retrieve the actual rows to compare the data.
*original bug report:* {quote} hi guys, i was reading about index... and i have a interesting problem... i need to check if a file (ok a big row... no problem...) is inside my table... what i´m thinking.... create table a(b int not null default '',c longblob not null, primary key b) ... ok no problems.... the problem is... how to know if a file, let´s sai a file of 16MB is inside my table... first solution is... MD5 and check each row... OK nice work.... but could be a other nicer solution?! i was thinking something like: alter table a add index some_index(c) using hash; could this work? since it´s a hash index, i don´t see why should i use a part of c value like c (100) for example... could check if this is possible? today not... i tryed and it return: /* SQL Error (1170): Coluna BLOB 'hash_automatico' usada na especificação de chave sem o comprimento da chave */ (in portugues PT_BR) i think that´s all {quote} |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-20918 [ MDEV-20918 ] |
| Link |
This issue includes |
| Link |
This issue relates to |
| Link | This issue causes MDEV-22676 [ MDEV-22676 ] |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link | This issue relates to MDEV-26035 [ MDEV-26035 ] |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 66291 ] | MariaDB v4 [ 131946 ] |
| Link | This issue relates to MDEV-27371 [ MDEV-27371 ] |
| Link |
This issue causes |
How does this handle COLLATION of a large TEXT field? Is there a way to "hash" while honoring complex UTF-* case and accent handling?
It should do that automatically. But that part has a bug, though so it doesn't always work. Reported as MDEV-27653
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link | This issue causes MDEV-28710 [ MDEV-28710 ] |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link | This issue causes MDEV-28514 [ MDEV-28514 ] |
| Link | This issue relates to MDEV-26035 [ MDEV-26035 ] |
| Link | This issue causes MDEV-26035 [ MDEV-26035 ] |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-29949 [ MDEV-29949 ] |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-30087 [ MDEV-30087 ] |
| Link | This issue relates to MDEV-30095 [ MDEV-30095 ] |
| Link | This issue relates to MDEV-30441 [ MDEV-30441 ] |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-13445 [ MDEV-13445 ] |
| Link | This issue relates to MDEV-31072 [ MDEV-31072 ] |
| Link | This issue causes MDEV-31093 [ MDEV-31093 ] |
| Link |
This issue causes |
For the record, the following patch should disable the MDEV-371 functionality:
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
|
index 6e8a4795f21..f8f3eefc114 100644
|
--- a/sql/sql_table.cc
|
+++ b/sql/sql_table.cc
|
@@ -2441,6 +2441,8 @@ static inline void make_long_hash_field_name(LEX_CSTRING *buf, uint num)
|
static Create_field * add_hash_field(THD * thd, List<Create_field> *create_list,
|
KEY *key_info)
|
{
|
+ my_error(ER_TOO_LONG_KEY, MYF(0), 1000);
|
+ return nullptr;
|
List_iterator<Create_field> it(*create_list);
|
Create_field *dup_field, *cf= new (thd->mem_root) Create_field();
|
cf->flags|= UNSIGNED_FLAG | LONG_UNIQUE_HASH_FIELD; |
This may be useful for testing, because there are many open bugs related to indexed virtual columns, and MDEV-371 is internally creating hidden indexed virtual columns.
| Link | This issue causes MDEV-22760 [ MDEV-22760 ] |
| Link | This issue causes MENT-1925 [ MENT-1925 ] |
| Link | This issue relates to MENT-1935 [ MENT-1935 ] |
| Link | This issue causes MDEV-32668 [ MDEV-32668 ] |
| Link | This issue causes MENT-1986 [ MENT-1986 ] |
| Link | This issue relates to MDEV-31290 [ MDEV-31290 ] |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-23206 [ MDEV-23206 ] |
| Link |
This issue causes |
| Link |
This issue causes |
| Link | This issue causes MDEV-34304 [ MDEV-34304 ] |
| Link | This issue causes MDEV-34344 [ MDEV-34344 ] |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-35297 [ MDEV-35297 ] |
| Link | This issue causes MDEV-28514 [ MDEV-28514 ] |
yes, this was discussed for quite a while, and has a long history in MySQL bug database.
there was even an attempt to implement this, but somehow it failed, don't really know why.
I guess, we can give it another try.