SQL Server - PDOException: SQLSTATE[42000] when attempting update

Well, I guess webform isn't compatible with IIS. Not too surprised. That said, you should be fine without the index so long as you aren't using unique validation on large data sets. You can comment out the offending code and run the update.

I suppose the database abstraction layer could do something, but I doubt it.

Obviously running IIS on Drupal means you are blazing a path.

It is possible that a shorter key might work with IIS. You could try that and report back.

I have the same issue.
On IIS + Ms SQL

I have never had an issue with webform before.
How can i resolve this?

Since Drupal supports both IIS and MS SQL i don't understand why you suddenly decide that Webform should no longer support thos platforms?

The source of the issue is the index created to make unique validation be reasonably efficient on large webform installations. See the related index.

Can you, with your MS SQL server, determine what index MS SQL *can* make? I would welcome a patch to have an engine-specific patch.

Not many people develop with MS SQL. I did read the manual when I approved the patch, but I had no way to test it.

Dear Dan,

I hope this helps:
SQL Server retains the 900-byte limit for the maximum total size of all index key columns. This excludes nonkey columns that are included in the definition of nonclustered indexes.

source: http://technet.microsoft.com/en-us/library/ms191241%28v=sql.105%29.aspx

--
For anybody with the same issue, i was able to update by simply commenting out the line with the index update. (line 1901 of webform.install) As stated by Dan, this should not break your Webform module, and I confirm that it didn't for me.

No, that information didn't help as I already knew it. The size of the index is limited to 64 characters, which could be up to 256 bytes with 4-byte unicode. Sill less than 900.

  db_add_index('webform_submitted_data', 'data', array(array('data', 64)));

Hi DanChadwick. I received this same error, and have commented out that line of code per liezie_D. In the meantime, I'm seeing if I can set this index up in SQL Server manually.

Does the webform module use the database abstraction layer? I seem to run into several issues with webform + SQL Server when running DB updates, which means that PostgreSQL and other non-MySQL users are probably running into those same issues as well. I know we're definitely not the majority, but I was hoping that was partially resolved with D7 + the database abstraction layer. Thanks for your input.

Does the webform module use the database abstraction layer?

Yes, as you can see by examining the code that was commented out. I suspect MSSQL does not like the length of the index. The abstraction limits the length of the index generated, but perhaps MSSQL doesn't honor that.

I would consider an engine-specific patch, if someone wants to make one.

DanChadwick: After more digging, I finally remembered a long-ago issue report that I'd created for something similar related to the SQL Server Driver module, so I've marked this as a duplicate of that report. Hopefully it will get solved someday soon.

Thanks for your help.

Just a word on this, where on the documentation do you understand that Drupal Abstraction layer supports size-limited indexes.

db_add_index('webform_submitted_data', 'data', array(array('data', 64)));

db_add_index($table, $name, $fields)

$table: The table to be altered.

$name: The name of the index.

$fields: An array of field names.

This is a very MySQL specific feature that does not exist on other database engines, not to mention that the need to have these type of indexes is just the result of very bad database usage and design by the webform module.

I'm not complaining about that, this is open source, free and I confess that some of my contributed modules have tons of crap code and crap design inside. It's a matter of time and budget. So I'm more than thankful that there is a module such as Webform even if the design is not of my like.

MySQL does not help either, it supports many some "bad practice" features or loose behaviours.

I mean, how much formal sense does it make to "partially index a column" or "indexing a column with tons of data" such as an unlimited size text fields. Of course SQL Server won't let you do this (or at least limit it to a certain extent), to prevent "bad" usage of database.

MS SQL is forcing you to use a special feature called FULL TEXT INDEXING if you want to index very big text columns. But these indexes are more for catalog-like searches and not for uniqueness comparissons. And because of that, this type of indexing is heavy (but powerfull) allowing for thesaurus like searches, hit highliting, etc...

The three core database engines all provide code to support this. Clearly they would not do that if they were only expecting field names in the schema. I would argue that the documentation is incomplete. I don't know about SQLite, but I'm sure there are lots of Postgres installations running webform.

I totally get that MS Server has differences and that those differences can be difficult to abstract away. If were an easy problem, it would have been done long ago.

By way of background, webform is not like other modules that work with a fixed or even administrator-created schema. The content is the schema. Accordingly, all submission data is stored in one table. If that weren't the case, then there are webform installations that would have 100,000 tables.

Another use case of webform is single webforms with 10^5 to 10^7 submissions. A valuable feature is unique validation. This requires a query on the data, which if the submission data table has 10^9 rows, is exceedingly slow. Because the maximum data since is quite long -- too long for the 900 character MS Server limit, at least in a 4-byte character set -- the decision was made to index only the first 64 bytes. This causes good performance for all but the most extreme use cases of unique validation. It also has a reasonable space trade-off. It seemed like a sound idea to the patch creator, quicksketch, and to me. Still does, although I'm always happy to consider a better idea.

We can't use full text indexing because we can't be assured that it is available. So what can be done?

One possibility would be to attempt the index without the length limit. Perhaps that would work in at least some of the use cases.

In the case of webform, it might be use to special-case the MS Server driver and skip the index. If anyone wants to write such a patch (in a new issue, please), I would welcome it. If you have lots of submissions and need unique validation, then your customers just get to wait as everyone did before update .

Skipping the update if running on SQL Server seems a reasonable, quick and effective approach.

The only way I can think of overcoming the issue, while still keeping uniqueness + performance, would be to rely on a checksum or hashed column (I think there was a discussion about that on the original thread).

I've scratched my head thinking on how to support this in the SQL Server driver without success (except by implementing FULL TEXT SEARCH support) wich is something I want to avoid because this is a server component itself and not available to everyone.

Patch #14 worked great for me. Thanks david_garcia. Your idea of using a checksum or hashed column approach is an interesting one, and I'm willing and able to test that idea out in the future.

@david_garcia -- do you have time to try adding the same index without the 64 character length consideration. I think almost all sites probably DON'T use 4-byte characters, so an index on a 255 character column would be less than the magic 900 byte limit. That would be a nicer shim.

One or the other should go into webform.

In the SQL Server driver text:medium is mapped to the native nvarchar(max) data type.

      'text:tiny' => 'nvarchar(max)',
      'text:small' => 'nvarchar(max)',
      'text:medium' => 'nvarchar(max)',
      'text:big' => 'nvarchar(max)',
      'text:normal' => 'nvarchar(max)',

This is quite similar to what is done in postgre and sqlite, a nvarchar(max) column cannot be indexed.

I don't understand why you say this is a 255 character column. Even in the MySQL mappings, this column is being represented by a native MEDIUMTEXT type.

text medium mediumtext, 16 MB

My error. I was thinking the data column was varchar(255).

I have committed the "skipped index" for MS SQL Server to webform 7.x-4.x. Thank you for your help.
#2457045: Index on submission data incompatible with MS SQL Server

If a better solution is committed to the database driver, please open an issue in webform to add the index back.

In case anyone is interested for a 7.x-3.x patch.