MariaDB 10.3 Native JSON Support #3202

kiler129 · 2018-06-29T22:11:47Z

Feature Request

Q	A
New Feature	yes
RFC	no
BC Break	no

Summary

52b4692 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?

The text was updated successfully, but these errors were encountered:

morozov · 2018-06-29T22:44:11Z

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 · 2018-07-04T22:15:19Z

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 · 2018-07-04T23:26:11Z

@hal869 please provide the details.

ghost · 2018-07-05T00:08:29Z

@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 · 2018-09-05T18:47:02Z

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

ghost · 2018-09-05T21:02:07Z

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

#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 · 2018-09-11T20:57:33Z

@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 (#2825) should be compatible with 10.3 version as well.

stephanvierkant · 2020-04-10T11:30:04Z

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 · 2020-04-10T16:10:50Z

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 · 2020-04-28T08:18:26Z

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 · 2021-11-29T18:59:45Z

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

dbal/src/Platforms/MariaDb1027Platform.php

Lines 20 to 23 in ebd986b

    
           public function getJsonTypeDeclarationSQL(array $column): string 
        
           { 
        
               return 'LONGTEXT'; 
        
           }

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 · 2021-11-30T11:56:27Z

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

greg0ire · 2021-12-06T20:55:26Z

@alexander-schranz I think a platform option would be more appropriate. See for instance

dbal/src/Platforms/PostgreSQLPlatform.php

Lines 1217 to 1224 in 2aec3f8

    
           public function getJsonTypeDeclarationSQL(array $column) 
        
           { 
        
               if (! empty($column['jsonb'])) { 
        
                   return 'JSONB'; 
        
               } 
        
               return 'JSON'; 
        
           }

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 · 2021-12-07T10:24:20Z

Maybe it was simply overlooked?

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

lcobucci · 2021-12-07T10:48:58Z

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 · 2021-12-10T17:07:29Z

To @lcobucci's point, given the behavior documented in #5100 (comment), 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 · 2021-12-10T17:10:39Z

@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 · 2021-12-11T01:34:04Z

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 · 2022-02-01T19:18:33Z

@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 · 2022-02-01T20:49:08Z

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)

MariaDb aliases columns specified as JSON to LONGTEXT. Since version 10.4.3, MariaDb adds a CHECK constraint to JSON columns to ensure they contain valid JSON and sets the column collation to utf8mb4_bin. Simply setting JSON as the column type in the platform results in introspection failures as the database reports LONGTEXT (not JSON) with changed collation. Therefore, inverse the aliasing where the relevant check constraint exists and ignore collation changes for JSON columns in the relevant database versions. The modified methods are MariaDBPlatform->getListTableColumnsSQL() and MySQLSchemaManager->selectTableColumns() The changes have been implemented in the MariaDBPlatform (guarded by a check for JSON as column type so they do not affect MariaDB1027Platform) and MySQLSchemaManager (guarded by a platform test). It ought to be possible to push most of the platform changes into the MySQLPlatform given the SQL query changes are guarded by MariaDB specific executable comments and so should be a no op for MySQL. Doing so would allow getDatabaseNameSQL() to be kept private. However, because the changes are entirely MariaDb specific they have been made in the MariaDBPlatform (this is also consistent with the plan to separate MariaDb and MySQL platforms). MariaDb1043Platform extends MariaDb1027Platform rather than MariaDBPlatform to ensure the platform checks in MySQLSchemaManager (based on MariaDb1027Platform) continue to work as expected. The alternative approach of adding the CHECK constraint to the SQL for the column is not robust as MariaDb syntax seems to require the CHECK constraint to be the final part of the column declaration and it was not obvious how to guarantee this (specifically, appending the CHECK constraint to the output of getJsonTypeDeclarationSQL() did not work). New public methods: MariaDBPlatform->getInverseJsonToLongtextAliasSQL() generates SQL snippets to inverse the JSON to LONGTEXT aliasing for JSON-specified columns. It is marked public because it is used in MySQLSchemaManager. Overridden methods: MariaDb1043Platform->getJsonTypeDeclarationSQL(). To return JSON. MariaDBPlatform->getListTableColumnsSQL(). To reverse the JSON to LONGTEXT aliasing carried out by MariaDb. MariaDBPlatform->MariaDBColumnToArray(). To unset collation and charset in column array (so parent needed to be marked protected). New test cases: 1. Types/JsonTest. Test storage and retrieval of json data. 2. Platforms/MariaDb1043PlatformTest. A clone of MariaDb1027Platform test. 3. Schema/MySQLSchemaManagerTest->testColumnIntrospection(). Ensures introspected table matches original table. Tests all doctrine types not just json. Based on failure highlighted in pull request doctrine#5100. For previous discussion on this topic, see: doctrine#5100 doctrine#3202 Further background at: https://mariadb.com/kb/en/json-data-type/ https://mariadb.com/kb/en/information-schema-check_constraints-table/ https://jira.mariadb.org/browse/MDEV-13916

MariaDb aliases columns specified as JSON to LONGTEXT. Since version 10.4.3, MariaDb adds a CHECK constraint to JSON columns to ensure they contain valid JSON and sets the column collation to utf8mb4_bin. Simply setting JSON as the column type in the platform results in introspection failures as the database reports LONGTEXT (not JSON) with changed collation. Therefore, inverse the aliasing where the relevant check constraint exists and ignore collation changes for JSON columns in the relevant database versions. The modified methods are MariaDBPlatform->getListTableColumnsSQL() and MySQLSchemaManager->selectTableColumns() The changes have been implemented in the MariaDBPlatform (guarded by a check for JSON as column type so they do not affect MariaDB1027Platform) and MySQLSchemaManager (guarded by a platform test). It ought to be possible to push most of the platform changes into the MySQLPlatform given the SQL query changes are guarded by MariaDB specific executable comments and so should be a no op for MySQL. Doing so would allow getDatabaseNameSQL() to be kept private. However, because the changes are entirely MariaDb specific they have been made in the MariaDBPlatform (this is also consistent with the plan to separate MariaDb and MySQL platforms). MariaDb1043Platform extends MariaDb1027Platform rather than MariaDBPlatform to ensure the platform checks in MySQLSchemaManager (based on MariaDb1027Platform) continue to work as expected. The alternative approach of adding the CHECK constraint to the SQL for the column is not robust as MariaDb syntax seems to require the CHECK constraint to be the final part of the column declaration and it was not obvious how to guarantee this (specifically, appending the CHECK constraint to the output of getJsonTypeDeclarationSQL() did not work). New public methods: MariaDBPlatform->getInverseJsonToLongtextAliasSQL() generates SQL snippets to inverse the JSON to LONGTEXT aliasing for JSON-specified columns. It is marked public because it is used in MySQLSchemaManager. Overridden methods: MariaDb1043Platform->getJsonTypeDeclarationSQL(). To return JSON. MariaDBPlatform->getListTableColumnsSQL(). To reverse the JSON to LONGTEXT aliasing carried out by MariaDb. MariaDBPlatform->MariaDBColumnToArray(). To unset collation and charset in column array (so parent needed to be marked protected). New test cases: 1. Types/JsonTest. Test storage and retrieval of json data. 2. Platforms/MariaDb1043PlatformTest. A clone of MariaDb1027Platform test. 3. Schema/MySQLSchemaManagerTest->testColumnIntrospection(). Ensures introspected table matches original table. Tests all doctrine types not just json. Based on failure highlighted in pull request doctrine#5100. For previous discussion on this topic, see: doctrine#5100 doctrine#3202 Further background at: https://mariadb.com/kb/en/json-data-type/ https://mariadb.com/kb/en/information-schema-check_constraints-table/ https://jira.mariadb.org/browse/MDEV-13916 v2 of JsonTest.php: 1. Simplify array shapes of json structures. 2. Deal with json returned as a stream not string. It seems Oracle 11 returns json as a stream so convert streams to strings.

MariaDb aliases columns specified as JSON to LONGTEXT. Since version 10.4.3, MariaDb adds a CHECK constraint to JSON columns to ensure they contain valid JSON and sets the column collation to utf8mb4_bin. Simply setting JSON as the column type in the platform results in introspection failures as the database reports LONGTEXT (not JSON) with changed collation. Therefore, inverse the aliasing where the relevant check constraint exists and ignore collation changes for JSON columns in the relevant database versions. The modified methods are MariaDBPlatform->getListTableColumnsSQL() and MySQLSchemaManager->selectTableColumns() The changes have been implemented in the MariaDBPlatform (guarded by a check for JSON as column type so they do not affect MariaDB1027Platform) and MySQLSchemaManager (guarded by a platform test). It ought to be possible to push most of the platform changes into the MySQLPlatform given the SQL query changes are guarded by MariaDB specific executable comments and so should be a no op for MySQL. Doing so would allow getDatabaseNameSQL() to be kept private. However, because the changes are entirely MariaDb specific they have been made in the MariaDBPlatform (this is also consistent with the plan to separate MariaDb and MySQL platforms). MariaDb1043Platform extends MariaDb1027Platform rather than MariaDBPlatform to ensure the platform checks in MySQLSchemaManager (based on MariaDb1027Platform) continue to work as expected. The alternative approach of adding the CHECK constraint to the SQL for the column is not robust as MariaDb syntax seems to require the CHECK constraint to be the final part of the column declaration and it was not obvious how to guarantee this (specifically, appending the CHECK constraint to the output of getJsonTypeDeclarationSQL() did not work). New public methods: MariaDBPlatform->getInverseJsonToLongtextAliasSQL() generates SQL snippets to inverse the JSON to LONGTEXT aliasing for JSON-specified columns. It is marked public because it is used in MySQLSchemaManager. Overridden methods: MariaDb1043Platform->getJsonTypeDeclarationSQL(). To return JSON. MariaDBPlatform->getListTableColumnsSQL(). To reverse the JSON to LONGTEXT aliasing carried out by MariaDb. MariaDBPlatform->MariaDBColumnToArray(). To unset collation and charset in column array (so parent needed to be marked protected). New test cases: 1. Types/JsonTest. Test storage and retrieval of json data. 2. Platforms/MariaDb1043PlatformTest. A clone of MariaDb1027Platform test. 3. Schema/MySQLSchemaManagerTest->testColumnIntrospection(). Ensures introspected table matches original table. Tests all doctrine types not just json. Based on failure highlighted in pull request doctrine#5100. For previous discussion on this topic, see: doctrine#5100 doctrine#3202 Further background at: https://mariadb.com/kb/en/json-data-type/ https://mariadb.com/kb/en/information-schema-check_constraints-table/ https://jira.mariadb.org/browse/MDEV-13916 v2 JsonTest.php: 1. Simplify array shapes of json structures. 2. Deal with json returned as a stream not string. It seems Oracle 11 returns json as a stream so convert streams to strings. v3 JsonTest.php: 1. 'json_table' is a reserved word in MySQL (and a function name in Oracle SQL). Use json_test_table instead. 2. Some databases e.g. MySQL reorder json arrays so the insert order will not necessarily match the select order. Resort the arrays before testing for equality. MySQLSchemaManager.php: Remove 'instanceof MariaDb1027Platform' test as follows: a. Add getColumnTypeSQLSnippets() method to AbstractMySQLPlatform which returns the default snippets for all databases except MariaDb. b. Override the method in MariDBPlatform to return the SQL snippets which inverse the JSON to LONGTEXT aliasing. Note that the getColumnTypeSQLSnippets() method in MariaDBPlatform was previously called getInverseJsonToLongtextAliasSQL() and was renamed to make more sense for MySQL platforms in general.

MariaDb aliases columns specified as JSON to LONGTEXT. Since version 10.4.3, MariaDb adds a CHECK constraint to JSON columns to ensure they contain valid JSON and sets the column collation to utf8mb4_bin. Simply setting JSON as the column type in the platform results in introspection failures as the database reports LONGTEXT (not JSON) with changed collation. Therefore, inverse the aliasing where the relevant check constraint exists and ignore collation changes for JSON columns in the relevant database versions. The main functional changes are in: - MariaDB1043Platform->getListTableColumnsSQL() and - MySQLSchemaManager->selectTableColumns() MariaDb1043Platform extends MariaDb1027Platform rather than MariaDBPlatform to ensure the platform checks in MySQLSchemaManager (based on MariaDb1027Platform) continue to work as expected. The alternative approach of adding the CHECK constraint to the SQL for the column is not robust as MariaDb syntax seems to require the CHECK constraint to be the final part of the column declaration and it was not obvious how to guarantee this (specifically, appending the CHECK constraint to the output of getJsonTypeDeclarationSQL() did not work). New public methods: MariaDBPlatform->getColumnTypeSQLSnippets() generates SQL snippets to reverse the JSON to LONGTEXT aliasing for JSON-specified columns. It is also used in MySQLSchemaManager. It still checks that json columns are JSON data type so that switching the getJsonTypeDeclarationSQL to LONGTEXT is all that is necessary to revert to old behaviour which is helpful for testing. Overridden methods in MariaDb1043Platform: MariaDb1043Platform->getJsonTypeDeclarationSQL(). To return JSON. MariaDBPlatform->getListTableColumnsSQL(). To reverse the JSON to LONGTEXT aliasing carried out by MariaDb. MariaDBPlatform->getColumnDeclarationSQL(). To unset collation and charset (used by the comparator). New test cases: 1. Types/JsonTest. Test storage and retrieval of json data. 2. Platforms/MariaDb1043PlatformTest. A clone of MariaDb1027Platform test. 3. Schema/MySQLSchemaManagerTest->testColumnIntrospection(). Ensures introspected table matches original table. Tests all doctrine types not just json. Based on failure highlighted in pull request doctrine#5100. For previous discussion on this topic, see: doctrine#5100 doctrine#3202 Further background at: https://mariadb.com/kb/en/json-data-type/ https://mariadb.com/kb/en/information-schema-check_constraints-table/ https://jira.mariadb.org/browse/MDEV-13916 Notes regarding JsonTest.php: 1. Some platforms return json as a stream so convert streams to strings. 2. 'json_table' is a reserved word in MySQL (and a function name in Oracle SQL). Use json_test_table instead. 3. Some platforms reorder json arrays so the insert order will not necessarily match the select order. Resort the arrays before testing for equality.

morozov added Improvement MariaDB Platforms Type Mapping labels Jun 29, 2018

javiereguiluz mentioned this issue Sep 6, 2018

Add support for MariaDB 10.3 #3278

Closed

sidz mentioned this issue Sep 10, 2018

MariaDB improvements, support 10.3 #3283

Merged

3 tasks

alexander-schranz mentioned this issue Dec 7, 2021

Use Native JSON type instead of LONGTEXT for MariaDB #5100

Closed

morozov mentioned this issue Dec 12, 2021

MariaDB 10.2 extended support (meta) #2923

Closed

4 tasks

cgknx mentioned this issue Feb 9, 2023

Use Native JSON type instead of LONGTEXT for MariaDB #5916

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MariaDB 10.3 Native JSON Support #3202

MariaDB 10.3 Native JSON Support #3202

kiler129 commented Jun 29, 2018

morozov commented Jun 29, 2018

ghost commented Jul 4, 2018

morozov commented Jul 4, 2018

ghost commented Jul 5, 2018

lcobucci commented Sep 5, 2018 •

edited

Loading

ghost commented Sep 5, 2018 •

edited by ghost

Loading

sidz commented Sep 11, 2018

stephanvierkant commented Apr 10, 2020 •

edited

Loading

morozov commented Apr 10, 2020 •

edited

Loading

spackmat commented Apr 28, 2020

alexander-schranz commented Nov 29, 2021 •

edited

Loading

alexander-schranz commented Nov 30, 2021

greg0ire commented Dec 6, 2021 •

edited

Loading

alexander-schranz commented Dec 7, 2021

lcobucci commented Dec 7, 2021

morozov commented Dec 10, 2021 •

edited

Loading

alexander-schranz commented Dec 10, 2021 •

edited

Loading

morozov commented Dec 11, 2021 •

edited

Loading

tigitz commented Feb 1, 2022 •

edited

Loading

morozov commented Feb 1, 2022

MariaDB 10.3 Native JSON Support #3202

MariaDB 10.3 Native JSON Support #3202

Comments

kiler129 commented Jun 29, 2018

Feature Request

Summary

morozov commented Jun 29, 2018

ghost commented Jul 4, 2018

morozov commented Jul 4, 2018

ghost commented Jul 5, 2018

lcobucci commented Sep 5, 2018 • edited Loading

ghost commented Sep 5, 2018 • edited by ghost Loading

sidz commented Sep 11, 2018

stephanvierkant commented Apr 10, 2020 • edited Loading

morozov commented Apr 10, 2020 • edited Loading

spackmat commented Apr 28, 2020

alexander-schranz commented Nov 29, 2021 • edited Loading

alexander-schranz commented Nov 30, 2021

greg0ire commented Dec 6, 2021 • edited Loading

alexander-schranz commented Dec 7, 2021

lcobucci commented Dec 7, 2021

morozov commented Dec 10, 2021 • edited Loading

alexander-schranz commented Dec 10, 2021 • edited Loading

morozov commented Dec 11, 2021 • edited Loading

tigitz commented Feb 1, 2022 • edited Loading

morozov commented Feb 1, 2022

lcobucci commented Sep 5, 2018 •

edited

Loading

ghost commented Sep 5, 2018 •

edited by ghost

Loading

stephanvierkant commented Apr 10, 2020 •

edited

Loading

morozov commented Apr 10, 2020 •

edited

Loading

alexander-schranz commented Nov 29, 2021 •

edited

Loading

greg0ire commented Dec 6, 2021 •

edited

Loading

morozov commented Dec 10, 2021 •

edited

Loading

alexander-schranz commented Dec 10, 2021 •

edited

Loading

morozov commented Dec 11, 2021 •

edited

Loading

tigitz commented Feb 1, 2022 •

edited

Loading