[Maria-discuss] Change to innodb_large_prefix with respect to creating long indexes
Hi there, 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. Thanks! -- Marco Nicosia Pivotal Software, Inc.
On 07/03/17 13:00, Marco Nicosia wrote:
Hi there,
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.
That is part of the requirement: https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#inno...
Is this the desired behavior?
It appears to be.
Was there a bug in 10.1.18 (and maybe previous)?
Possibly. I couldn't see anything in jira either.
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.
Thanks!
-- Marco Nicosia Pivotal Software, Inc.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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?
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@mariadb.org> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (4)
-
Daniel Black -
Ian Gilfillan -
Karthick Subramanian -
Marco Nicosia