All-caps "KEY" in the key-value table description prevents database dump and import

I

The patch is simple and fixed the issue for me.

I tested on MariaDB 10.0.20.

Um, hm. That's... weird. I wonder if this is a bug in the way Maria parses exports? As far as I know any reserved word in the comment should be fine since it's quoted, and it's especially weird that the bug is case-sensitive like this. I can't find any resources with issues about SQL reserved words in column comments.

This fix might be fine since it resolves the bug, but it'd be good to know why it's happening so we can either document it or ensure that there isn't an underlying bug in Drupal. I think a DB subsystem maintainer's input or +1 would be good here. Pinging Crell in particular.

... Wow, MariaDB. Wow.

Can we test this on a recent MySQL, too? I'm fine with the patch posted, but I too would like to have a better understanding of what's going on to know if we need to do anything else with it. I'll see if I can track down any MariaDB experts who might have some insight. My gut feeling is that it's a parser bug in MariaDB (or maybe MySQL as well? I've no idea), in which case we should at least make sure it gets reported upstream.

Actually I am unable to run the create table from MySQL.

CREATE TABLE IF NOT EXISTS `key_value` (
  `collection` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'A named collection of key and value pairs.'
`value` longblob NOT NULL COMMENT 'The value.',
  PRIMARY KEY (`collection`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Generic key-value storage table. See the state system for…';

Error in query (1064): Syntax error near '`value` longblob NOT NULL COMMENT 'The value.', PRIMARY KEY (`collection`,`na' at line 3

Is it really a MariaDB only error or is it related to MySQL also ? (Version : 5.6.24 )

I'm looking at this and there are a lot of issues with the SQL that was originally posted.

1. There should be a comma between each field/column definition
2. 'value' column mentions being NOT NULL yet does not provide a default
3. There is no column named 'name', only 'value'
4. Since 'value' column is a longblob, it cannot really be indexed unless you specify a key length. See http://www.mydigitallife.info/mysql-error-1170-42000-blobtext-column-use...

As far as I see, this is less an issue of MariaDB and more an issue of bad SQL.

Oh, and there is no 'name' column definition. I initially thought value was what is being referred to.

If the case is against a dump of d8 and trying to recreate it, doing so now.

I just tried against a fresh sql-dump into Maria 10.0.20 and it worked without any issues.

@slowflyer I understood that after the fact; I apologize for my comment. However, I was able to do a successful export/import of the dump.

So based on the most recent comments, is this issue now a wont-fix red herring?

I definitely saw the issue in the configuration I mentioned.

@slowflyer, @cilefen: How did you create the database dump? I've tried with drush-master, mysqldump and they both export/import correctly (again, with MariaDB-10.0.20). Mind you, I have tried this in Ubuntu and Debian and not in OpenSuse.

@BTMash phpMyAdmin 4.3.2. Maybe that's my problem.

Ha - I think we may have the answer.

I can confirm being able to replicate this issue locally with phpMyAdmin (I have acquia dev desktop locally and was able to replicate the bad sql from the export since it uses phpMyAdmin 4.3.7). Will be able to try with newer versions soon-ish to see if there is an issue but atleast now we can confirm MariaDB is not cause of the issue. But phpMyAdmin may still contain the problem so we should keep the issue open.

I tried and it is fixed in the 4.4.4 phpMyAdmin release. This is a phpMyAdmin bug that has been fixed. This is not a Drupal issue.