Suggestion: change default MySQL VARCHAR length to 191 characters

[jstsch]

When using MySQL, In Fluid-mode, RedBean creates InnoDB tables with the utf8mb4_unicode_ci collation. Great! That's a very sane default.

However, InnoDB has a maximum index length of 767 bytes, so with utf8mb4 you can only store 191 characters. This means that when you subsequently add an index to the column you get a (not-so-obvious) MySQL-error. See also this post.

My suggestion is to change the default for C_DATATYPE_TEXT8 in RedBean, so that VARCHAR(191) columns get created instead of VARCHAR(255) columns. And of course, that when content longer than 191 characters is detected the column gets upgraded to TEXT.

Posting it here instead of sending a pull request, since I can imagine this will lead to some discussion...

[gabor]

hi,

Varchars are not indexed by RedbeanPHP by default, therefore it does not make sense for RedBeanPHP to limit the field to 191 chars.
However this issue keeps coming up, so I'll look into this.

cheers,
Gabor

[Matthew Frederico]

I had this same issue - There are constants in RedBean_QueryWriter that I added to fix this:

I added: 

const C_DATATYPE_TEXTUTF8 = 7;

Then under the constructor in the typeno_sqltype array I added:

 RedBean_QueryWriter_MySQL::C_DATATYPE_TEXTUTF8            => ' VARCHAR(191) ',

And under the "scanType" function I added:

if ( mb_strlen( $value, 'UTF-8' ) <= 191 ) {

            return RedBean_QueryWriter_MySQL::C_DATATYPE_TEXTUTF8;

        }

... and indexing appeared to work just fine after I did those changes.  

However, this only works if the varchar is less than 191 UTF8 chars.

--
You received this message because you are subscribed to the Google Groups "redbeanphp" group.
To unsubscribe from this group and stop receiving emails from it, send an email to redbeanorm+...@googlegroups.com.
To post to this group, send email to redbe...@googlegroups.com.
Visit this group at http://groups.google.com/group/redbeanorm.
For more options, visit https://groups.google.com/d/optout.

--

--
-- Matthew Frederico

[gabor]

Hi,

Frederico, I think your solution is the best, once the varchar exceeds 191 chars, RedBeanPHP should continue to use varchar 255, there is no way to apply an InnoDB index after that.

Created an issue:
https://github.com/gabordemooij/redbean/issues/411

cheers,
Gabor