Document that exporting a Drupal 8 database with some versions of phpmyadmin is broken

Nothing here to do with the migration system (migrate and migrate_drupal modules). If I'm understanding you correctly, you're getting the CREATE TABLE error when loading your mysql dump on the server? If so, it doesn't seem to even be a Drupal issue. How precisely are you exporting the DB on your local, and how are you importing it on the server? What version of MySQL do you have locally?

It looks like you need some support with database issues.

I'm sorry, but although you can create issues in Drupal Core and mark the category as "support request", we don't really handle support requests in the Drupal Core issue queue as a regular practice (that option is mostly there for filing support issues for contributed modules and themes).

There are several support options listed if you click on "Support" at the top of Drupal.org, which will take you to:
http://drupal.org/support

There you can find out about the Drupal IRC channels, and the Forums, which are our two main support mechanisms in the Drupal community. You might also try http://drupal.stackexchange.com/

Good luck with your issue!

I just ran into this same exact issue, attempting to migrate my D8 site from a local to a shared hosting account. I'm troubleshooting the query now and will post here (or elsewhere, I know: not a core issue) once I have it figured out.

I'm pretty sure that phpMyAdmin is doing something funky. Perhaps a terminal dump will work more nicely? We'll see...

EDIT: Name isn't being defined properly, before being assigned as a key (I think). There's one potential issue... (be right back)

Alright. Here's what worked for my needs today when I was running into the same issue:

Change this:

CREATE TABLE `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=utf8 COMMENT='Generic key-value storage table. See the state system for…'

to this:

CREATE TABLE `key_value` (
  `collection` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'A named collection of key and value pairs.',
  `name` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The key of the key-value pair. As KEY is a SQL reserved keyword, name was chosen instead.',
  `value` longblob NOT NULL COMMENT 'The value.',
  PRIMARY KEY (`collection`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Generic key-value storage table. See the state system for';

then remove this near the end to prevent another error:

ALTER TABLE `key_value`
 ADD `name` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The key of the key-value pair. As KEY is a SQL reserved keyword, name was chosen instead.';

I encountered the same with phpmyadmin 4.2.12deb2.

More people will run into this, so #5 should be documented somewhere. Where?

I still do not understand how this is a Drupal Core issue at all?

Hm.

When I export the key_value table in PHPMyAdmin, I get this:

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.',
  `name` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The key of the key-value pair. As KEY is a SQL reserved keyword, name was chosen instead.',
  `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…';

There is no ALTER TABLE. So basically, what I'm getting is what is what is suggested in comment #5 to change the output to.

So apparently some versions of MySQL and/or PHPMyAdmin are making output that other versions of MySQL and/or PHPMyAdmin cannot read. This is not a Drupal bug. It is an incompatibility between versions of MySQL and/or PHPMyAdmin used to export and import dumps.

Anyway... You can feel free to create a page in the Community Documentation about this problem, since several people have come across it.... But please document which versions of PHPMyAdmin/MySQL are producing the output and which other versions cannot read it, if you do so, and you might also check to see if such general problems are already mentioned in the Community Documentation somewhere. I bet that if you imported the dump using the same version of PHPMyAdmin/MySQL that created it, you would not have problems.

As a note, the versions I am running (that create the "correct" output suggested above):
MySQL: 5.5.41-0ubuntu0.14.04.1
PHPMyAdmin: 4.0.10deb1

I would like to create a documentation page, but where should I put it (in the documentation hierarchy)?

Some more results:

mysqldump: correct export
PHPMyAdmin: incorrect export (both whole database and just key_value table)

The export contains a syntax error (missing comma, see #5), so importing won't work at all.

Correct output:
MySQL: 5.5.41-0ubuntu0.14.04.1
PHPMyAdmin: 4.0.10deb1
(jhodgdon)

Incorrect output:
MySQL: 5.6.25-0ubuntu0.15.04.1
PHPMyAdmin: 4.2.12deb2
(gaele)
MySQL: 5.6.20
phpMyAdmin: 4.2.7.1
(Raysunil)

+1 for #5 :-)

Thanks!

#5 also solved my issue.

Best regards,
Michel.

+1 to mysqldump, no problem with export/import

Ran into the same issue, export + re-import drupal8 in phpmyadmin.
The fix from #5 does help, thanks

This worked for me. Change the sql statement to this:

--
-- Table structure for table `key_value`
--

DROP TABLE IF EXISTS `key_value`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `key_value`
(
`collection` varchar(128)
CHARACTER SET ascii
NOT NULL
DEFAULT ''
COMMENT 'A named collection of key and value pairs.',
`name` varchar(128)
CHARACTER SET ascii
NOT NULL
DEFAULT ''
COMMENT 'The key of the key-value pair. As KEY is a SQL reserved keyword, name was chosen instead.',
`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…';
/*!40101 SET character_set_client = @saved_cs_client */;

Got the same issue - thanks to #5 it was quickly solved.

I have tested 2 different server, one that outputs correctly (phpMyAdmin 4.0.10deb1) and one that does it wrong (phpMyAdmin 4.3.8)

#5 worked for me.

Many thanks!

On my case the problem came when importing DB from mysqlnd 5.0.11-dev on my local server with XAMPP to mysqlnd 5.0.12-dev on Ubuntu 16.04 to my remote server, which I found very weird as they are very close versions.

Another problem I faced is that D8 installed on remote server was automatically recreating some cache tables after dropping them all.

So I had to leave a side drush for importing the db and did it via phpMyAdmin.

But first I needed to change the name of the DB in my settings.php file, then make the import and change back settings.php with the right db name in order to avoid the automatic creation of those cache tables. Weird!

I agree with jhodgdon (#11), some incompatibilities between MySQL versions causing this issue.

#5 Solved my issue also. Thank you!

Thanks for #5

#5 works perfectly. Many thanks!

Thanks, that change in sql file resolve the problem!!!
You are great!!!

Same error for which owntheweb gave the sollution.
Change this:

CREATE TABLE `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=utf8 COMMENT='Generic key-value storage table. See the state system for…'
to this:

CREATE TABLE `key_value` (
`collection` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'A named collection of key and value pairs.',
`name` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The key of the key-value pair. As KEY is a SQL reserved keyword, name was chosen instead.',
`value` longblob NOT NULL COMMENT 'The value.',
PRIMARY KEY (`collection`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Generic key-value storage table. See the state system for';
then remove this near the end to prevent another error:

ALTER TABLE `key_value`
ADD `name` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The key of the key-value pair. As KEY is a SQL reserved keyword, name was chosen instead.';
Log in or register to post comments

Same problem here, fixed with #5.

Output not working with:
MySQL: 5.5.38
PHPmyadmin : 4.2.10
(as seen in MAMP 3.0.7.3,
with PHP 5.6 and Drupal 8.3)

#5 worked for me.

Thanks

+1 for #5

Thanks

#5 Great fix +1 - thanks @owntheweb for coming back to post, much appreciated!

Edited 080817****

Just wanted to add that by installing the latest version of PHPMYADMIN 4.7.3 I was able to export a Drupal 8 database and import successfully into a PHPMYADMIN 4.6x version.

I also run into this trouble for days, with a development server where I can only use PHPMyAdmin to export the database.

I finally found a solution on this page : https://www.drupal.org/node/2535096

It seems to be a PHPMyAdmin bug and not a Drupal issue, however knowing this issue is existing it could be nice to avoid using reserved names in capslock in fields comments.

The correct create table with just the replacement of the word `KEY` by `key` in the comment of field name does the trick.

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.',
  `name` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The key of the key-value pair. As key is a SQL reserved keyword, name was chosen instead.',
  `value` longblob NOT NULL COMMENT 'The value.',
  PRIMARY KEY (`collection`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Generic key-value storage table. See the state system for';

I'm finally able to export and import my DB without editing the sql file.

Thanx #30

Saved some quality time

Still have to remove

ALTER TABLE `key_value`
 ADD `name` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The key of the key-value pair. As KEY is a SQL reserved keyword, name was chosen instead.';

as stated by #5

#5 worked for me too. Thanks

#5 worked for me as well, but I'd suggest everyone to stay away from phpMyAdmin when working with Drupal 8 sites, personally I've been having more issues besides this.

Is anyone still experiencing this problem?

There has been no discussion here for 5 years so perhaps there has been a change in phpmyadmin that resolved this. This seems like it is outdated. If there in further discussion this issue will be closed in three months.

It has been 8 months, so closing.