Search code examples
mysqlmysqlimport

mysql import for self reference table skipping records

I'm trying to migrate tables and data from one DB to another in programmatic manner.

Source and destination DB's are MySQL. I'm using mysql import command to insert data from sqldump . I have a self related table (say image table). Whenever I do mysql import for this table, I see some portion of records are skipped. I couldn't get the reason why they are skipped, tried both --verbose and --debug options, not sure why these didn't work.

I tried few things,

  1. First I tried to import data using mysql import, 100/209 records we skipped. Result => Records:209 Deleted:0 Skipped:100 Warning:100
  2. Then I tried to import same sql dump file again, now remaining 100 records got inserted skipping already inserted 109 records. Result => Records:209 Deleted:0 Skipped:109 Warning:109

I can see 209 records in destination DB table.

Table has related_image column which says what all images are related to that particular ID.

So, it looks like the relation is too deep that it takes N times to get all the records. Is there a way to insert all the data at once using some options in mysql import?

#!/bin/sh
MYSQLDUMP="mysqldump --login-path=$dest_loc$env"
MYSQLIMPORT="mysqlimport --login-path=$dest_loc$env"
#disable constraints
$MYSQL --database $dest_db -e "ALTER TABLE $table_name DISABLE KEYS; SET FOREIGN_KEY_CHECKS=0;"
#import data
$MYSQLIMPORT --columns "Id, Created, Status, Updated, ImageName, ImageType, MediaType, Sequence, Tag, ReceiptId, RelatedImageId, OriginalCreateDate, ContentLength"  --lines-terminated-by="\n" --fields-terminated-by=',' --fields-optionally-enclosed-by="'" $dest_db $image_dump_source_file
#enable constraints
$MYSQL --database $dest_db -e "SET FOREIGN_KEY_CHECKS=1; ALTER TABLE $table_name ENABLE KEYS;"

I don't want to repeat two mysql import, not sure what's the way to import in single command.

Let me know if someone knows it.

Solution

  • Found the solution that worked for me

    Say if the records in the table are interdependent on one another, I tried to sort the tables with child rows dump first and parent rows dump next.

    enter image description here

    For the above table, used to sort the records by Related-ID, in my case, child records had larger IDs than parent records.

    Sorted rows by relatedID looks like the same in the above image and finally inserts in the table