Re^6: perl DBI statement

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)"

Comment on Re^6: perl DBI statement Select or Download Code

Replies are listed 'Best First'.
Re^7: perl DBI statement by poj (Abbot) on Dec 04, 2016 at 20:12 UTC
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	[reply] [d/l] [select]
Re^8: perl DBI statement by cbtshare (Monk) on Dec 04, 2016 at 23:28 UTC
YES!!!...you are so correct, I apologize for wasting everybody's time, I overlooked that, cant tell how many times I reviewed those lines and saw what I wanted to put there and not what is .	[reply]
Re^9: perl DBI statement by poj (Abbot) on Dec 05, 2016 at 19:36 UTC
You are fortunate that the Primary Key constraint stopped your error from corrupting the production database. I would suggest you ask your DBA to create a user with `SELECT` only privileges and use that for `$from_feed_user` instead of `root`. poj	[reply] [d/l] [select]
Re^7: perl DBI statement by huck (Prior) on Dec 04, 2016 at 22:29 UTC
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	[reply] [d/l]