dbal MariaDB 10.3 Native JSON Support

MariaDB 10.3 Native JSON Support

Open kiler129 opened this issue 6 years ago • 20 comments

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?

Jun 29 '18 22:06 kiler129

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.

Jun 29 '18 22:06 morozov

with

doctrine/dbal v2.7.1

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.

Jul 04 '18 22:07 ghost

@hal869 please provide the details.

Jul 04 '18 23:07 morozov

@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 ...

Jul 05 '18 00:07 ghost

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

Sep 05 '18 18:09 lcobucci

@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 ...

Sep 05 '18 21:09 ghost

@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.

Sep 11 '18 20:09 sidz

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.

Apr 10 '20 11:04 stephanvierkant

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.

Apr 10 '20 16:04 morozov

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?)

Apr 28 '20 08:04 spackmat

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.

Nov 29 '21 18:11 alexander-schranz

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

Nov 30 '21 11:11 alexander-schranz

@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?

Dec 06 '21 20:12 greg0ire

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?

Dec 07 '21 10:12 alexander-schranz

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.

Dec 07 '21 10:12 lcobucci

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.

Dec 10 '21 17:12 morozov

@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.

Dec 10 '21 17:12 alexander-schranz

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.

Dec 11 '21 01:12 morozov

@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

Feb 01 '22 19:02 tigitz

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)

Feb 01 '22 20:02 morozov

dbal dbal copied to clipboard

MariaDB 10.3 Native JSON Support

Feature Request

Summary

dbal
dbal copied to clipboard