I get a

WD node: PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'field_project_program_reference_nid' at
row 1: INSERT INTO {field_data_field_project_program_reference} (entity_type, entity_id, revision_id, bundle, delta, language,
field_project_program_reference_nid) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2,
:db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6); Array
(
[:db_insert_placeholder_0] => node
[:db_insert_placeholder_1] => 296
[:db_insert_placeholder_2] => 296
[:db_insert_placeholder_3] => program
[:db_insert_placeholder_4] => 0
[:db_insert_placeholder_5] => und
[:db_insert_placeholder_6] =>
)
in field_sql_storage_field_storage_write() (line 424 of
/.../modules/field/modules/field_sql_storage/field_sql_storage.module).
SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'field_project_program_reference_nid' at row 1

Whenever I am importing into a content type that has a node reference. I can squash this error by setting a default value using something like:

$this->addFieldMapping('field_project_program_reference')
     ->defaultValue(0);

in my migration class, but to do this for every reference in my migration is going to be a PITA. Is there any other way around this?

Comments

tiyberius’s picture

So as far as I can tell, this isn't necessarily a Migrate problem, as I've seen the same error message when I tried to save a node, but an the integer field blank. It would be great if I could get verification on this though.

mikeryan’s picture

Status: Active » Closed (cannot reproduce)

If you get the same error through the UI, then it's clearly not a Migrate problem.

Is the field set to be required? What if you change it to be optional?

tiyberius’s picture

You're right, it's not a Migrate problem. It is an optional field and not required, but I still get the same error message. Clearly this issue really shouldn't be in the Migrate queue, sorry for any confusion.

dman’s picture

FYI, for searchers who came here on the keywords (like I did)
D7 DB calls are intensely anal about the *type* of the data being sent to the DB schema. "1366 Incorrect integer value"
I've found that if the data array contained a BOOL but the DB schema declaration expected an INT ... fatal error. What a fail (especially if you, y'know use PHP which does automatic casting all the rest of the time)
Anyway.

That is the cause.

I fixed it (in an unrelated module with the same problem) with something like:

$values[$field] = (int)$values[$field];

as appropriate. (Sorry if that fix doesn't map to this exact code).

Point being - the fix *I* found was to force a value to be cast to the right type before trying to use a DB update.
Sucky.

Sorry for noise in this queue, I'm leaving clues for folk who find this thread in search.
I'll look at addFieldMapping to see if it helps better.

drasgardian’s picture

I was getting this after a migrate on any node reference fields that I tried to save without a value. I found that editing and saving the offending field definitions (twice!) cleared it for me.

Using features to export the field definitions before and after shows a change from

      'default_value' => array(
        0 => array(
          'nid' => '',
        ),

to

       'default_value' => array(
         0 => array(),
       ),
mgifford’s picture

Status: Closed (cannot reproduce) » Active

Thanks @dman, I'm trying to figure out how to do this via the Migrate module.

I was hoping that there was a:

  $this->addFieldMapping('field_channel', 'number')
    ->description('Should be an integer.')
    ->filterType('int');

Or something like that. Think it is probably something to be organized via prepareRow();

mikeryan’s picture

Status: Active » Closed (cannot reproduce)

Please don't tack on off-topic comments, especially to closed issues.

@mgifford: Please see the Callbacks section of http://drupal.org/node/1133448.

mgifford’s picture

@mikeryan - Thanks

zserno’s picture

The following workaround worked for me:

function prepare($entity, $row) {
  if ($entity->field_no_of_paid_staff[LANGUAGE_NONE][0]['value'] == '') {
    unset($entity->field_no_of_paid_staff);
  }
}

So this way it'll end up with empty fields when source field is empty.

ifernando’s picture

This error happens to me with a corrupt image because Drupal cannot determine the image size.

mgifford’s picture

@ifernando - what in Drupal?

PHP can determine image size no problem, so certainly something could be written to evaluate this here.

kari.kaariainen’s picture

Thanks to #4 and #5 I resaved all term reference and integer field settings on several node types which added stuff to the definition blobs. After doing this, all of those node types started working ie not crashing on creating new nodes. Site migrated from D6 to D7 with Migrate 7.x-2.5.