Setting key / index length

Question

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!

Comments

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

---

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?

---

And are you sure it's

options={"lengths": {191}}

and not

options={"lengths": 191}

?

Answer 1

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.

Comments

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

---

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.

---

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

Answer 2

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)

Comments

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}}

---

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.

---

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".

---

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.

---

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]"])]