Error on index declaration in channels table migration on mysql

[Bibendus83]

This is the error SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'cedente' used in key specification without a key length

I would define a new id field for this table and use it as index instead of a text field. In alternative the quicker fix to keep all as it is is to define the key with a max length

Change 20180923120240_create_channels_table.php line 18 $table->primary('cedente'); in $table->primary([DB::raw('cedente(191)')]);

The choice of 191 is related to compatibility with older version of mysql, innodb and utf8 https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes/31474509

[simevo]

mmmh seems like a limitation of MySql but it definitely makes sense to support MySql... in this column we intend to store this type of data:

testsdi=> select * from channels;
    cedente     | issuer  
----------------+---------
 AT-00000000000 | 9999993
 IT-23456789012 | 1111111
 IT-99999999999 | 0000003
 IT-19999999999 | 0000003
 IT-8888888888A | 0000005
(5 rows)

so we can find a better estimate from the max length as follows we identify Cedente / Prestatore by the FatturaElettronicaHeader.CedentePrestatore.IdFiscaleIVA field: https://github.com/simevo/fattura-elettronica-json/blob/master/Schema_del_file_xml_FatturaPA_versione_1.2_cleanup.xsd#L521

we store the IdFiscaleIVA as a string obtained by collating the two fields that make up the IdFiscaleIVAType: https://github.com/simevo/fattura-elettronica-json/blob/master/Schema_del_file_xml_FatturaPA_versione_1.2_cleanup.xsd#L54 separated with an hyphen; since NazioneType is 2-chars and CodiceType is 1-28 chars it follows that the right length to use is 2+1+28 = 31

[Bibendus83]

In general I would try to limit the usage of text fields when not necessary like in this case when we know exactly the max length of the field. I saw there are other unnecessary text fields in other tables too!

I never worked with postgres but in mysql text fields are not stored inside the table like varchars. They are stored externally and that makes them less performant, seems more or less 300% slower: https://forums.mysql.com/read.php?24,105964,105964