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

lyricnz’s picture

It looks like this conversion happens in feeds_update_7207() which came from #1715124: feeds_importer "config" column to short - change size to big.

lyricnz’s picture

Project: Feeds » Drupal driver for SQL Server and SQL Azure
Version: 7.x-2.0-alpha7 » 7.x-1.x-dev
lyricnz’s picture

Project: Drupal driver for SQL Server and SQL Azure » Feeds
Version: 7.x-1.x-dev » 7.x-2.0-alpha7

Whoops, didn't mean to move this issue. Will address in #1830024 and report back.

katannshaw’s picture

I received the same error upon trying to update this module.

Al_McK’s picture

Hi,
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.

lyricnz’s picture

Uninstalling 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.

Al_McK’s picture

Thanks for the reply. I'll give it a go and report back.

katannshaw’s picture

Would uninstalling and reinstalling this module result in me losing any feeds that I've set up?

lyricnz’s picture

Yes

lyricnz’s picture

The 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!!!

katannshaw’s picture

FYI: I first tried updating from alpha5 > aplha7, and I received the following errors:

warning: Missing Feeds plugin . See MYFEEDIMPORTER. Check whether all required libraries and modules are installed properly.
Error message: Notice: Undefined index: plugin_key in feeds_rules_event_info() (line 17 of C:\inetpub\wwwroot\drupal\sites\all\modules\feeds\feeds.rules.inc).

Update #7207

    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 [{feeds_importer}] SET [config] = [config_old]; Array ( ) in db_change_field() (line 2988 of C:\inetpub\wwwroot\drupal\includes\database\database.inc).

Then when I went to view my feed importer (MYFEEDIMPORTER), I saw these errors:

    Notice: Undefined index: plugin_key in feeds_ui_edit_page() (line 357 of C:\inetpub\wwwroot\drupal\sites\all\modules\feeds\feeds_ui\feeds_ui.admin.inc).
    Notice: Undefined index: in feeds_ui_edit_page() (line 357 of C:\inetpub\wwwroot\drupal\sites\all\modules\feeds\feeds_ui\feeds_ui.admin.inc).
    Notice: Undefined index: FeedsMissingPlugin_feeds_form in drupal_retrieve_form() (line 763 of C:\inetpub\wwwroot\drupal\includes\form.inc).
    Warning: call_user_func_array() expects parameter 1 to be a valid callback, function 'FeedsMissingPlugin_feeds_form' not found or invalid function name in drupal_retrieve_form() (line 798 of C:\inetpub\wwwroot\drupal\includes\form.inc).
    Notice: Undefined index: plugin_key in feeds_ui_edit_page() (line 360 of C:\inetpub\wwwroot\drupal\sites\all\modules\feeds\feeds_ui\feeds_ui.admin.inc).
    Notice: Undefined index: plugin_key in feeds_ui_edit_page() (line 374 of C:\inetpub\wwwroot\drupal\sites\all\modules\feeds\feeds_ui\feeds_ui.admin.inc).
    Notice: Undefined index: in feeds_ui_edit_page() (line 374 of C:\inetpub\wwwroot\drupal\sites\all\modules\feeds\feeds_ui\feeds_ui.admin.inc).
    Notice: Undefined index: FeedsMissingPlugin_feeds_form in drupal_retrieve_form() (line 763 of C:\inetpub\wwwroot\drupal\includes\form.inc).
    Warning: call_user_func_array() expects parameter 1 to be a valid callback, function 'FeedsMissingPlugin_feeds_form' not found or invalid function name in drupal_retrieve_form() (line 798 of C:\inetpub\wwwroot\drupal\includes\form.inc).
    Notice: Undefined index: plugin_key in feeds_ui_edit_page() (line 377 of C:\inetpub\wwwroot\drupal\sites\all\modules\feeds\feeds_ui\feeds_ui.admin.inc).
    Notice: Undefined index: plugin_key in feeds_ui_edit_page() (line 391 of C:\inetpub\wwwroot\drupal\sites\all\modules\feeds\feeds_ui\feeds_ui.admin.inc).
    Notice: Undefined index: in feeds_ui_edit_page() (line 391 of C:\inetpub\wwwroot\drupal\sites\all\modules\feeds\feeds_ui\feeds_ui.admin.inc).
    Notice: Undefined index: FeedsMissingPlugin_feeds_form in drupal_retrieve_form() (line 763 of C:\inetpub\wwwroot\drupal\includes\form.inc).
    Warning: call_user_func_array() expects parameter 1 to be a valid callback, function 'FeedsMissingPlugin_feeds_form' not found or invalid function name in drupal_retrieve_form() (line 798 of C:\inetpub\wwwroot\drupal\includes\form.inc).
    Notice: Undefined index: plugin_key in feeds_ui_edit_page() (line 394 of C:\inetpub\wwwroot\drupal\sites\all\modules\feeds\feeds_ui\feeds_ui.admin.inc).

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.

lyricnz’s picture

That 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.

rvdtuin’s picture

I 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

lyricnz’s picture

The problem is that the update function of feeds attempts to convert two DB columns from "text" to "big blobs":

/**
 * Change config fields from text to big blobs.
 */
function feeds_update_7207() {
  db_change_field('feeds_importer', 'config', 'config', array(
    'type' => 'blob',
    'size' => 'big',
    'not null' => FALSE,
    'description' => 'Configuration of the feeds object.',
    'serialize' => TRUE,
  ));

  db_change_field('feeds_source', 'config', 'config', array(
    'type' => 'blob',
    'size' => 'big',
    'not null' => FALSE,
    'description' => 'Configuration of the feeds object.',
    'serialize' => TRUE,
  ));
}

However this doesn't work on SQL Server. The problem with the SQL Server driver is described in the error message:

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 [{feeds_importer}] SET [config] = [config_old]; Array ( ) in db_change_field() (line 2988 of C:\inetpub\wwwroot\drupal\includes\database\database.inc).

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!!!)

UPDATE [feeds_importer] SET [config] = CAST([config_old] AS varbinary(max))
UPDATE [feeds_source] SET [config] = CAST([config_old] AS varbinary(max))

That's the same as what the patch will do, and is untested. It'll likely either work perfectly, or not at all.

rvdtuin’s picture

I'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??

Uncle_Code_Monkey’s picture

Status: Active » Needs review

lyricnz said:

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 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.

omegamonk’s picture

The 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.

tterranigma’s picture

Issue summary: View changes
Status: Needs review » Fixed

Almost 3 years later with no follow up, it would be safe to assume the issue is fixed.

MegaChriz’s picture

Status: Fixed » Closed (works as designed)

I think this was an issue with the SQL Server driver, so no Feeds issue. As such, closing as works as designed.