On running update.php for 7.x-2.0-alpha7 the script errored.
The log shows
"PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.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 [{feeds_importer}] SET [config] = [config_old]; Array ( ) in db_change_field() (line 2988 of C:\inetpub\wwwroot\Archive\includes\database\database.inc)."
My feeds_importer table now has 3 columns
id nvarchar(128)
config_old nvarchar(MAX)
config varbinary(MAX)
The config field contains NULLs.
I'm happy to manually update the database, but I don't know what changes are required.
Comments
Comment #1
lyricnz CreditAttribution: lyricnz commentedIt looks like this conversion happens in feeds_update_7207() which came from #1715124: feeds_importer "config" column to short - change size to big.
Comment #2
lyricnz CreditAttribution: lyricnz commentedRaised #1830024: Cannot execute db_change_field() to convert nvarchar to varbinary against sqlsvr project.
Comment #3
lyricnz CreditAttribution: lyricnz commentedWhoops, didn't mean to move this issue. Will address in #1830024 and report back.
Comment #4
katannshaw CreditAttribution: katannshaw commentedI received the same error upon trying to update this module.
Comment #5
Al_McK CreditAttribution: Al_McK commentedHi,
In some ways I'm pleased that I'm not the only one with this.
Is there any recognised course of action to take from here? My installation still reports that there are database scripts to run, how can I clear that status?
My thought is to uninstall feeds then install the latest version, any thoughts on how that would work?
I also have other updates pending and don't want to run them whilst this one is in an unstable situation.
Thoughts/comments?
Al.
Comment #6
lyricnz CreditAttribution: lyricnz commentedUninstalling feeds and reinstalling it will work. The problem appears to be that the step that changes the structure of the {feeds_importer} table fails on MSSQL.
Comment #7
Al_McK CreditAttribution: Al_McK commentedThanks for the reply. I'll give it a go and report back.
Comment #8
katannshaw CreditAttribution: katannshaw commentedWould uninstalling and reinstalling this module result in me losing any feeds that I've set up?
Comment #9
lyricnz CreditAttribution: lyricnz commentedYes
Comment #10
lyricnz CreditAttribution: lyricnz commentedThe problem is caused by an incomplete table migration in MSSQL driver. You can probably fix it by manually executing something like
UPDATE [feeds_importer] SET [config] = CAST([config_old] AS varbinary(max))
If that works, and the data in feeds_importer.config looks right, and the module works OK, you can probably drop column config_old.
BACK UP YOUR DATA FIRST!!!
Comment #11
katannshaw CreditAttribution: katannshaw commentedFYI: I first tried updating from alpha5 > aplha7, and I received the following errors:
Then when I went to view my feed importer (MYFEEDIMPORTER), I saw these errors:
So then I completely uninstalled and reinstalled the Feeds module, and everything is working great now. If others can verify that this works for them, then I believe that a workaround has been found.
Comment #12
lyricnz CreditAttribution: lyricnz commentedThat looks like two errors: one about the missing feeds importer, and one about the failed DB upgrade. I don't know about the first one, possibly caused by you disabling a custom module that provided this? If it was OK afterwards, I wouldn't worry too much about that. The second one is caused by #1830024: Cannot execute db_change_field() to convert nvarchar to varbinary which needs somebody with this configuration to work on. I wrote the patch from docs, having never used Drupal on SQL Server. I'm just trying to help out Feeds module maintainers.
Comment #13
rvdtuin CreditAttribution: rvdtuin commentedI updated feeds 7.x-2.0-alpha5 to alpha7 (still having chaos tool suit 7.x-1.0)
no error's everything worked.
updating ctools 1.0 to 1.2 gave me the erros's discribed above.
someone suggested to uninstall en reinstall feeds. this means losing the feed importers.
and that means doing a lot of work again. that much infact that i think doing some work manually on the db is
better. (I did a lot of work with feeds tamper which I can't clone)
My problem is that i can't figure out myself what to do, so is there any one prepaired to make a step by step list on what to do??
thanks in advance
Comment #14
lyricnz CreditAttribution: lyricnz commentedThe problem is that the update function of feeds attempts to convert two DB columns from "text" to "big blobs":
However this doesn't work on SQL Server. The problem with the SQL Server driver is described in the error message:
I provided a patch for the SQL Server driver in #1830024: Cannot execute db_change_field() to convert nvarchar to varbinary, but nobody has tested it. I don't have Drupal on SQL server myself, I just wrote the patch based on documentation.
You MAY be able to work around the situation by executing the following SQL: (back up your database first!!!)
That's the same as what the patch will do, and is untested. It'll likely either work perfectly, or not at all.
Comment #15
rvdtuin CreditAttribution: rvdtuin commentedI'm not a programmer so I choose to uninstall feeds, reinstall and adding the importers again.
this Worked on the localhost (DAMP) (all importers where deleted)
On the (remote) Apache server:
Probably the feeds_importer column(and maybe other columns?) wasn't deleted. But it looks like the conversion of the two DB columns to one BLOB seams to have worked (although it shouldn't), no errors and the feeds importers are still there.
(value of feeds_importer config is (BLOB))
The feed_importers don't work, so I will delete them and add new ones.
The only thing is what causes the difference in outcome of the uninstall on localhost(DAMP) and (remote)Apache server??
Comment #16
Uncle_Code_Monkey CreditAttribution: Uncle_Code_Monkey commentedlyricnz said:
I ran into a similar error converting CTools module and have tested out lyrincz patch (slightly modified) and found that it fixed the issue quite nicely. Thanks lyricnz!
See my comment for my modified and tested patch.
Comment #17
omegamonk CreditAttribution: omegamonk commentedThe contrib patch for #1830024: Cannot execute db_change_field() to convert nvarchar to varbinary has been committed to the sqlsrv development release. Per the comments, this issue is likely resolved, but since I didn't verify that I am not marking as fixed.
Comment #18
tterranigma CreditAttribution: tterranigma commentedAlmost 3 years later with no follow up, it would be safe to assume the issue is fixed.
Comment #19
MegaChriz CreditAttribution: MegaChriz as a volunteer commentedI think this was an issue with the SQL Server driver, so no Feeds issue. As such, closing as works as designed.