How can I migrate the Domain publishing settings for nodes via SQL?

Question

I am using Drupal 7 and Domain Access (latest stable versions).

In my source site, there are nodes with "Domain access options", "publish to.." then a number of selected domains.

I migrate the data in the node_access table, mapping the old nid to the new nid using the migrate_map_xxx table.

However when I view a Node which has rows in the node_access table, publishing the node to certain domain id, the relevant box is not ticked on the node edit page.

When I rebuild permissions, the rows of data I migrated into node_access disappear and the node is still published to just the default domain (three other rows were inserted into the node_access table, now there is just one).

How can I migrate the publishing options for nodes from one DB to another?

Answer 1

OK, I figured it out... The domain_access table is the one I need to migrate the data from and to.

Key is to rebuild permissions after updating the domain_access table, so the node_access table is updated with the latest domain publishing options.

So I executed the following SQL to migrate the old domain_access records in to the new DB, then update with the new nid values from the migrate_d2d mapping table:-

delete from destinationdatabase.domain_access
    where nid in (select destid1 from destinationdatabase.migrate_map_blognodearticle);

CREATE TABLE IF NOT EXISTS destinationdatabase.domain_access_tmp LIKE destinationdatabase.domain_access;

INSERT INTO destinationdb.domain_access_tmp (nid, gid, realm)
SELECT nid, gid, realm
FROM sourcedatabase.domain_access
where sourcedatabase.domain_access.nid in (select sourceid1 from destinationdatabase.migrate_map_blognodearticle);

UPDATE destinationdatabase.domain_access_tmp AS ds, destinationdatabase.migrate_map_blognodearticle AS new
SET ds.nid=new.destid1
WHERE ds.nid=new.sourceid1  
and ds.nid in (select sourceid1 from destinationdatabase.migrate_map_blognodearticle);

INSERT INTO destinationdatabase.domain_access (nid, gid, realm)
SELECT nid, gid, realm
FROM destinationdatabase.domain_access_tmp;

Then ran drush eval 'node_access_rebuild();