twisted1919
commented
3 years ago Please hold with this, i think i know what is going on.
Open twisted1919 opened 3 years ago
twisted1919
commented
3 years ago Please hold with this, i think i know what is going on.
twisted1919
commented
3 years ago Okay, so it's the database engine...
create table testing_json ( the_content JSON );show create table testing_json
# result
CREATE TABLE `testing_json` (
`the_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1So MariaDB silently fails to create the JSON column and creates a longtext instead.
but wait, there is more. If you read the docs at https://mariadb.com/kb/en/json-data-type/ they say:
JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent.So while they implement all the functionality for json, their data type is actually a longtext.
This is why Yii cannot detect the right the column type, after all, for Yii this is a longtext column which only accepts text. This is also the reason why when I pass an array to the property it errors out with the array to string conversion error.
What are our options here?
Could we wrap our values into \yii\db\JsonExpression maybe? Or what options do we have, except changing the database engine from mariadb to mysql?
twisted1919
commented
3 years ago Additionally, please beware that the casting done by yii\db\mysql\JsonExpressionBuilder::build will fail for mariadb as the JSON data type does not exists.
samdark
commented
3 years ago twisted1919
commented
3 years ago @samdark - Thanks, i didn't know about that link.
Since I don't actually need to search the json right now, but just store it, I will use the longtext option for now, so from my point of view, you can close this issue, but I think I raised a good problem that should be covered by the framework and documented.
samdark
commented
3 years ago I think we can explicitly link to yii2-mariadb in DB drivers list.
st0at
commented
3 years ago @twisted1919 for now for such problem I used such solution:
class JsonModel extends ActiveRecord
{
public function beforeSave($insert)
{
$this->json_field = json_encode($this->json_field);
return parent::beforeSave($insert);
}
public function afterFind()
{
$this->json_field = json_decode($this->json_field);
parent::afterFind();
}
}
What steps will reproduce the problem?
Install MariaDB 10.3.17, and create a json column, then save a PaymentIntent (https://github.com/stripe/stripe-php/blob/master/lib/PaymentIntent.php ) object into it.
What is the expected result?
What do you get instead?
Additional info
On our local environment, using MySQL Server version: 8.0.19, the behavior is correct, we get the right value saved in the database column.
What is more interesting, is that if we try to save an actual array, i.e by explicitly calling
PaymentIntent::toArraythen Yii errors out with:The documentation at https://www.yiiframework.com/doc/guide/2.0/en/db-active-record is not really clear on how exactly we should save json data into a json column, shouldn't it accept an array/object/etc and then just json_encode it when it saves it? Because if that's the case, it isn't working correctly.
I also tried passing a json encoded string, but this resulted into escaped json saved into the database, which at this point i don't know if it's caused by Yii or by the database engine.