In update 7006 field "vsid" is dropped:
db_drop_field('vef_video_styles', 'vsid');
The problem is that this field was previously a Primary Key.
Before dropping the field, the PK should be dropped:
db_drop_primary_key('vef_video_styles');
db_drop_field('vef_video_styles', 'vsid');
I guess it does not complain on MySQL but it does on other database engines.
This is from official db_change_field documentation, I guess they are missing to add the same warning for db_drop_field:
IMPORTANT NOTE: To maintain database portability, you have to explicitly recreate all indices and primary keys that are using the changed field.
That means that you have to drop all affected keys and indexes with db_drop_{primary_key,unique_key,index}() before calling db_change_field(). To recreate the keys and indices, pass the key definitions as the optional $keys_new argument directly to db_change_field().
Priority set to Major because it breaks updates on engines different from MySQL.
Comment | File | Size | Author |
---|---|---|---|
#12 | 2241809-add-index.patch | 561 bytes | danwonac |
#9 | 2241809-failing-update-mysql.patch | 729 bytes | pm5 |
#5 | 2241809-failing-update.patch | 940 bytes | david_garcia |
Comments
Comment #1
david_garcia CreditAttribution: david_garcia commentedComment #2
plopescHello @david_garcia_garcia
Thank you for your patch. Do you think this patch will fix the bug reported in #2201495: Problem updating?
Waiting for some feedback on that issue before add tis patch to the repo.
Cheers
Comment #3
david_garcia CreditAttribution: david_garcia commentedI think it will solve the issue in #2201495, I was seeing that exact same problem.
What happens is that on the first run of the update, "db_drop_field('vef_video_styles', 'vsid');" fails (because the PK needs to be dropped first), but at this point the TITLE field has already been created.
Then, no matter how many times after you try the update you will get an error because the field was already created (unless you manually drop the field), and the error is missleading because what made everything fail on the first place was the problem with the PK.
It would be safer to move the creation of the "Title" field to be right after the dropping of "vsid".
Greetings,
Comment #4
plopescHello @david_garcia_garcia
Thanks a lot, it looks nice!
Could you try to post a new patch including changes indicated in #3?
Given that you can replicate the bug, it would be great, in order to be sure that the patch is working.
Thank you so much
Comment #5
david_garcia CreditAttribution: david_garcia commentedComment #7
plopescPatch committed. It should be fixed now.
Thank you so much1
Comment #9
pm5 CreditAttribution: pm5 commentedHello. On my site #5 breaks updates on MySQL. The message during updatedb being:
I changed the order of 2 lines in update 7006, then the updates passed smoothly. I have included a patch, but I am not sure if it is the best way to fix it.
The MySQL version I am using is
Comment #10
pm5 CreditAttribution: pm5 commentedComment #11
david_garcia CreditAttribution: david_garcia commentedmmmm... the change is strange.
Instead of having this:
db_drop_primary_key('vef_video_styles');
db_drop_field('vef_video_styles', 'vsid');
Your are now proposing this (just changing the order):
db_drop_field('vef_video_styles', 'vsid');
db_drop_primary_key('vef_video_styles');
The thing is that vsid is supposed to be the primary key, and it makes no sense to drop first the field and then drop the constraint. The constraint must be removed prior to removing the key. This needs some further investigation.
I am sure SQL Server will complain if that change is done because it won't let you drop the column if there is a constraint defined over that column, but My SQL is often "formally incorrect" and swallows a lot of crap, maybe in this case it is dropping both column and constraint at the same time.
By the way, one must be carefull when re-running this kind of updates over the same database because maybe the first time you run it the primary key was efectively dropped. The change must be tested on a database that has no update attempts.
Comment #12
danwonac CreditAttribution: danwonac commentedI note a relevant issue "db_change_field on a serial column in the primary key" (as vsid is a serial type) so adding an index to vsid fixes this issue for me:
db_add_index('vef_video_styles', 'vsid', array('vsid'));
Attached is a patch.
Comment #13
circuscowboy CreditAttribution: circuscowboy commentedThe patch in 12 worked for me. I updated from 7.x-2.0-beta5 to 7.x-2.0-beta8 and was getting an error on the updb.
The simple patch allowed the database to be updated and things seem to be fine.
Comment #14
plopescFixed in #2323481: #7006 PDO Exception Error and Whitescreen
Comment #16
Slown CreditAttribution: Slown commentedThe patch in 12 worked for me too. Thank's @danwonac
Comment #17
KevinDuggan CreditAttribution: KevinDuggan commentedSorry the post is below, I had a double post here.
Kevin
Comment #18
KevinDuggan CreditAttribution: KevinDuggan commentedI just did a minor up date from 7.23 to 7.24,
I get this message :
The following updates returned messages.
video_embed_module
Update #7006
failed: PDO Exception: SQLSTATE[4200]; Syntax error or
access violation: 1075 incorrect table definition; there can be
only one auto column and it must be defined as a key:
ALTER TABLE {vef_video_styles} DROP PRIMARY KEY;
Array () in db_drop_primary_key() (line 2901 of
home/kelvis/blsgtl.tv/includes/database/database.inc
Here is line 2900 and 1901 and I am a bit confused about
how to apply the patch?
function db_drop_primary_key($table) {
return Database::getConnection()->schema()->dropPrimaryKey($table);
}
Hopefully I am not posting this question in the wrong place.
Thanks
Kevin
Comment #19
CptCasual CreditAttribution: CptCasual commentedI just got the same error doing "drush up video_embed_field" from 7.x-2.0-beta5 to 7.x-2.0-beta8.
Comment #20
plopescDid you apply the patch on #12?
I think it should work once the patch is applied. I t should work with -dev version too.
Thank you
Comment #21
CptCasual CreditAttribution: CptCasual commentedYes that worked, thanks. (I'm a newbie.)
Comment #22
KevinDuggan CreditAttribution: KevinDuggan commentedI have used SSH to access the ColorBox module folder, and SSH is asking what file to patch and I have no idea what file I would be patching here ? Is it the color box.api.php?
Thanks
Kevin
Comment #23
KevinDuggan CreditAttribution: KevinDuggan commentedI have used SSH to access the ColorBox module folder, and SSH is asking what file to patch and I have no idea what file I would be patching here ? Is it the color box.api.php?
Thanks
Kevin
Comment #24
plopescBug is not in colorbox module. It is in Video Embed field module.
If you don't know how to apply a patch, you can download the -dev version of this module, where the patch is already applied and works fine.
Comment #25
KevinDuggan CreditAttribution: KevinDuggan commentedI will try reinstalling the Video_Embed_Module,
I ended up applying the patch via SSH twice and my sites structure
fell apart. The first time applied I was not sure if the patch had been applied.
Thanks for your help,just did it and worked great!
Kevin
Comment #26
anpri31 CreditAttribution: anpri31 as a volunteer commentedI had the same issue what I did is :
1. deleted the 'name' & 'title' fields from the table 'vef_video_styles'
2. created only name field without primary key constraint
3.run update.php
when you run update.php it will create the 'title' field & add primary key constraint to the 'name' field, now go to status report your database is up to date :)