Setting key / index length

0 votes
asked Mar 24, 2022 in Feature Request by mklewitz (190 points)

Hi,

MySQL 5.6 and Amazon Aurora have a max key length of 767 bytes. Because one character uses 4 bytes when using utf8mb4 charset, that means an index can only be created for max 191 chars. So even creating an index on a VARCHAR(255) field will fail with the error message: #1071 - Specified key was too long.

The only way to index such fields is either by reducing the field's size to 191 or by limiting the index/key length to 191.

Defining the field size in Doctrine is possible since 2.9 (see https://github.com/doctrine/dbal/pull/2412).

Can you please add an option to set the key length in Skipper? Exporting to entities should be pretty straightforward. E.g.: @Index(name="indexname", columns={"columnname"}, options={"lengths": {191}})

Thanks very much!

commented Mar 24, 2022 by ludek.vodicka Skipper developer (140,450 points)

Thanks for your message. We will check it and let you know.

commented Mar 24, 2022 by ludek.vodicka Skipper developer (140,450 points)

Unfortunately, the link you provided doesn't show any differences/code.

Can you please send me any link to documentation where is this feature described?

commented Mar 24, 2022 by ludek.vodicka Skipper developer (140,450 points)

And are you sure it's

options={"lengths": {191}}

and not

options={"lengths": 191}

?

2 Answers

0 votes
answered Mar 24, 2022 by mklewitz (190 points)

Well, both versions work. However the {} version also works for composite indexes. E.g. {191, 191}

Please see https://stackoverflow.com/questions/32539973/configuring-index-text-length-mysql-in-doctrine

I verified it does indeed work.

commented Mar 24, 2022 by ludek.vodicka Skipper developer (140,450 points)

Oh, ok. So it's necessary to be able to enter this because of composited indexes. ok.

commented Mar 24, 2022 by mklewitz (190 points)

Well, it would be enough to add an option "lengths" to the properties of indexes. The contents of that field can simply be inserted between { and }.
That way it would work for both single and composited indexes.

commented Mar 24, 2022 by ludek.vodicka Skipper developer (140,450 points)

this is exactly what we did ;-). We already sent a new beta to our testing/deploy server. I will let you know when new beta will be ready

0 votes
answered Mar 25, 2022 by ludek.vodicka Skipper developer (140,450 points)

Hi,

please try to download the latest beta

https://www.skipper18.com/support/402/downloads-skipper-beta

and try new options property on indexes. (we implemented lengths and where property)

enter image description here

commented Apr 21, 2022 by mklewitz (190 points)
edited Apr 21, 2022 by mklewitz

Hi,

first of all, sorry for my delayed response!

Thanks for implementing this feature! For my purposes it would be enough, because it works by entering "{191}" in the options field, but I think for others it isn't intuitive at all.

During my tests, I noticed that Skipper doesn't include the { } by itself. However, when leaving them out, entering a value of 191 or 191,191 for composite keys result in an error while generating doctrine migrations:

12:25:53 ERROR     [app] TypeError: array_shift(): Argument #1 ($array) must be of type array, string given (uncaught error) at /application/vendor/doctrine/dbal/src/Schema/Index.php line 107 while running console command `doctrine:migrations:diff` ["exception" => TypeError { …}]
Notification also broke

In Index.php line 107:

  array_shift(): Argument #1 ($array) must be of type array, string given  

My suggestion is to let Skipper insert the { } itself. Also, please avoid adding "" for composite keys.

This is how it is currently:

Value -> Annotation
191 -> options={"lengths":191}
191,191 -> options={"lengths":"191,191"}
{191} -> options={"lengths":{191}}
{191,191} -> options={"lengths":{191,191}}

The first two lead to an error while generating migrations. It would be great if Skipper just surrounds the entered value with { }, so that it is like this:

191 -> options={"lengths":{191}}
191,191 -> options={"lengths":{191,191}}

commented Apr 21, 2022 by ludek.vodicka Skipper developer (140,450 points)

thanks for the feedback. It's strange that {} doesn't work for you. If I remember correctly, we tested that and it worked.

We will check it and let you know.

commented Apr 21, 2022 by mklewitz (190 points)

Well, as I said. It works for me! I can e.g. fill in "{191}" and it works like a charm.

However, what I am saying is: Other users are not aware that they have to enter braces around the length. So if they simply enter "191" they run into errors when generating migrations with doctrine. They won't know why (because there is no informationion that says they have to surround the values with braces) and will get frustrated.

To avoid this, it would be nice if Skipper sorrounds the value with braces automatically, so users just have to enter the length(s) by setting the value to either "191" or to a comma separated value like "191,191".

commented Apr 21, 2022 by ludek.vodicka Skipper developer (140,450 points)

oh, right. I wrongly read it, thanks

We will check if we are able to add missing braces. Because the property system is very universal and not sure if there is an easy way how to do that.

commented May 19, 2023 by easymailing (190 points)

I ave the same problem but i am using attributes i need an array like this:

[ORM\Index(name: "array", columns: ["array"], options: ["lengths"=>[100]])]

If i put [100] the result is:

[ORM\Index(name: "array", columns: ["array"], options: ["lengths"=>"[100]"])]

...