Hi Sergei,

As I told you, I was working on prototype just for blobs.

I was having some issue with key definition between MariaDB .frm file

and information in the MyISAM storage engine. I have solved that problem temporarily. Here is my output

MariaDB [database1]> set storage_engine=myisam;

Query OK, 0 rows affected (0.00 sec)

MariaDB [database1]> create database mydb;

Query OK, 1 row affected (0.00 sec)

MariaDB [database1]> use mydb;

Database changed

MariaDB [mydb]> create table table1(a int,b1 blob,b2 longblob,unique(a,b1,b2(5))

);

Query OK, 0 rows affected (0.09 sec)

MariaDB [mydb]> insert into table1 values(4,3333333,55555555);

Query OK, 1 row affected (0.00 sec)

MariaDB [mydb]> insert into table1 values(4,3333333,55555);

ERROR 1169 (23000): Can't write, because of unique constraint, to table 'table1'

MariaDB [mydb]> insert into table1 values(4,3333333,55555555);

ERROR 1169 (23000): Can't write, because of unique constraint, to table 'table1'

MariaDB [mydb]> insert into table1 values(4,333333453,55555555);

Query OK, 1 row affected (0.00 sec)

3 rows in set (0.00 sec)

MariaDB [mydb]> insert into table1 values(5,333333453,55555555);

Query OK, 1 row affected (0.00 sec)

MariaDB [mydb]> insert into table1 values(5,333333453,55555);

ERROR 1169 (23000): Can't write, because of unique constraint, to table 'table1'

MariaDB [mydb]> show indexes from table1;

+--------+------------+----------+--------------+-------------+-----------+-----

--------+----------+--------+------+------------+---------+---------------+

+--------+------------+----------+--------------+-------------+-----------+-----

--------+----------+--------+------+------------+---------+---------------+

| table1 | 0 | a | 1 | a | A |

| table1 | 0 | a | 2 | b1 | A |

| table1 | 0 | a | 3 | b2 | A |

0 | 5 | NULL | YES | BTREE | | |

+--------+------------+----------+--------------+-------------+-----------+-----

--------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.00 sec)

Any suggestions would be helpful.

Thanks,

Shubham

On 20 March 2016 at 11:10, Shubham Barai <shubhambaraiss@gmail.com> wrote:

Hi Sergei,
Sorry! I made a mistake. Actually, when I changed my storage engine to MyISAM and executed create table statement(with unique blobs and no prefix length),mysqld crashed. After reconnecting to the server, the storage engine got changed to default (InnoDB).I didn't know about that and I executed all queries on InnoDB. I thought my patch was working for MyISAM but there are some issues.I am working on it but it might require some time to get it working. Since application deadline for GSoC is 25 March, I will try to submit my proposal as early as possible.

Thanks,
Shubham

On 19 March 2016 at 14:22, Shubham Barai <shubhambaraiss@gmail.com> wrote:
Hello Sergei,

As you suggested, I tried to develop a prototype just for blobs.
So far it is working fine but I am still verifying it for different cases.
I will provide you the link to my Github repository shortly.

Thanks,
Shubham

On 16 March 2016 at 17:55, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Shubham!

On Mar 16, Shubham Barai wrote:
> Hello, Sergie!
> I tried to explore the source code from
> mi_create.c,ha_myisam.cc,sql/sql_table.cc,include/myisam.h,.and some other
> files.
> The main task is to create MI_UNIQUEDEF "uniques" for long unique
> constraints.
> We have to consider all the cases where we need to create MI_UNIQUEDEF
> instead of MI_KEYDEF.
>
> It will include queries like
> create table table1 (blob_column blob,unique(blob_column) );
> create table table1 (a int,blob_column
> blob,unique(a,blob_column) );
> create table table1 (a int,blob_column1 blob,blob_column2
> blob,unique(blob_column1(300),blob_column2) );
> (key with multiple blob columns and one of the blob column specified with
> prefix length).
>
> I think we have to create MI_UNIQUEDEF if any one of the columns in a key
> is a blob field without prefix length.

Yes. And also for any other UNIQUE constraint that is too long for a
normal index. For example, many long VARCHAR columns.

But, of course, for a prototype one can start just with blobs.

> In sql/sql_table, mysql_prepare_create_table is the function which
> prepares the table and key structures for table creation in mi_create.
> It generates an error if any one of the blob fields in a key is
> specified without length. Currently, this task is limited to MyISAM,
> so if any other storage engine is selected, we have to generate the
> same error in mysql_prepare_create_table.

There's a generic check whether the key is too long. It can be used
here, it doesn't depend on the storage engine.

> In storage/myisam/ha_myisam.cc, table2myisam is a function which allocates
> and initializes myisam key and column definitions.The current function
> prototype of table2myisam is
> table2myisam(TABLE *table_arg, MI_KEYDEF **keydef_out,
> MI_COLUMNDEF **recinfo_out, uint records_out)
> We have to change it to
> table2myisam(TABLE *table_arg,MI_KEYDEF **keydef_out,MI_UNIQUEDEF **
> uniquedef_out,MI_COLUMNDEF **recinfo_out,uint records_out)

Right.

> table2myisam initializes all the key definitions from table_arg->keyinfo.
> So we can set a new flag (say uniquedef) in a keyinfo struct in
> mysql_prepare_create_table if any one of the key_part consists of blob
> field without prefix length.

There's a field 'algorithm' already. Because MI_UNIQUEDEF in MyISAM is,
basically, an index of hashed column values, it is kind of a hash index.
So you can use algorithm=HA_KEY_ALG_HASH to mark such columns. And the
user will be able to create these indexes explicitly with

create table table1 (blob_column blob,unique(blob_column) using hash);

> Later we can check the flag in table2myisam to see if we want to
> create MI_KEYDEF or MI_UNIQUEDEF.
>
> Thanks,
> Shubham.

Very good!

Regards,
Sergei
Chief Architect MariaDB
and security@mariadb.org