PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071

Assuming your backup file wasn't corrupted somehow, this sounds like one of the keys in the file exceeds the size limit set by MySQL. This proably means the database you exported from has different limts to the one you're restoring to. Check if the MySQL version, character encoding and storage engines match. I believe, for example, that innodb supports much longer keys than myisam. Don't quote me on that.

Report back if you figure out the issue in case anybody else has the same issues.

hi,
i'm trying to import a module from 6.x to 7.x.
is a key length error, but the error says key max length is 767, and the key defined size is 256 :S

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes: CREATE TABLE {travels_transaction_files} ( `transaction` INT unsigned NOT NULL, `pathname` VARCHAR(256) NOT NULL, `name` VARCHAR(64) NOT NULL, PRIMARY KEY (`pathname`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8; Array ( ) in db_create_table() (line 2588 of /home/gsi/html/intranet_dac/includes/database/database.inc).

Seems because the dump file will set the tables into the mysql default setting char and myisam although Drupal 7 was installed as innodb and utf-8

I was also getting this error, but the latest dev version sort-of fixed it, without fixing the function of the module.

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes in backup_migrate_destination_db_mysql->_restore_db_from_file() (line 134 of /var/www/drupal-7.0/sites/all/modules/backup_migrate/includes/destinations.db.mysql.inc).

I'm running several sites dev on a turnkey linux server, live on godaddy. B&M version 7.x-2.1

A couple of the sites (those with less content) have no problems. I was able to move databases back and forth. At least two of the sites DO have this problem. I got this error the first time I tried to import the database on godaddy (I eventually had to just backup the full database, not using this module), I got it again when I tried to migrate the database back from live to dev.
And just to test, I tried to import the database from dev to dev, and got the same error, so it's nothing to do with differing SQL settings.

MySQL version 5.1.41 on the turnkey dev site.

With the latest dev version, I no longer get the error, but it also doesn't restore my database, apparently.

subscribe

I'm seeing this error, too, if I BAM/backup locally and upload my BAM-created backup to my production environment for restoration. If I do a phpmyadmin import or mysql dump procedure, I can import the database without problem.

I can also do a local to local -> local backup and restore and a production -> production backup and restore, but not local -> production backup and restore.
I CAN do a production -> local backup and restore, however.

Here are my details:

dev mysql server: 5.1.44
production mysql server: 5.0.67

Both mysql servers:
character set results utf8
character set server latin1
character set system utf8
storage engine MyISAM

I've got this issue as well, same message. Basically I cannot migrate a site from production to live.

I suspect that this is related to the table property issue #1128620: Module needs to back table properties up as well which would have turned all your InnoDB into MyISAM tables. This is fixed in the latest version so try a taking a new backup and see if it causes the same issues.

If it still happens with the latest version please reopen.

Has this been sorted out? I tried enabling WordPress Migrate and got the error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: CREATE TABLE {wordpress_migrate_attachment} ( `filename` VARCHAR(255) NOT NULL COMMENT 'Filename of XML imported by this migration', `original_url` VARCHAR(255) NOT NULL COMMENT 'URL of attachment on WordPress', `new_uri` VARCHAR(255) NOT NULL COMMENT 'URI of attachment on Drupal', PRIMARY KEY (`filename`, `original_url`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'Map original attachment URL to Drupal URI'; Array ( ) in db_create_table() (line 2684 of D:\xampp\htdocs\drupal\includes\database\database.inc).

I'm getting a similar error going from GoDaddy to HostGator :(

PDOException: in backup_migrate_destination_db_mysql->_restore_db_from_file() (line 198 of /home/uname/public_html/modules/backup_migrate/includes/destinations.db.mysql.inc).

This happens when the backup file is encoded twice. A workaround is to extract the .mysql file from the gz file and change its extension from .mysql to .gz, and use that file as a backup source.