table feeds_item could not be created pdo exception key was too long

PHPadmin return the same error: "Specified key was too long; max key length is 1000 bytes" when i try to backup the entire DB with PHPmyadmin,

I can import the whole database WHITHOUT feeds_item (containing actually 563 cardinalities on PRIMARY) resulting from feeds_importer engine.

Yay, you got it.
This looks like what was going on here #1047442: PDOException during installation: Invalid multibyte sequence in argument in check_plain() ....
I closed that one as a duplicate.

Solved :

1/ did the backup with Backup and migrate, cleaning the feeds_logs to be empty, and excluding datas from feeds_item module.
2/ restore the DB with Import within phpMyadmin, working!
3/Backup Migrate only the datas from feeds_item
4/ Import within phpMyadmin the datas from feeds_item
5/ Voila, working fine.

I think it has something to do the character set, changing the database and query to latin1 worked fine, anyway, if wanting to use utf8 change the lines on the feeds.install from

'lookup_url' => array('entity_type', 'id', 'feed_nid', array('url', 255)),
'lookup_guid' => array('entity_type', 'id', 'feed_nid', array('guid', 255)),
'global_lookup_url' => array('entity_type', array('url', 255)),
'global_lookup_guid' => array('entity_type', array('guid', 255)),

to

'lookup_url' => array('entity_type', 'id', 'feed_nid', array('url', 100)),
'lookup_guid' => array('entity_type', 'id', 'feed_nid', array('guid', 100)),
'global_lookup_url' => array('entity_type', array('url', 100)),
'global_lookup_guid' => array('entity_type', array('guid', 100)),

I still don't understand why it worked but it worked. If someone knows why it worked or have a better solution, please share it.

Yes, there is a difference between the number of bytes (intresting for the index) and the number of characters. For example some character sets might use 7 bit (2^7 = 128) or 8 bit (2^8 = 256).

The maximum index length also depends on the storage engine. For InnoDB that means:

The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)

There are downsides to long indexes anyway, so 100 should be fine. Or 2^7 - 1 = 128 - 1. I applied your changes to my local copy - works fine. Patch attached.

Sorry, I didn't notice the update functions on the first glance. They also create indexes and need to be fixed.

thanks niklas, that's a good explanation about the issue. And thanks for submitting the patch.

Linked some duplicates here (or maybe this is a duplicate).
#1077350: error on install
#1050624: Error after enabling Feeds - "PDOException SQLSTATE[42502]: Base table or view not found... mysite.job_schedule..."
#1112882: feeds install : max key length : 1000 bytes
#982980: #1071 - Specified key was too long; max key length is 1000 bytes -> kills site & possible backups wont work

Also see #911118: Key length error for update 6004 and 6005, which is about D6 and has it's own patch:

Maybe we should also create a patch to add a new update function?

The patch worked fine for me, although I think adding another hook_update_n so that folks don't have to completely uninstall feeds to get the change is a good idea.

If they installed it and it works it's fine, only exporting backups to another server might fail. If they tried to install and failed they are basically screwed. It will be hard (but not impossible) to rescue them from that state. Were you refering to the first one?

niklas, if your question is aimed at me, I'm afraid I don't understand it.

I think @niklas's the point is the following:

If you install Feeds with this patch for the first time, you'll be fine.
HOWEVER
If you install Feeds without the patch, you'll have to uninstall Feeds, fix the damage it's done to your database (as described in #3?), and patch the clean copy of Fields, and reinstall it. If you have a recent database backup (before you installed Feeds), use that.

Thank you. I really should have worded that more clear.

An FYI on this one: I hit this error on MariaDB 5.1, but when I happened to upgrade to MariaDB 5.2, I don't have it any more (with or without the patch).

Patch in #6 failed for me. I remade it for 7.x-2.x-dev

In #14, I was apparently on crack. I have this same problem with MariaDB 5.2.

Thank you @lelizondo.

This seams to be critical: A lot of people won't be able to install Feeds without that patch - and if they try, they are left with a broken installation, that they manually have to clean up, before they can retry. Maybe upgrade this issue?

@rfay: I think there are some things that might influence success, anyway. It would only be strange, if you tried it with exactly the same database settings.

Setting to critical and "Needs review". This needs a good review and to get to RTBC. Feeds is getting some love now so I'm sure it can get in.

I wonder how that patch could break anything. Does HEAD pass all tests?

(Attached a patch that creates a random file for testing, if you like.)

I just forced a test, but my bet is it's all broken. http://qa.drupal.org/pifr/test/143534 (Yup, it failed)

Note that the D6 tests don't succeed... We should probably open up an issue and get the tests working.

Update: Per davereid, I've disabled automated testing on Feeds.

davereid mentioned in IRC that entity_type needs to be 32 chars long as well. This patch changes that.

If you're affected by this, hurry up and test it and RTBC it. I'd love to see this go in.

I noticed that the previous patch was retroactively changing feeds_update_7202() and feeds_update_7204(). We can't do that - we have to deal with where people are. But feeds_update_7205() will now do that.

Also (thanks davereid and DamZ) per db_change_field() we have to jump through some hoops with the primary key due to this change, so we now drop the primary key and rebuild it.

We should also maybe check if installations failed to create this table since the long key doesn't allow the table to be created. See #1044866: Error on import node page.

Would like to ensure this gets fixed before #1190816: Creating new 7.x and 6.x releases this weekend

I have studied how to repair the missing table and it seems to me to be nearly impossible, at least in the environment I'm studying. Rules seems to invoke entity, which seems to invoke feeds, regardless of whether feeds is enabled or not.

The bottom line: Just trying to recreate the table in a hook_update_N() or wherever doesn't really do the job, because the system has already crashed in hook_init() and can't get bootstrapped.

I'll see if I can figure out any other workarounds. It looks like a database-specific script might be the only approach. I can't even do a drush php-eval, because that requires bootstrapping first.

OK, here's a proposed fix to solve the problem in the future and also to help people hack out of this if their install is ruined.

This adds the same as in #23, also attempts to create feeds_item if it doesn't exist during update. Most importantly, it makes the database error in feeds_entity_load() not result in a fatal. That allows us to disable and uninstall feeds, so the workaround below can be accomplished.

Here's what to do if your install is already ruined and you can't get anything except the PDO error described in this issue.

1. Install the latest version of feeds (or apply the patch here) or you won't be able to do anything anyway.
2. Disable feeds and feeds_ui "drush dis feeds feeds_ui"
3. Uninstall feeds and feeds_ui: "drush uninstall feeds feeds_ui"
4. Run the script feeds_cleanup.sh.txt inside your drupal directory "bash feeds_cleanup.sh_.txt"
5. Enable feeds and reconfigure it.

Well done. This works for me on a setup where the installation formerly failed.

Somebody please RTBC this to get it in :-)

Just committed a modified version of this:
1. Uses watchdog_exception in feeds_entity_load().
2. Does not attempt to create the {feeds_item} table in the update hook as likely the other tables in the schema also failed to get created. The proper process is to uninstall/re-install the module.
3. Fixed the patch did not properly update the lookup_url and lookup_guid indexes.

http://drupalcode.org/project/feeds.git/commit/808eb92