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();
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
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
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)" | [reply] [d/l] [select] |
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 ;
Shouldn't that be
my $dbh3 = DBI->connect( $destination_dsn, $destination_user, $destina
+tion_pass, {RaiseError => 1, PrintError => 1 }) or die;
Maybe a copy/paste and forgot to edit error !
poj
| [reply] [d/l] [select] |
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] |
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 >= ?
");
( 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] |