search

yiisoft / db-mysql

MySQL and MariaDB driver for Yii Database
https://www.yiiframework.com
BSD 3-Clause "New" or "Revised" License
37 stars 15 forks source link

Schema::TYPE_TEXT is too short in MySQL #2

Open nkovacs opened 9 years ago

nkovacs commented 9 years ago

Schema::TYPE_TEXT converts to TEXT in MySQL. This is just 64K, which is not a lot. Unless it's in strict mode, MySQL will truncate anything longer when inserting, and not throw an error (it'll throw a warning, but you'll have to look in the mysql server log). You can use MEDIUMTEXT and LONGTEXT for your fields, but that only works in MySQL, and not in Postgre and Sqlite for example (where TEXT is long enough), so if you're writing portable code, you can't just use 'MEDIUMTEXT'. It's also likely that most developers will just use Yii's schema types, without knowing that the data is limited to 64K.

I see several possible solutions to this:

  1. Adding Schema::TYPE_MEDIUMTEXT and Schema::TYPE_LONGTEXT, similar to how we have TYPE_INTEGER and TYPE_BIGINT. This probably breaks BC, because custom QueryBuilders will be missing these types, and it also makes the typeMap more bloated, just because of a quirk in MySQL (unlike bigint, which is present in other databases). A developer using Postgre might not even care, since they won't have problems with TYPE_TEXT. I don't think this is a good solution.
  2. Changing \yii\db\mysql\QueryBuilder::$typeMap[Schema::TYPE_TEXT] to 'MEDIUMTEXT' or 'LONGTEXT'. This adds 1 or 2 extra bytes per field per row, which can add up if you have a lot of rows, but in typical use cases shouldn't matter much. It's better to have a safe default, and let those who know the trade-offs optimize it. This change would cause inconsistency in existing databases (fields that were created before the change would remain TEXT, new ones would become MEDIUMTEXT, while rebuilding the database using the new version would create all MEDIUMTEXT).
  3. Making the default configurable. You could have a setting that determines globally what Schema::TYPE_TEXT will be converted to in MySQL. You could default this to 'TEXT' for BC, but I think it would be a good idea to default it to 'MEDIUMTEXT' (maybe in Yii 2.1?).

I'm going to change the default to MEDIUMTEXT for my projects, overwriting the typeMap in mysql\QueryBuilder. Unfortunately, the query builder is not created using Yii::createObject, so I can't use DI to do this, and I'll have to extend both \yii\db\mysql\Schema and \yii\db\mysql\QueryBuilder, then overwrite the db connection's schemaMap (and I can't change just the mysql schema in the application configuration, I have to replace the whole thing). This is really inconvenient. Is there a better way to do this? I can use DI to replace the Schema at least.

nkovacs commented 9 years ago

I made a simple extension that does this: https://github.com/nkovacs/yii2-mysql

spiritdead commented 8 years ago

why this important feature is in the 2.1 and not for the 2.0.9 ? :/

thx

nkovacs commented 8 years ago

Because it probably breaks backwards compatibility, and such changes can only go into 2.1.

spiritdead commented 8 years ago

oh well :/ for now only work adding directly i will wait 2.1.x

Tigrov commented 1 month ago

Can be solved using $size argument for ColumnBuilder::text() method. Then generate db type depends on the size.

Tigrov commented 1 week ago

Checked, if size is specified, type will be created depending on size.

For example, use ColumnBuilder::text(100_000) for MEDUIMTEXT type

From the documentation: An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.

https://dev.mysql.com/doc/refman/8.4/en/string-type-syntax.html

Tigrov commented 1 week ago

Tests and doc will be added