search

doctrine / dbal

Doctrine Database Abstraction Layer
https://www.doctrine-project.org/projects/dbal.html
MIT License
9.44k stars 1.33k forks source link

MariaDB 10.3 Native JSON Support #3202

Open kiler129 opened 6 years ago

kiler129 commented 6 years ago

Feature Request

Q A
New Feature yes
RFC no
BC Break no

Summary

https://github.com/doctrine/dbal/commit/52b4692ebefd5a25420238aa53176ef8d4dc2e83 disabled JSON support for MariaDB 10.2, since the engine is based on 5.6.

MariaDB 10.3 doesn't have its own platform file, but I think it needs one now to enable native JSON storage.

WDYT?

morozov commented 6 years ago

I think it makes sense. If you're going to file a pull request, please make sure there's a functional test which creates and uses a JSON column. This way, we'll make sure that all platforms which declare JSON as supported actually support it.

ghost commented 6 years ago

with

doctrine/dbal v2.7.1

on

Ver 10.3.8-MariaDB-log for Linux on x86_64 (Source distribution)

I'm seeing similar issues to those previously reported (& solved) for MDB 10.2x here

https://github.com/doctrine/dbal/pull/2825

Particularly, the 'infinite schema updates' and failing

doctrine schema:validate

after a (reported) 'successful'

doctrine schema:update

Other than the common issue of 'MDB 10.3 support', not clear that this is the right place for this.

If so, and it's useful, I can add the details here on request.

Atm, I've no specific functional test, other than a new DB init on a symfony4 project install.

morozov commented 6 years ago

@hal869 please provide the details.

ghost commented 6 years ago

@morozov

On a new SF4 + phpcr project, after package installs,

starting with new/clean DBs

mysqlshow ... ``` mysqlshow testdb Database: testdb +--------+ | Tables | +--------+ +--------+ mysqlshow testdbphpcr Database: testdbphpcr +--------+ | Tables | +--------+ +--------+ ```

a phpcr DB init reports successful completion

    bin/console doctrine:phpcr:init:dbal --force
        Jackalope Doctrine DBAL tables have been initialized successfully.
mysqlshow ... ``` mysqlshow testdbphpcr Database: testdbphpcr +----------------------------+ | Tables | +----------------------------+ | phpcr_binarydata | | phpcr_internal_index_types | | phpcr_namespaces | | phpcr_nodes | | phpcr_nodes_references | | phpcr_nodes_weakreferences | | phpcr_type_childs | | phpcr_type_nodes | | phpcr_type_props | | phpcr_workspaces | +----------------------------+ ```

where

tail -f mariadb.log ``` 180704 16:36:31 35 Connect testuser@localhost as anonymous on testdbphpcr 35 Query SET NAMES utf8mb4 35 Query CREATE TABLE phpcr_namespaces (prefix VARCHAR(191) NOT NULL, uri VARCHAR(255) NOT NULL, PRIMARY KEY(prefix)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 35 Query CREATE TABLE phpcr_workspaces (name VARCHAR(191) NOT NULL, PRIMARY KEY(name)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 35 Query CREATE TABLE phpcr_nodes (id INT AUTO_INCREMENT NOT NULL, path VARCHAR(191) NOT NULL, parent VARCHAR(191) NOT NULL, local_name VARCHAR(191) NOT NULL, namespace VARCHAR(191) NOT NULL, workspace_name VARCHAR(191) NOT NULL, identifier VARCHAR(191) NOT NULL, type VARCHAR(191) NOT NULL, props LONGTEXT NOT NULL, numerical_props LONGTEXT DEFAULT NULL, depth INT NOT NULL, sort_order INT DEFAULT NULL, UNIQUE INDEX UNIQ_A4624AD7B548B0F1AC10DC4 (path, workspace_name), UNIQUE INDEX UNIQ_A4624AD7772E836A1AC10DC4 (identifier, workspace_name), INDEX IDX_A4624AD73D8E604F (parent), INDEX IDX_A4624AD78CDE5729 (type), INDEX IDX_A4624AD7623C14D533E16B56 (local_name, namespace), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 35 Query CREATE TABLE phpcr_internal_index_types (type VARCHAR(191) NOT NULL, node_id INT NOT NULL, PRIMARY KEY(type, node_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 35 Query CREATE TABLE phpcr_binarydata (id INT AUTO_INCREMENT NOT NULL, node_id INT NOT NULL, property_name VARCHAR(191) NOT NULL, workspace_name VARCHAR(191) NOT NULL, idx INT DEFAULT 0 NOT NULL, data LONGBLOB NOT NULL, UNIQUE INDEX UNIQ_37E65615460D9FD7413BC13C1AC10DC4E7087E10 (node_id, property_name, workspace_name, idx), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 180704 16:36:32 35 Query CREATE TABLE phpcr_nodes_references (source_id INT NOT NULL, source_property_name VARCHAR(191) NOT NULL, target_id INT NOT NULL, INDEX IDX_F3BF7E1158E0B66 (target_id), PRIMARY KEY(source_id, source_property_name, target_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 35 Query CREATE TABLE phpcr_nodes_weakreferences (source_id INT NOT NULL, source_property_name VARCHAR(191) NOT NULL, target_id INT NOT NULL, INDEX IDX_F0E4F6FA158E0B66 (target_id), PRIMARY KEY(source_id, source_property_name, target_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 35 Query CREATE TABLE phpcr_type_nodes (node_type_id INT AUTO_INCREMENT NOT NULL, name VARCHAR(191) NOT NULL, supertypes VARCHAR(255) NOT NULL, is_abstract TINYINT(1) NOT NULL, is_mixin TINYINT(1) NOT NULL, queryable TINYINT(1) NOT NULL, orderable_child_nodes TINYINT(1) NOT NULL, primary_item VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_34B0A8095E237E06 (name), PRIMARY KEY(node_type_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 35 Query CREATE TABLE phpcr_type_props (node_type_id INT NOT NULL, name VARCHAR(191) NOT NULL, protected TINYINT(1) NOT NULL, auto_created TINYINT(1) NOT NULL, mandatory TINYINT(1) NOT NULL, on_parent_version INT NOT NULL, multiple TINYINT(1) NOT NULL, fulltext_searchable TINYINT(1) NOT NULL, query_orderable TINYINT(1) NOT NULL, required_type INT NOT NULL, query_operators INT NOT NULL, default_value VARCHAR(255) DEFAULT NULL, PRIMARY KEY(node_type_id, name)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 35 Query CREATE TABLE phpcr_type_childs (node_type_id INT NOT NULL, name VARCHAR(255) NOT NULL, protected TINYINT(1) NOT NULL, auto_created TINYINT(1) NOT NULL, mandatory TINYINT(1) NOT NULL, on_parent_version INT NOT NULL, primary_types VARCHAR(255) NOT NULL, default_type VARCHAR(255) DEFAULT NULL) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 35 Quit ```

then a general migration diff

bin/console doctrine:migrations:diff
    Generated new migration class to "/srv/www/test1/src/Migrations/Version20180704234248.php" from schema differences.
tail -f mariadb.log ``` 180704 16:42:48 52 Connect testuser@localhost as anonymous on testdb 52 Query SET NAMES utf8mb4 52 Query SHOW FULL TABLES WHERE Table_type = 'BASE TABLE' 52 Quit ```

and the migrate

bin/console doctrine:migrations:migrate --no-interaction

                        Application Migrations                    

    Migrating up to 20180704234248 from 0

      ++ migrating 20180704234248

         -> CREATE TABLE media__gallery_media (id INT AUTO_INCREMENT NOT NULL, position INT NOT NULL, enabled TINYINT(1) NOT NULL, updated_at DATETIME NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
         -> CREATE TABLE media__media (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, description TEXT DEFAULT NULL, enabled TINYINT(1) NOT NULL, provider_name VARCHAR(255) NOT NULL, provider_status INT NOT NULL, provider_reference VARCHAR(255) NOT NULL, provider_metadata LONGTEXT DEFAULT NULL COMMENT '(DC2Type:json)', width INT DEFAULT NULL, height INT DEFAULT NULL, length NUMERIC(10, 0) DEFAULT NULL, content_type VARCHAR(255) DEFAULT NULL, content_size INT DEFAULT NULL, copyright VARCHAR(255) DEFAULT NULL, author_name VARCHAR(255) DEFAULT NULL, context VARCHAR(64) DEFAULT NULL, cdn_is_flushable TINYINT(1) DEFAULT NULL, cdn_flush_identifier VARCHAR(64) DEFAULT NULL, cdn_flush_at DATETIME DEFAULT NULL, cdn_status INT DEFAULT NULL, updated_at DATETIME NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
         -> CREATE TABLE media__gallery (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, context VARCHAR(64) NOT NULL, default_format VARCHAR(255) NOT NULL, enabled TINYINT(1) NOT NULL, updated_at DATETIME NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
         -> CREATE TABLE role (name VARCHAR(255) NOT NULL, PRIMARY KEY(name)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
         -> CREATE TABLE user_role (user_id INT NOT NULL COMMENT '(DC2Type:msgphp_user_id)', role_name VARCHAR(255) NOT NULL, INDEX IDX_2DE8C6A3A76ED395 (user_id), INDEX IDX_2DE8C6A3E09C0C92 (role_name), PRIMARY KEY(user_id, role_name)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
         -> CREATE TABLE user (id INT AUTO_INCREMENT NOT NULL COMMENT '(DC2Type:msgphp_user_id)', credential_email VARCHAR(255) NOT NULL, credential_password VARCHAR(255) NOT NULL, password_reset_token VARCHAR(255) DEFAULT NULL, password_requested_at DATETIME DEFAULT NULL, UNIQUE INDEX UNIQ_8D93D649A5D24B55 (credential_email), UNIQUE INDEX UNIQ_8D93D6496B7BA4B6 (password_reset_token), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
         -> ALTER TABLE user_role ADD CONSTRAINT FK_2DE8C6A3A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE
         -> ALTER TABLE user_role ADD CONSTRAINT FK_2DE8C6A3E09C0C92 FOREIGN KEY (role_name) REFERENCES role (name) ON DELETE CASCADE

      ++ migrated (1.63s)

      ------------------------

      ++ finished in 1.63s
      ++ 1 migrations executed
      ++ 8 sql queries
tail -f mariadb.log ``` 180704 16:43:42 53 Connect testuser@localhost as anonymous on testdb 53 Query SET NAMES utf8mb4 53 Query SHOW FULL TABLES WHERE Table_type = 'BASE TABLE' 53 Query CREATE TABLE migration_versions (version VARCHAR(255) NOT NULL, PRIMARY KEY(version)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB 53 Query SELECT version FROM migration_versions 53 Query SELECT version FROM migration_versions WHERE version IN ('20180704234248') ORDER BY version DESC LIMIT 1 53 Query SELECT version FROM migration_versions 53 Query START TRANSACTION 53 Query CREATE TABLE media__gallery_media (id INT AUTO_INCREMENT NOT NULL, position INT NOT NULL, enabled TINYINT(1) NOT NULL, updated_at DATETIME NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 53 Query CREATE TABLE media__media (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, description TEXT DEFAULT NULL, enabled TINYINT(1) NOT NULL, provider_name VARCHAR(255) NOT NULL, provider_status INT NOT NULL, provider_reference VARCHAR(255) NOT NULL, provider_metadata LONGTEXT DEFAULT NULL COMMENT '(DC2Type:json)', width INT DEFAULT NULL, height INT DEFAULT NULL, length NUMERIC(10, 0) DEFAULT NULL, content_type VARCHAR(255) DEFAULT NULL, content_size INT DEFAULT NULL, copyright VARCHAR(255) DEFAULT NULL, author_name VARCHAR(255) DEFAULT NULL, context VARCHAR(64) DEFAULT NULL, cdn_is_flushable TINYINT(1) DEFAULT NULL, cdn_flush_identifier VARCHAR(64) DEFAULT NULL, cdn_flush_at DATETIME DEFAULT NULL, cdn_status INT DEFAULT NULL, updated_at DATETIME NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 53 Query CREATE TABLE media__gallery (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, context VARCHAR(64) NOT NULL, default_format VARCHAR(255) NOT NULL, enabled TINYINT(1) NOT NULL, updated_at DATETIME NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 53 Query CREATE TABLE role (name VARCHAR(255) NOT NULL, PRIMARY KEY(name)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 53 Query CREATE TABLE user_role (user_id INT NOT NULL COMMENT '(DC2Type:msgphp_user_id)', role_name VARCHAR(255) NOT NULL, INDEX IDX_2DE8C6A3A76ED395 (user_id), INDEX IDX_2DE8C6A3E09C0C92 (role_name), PRIMARY KEY(user_id, role_name)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 53 Query CREATE TABLE user (id INT AUTO_INCREMENT NOT NULL COMMENT '(DC2Type:msgphp_user_id)', credential_email VARCHAR(255) NOT NULL, credential_password VARCHAR(255) NOT NULL, password_reset_token VARCHAR(255) DEFAULT NULL, password_requested_at DATETIME DEFAULT NULL, UNIQUE INDEX UNIQ_8D93D649A5D24B55 (credential_email), UNIQUE INDEX UNIQ_8D93D6496B7BA4B6 (password_reset_token), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB 53 Query ALTER TABLE user_role ADD CONSTRAINT FK_2DE8C6A3A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE 180704 16:43:43 53 Query ALTER TABLE user_role ADD CONSTRAINT FK_2DE8C6A3E09C0C92 FOREIGN KEY (role_name) REFERENCES role (name) ON DELETE CASCADE 53 Query INSERT INTO migration_versions (version) VALUES ('20180704234248') 53 Query COMMIT 53 Quit ```

next, validate fails

bin/console doctrine:schema:validate
    Mapping
    -------
     [OK] The mapping files are correct.

    Database
    --------
     [ERROR] The database schema is not in sync with the current mapping file.
tail -f mariadb.log ``` 180704 16:45:35 55 Connect testuser@localhost as anonymous on testdb 55 Query SET NAMES utf8mb4 55 Query SHOW FULL TABLES WHERE Table_type = 'BASE TABLE' 55 Query SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS Collation FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'media__gallery' 55 Query SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ FROM information_schema.key_column_usage k /*!50116 INNER JOIN information_schema.referential_constraints c ON c.constraint_name = k.constraint_name AND c.table_name = 'media__gallery' */ WHERE k.table_name = 'media__gallery' AND k.table_schema = 'testdb' /*!50116 AND c.constraint_schema = 'testdb' */ AND k.`REFERENCED_COLUMN_NAME` is not NULL 55 Query SELECT TABLE_NAME AS `Table`, NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, SEQ_IN_INDEX AS Seq_in_index, COLUMN_NAME AS Column_Name, COLLATION AS Collation, CARDINALITY AS Cardinality, SUB_PART AS Sub_Part, PACKED AS Packed, NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment FROM information_schema.STATISTICS WHERE TABLE_NAME = 'media__gallery' AND TABLE_SCHEMA = 'testdb' 55 Query SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS Collation FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'media__gallery_media' 55 Query SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ FROM information_schema.key_column_usage k /*!50116 INNER JOIN information_schema.referential_constraints c ON c.constraint_name = k.constraint_name AND c.table_name = 'media__gallery_media' */ WHERE k.table_name = 'media__gallery_media' AND k.table_schema = 'testdb' /*!50116 AND c.constraint_schema = 'testdb' */ AND k.`REFERENCED_COLUMN_NAME` is not NULL 55 Query SELECT TABLE_NAME AS `Table`, NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, SEQ_IN_INDEX AS Seq_in_index, COLUMN_NAME AS Column_Name, COLLATION AS Collation, CARDINALITY AS Cardinality, SUB_PART AS Sub_Part, PACKED AS Packed, NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment FROM information_schema.STATISTICS WHERE TABLE_NAME = 'media__gallery_media' AND TABLE_SCHEMA = 'testdb' 55 Query SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS Collation FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'media__media' 55 Query SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ FROM information_schema.key_column_usage k /*!50116 INNER JOIN information_schema.referential_constraints c ON c.constraint_name = k.constraint_name AND c.table_name = 'media__media' */ WHERE k.table_name = 'media__media' AND k.table_schema = 'testdb' /*!50116 AND c.constraint_schema = 'testdb' */ AND k.`REFERENCED_COLUMN_NAME` is not NULL 55 Query SELECT TABLE_NAME AS `Table`, NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, SEQ_IN_INDEX AS Seq_in_index, COLUMN_NAME AS Column_Name, COLLATION AS Collation, CARDINALITY AS Cardinality, SUB_PART AS Sub_Part, PACKED AS Packed, NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment FROM information_schema.STATISTICS WHERE TABLE_NAME = 'media__media' AND TABLE_SCHEMA = 'testdb' 55 Query SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS Collation FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'migration_versions' 55 Query SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ FROM information_schema.key_column_usage k /*!50116 INNER JOIN information_schema.referential_constraints c ON c.constraint_name = k.constraint_name AND c.table_name = 'migration_versions' */ WHERE k.table_name = 'migration_versions' AND k.table_schema = 'testdb' /*!50116 AND c.constraint_schema = 'testdb' */ AND k.`REFERENCED_COLUMN_NAME` is not NULL 55 Query SELECT TABLE_NAME AS `Table`, NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, SEQ_IN_INDEX AS Seq_in_index, COLUMN_NAME AS Column_Name, COLLATION AS Collation, CARDINALITY AS Cardinality, SUB_PART AS Sub_Part, PACKED AS Packed, NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment FROM information_schema.STATISTICS WHERE TABLE_NAME = 'migration_versions' AND TABLE_SCHEMA = 'testdb' 55 Query SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS Collation FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'role' 55 Query SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ FROM information_schema.key_column_usage k /*!50116 INNER JOIN information_schema.referential_constraints c ON c.constraint_name = k.constraint_name AND c.table_name = 'role' */ WHERE k.table_name = 'role' AND k.table_schema = 'testdb' /*!50116 AND c.constraint_schema = 'testdb' */ AND k.`REFERENCED_COLUMN_NAME` is not NULL 55 Query SELECT TABLE_NAME AS `Table`, NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, SEQ_IN_INDEX AS Seq_in_index, COLUMN_NAME AS Column_Name, COLLATION AS Collation, CARDINALITY AS Cardinality, SUB_PART AS Sub_Part, PACKED AS Packed, NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment FROM information_schema.STATISTICS WHERE TABLE_NAME = 'role' AND TABLE_SCHEMA = 'testdb' 55 Query SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS Collation FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'user' 55 Query SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ FROM information_schema.key_column_usage k /*!50116 INNER JOIN information_schema.referential_constraints c ON c.constraint_name = k.constraint_name AND c.table_name = 'user' */ WHERE k.table_name = 'user' AND k.table_schema = 'testdb' /*!50116 AND c.constraint_schema = 'testdb' */ AND k.`REFERENCED_COLUMN_NAME` is not NULL 55 Query SELECT TABLE_NAME AS `Table`, NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, SEQ_IN_INDEX AS Seq_in_index, COLUMN_NAME AS Column_Name, COLLATION AS Collation, CARDINALITY AS Cardinality, SUB_PART AS Sub_Part, PACKED AS Packed, NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment FROM information_schema.STATISTICS WHERE TABLE_NAME = 'user' AND TABLE_SCHEMA = 'testdb' 55 Query SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS Collation FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'user_role' 55 Query SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ FROM information_schema.key_column_usage k /*!50116 INNER JOIN information_schema.referential_constraints c ON c.constraint_name = k.constraint_name AND c.table_name = 'user_role' */ WHERE k.table_name = 'user_role' AND k.table_schema = 'testdb' /*!50116 AND c.constraint_schema = 'testdb' */ AND k.`REFERENCED_COLUMN_NAME` is not NULL 55 Query SELECT TABLE_NAME AS `Table`, NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, SEQ_IN_INDEX AS Seq_in_index, COLUMN_NAME AS Column_Name, COLLATION AS Collation, CARDINALITY AS Cardinality, SUB_PART AS Sub_Part, PACKED AS Packed, NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment FROM information_schema.STATISTICS WHERE TABLE_NAME = 'user_role' AND TABLE_SCHEMA = 'testdb' 55 Quit ```

and checking for the issue

bin/console doctrine:schema:update --dump-sql
 The following SQL statements will be executed:
     ALTER TABLE media__media CHANGE provider_metadata provider_metadata LONGTEXT DEFAULT NULL COMMENT '(DC2Type:json)', CHANGE width width INT DEFAULT NULL, CHANGE height height INT DEFAULT NULL, CHANGE length length NUMERIC(10, 0) DEFAULT NULL, CHANGE content_type content_type VARCHAR(255) DEFAULT NULL, CHANGE content_size content_size INT DEFAULT NULL, CHANGE copyright copyright VARCHAR(255) DEFAULT NULL, CHANGE author_name author_name VARCHAR(255) DEFAULT NULL, CHANGE context context VARCHAR(64) DEFAULT NULL, CHANGE cdn_is_flushable cdn_is_flushable TINYINT(1) DEFAULT NULL, CHANGE cdn_flush_identifier cdn_flush_identifier VARCHAR(64) DEFAULT NULL, CHANGE cdn_flush_at cdn_flush_at DATETIME DEFAULT NULL, CHANGE cdn_status cdn_status INT DEFAULT NULL;
     ALTER TABLE user CHANGE password_reset_token password_reset_token VARCHAR(255) DEFAULT NULL, CHANGE password_requested_at password_requested_at DATETIME DEFAULT NULL;

bin/console doctrine:schema:update --force
     Updating database schema...
         2 queries were executed
     [OK] Database schema updated successfully!

bin/console doctrine:schema:update --dump-sql
 The following SQL statements will be executed:

     ALTER TABLE media__media CHANGE provider_metadata provider_metadata LONGTEXT DEFAULT NULL COMMENT '(DC2Type:json)', CHANGE width width INT DEFAULT NULL, CHANGE height height INT DEFAULT NULL, CHANGE length length NUMERIC(10, 0) DEFAULT NULL, CHANGE content_type content_type VARCHAR(255) DEFAULT NULL, CHANGE content_size content_size INT DEFAULT NULL, CHANGE copyright copyright VARCHAR(255) DEFAULT NULL, CHANGE author_name author_name VARCHAR(255) DEFAULT NULL, CHANGE context context VARCHAR(64) DEFAULT NULL, CHANGE cdn_is_flushable cdn_is_flushable TINYINT(1) DEFAULT NULL, CHANGE cdn_flush_identifier cdn_flush_identifier VARCHAR(64) DEFAULT NULL, CHANGE cdn_flush_at cdn_flush_at DATETIME DEFAULT NULL, CHANGE cdn_status cdn_status INT DEFAULT NULL;
     ALTER TABLE user CHANGE password_reset_token password_reset_token VARCHAR(255) DEFAULT NULL, CHANGE password_requested_at password_requested_at DATETIME DEFAULT NULL;

bin/console doctrine:schema:validate
    Mapping
    -------
     [OK] The mapping files are correct.

    Database
    --------
     [ERROR] The database schema is not in sync with the current mapping file.

ad infinitum ...

lcobucci commented 6 years ago

@hal869 and what's the entity mapping? Also, as we always ask, could you try to isolate the issue in a test case?

ghost commented 6 years ago

@lcobucci After our offline chat, working on test case, came across this:

https://github.com/doctrine/dbal/issues/2985

and related ...

Updated to latest dbal/symfony/mariadb, and explicitly removed the

config/packages/doctrine.yaml
    ....
-   server_version: 'mariadb-10.3.8'
    ....

letting the version returned from mariadb get used.

which seems to do the trick for the :validate,

bin/console doctrine:schema:validate
    Mapping
    -------
     [OK] The mapping files are correct.

    Database
    --------
     [OK] The database schema is in sync with the mapping files.

now, to look further downstream ...

sidz commented 6 years ago

@morozov as part of my PR I started to investigate how the MariaDB platform can be improved and faced that MariaDB >= 10.3 still doesn't have native JSON support and it convert this type into LONGTEXT by default.

So the previous PR (https://github.com/doctrine/dbal/pull/2825) should be compatible with 10.3 version as well.

stephanvierkant commented 4 years ago

What's the status of this feature? What has to be done in order to get native JSON support for MariaDB?

I've tried to test it by creating a platform class (by extending \Doctrine\DBAL\Platforms\MySqlPlatform, but I see that MariaDb1027Platform is final, has no interface and is used \Doctrine\DBAL\Schema\MySqlSchemaManager::_getPortableTableColumnDefinition using a private method. Doesn't seem like a good practice, isn't it? Or am I wrong?

I've never contributed to Doctrine before, but let me know if I can help.

morozov commented 4 years ago

What's the status of this feature? What has to be done in order to get native JSON support for MariaDB?

@stephanvierkant all you can see here is the status. There's nothing happening in this regard behind the curtain.

MariaDb1027Platform is final […] Doesn't seem like a good practice, isn't it? Or am I wrong?

It looks like an exception. All other platform classes are not final because they don't have an interface that developers could implement instead of extending the class. Although, in this case, it forces you to contribute the change upstream instead of keeping it to yourself which could be seen as a positive side effect for the community.

I've never contributed to Doctrine before, but let me know if I can help.

Please submit a pull request.

spackmat commented 4 years ago

As stated in the MariaDB documentation, the JSON-alias leads to an automatic JSON_VALID-check from version 10.4.3 on: https://mariadb.com/kb/en/json-data-type/

So maybe that behavior could be kept for later MariaDB versions by using this alias? (I'm on 10.3 and setting the JSON-alias always leads to LONGTEXT columns, how is that handled in 10.4.3 onwards?)

alexander-schranz commented 2 years ago

I did stumble over this today. As I think it could be a bc break changing:

https://github.com/doctrine/dbal/blob/ebd986b6e4f205b54cc27f5d5ba0323d84fa29b1/src/Platforms/MariaDb1027Platform.php#L20-L23

from LONGTEXT to JSON I think maybe it would be possible to add an additional options to teh DSN e.g.: &nativeJson=true or something and then use JSON instead of LONGTEXT.

alexander-schranz commented 2 years ago

Seems like this was changed in https://github.com/doctrine/dbal/pull/2825 but I'm not understanding why not let MariaDB handle this?

greg0ire commented 2 years ago

@alexander-schranz I think a platform option would be more appropriate. See for instance https://github.com/doctrine/dbal/blob/2aec3f8da5f601462162d9b90d013554b240292b/src/Platforms/PostgreSQLPlatform.php#L1217-L1224

Seems like this was changed in #2825 but I'm not understanding why not let MariaDB handle this?

Maybe it was simply overlooked?

alexander-schranz commented 2 years ago

Maybe it was simply overlooked?

Not sure 🙈. @lcobucci Do you remember why the JSON type was changed to TEXT again in: https://github.com/doctrine/dbal/pull/2825#issue-253126409?

lcobucci commented 2 years ago

Maybe it was simply overlooked?

Not sure 🙈. @lcobucci Do you remember why the JSON type was changed to TEXT again in: #2825 (comment)?

It's been a while but it was to reflect the reality (JSON is just an alias): https://mariadb.com/kb/en/json-data-type/ I think that tests were failing and we removed things from the PR to unblock things. But, again, it's been a while.

I'd advise to send a PR proposing a fix and see what happens on the suite now.

morozov commented 2 years ago

To @lcobucci's point, given the behavior documented in https://github.com/doctrine/dbal/pull/5100#discussion_r764557358, I'd say that MariaDB 10.3 does not natively support JSON. And assuming their reasoning:

as the JSON data type contradicts the SQL standard,

It won't support it in the near future.

There is a possibility to improve JSON support in MariaDB by adding CHECK (JSON_VALID()) to the column and introspecting it. This may work.

alexander-schranz commented 2 years ago

@morozov they support it in there way by having a JSON alias to LONGTEXT and JSON fields automatically gets a CHECK (JSON_VALID(j)) to them. So I think doctrine/dbal should use the JSON here and not map it themselves to LONGTEXT which would not add the JSON_VALID check. So I think we should define it just as JSON here in dbal and let MariaDB do they work for us.

morozov commented 2 years ago

JSON fields automatically gets a CHECK (JSON_VALID(j)) to them.

It doesn't look like they do, nor that it's stated in the documentation:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.5-10.3.32-MariaDB-1:10.3.32+maria~focal mariadb.org binary distribution

mysql> CREATE TABLE t (j JSON);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t VALUES('foo');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| j    |
+------+
| foo  |
+------+
1 row in set (0.01 sec)

The DBAL would have to add this check automatically and then check if it exists during the introspection.

tigitz commented 2 years ago

@morozov it says in the reasoning you linked that this behavior is supported since version 10.4.3 and it looks like you used 10.3 in the provided logs

morozov commented 2 years ago

Yeah, it looks like it adds the constraint automatically on the newer versions:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.5-10.6.5-MariaDB-1:10.6.5+maria~focal mariadb.org binary distribution

mysql> CREATE TABLE t (j JSON);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t VALUES('foo');
ERROR 4025 (23000): CONSTRAINT `t.j` failed for `doctrine`.`t`

mysql> SHOW CREATE TABLE t;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `j` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`j`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)