maria-developers team mailing list archive

[Shubham Barai <shubhambaraiss@xxxxxxxxx>]

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;
+--------+------------+----------+--------------+-------------+-----------+-----
--------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Card
inality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-----
--------+----------+--------+------+------------+---------+---------------+
| table1 |          0 | a        |            1 | a           | A         |
   NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| table1 |          0 | a        |            2 | b1          | A         |
   NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxx> 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@xxxxxxxxxxx
>>>
>>
>>
>