Exporting a Drupal 8 database with phpMyAdmin will result in a broken database dump.

On import you will get the following mysql error:

 CREATE  TABLE  `test`.`key_value` (  `collection` varchar( 128  )  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…'

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`value` longblob NOT  NULL  COMMENT  'The value.',

The problem is in the name column of the key_value table. See #5.

Comments

Raysunil’s picture

mikeryan’s picture

Component: migration system » documentation
Category: Bug report » Support request

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?

jhodgdon’s picture

Component: documentation » database system
Status: Active » Fixed

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!

owntheweb’s picture

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)

owntheweb’s picture

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.';

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

gaele’s picture

Title: Try to change server from localhost to dev server » Export with phpmyadmin is broken
Version: 8.0.0-beta10 » 8.0.0-beta14
Component: database system » documentation
Category: Support request » Task
Status: Closed (fixed) » Active
Issue tags: -Backup and Migrate, -server change +phpMyAdmin

I encountered the same with phpmyadmin 4.2.12deb2.

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

gaele’s picture

Issue summary: View changes
jhodgdon’s picture

Category: Task » Support request
Priority: Major » Normal

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

gaele’s picture

Title: Export with phpmyadmin is broken » D8 Export with phpmyadmin is broken
Project: Drupal core » Documentation
Version: 8.0.0-beta14 »
Component: documentation » Missing documentation
Category: Support request » Task
jhodgdon’s picture

Title: D8 Export with phpmyadmin is broken » Document that Drupal 8 database exports are not portable across MySQL versions

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

gaele’s picture

Title: Document that Drupal 8 database exports are not portable across MySQL versions » Document that Drupal 8 database exports with some versions of phpmyadmin are broken
Issue tags: +d8docs

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)

Anonymous’s picture

Thanks so much! #5 Solved my issue also. Was a bit of a head scratcher until I found this post.

Cheers
Andrew Agrios

ezoulou’s picture

+1 for #5 :-)

michel.settembrino’s picture

Thanks!

#5 also solved my issue.

Best regards,
Michel.

wisniewski’s picture

+1 to mysqldump, no problem with export/import

Schroeffu’s picture

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

miguellopez’s picture

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 */;

roald’s picture

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)

atomicnation’s picture

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

zanvidmar’s picture

#5 Solved my issue also. Thank you!

JFeltkamp’s picture

Thanks for #5

drupal4u.org’s picture

#5 works perfectly. Many thanks!

sylvain_a’s picture

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)

hiramanpatil’s picture

#5 worked for me.

Thanks

sidharthap’s picture

+1 for #5

Thanks

lsabug’s picture

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

ccrosaz’s picture

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.

Christijan’s picture

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