No, the issue is in the destination table. The issue is why does it already have a record with that same primary key?

What is contained in $feed_table when the prepare select is run and then what is in $feed_table when the prepare insert is run? And what are the statements that assign values to $dbh2 and $dbh3. And what are the create table commands used to create each of those tables? And are you sure that the variable order returned from select * is the same order you specified in the prepare insert statement. And why do you feel that the destination table is truncated, why are you sure of that?

Thank you for your help.

In production DB, I search for the table by clientID to see which table has the data. So the feed table I get from prod and I use the variable in the insert statement.

##get the feed table
        my $table= $dbh->prepare("SELECT feed_table FROM database.stre
+amTable where id = ? ");

        $table->execute($client_ID);
 $feed_table = $table->fetchrow_array();
[download]

I do a print out of the values when the script runs The database you will connect to is-> production-feed02.czdsk6vbk.us-west-2.rds.amazonaws.com database to query -> database_feed002 The table will be -> FACEBOOK_1224734 The destination feed host is-> staging-feed02.czrsdsbk.us-west-2.rds.amazonaws.com The destination database is-> database_feed002

The values of dbh2 and dbh3 (dbh2 is to connect to the production feed database to get the data and dbh3 is to connect to the staging feed host and database to inset the data) are

##connect to the feed database and then run query to find tables 
               my $from_feed_host="production-feed.czdsk6vbk.us-west-2
+.rds.amazonaws.com";
        my $from_feed_dsn = "DBI:mysql:database=$dbrow;host=$from_feed
+_host";
        #my $from_feed_dsn = "DBI:mysql:host=$from_feed_host";
        my $from_feed_user="root";
        my $from_feed_pass="sewetrol";
        my $dbh2 = DBI->connect( $from_feed_dsn, $from_feed_user, $fro
+m_feed_pass, { RaiseError => 1, PrintError => 1 }) or die ( "Couldn't
+ connect to database: " . DBI-$

                ##Connect to the test feed02 database instance and ins
+ert data in staging
                my $destination_host="staging-feed02.czrdsk.us-west-2.
+rds.amazonaws.com";
                my $destination_dsn = "DBI:mysql:database=$dbrow;host=
+$destination_host";
                my $destination_user="root";
                my $destination_pass="S3w3l^16";
                my $dbh3 = DBI->connect( $from_feed_dsn, $from_feed_us
+er, $from_feed_pass, {RaiseError => 1, PrintError => 1 }) or die ( "C
+ouldn't connect to database
[download]

I am not sure what the create table commands are, but I can ask the DBA

I made the prepare insert statement based off the order returned when I did a SELECT * and got

Warning: Using a password on the command line interface can be insecur
+e.
*************************** 1. row ***************************
         id_code: 10153812429735_10154227424399735
      entry_time: 2016-10-24 21:13:32
  parent_id_code: 28193we4734_10153815104343735
     author_name: Chweweles+Lawevin
     author_code: 102057rw81019807
      author_url: https://www.facebook.com/1020dfdfw81019807
author_image_url: http://graph.facebook.com/102fdf1019807/picture
       entry_url: https://business.facebook.com/2819gf4734/posts/101gf
+g815100339735?comment_id=101db8699735
     entry_types: PHOTO COMMENT
     status_code: MOD
      entry_text: dfdppe+Lafdvin
      entry_data: {"is_hidden":false,"can_hide":true,"can_remove":true
+,"can_comment":true}
     last_update: 0000-00-00 00:00:00
       pull_time: 2016-11-16 19:07:27
      queue_code: NULL
[download]

I manually inserted data and then verified that data was in the table then I truncated the table

mysql -histaging-feed02.czrsywfk6vbk.us-west-2.rds.amazonaws.com -uroot -psoasp32 -e "TRUNCATE database_feed002.FACEBOOK_table224734\g"

My manual insert statement was

<code>mysql -hicuc-staging.czrsyfffvbk.us-west-2.rds.amazonaws.com -uroot -pftrol^16 -e "INSERT INTO database_feed002.FACEBOOK_table224734 (id_code, entry_time, parent_id_code, author_name, author_code, author_url, author_image_url, entry_url, entry_types, status_code, entry_text, entry_data, last_update, pull_time, queue_code) VALUES (\"1013555100339735_101542277569735\", \"2016-10-24 21:13:32\", \"281944734_1015384444339735\", \"Cles Lang\", \"10205451019807\", \"https://www.facebook.com/10205449807\", \"http://graph.facebook.com/1020565651019807/picture\", \"https://business.facebook.com/28193yty4734/posts/10153815100339735?comment_id=1015422888888699735\", \"PHOTO COMMENT\", \"MOD\", \"Ph+Lang\", \"{"is_hidden":false, "can_hide":true, "can_remove":true, "can_comment":true}\", \"0000-00-00 00:00:00\", \"2016-11-16 19:07:27\", NULL)"

Looks like you are connecting dbh2 and dbh3 to the same database

  my $dbh2 = DBI->connect( $from_feed_dsn, $from_feed_user, $from_feed
+_pass, { RaiseError => 1, PrintError => 1 }) or die; 
  my $dbh3 = DBI->connect( $from_feed_dsn, $from_feed_user, $from_feed
+_pass, {RaiseError => 1, PrintError => 1 }) or die ;
[download]

Shouldn't that be

my $dbh3 = DBI->connect( $destination_dsn, $destination_user, $destina
+tion_pass, {RaiseError => 1, PrintError => 1 }) or die;
[download]

Maybe a copy/paste and forgot to edit error !

poj

While i support that poj above has identified your immediate error, I think it needs to be pointed out that you have described 6 different hosts above, the host you manually inserted into is not the host you truncated, and neither is the destination host in the code. Also the table that you said the code would use is not the table that you manually inserted into or truncated.

and while it seems from your manual dump that the variable order is correct, i have learned the hard way that select * may not return the order you suspect. So as to insure the variable order is correct it may be better to use something like

my $table_results= $dbh2->prepare(
  "SELECT 
     id_code,entry_time,parent_id_code
    ,author_name,author_code,author_url,author_image_url
    ,entry_url,entry_types,status_code,entry_text,entry_data
    ,last_update,pull_time,queue_code
         from $feed_table 
         WHERE entry_time >= ?
  ");
[download]

( I once had the DBA change the default order by recreating the base table(from a backup) in a different variable order(sorted) but with the same var names)

The second reason to see the CREATE TABLE command was to see if the select-from table had the same key structure as the insert-into table. Ya just never know for sure until you look. For instance the select-from table may have been created as "PRIMARY KEY(id_code,entry_time)" while the insert into may have been just "PRIMARY KEY(id_code)"

But as i said, i feel that as poj has pointed out, the fact that $dbh2 and $dbh3 point to the same mysql instance is the problem you are facing at this time