maria-discuss team mailing list archive

[Karthick Subramanian <ksubramanian@xxxxxxxxxxxxxxx>]

Hi,

I come across similar situation today:

server version: 10.1.21-MariaDB
character_set_database   | utf8
character_set_filesystem | binary


Initially when I tried to create Unique Index on below columns, I got error
as max key length 762 bytes error:

col_1 varchar(500)
col_2 varchar(250)
col_3 varchar(5)

So I have changed the innodb_large_prefix=ON and innodb_file_format from
Antelop to Baracuda.

Even after changing to above settings, Still I am getting 762 bytes
limitation error. Even I tried to recreate the table with different name to
make sure the settings will apply to new tables, but still get the error.
So not sure what's really going on.

So I tried to change the col_1 datatype varchar(500) to varchar(250), its
successfully created. I thought the length calc will be on total keys (i.e.
3 column added together), but it doesn't seem to be.

Any thought on this,please.


On Tue, Mar 7, 2017 at 1:41 PM, Ian Gilfillan <ian@xxxxxxxxxxx> wrote:

> On 07/03/2017 04:00, Marco Nicosia wrote:
>
>>
>> We noticed a change between MariaDB 10.1.18 and 10.1.20, but I haven't
>> been able to find anything in the changelogs or JIRA that would help me
>> understand what changed, and why.
>>
>> On 10.1.18, if I set `innodb_large_prefix=OFF` I can create indexes with
>> lengths greater than 767 bytes and MariaDB only issues a warning. If I
>> configure `innodb_large_prefix=ON` I get an error and the index fails to
>> create. This seems like a bug?
>>
>> On 10.1.20, mysql fails to create the index regardless of how
>> innodb_large_prefix is configured.
>>
>> We think that the result is that in 10.1.20, tables must be created or
>> altered to use `ROW_FORMAT DYNAMIC` or `ROW_FORMAT COMPRESSED` if they are
>> to contain an index with greater than 767 bytes in it.
>>
>> Is this the desired behavior? Was there a bug in 10.1.18 (and maybe
>> previous)?
>>
>> I'm trying to understand the context better so that when devs ask us why
>> index creation is failing, we give them the correct answer for why it used
>> to work, and what they should be doing differently now that we're on
>> 10.1.20.
>>
>>
>>
> I wonder if it's related to this commit, from 10.1.19:
> https://github.com/MariaDB/server/commit/d451d77
>
> I can't recreate the behaviour you mention from 10.1.18, but yes, it does
> seems like a bug, as the only way I'm aware of that indexes greater than
> 767 can be created is with innodb_large_prefix and its related
> requirements. Can you share your settings/statements that permitted this?
>
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>