Drupal 7.14 compatibility issues

I have this same setup,SQL Server 2008, IIS 7.0, and php 5.3, and got the same following errors when trying to update drupal 7.12 to 7.14

node module
Update #7013

Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Object 'node.vid' cannot be renamed because the object participates in enforced dependencies.: EXEC sp_rename :old, :new, :type; Array ( [:old] => node.vid [:new] => vid_old [:type] => COLUMN ) in db_change_field() (line 2985 of C:\www\includes\database\database.inc).

system module
Update #7073

Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Object 'file_managed.uri' cannot be renamed because the object participates in enforced dependencies.: EXEC sp_rename :old, :new, :type; Array ( [:old] => file_managed.uri [:new] => uri_old [:type] => COLUMN ) in db_change_field() (line 2985 of C:\www\includes\database\database.inc).

I had already opened a forum question on this, but have identified what the problem is with 2 of the three errors you found. Repeated here is the issue.

Did a little more digging and found the problem, though I am not sure what the fix is.

the problem is the function in the driver that goes out an looks for unique indexes. I'll use the node table as an example, but the file_managed has the same issue. For reasons I don't quite understand, there is a computed column, __unique_vid based on the vid column that is getting updated. This __unique_vid column is what is actually used in the unique index, not the vid column.

Here is the code with the problem, it is passing the actual field name, but does not know that the field is actually in a computed column:

protected function dropFieldRelatedObjects($table, $field) {
// Fetch the list of indexes referencing this column.
$indexes = $this->connection->query('SELECT DISTINCT i.name FROM sys.columns c INNER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id INNER JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND c.object_id = OBJECT_ID(:table) AND c.name = :name', array(
':table' => $this->connection->prefixTables('{' . $table . '}'),
':name' => $field,
));

As a result the unique index is not dropped, and the rename fails because the column is used in the computed column.

Ideas of who can help with this?

I guess since this prevents those of us running Windows and Sql server from applying the current security updates, I think it is reasonable to mark this as a critical issue. Not being familiar with how critical is assigned, feel free to tell me otherwise, but I think this must get resolved.

It is not inconceivable that I can create a patch, but for me to do a patch with tests will take a week or more if I can free that much time as I have not submitted a patch to drupal before and don't know what dragons there are in the the test suite for sql server.

We were having a similar issue using MySQL 5.5

I attached a screen shot of the error.

The issue is that the indicies were not deleted before the field was changed as per the api docs says http://api.drupal.org/api/drupal/includes%21database%21database.inc/func...

Here is a patch to fix this.

Hi,

Tried to apply the patch, (being on windows did it manually) but still got the same error:

Update #7074

    Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Object 'file_managed.uri' cannot be renamed because the object participates in enforced dependencies.: EXEC sp_rename :old, :new, :type; Array ( [:old] => file_managed.uri [:new] => uri_old [:type] => COLUMN ) db_change_field() függvényben (D:\App\drupal\includes\database\database.inc 2985 sor).

node module
Update #7013

    Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Object 'node.vid' cannot be renamed because the object participates in enforced dependencies.: EXEC sp_rename :old, :new, :type; Array ( [:old] => node.vid [:new] => vid_old [:type] => COLUMN ) db_change_field() függvényben (D:\App\drupal\includes\database\database.inc 2985 sor).

Well, to be accurate the update ID changed according to the patch.

Find the patched file too, did I make a mistake?

Nukleraj

You may need to also run db_drop_primary_key before running the db_change_field

a couple of things. First of all, is this a spelling error in the patch?

db_drop_index('file_managed', 'fielnamme');

second, in the sql server version, this dropping of the index is supposed to be happening in the driver in the db_change_field function. I think I'm reading your patch as changing the module update function.

The reason I say that is because in the sql server version, the uri field is in a computed field and only indexed indirectly in an index called __unique_uri. Probably because sql server doesn't index binary fields.

My apologies on spelling error. Here is a new patch. This patch made my problem go away. It may be a different issue than this.

If db_change_field is suppose to drop the index, why does it explicitly tell you to do it before running db_change_field? Not trying to be argumentative, my knowledge of the db driver is not very high.

@pwaterz, my knowledge on the driver is equally suspect! In some ways, I would have expected they way you suggested to be the correct way because it would be up to each module to manage it's own indexes. So I don't know. It is conceivable that I will get some time to look at this more closely this week. I will try your patch too, assuming git can apply it on my computer.

I am having the same problems upgrading to 7.14. I tried the patch above, but it did not resolve the issue. I am getting the exact same errors after applying the patch.

I agree with the assessment that this is related to computed columns, which are not handled by the driver (the TODO note in the source for dropFieldRelatedObjects()). It is just not aware that there are computed columns referencing the column in question, which in turn is referenced by another index.

It is possible to get a list of computed columns in sys.computed_columns, so I would think that it should be possible to create the queries necessary to implement this in the driver.

Is there a possible temporary fix by manually dropping the relevant indexes prior to applying the patch, and then manually recreate the indexes?

Being new to Drupal, can anyone give me an idea as to the normal timescale for resolution of these sorts of problems? If it will be a matter of weeks rather than days than I will need to revert back to 7.12 :(

Kind regards

Ian Galletly

@IanDG these issue can sometimes take some time to work out. It's mostly up to the community to figure it out. Best thing you can do is post as much debugging info you have and add any insight that you find.

It doesn't hurt to drop the indexs. You may want to make sure you recreate them afterwards, if they are not auto created.

I tried this as well without success. Once I tried to load the update.php page, I received this error:

Fatal error: require_once(): Failed opening required 'C:\inetpub\wwwroot\drupal7/includes/database/sqlsrv/database.inc' (include_path='.;C:\php\pear') in C:\inetpub\wwwroot\drupal7\includes\database\database.inc on line 1666

I never even got to the point of receiving PDO errors within Drupal listed above, so I had to back out of it. I received some instructions on removing and recreating DB indexes and columns here: http://groups.drupal.org/node/217329#comment-758713, but unfortunately I still receive that same error message. Any advice would be appreciated.

UPDATE: I looked into it further after a weekend break, and I was able to get the update script to run without that error after doing the following:

1) Backup core and database
2) Kept "sites" directory as well as other SQLSRV-related files and folders, including "profiles/commerce_kickstart" and "includes/database/sqlsrv". I also made a backup of "includes/database/database.inc" just in-case.
3) Went to http://mysite.com/update.php to run DB update script
4) After the script was done, I received the following errors just as the original poster did:

The following updates returned messages:

trigger module
Update #7001

    Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot define PRIMARY KEY constraint on nullable column in table 'trigger_assignments'.: ALTER TABLE [{trigger_assignments}] ADD CONSTRAINT {trigger_assignments}_pkey_technical PRIMARY KEY CLUSTERED (__pk); Array ( ) in db_drop_primary_key() (line 2869 of C:\inetpub\wwwroot\drupal7\includes\database\database.inc).

node module
Update #7013

    Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Object 'node.vid' cannot be renamed because the object participates in enforced dependencies.: EXEC sp_rename :old, :new, :type; Array ( [:old] => node.vid [:new] => vid_old [:type] => COLUMN ) in db_change_field() (line 2985 of C:\inetpub\wwwroot\drupal7\includes\database\database.inc).

system module
Update #7073

    Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Object 'file_managed.uri' cannot be renamed because the object participates in enforced dependencies.: EXEC sp_rename :old, :new, :type; Array ( [:old] => file_managed.uri [:new] => uri_old [:type] => COLUMN ) in db_change_field() (line 2985 of C:\inetpub\wwwroot\drupal7\includes\database\database.inc).

So I'll follow this issue and pray to God that someone figures out how to fix it. Thanks to all.

My DBA and I sat down yesterday to try to make some of these changes directly, as opposed to the automated version. In particular, we started with the node.vid change.

It would appear that the __unique_vid column, which is a generated column, is what is causing that particular update to fail. I would imagine that the others are also failing because of similar dependencies.

I don't have an obvious resolution to the problem (other than updating by hand, including rebuilding that column), but at least we have a better definition of where the exact problem may lie.

After my #13 post, I had to revert back to 7.12 due to receiving errors when creating new nodes. I'm stuck like a lot of others are right now, and I'm glad that my site isn't live yet. I'm still a newbie and I do understand that it can take time, but is it normal to have this lack of response from module maintainers on such an important issue?

I am running Drupal 7.13 successfully in production, which includes the security fixes without the bug fixes which cause this issue with the sqlsrv driver.

I have tried dropping and re-creating the computed columns and associated indexes, but I still have problems with other modules throwing other SQL exceptions (especially views) in 7.14.

If I get some spare time, I'll try to look at the change log for 7.14 and see what might have introduced this issue. I too am a little concerned that there has been little response on this issue from the module maintainers. This is one of the key modules for widespread Drupal adoption on the Windows platform.

Good to know jcbriggs, and I absolutely agree. I had read somewhere that it might be best to install 7.13 with just security fixes for now, but I hadn't heard if that worked for anyone, so I was nervous to try it. So thanks for that information; it's really appreciated. I'll give it a try (with crossed fingers), and I'll continue to hope that someone can fix this problem soon.

FYI: I just tried the update from 7.12 to 7.13 on my local install as well as on a server site. The first one worked without any errors. The second one worked well except for one error related to the feeds module:

Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Error: The new name 'state_old' is already in use as a COLUMN name and would cause a duplicate that is not permitted.: EXEC sp_rename :old, :new, :type; Array ( [:old] => feeds_source.state [:new] => state_old [:type] => COLUMN ) in db_change_field() (line 2988 of C:\inetpub\wwwroot\includes\database\database.inc).

That's better than the other errors, but I'm still looking for an error-free update to 7.14. Hope to see more action on this issue soon. Thanks again jcbriggs.

Was able to get the update.php script to complete successfully by removing the vid_unique and the uri_unique indexes in the node and file_managed tables.

A fix will probably require that these indexes by deleted and then recreated. Or maybe find a way to remove the constraint restrictions prior to executing the alter table command.

OPIN, thanks for the information.

After following your steps for upgrading from 7.13 to 7.14, I was able to get my install down to two errors:

The following updates returned messages
node module
Update #7013

    Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Object 'node.vid' cannot be renamed because the object participates in enforced dependencies.: EXEC sp_rename :old, :new, :type; Array ( [:old] => node.vid [:new] => vid_old [:type] => COLUMN ) in db_change_field() (line 2988 of C:\inetpub\wwwroot\includes\database\database.inc).

system module
Update #7073

    Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Object 'file_managed.uri' cannot be renamed because the object participates in enforced dependencies.: EXEC sp_rename :old, :new, :type; Array ( [:old] => file_managed.uri [:new] => uri_old [:type] => COLUMN ) in db_change_field() (line 2988 of C:\inetpub\wwwroot\includes\database\database.inc).

There was also a third error for the "trigger module" with using the new database.inc file, but when I used the original database.inc file instead from my original Commerce Kickstart install instead, that error went away. So now I'm just left with these two.

For now, I'll have to revert back to 7.13 until this is resolved. Do you have any suggestions on how I can overcome this last hurdle?

This seems to be a duplicate issue of this one, http://drupal.org/node/1526404

I posted a patch there today that may be the answer. Could those of you following this try it out and see if it works for you?

I applied your patch and it worked! :)

Thanks so much for your help panmanphil. You rock!

Well I'm off for a week now, and won't be connecting to this issue, but hopefully the maintainers and a few more testers take a look at this and can move if forward. We (windows users) are now 3 versions out from a successful core upgrade 7.12 to 7.15.

The patch also worked for me. Thanks panmanphil.

I've run into another SQL Server error related to the feeds module:

Update #7206
Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Error: The new name 'state_old' is already in use as a COLUMN name and would cause a duplicate that is not permitted.: EXEC sp_rename :old, :new, :type; Array ( [:old] => feeds_source.state [:new] => state_old [:type] => COLUMN ) in db_change_field() (line 2985 of C:\inetpub\wwwroot\drupaltestglobal\includes\database\database.inc).

It's been reported here: http://drupal.org/node/1645306.

@mlittler, this looks like a hangover from a prior failed attempt, could that be possible? The module is doing what it normally does with updates, renaming the current field to whatever_old, creating the the new column with the new settings and then copying data from old to new. It looks like the _old column had already been created by something else.

Did you have a bad day earlier?

Thought I'd mention that the patch I mentioned earlier has been committed to the dev branch: http://drupal.org/node/1526404#comment-6387646

Perhaps we will be out of the woods on this issue.

That's great news panmanphil. I guess we can mark this as fixed then. Thanks for all of your great work and due diligence.