SQLSrv Driver does not gracefully treat big indexes

Sounds like this might be a bug with http://drupal.org/project/sqlsrv (I'm assuming you're using that) rather than Drupal core?

I've an error on the same module.

An AJAX HTTP request terminated abnormally. Debugging information follows. Path: http://example.com/drupal/update.php?op=selection&token=-pj8hsl9QDTfkmOZ... StatusText: ResponseText: ReadyState: 4

Oddly, the near enough identical test site upgraded OK.

I moved the database over and all was well.

Not sure what the differences are as the only things I had done on the test site were to add Rubik and Tao themes and enable Rubik for admin, and I'd installed the Apps and Levelten_apps modules in preparation for testing some of their apps.

jayhawkfan75:
I am working on replicating the issue.

I'm also getting the same error after the most recent security update. The site works properly but still getting the database update prompt. I'm also using the sqlsrv module.

Thanks for any help resolving this issue.

I just updated my Acquia Drupal from 7.15 to 7.18 and I am receiving the same URL index error in the database update.
Specifically my error is:
Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]
Column 'url' in table 'aggregator_feed' is of a type that is invalid for use as a key column
in an index.: CREATE INDEX url_idx ON [{aggregator_feed}] (url); Array ( ) in db_add_index()
(line 2912 of C:\inetpub\wwwroot\acquia-drupal7\includes\database\database.inc).

Any idea how to run this manually to remove the error and complete the database schema update?

Getting the same issue trying to update 7.16 -> 7.18

I'm not running Aquia, but rather the drupal.org core along with the Microsoft sqlsrvr libraries. As noted above, things were working fine until trying update 7.18.

Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]
Column 'url' in table 'aggregator_feed' is of a type that is invalid for use as a key column
in an index.: CREATE INDEX url_idx ON [{aggregator_feed}] (url); Array ( ) in db_add_index()
(line 2912 of C:\inetpub\wwwroot\drupal7\includes\database\database.inc).

Looking at the list of changes here, the authors make reference [CHANGELOG.txt] to changing the SQL type of [aggregator_feed].[url] from a varchar(MAX) to a TEXT (though, since the two are functionally equivalent, I can't imagine why). I'm not a MySQL guy so perhaps that's the reasoning.... The error message doesn't seem to concern this change though, and instead infers that the update is trying to make the [url] an index.

Anybody have more insight?

Did some more testing today,

The problem appears to be that MS SQL sever does not want to index columns of type [text].

CREATE TABLE [dbo].[Table_1](
	[__me] [varchar](50) NOT NULL,
	[__you] [text] NOT NULL,
	[__io] [int] NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[__me] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE UNIQUE NONCLUSTERED INDEX IX_you 
    ON [dbo].[Table_1] (__you); 
GO

Will produce the following error (look familiar?)

Msg 1919, Level 16, State 1, Line 2
Column '__you' in table 'dbo.Table_1' is of a type that is invalid for use as a key column in an index.

This press's further the case that SQL types [TEXT] and [IMAGE] are going to disappear soon. This has been news for more than 5 or 6 years now to MS SQL developers, but some Drupal developers are still hanging onto them. Would appreciate someone looking into MySQL on this and I bet, that it allows indexing on [TEXT] columns.

More insight as it happens.

Seems to still be a problem with drupal version 7.19. Is this scheduled to be fixed? Or is there a workaround?

@jayhawkfan75: My apologies if it appears that no one is working on a solution. I am trying to get up to speed on the project, overall. As @Vitesse indicated, this is an issue because Microsoft SQL does not support indexing a nonkey text field (http://msdn.microsoft.com/en-us/library/ms190806.aspx). I am working on an alternative solution for this issue.

In the table aggregator_feed, the column "url" changed from type "varchar(255)" to "nvarchar(MAX)" and SQL Server is not capable of indexing anything larger than "nvarchar(900)" for key columns.

I am not quite sure how to fix such an issue.

I created a workaround patch that will change the Core module Aggregator such that column url is a "varchar(900)" instead of "text".

Again, this is just a workaround to get my site up and running that I thought I would share, it is not a suggested fix.

@Uncle Code Monkey, Technically, SQL Server can only create an index with a TOTAL of 900 bytes for all keys. So, if all of the other key column sizes added up to 900 bytes, then you couldn't even add an nvarchar(1) as a part of the key. You COULD, however, include the column in the INCLUDE clause of the CREATE INDEX statement, as those are not counted when calculating the key size.

But, that's not the real problem, I think. The real problem is that TEXT fields aren't allowed to be a part of indexes at all.

Your patch corrects the TEXT issue (for small, ordinary URLs of less than 900 bytes), but not if you have a URL of greater than that, which couldn't be accommodated at all. It also wouldn't fix other, similar situations with other keys.

I see at least one way this could be fixed.

Create a view with a table behind the scenes that keeps the first 255 characters of the [url] column as a VARCHAR[255]. When actually querying the view named [aggregator_feed] we actually would get [aggregator_feed_behind].[url_key] + [aggregator_feed_behind].[url_text] AS [url]. The [url_key] column would be the first 255 and the [url_text] column would be the remaining characters (if any). The view could then have the index on [url_key] and everything's hunky dory, WITH clauses would still work properly.

This should be fairly easy to generalize for all indexed TEXT fields, but I'd be interested in seeing performance tests (I bet it'd still out perform MySQL handily because most of the time there won't be anything after the 255 characters, so no performance impact when querying). I have used this technique before without performance degradation on high demand, big data databases.

nvarchar(MAX) did not work, only when I used nvarchar(900) did things work for me. I don't like the solution which is why I consider it a workaround until someone with more experience dealing with these issues creates a proper fix.

I just upgraded to 7.23 and this is still an issue. I saw the patch above and am going to try and figure out how to install and use it (I've never installed a patch before)... does anyone have any news as to whether this is going to be fixed? It looks like it is still "unassigned", does that mean no one is looking at it? Also, if I install the patch and then upgrade to a newer core version (when one becomes available), will I need to reapply? BTW - would this be considered an issue for SQL module or for Aggregator?

I just updated to 7.23 and this is still an issue. I just go into aggregator.install and comment out the 7003 update code.

@jayhawkfan75: This is happening for us Oracle users too!

It might not be; it's also possible it's just the only (or the first) module to do an action that doesn't work in non-MySQL that hasn't been accounted for by the driver. Oracle has different varchar lengths too.

When I try comment #18 with varchar(900) or even smaller varchar(890) I receieve the error message:
aggregator module

Update #7003
Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060.: ALTER TABLE {aggregator_feed} ALTER COLUMN [url] nvarchar(890) NOT NULL; Array ( ) in db_change_field() (line 3017 of C:\inetpub\wwwroot\acquia-drupal7\includes\database\database.inc).

Therefore I just comment out the 7003 update. But, I don't use the aggregator module.

There is some tough work fixing this.

There are two options:

1. Use full text indexes, these are fully transparent to and easier to implement but... it will be an aditional requierement to have full-text-search enabled.

2. When the database layer requests an index on a column that can be larger than 900bytes:

(http://www.brentozar.com/archive/2013/05/indexing-wide-keys-in-sql-server/)

- Create a computed column based on the original column/s (CHECKSUM OR SHA2)
- Create the index on the computed column
(up to here easy)
- Whenever there is an equality comparison modify queries to work on the computed column

This is showing again with a recent WEBFORM module update.

The last version of the driver is already "compressing" primary keys when it detects the size exceeds SQL Server limits, that could be a strategy to use when creating regular indexes.

The 7.x branch is unsupported