When applying a security update for Chaos tool suite (ctools), getting this error:
Update #6008
Failed: PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.: UPDATE [{ctools_object_cache}] SET [data] = [data_old]; Array ( ) in db_change_field() (line 2988 of \includes\database\database.inc).

Comments

thorsten.’s picture

thorsten. commented

Hi,

Same error here!
Also look here: #1958592: PDOException: SQLSTATE[42000]

Thanks thorsten

xenphibian’s picture

xenphibian commented

I resolved this by deleting the column [ctools_object_cache]. [data_old] and rerunning the update.

HOWEVER, this was on a completely empty database. This will very likely NOT be empty on all (most?) databases.

I do not know the implications of deleting some but not all ctools cache data if the table isn't empty. If it is truly a cache then deleting the whole table and recreating the cache should have no long term effect. If it's only CALLED a cache but isn't really one, though... who knows what that could break? Backup your db before trying this.

The problem here is that SQL Server cannot implicitly convert from a string to a binary object. You have to tell it what you want it to do. (Databases which do this implicitly are explicitly breaking the rules of strong typing just so that programmers can be lazy and, being a software engineer myself, I fully understand the desire to take advantage of places where I'm allowed to be lazy but that doesn't excuse my making it harder for everyone else just because I want to be lazy.)

jcook4now’s picture

jcook4now commented

Thanks xenphibian - that worked for me. . .

Jeff

thorsten.’s picture

thorsten. commented

Also a thank you from me! Works perfect.

Cya thorsten

Uncle_Code_Monkey’s picture

Uncle_Code_Monkey commented
Status: Active » Needs review

This sounds like it may have to do with another bug, recently fixed:
SQL Server driver in #1830024: Cannot execute db_change_field() to convert nvarchar to varbinary

lyricnz provided a patch for it, which I have modified slightly and found that it fixed my similar issues quite nicely.

See my comment for my modified and tested patch.

The workaround to remove any *_old fields still applies, though.

gabriel.achille’s picture

gabriel.achille commented
Status: Needs review » Closed (duplicate)

Definitely for me it is a duplicate of #1830024: Cannot execute db_change_field() to convert nvarchar to varbinary.
If you had already run the update before applying the patch, you may need manually restore your database as it was BEFORE running the update. If you don't have a backup (like me, this is bad!) you can just drop the data field, rename the data_old to data and run the update.

juahoo’s picture

juahoo commented

Solution in post #2 worked for me also. I didn't have any data in that table even though it wasn't a fresh install.

omegamonk’s picture

omegamonk commented

Though this issue has been closed, i wanted to note that the contrib patch for #1830024: Cannot execute db_change_field() to convert nvarchar to varbinary has been committed to the sqlsrv development release. Since this has been noted as a duplicate, i wanted to make sure this was updated.

** Updated for spelling correction **