Non-integer $item_ID_xpath for MigrateSourceXML

@mikeryan - thank you for your prompt response and attention to this.

I'm a little confused by your response. What is the connection between the source id and the $item_ID_xpath. Are they somehow connnected?

Here is the code relating to my source id:

$this->map = new MigrateSQLMap($this->machineName,
      array(
        'sourceid' => array(
          'type' => 'varchar',
          'not null' => TRUE,
					'description' => 'The source ID',
        )
      ),
      MigrateDestinationNode::getKeySchema()
    );

Currently, my source id is not set to anything on the xml, because there is no field that is completely unique. Previously, I had been trying generate a source id in the prepareRow() function, but that was causing problems and when I updated migrate to the most recent version and removed that code, it just seemed to work, without setting source id to anything. That worked for importing 800+ records out of 30k. But now I am stuck again.

In case it is helpful, here is the complete contents of my document.inc file:

class DocumentMigration extends XMLMigration {
    public function __construct() {
    parent::__construct();
    $this->description = t('XML feed (multi items) of roles (positions)');
		
    $fields = array(
      'filename' => t('File name'),
      'title' => t('Publication title'),
      'country' => t('Publication country'),
      'state' => t('Publication state'),
			'city' => t('Publication city'),
			'date' => t('Publication date'),
			'lang' => t('Language'),
			'pagenum' => t('Page number'),
			'exceptions' => t('Exceptions'),
    );

    $this->map = new MigrateSQLMap($this->machineName,
      array(
        'sourceid' => array(
          'type' => 'varchar',
          'not null' => TRUE,
					'description' => 'The source ID',
        )
      ),
      MigrateDestinationNode::getKeySchema()
    );

    // This can also be an URL instead of a file path.
    $xml_folder = variable_get('migrate_preservation_document_path_absolute', DRUPAL_ROOT); // This value is set by a configuration page for my custom module.
		
		$files = file_scan_directory($xml_folder, '/\.xml$/', array('recurse' => FALSE));
		$items_url = array_keys($files);
	
		$item_xpath = '/reel/image';  // relative to document
    $item_ID_xpath = 'pagenum';          // relative to item_xpath
		
    $this->source = new MigrateSourceXML($items_url, $item_xpath, $item_ID_xpath, $fields);
    $this->destination = new MigrateDestinationNode('document');
		
		$this->addFieldMapping('sourceid');
		$this->addFieldMapping('field_document_docid', 'docid')
				->description('Populated by prepareRow');		
    $this->addFieldMapping('title', 'full_title')
				->description('Populated by prepareRow');		
		$this->addFieldMapping('field_document_country', 'country')
         ->xpath('country');
		$this->addFieldMapping('field_document_state', 'state')
         ->xpath('state');
		$this->addFieldMapping('field_document_city', 'city')
         ->xpath('city');
		$this->addFieldMapping('field_document_pubtitle', 'title')
         ->xpath('title');
		$this->addFieldMapping('field_document_pagenum', 'pagenum')
         ->xpath('pagenum');
		$this->addFieldMapping('field_document_text_raw', 'raw_text') // works!
				->description('Populated by prepareRow from a text file');	
		$this->addFieldMapping('field_document_dirid', 'directory_id')
				->description('Populated by prepareRow');
		$this->addFieldMapping('field_document_collection', 'collection')
				->description('Populated by prepareRow');		
		$this->addFieldMapping('field_document_filename', 'filename')
         ->xpath('filename');
		// For simple date fields, we just need the xpath
    $this->addFieldMapping('field_document_date', 'date')
         ->xpath('date');
		$this->addFieldMapping('field_document_pdf', 'pdf');
  }
	
	public function prepareRow($row) {
		$filename = (string) $row->xml->filename;
		$path = $this->source->activeUrl();
		$row->docid = $this->getDocId($path, $filename);
		$row->raw_text = $this->getTextFile($path, $filename);
		$row->directory_id = $this->getDirectoryId($path);
		$row->collection = $this->getCollection($path);
		$row->full_title = $this->getFullTitle($row);
		$row->pdf = $this->getPDF($path, $filename);
	}
	
	public function getFullTitle($row) {
		return (string) $row->xml->title . ' ' . (string) $row->xml->date . ' - Page ' . (string) $row->xml->pagenum;
	}
	
	public function getDocId($xmlpath, $scanfilename) {
		$file = basename($xmlpath);
		$filename = substr($file, 0, strrpos($file, '.'));
		$parts = explode('@#@', $filename);
		$docid = $parts[0] . '--' . $parts[1] . '--' . $scanfilename;
		return $docid;
	}
	
	public function getTextFile($xmlpath, $scanfilename) {
		$file = basename($xmlpath);
		$filename = substr($file, 0, strrpos($file, '.'));
		$parts = explode('@#@', $filename);
		$path = variable_get('migrate_preservation_document_path_absolute', DRUPAL_ROOT) . '/' . $parts[0] . '/' . $parts[1] . '/' . $scanfilename . '.txt';
		$data = file_exists($path) ? utf8_encode(file_get_contents($path)) : '';
		return $data;
	}
	
	public function getPDF($xmlpath, $scanfilename) {
		$file = basename($xmlpath);
		$filename = substr($file, 0, strrpos($file, '.'));
		$parts = explode('@#@', $filename);
		$path = variable_get('migrate_preservation_document_path_absolute', DRUPAL_ROOT) . '/' . $parts[0] . '/' . $parts[1] . '/' . $scanfilename . '.pdf';
		$pdf = array();
		$pdf['path'] = $path;
		$pdf = drupal_json_encode($pdf);
		return $pdf;
	}
	
	public function getDirectoryId($xmlpath) {
		$file = basename($xmlpath);
		$filename = substr($file, 0, strrpos($file, '.'));
		$parts = explode('@#@', $filename);
		return $parts[1];
	}
	
	public function getCollection($xmlpath) {
		$file = basename($xmlpath);
		$filename = substr($file, 0, strrpos($file, '.'));
		$parts = explode('@#@', $filename);
		return $parts[0];
	}
	
}

Here is the error I get with the above code that sets the value of $item_ID_xpath to a field that does not always contain a value.

An AJAX HTTP error occurred. 
HTTP Result Code: 500 
Debugging information follows. 
Path: /batch?id=158&op=do 
StatusText: Service unavailable (with message) ResponseText: PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'field_document_pagenum_value' at row 1: INSERT INTO {field_data_field_document_pagenum} (entity_type, entity_id, revision_id, bundle, delta, language, field_document_pagenum_value) 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] => 1173 [:db_insert_placeholder_2] => 1173 [:db_insert_placeholder_3] => document [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => ) in field_sql_storage_field_storage_write() (line 448 of /var/aegir/platforms/preservation/preservation-7.x-dev/modules/field/modules/field_sql_storage/field_sql_storage.module).PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'sourceid1' at row 1: INSERT INTO {migrate_map_document} (sourceid1, needs_update) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => [:db_insert_placeholder_1] => 3 ) in MigrateSQLMap->saveIDMapping() (line 322 of /path/to/site/sites/all/modules/migrate/plugins/sources/sqlmap.inc).

After changing $item_ID_xpath back to filename - which always has a value, but is a non-integer string, I am getting the following error message:

An AJAX HTTP error occurred. HTTP Result Code: 500 Debugging information follows. Path: /batch?id=160&op=do StatusText: Service unavailable (with message) ResponseText: PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: 'marion_volume1thru5_page_001' for column 'sourceid1' at row 1: INSERT INTO {migrate_map_document} (sourceid1, needs_update) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => marion_volume1thru5_page_001 [:db_insert_placeholder_1] => 3 ) in MigrateSQLMap->saveIDMapping() (line 322 of /path/to/site/sites/all/modules/migrate/plugins/sources/sqlmap.inc).

This time through it has managed to import about 800+ items again. So I am pretty confused about what exactly the issue is here.

@mikeryan, thank you for the prompt response!

This all makes a lot of sense and explains a lot of the errors I have been seeing. The only issue I see with the above comment is that there is no completely unique field. From XML file to XML file, the filename may be duplicated many times over. It is only unique per XML file. Is that going to be a problem for using it as my source id?

@mikeryan, I have followed the instructions provided in #5. Now when I attempt to access the migrate UI, I get the following error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL COMMENT 'The source ID', `destid1` INT unsigned NULL DEFAULT NULL COMM' at line 2: CREATE TABLE {migrate_map_document} ( `sourceid1` VARCHAR NOT NULL COMMENT 'The source ID', `destid1` INT unsigned NULL DEFAULT NULL COMMENT 'ID of destination node', `needs_update` TINYINT unsigned NOT NULL DEFAULT 0 COMMENT 'Indicates current status of the source row', `last_imported` INT unsigned NOT NULL DEFAULT 0 COMMENT 'UNIX timestamp of the last time this row was imported', PRIMARY KEY (`sourceid1`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'Mappings from source key to destination key'; Array ( ) in MigrateSQLMap->ensureTables() (line 141 of /path/to/site/sites/all/modules/migrate/plugins/sources/sqlmap.inc).

I don't immediately understand what this error is about.

Made that change:

    $this->map = new MigrateSQLMap($this->machineName,
      array(
        'sourceid' => array(
          'type' => 'varchar',
          'size' => 255,
          'not null' => TRUE,
          'description' => 'The source ID',
        )
      ),
      MigrateDestinationNode::getKeySchema()
    );

... getting a new error:

Notice: Undefined index: varchar:255 in DatabaseSchema_mysql->processField() (line 200 of /path/to/site/includes/database/mysql/schema.inc).
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL COMMENT 'The source ID', `destid1` INT unsigned NULL DEFAULT NULL COMM' at line 2: CREATE TABLE {migrate_map_document} ( `sourceid1` NOT NULL COMMENT 'The source ID', `destid1` INT unsigned NULL DEFAULT NULL COMMENT 'ID of destination node', `needs_update` TINYINT unsigned NOT NULL DEFAULT 0 COMMENT 'Indicates current status of the source row', `last_imported` INT unsigned NOT NULL DEFAULT 0 COMMENT 'UNIX timestamp of the last time this row was imported', PRIMARY KEY (`sourceid1`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'Mappings from source key to destination key'; Array ( ) in MigrateSQLMap->ensureTables() (line 141 of /path/to/site/sites/all/modules/migrate/plugins/sources/sqlmap.inc).

Also tried uninstalling and reinstalling. Looked to make sure that there weren't database tables that needed to be dropped.

Thanks, that got me to the point where the migrate page loads again! I just got back from DrupalCon, so I am going to spend some time looking into this to see what other issues there are. Thanks for your help troubleshooting.