perl DBI statement

cbtshare has asked for the wisdom of the Perl Monks concerning the following question:

I have a small script which uses perl DBI to connect to two databases and transfer content of one table(prod) to another table in another DB(prod).I can connect and select data, but the insert statement doesn't work and I get no errors, help please,.

I am getting data in the variable @array

my $table_results= $dbh2->prepare("SELECT * from $feed_table WHERE ent
+ry_time >= $time");

$table_results->execute();

#Prepare insert statement
$sth_insert = $dbh3->prepare("INSERT IGNORE INTO $feed_table (id_code,
+entry_time,parent_id_code,author_name,author_code,author_url,author_i
+mage_url,entry_url,entry_types,status_code,entry_text,entry_data,last
+_update,pull_time,queue_code) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
+, ?, ?, ?, ?)") or die $dbh3->errstr;



while (my @insert = $table_results->fetchrow_array())
{

$sth_insert->execute(@insert) or die $dbh->errstr; #I get no errors 
}
[download]

My data structure looks like the following

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]

My statement @insert = $table_results->fetchrow_array() does have data, but it doesn't seem to enter it in the DB in staging. If I do a print of @insert, I get :


Use of uninitialized value $insert[14] in join or string at /home/anw/
+rds/rdscopy.pl line 99. 862010910596315 2016-10-28 23:32:50 78i404507
+1669_861799288022 Sally+Hill+Deehan 10154kjip13891 https://www.facebo
+ok.com/101o8ukuk9002413891 http://graph.facebook.com/10154o8jkjy41389
+1/picture https://business.facebook.com/2819iukjk734/posts/86176kkjkj
+288022?comment_id=862010910596315 REMOTE MENTION COMMENT MOD Go+Cubs%
+21 {"is_hidden":false,"can_hide":false,"can_remove":false,"can_commen
+t":false} 0000-00-00 00:00:00 2016-11-16 18:53:39
Use of uninitialized value $insert[14] in join or string at /home/aw/r
+ds/rdscopy.pl line 99.
[download]

I also have tried entering the data manually and it works, so there seems to be an issue with just the insert statement. Is there a way to print the insert statement being issued to the DB with the values so I can see what is being passed? My insert statement is below

mysql -hicuc-staging.czrsyfffvbk.us-west-2.rds.amazonaws.com -uroot -pftrol^16 -e "INSERT INTO sstaging_db.FACEBOOK_table34 (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 perl DBI statement Select or Download Code

Replies are listed 'Best First'.
Re: perl DBI statement by haukex (Archbishop) on Dec 04, 2016 at 08:13 UTC
Hi cbtshare, It would be good if you had linked to your previous thread, in which you appear to be asking the same question, with the same answer (remove the IGNORE from the INSERT statement). Is there a way to print the insert statement being issued to the DB with the values so I can see what is being passed? Yes, see the "Tracing" section in the DBI docs. Regards, -- Hauke D	[reply]
Re^2: perl DBI statement by cbtshare (Monk) on Dec 04, 2016 at 13:54 UTC
thank you	[reply]
Re: perl DBI statement by huck (Prior) on Dec 04, 2016 at 05:02 UTC
"If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored." It would be interesting to see the results if "IGNORE" were removed. If it were me, I would also make sure that the insert loop is actually running, to make sure "WHERE entry_time >= $time" was returning the rows i thought it should.	[reply]
Re^2: perl DBI statement by cbtshare (Monk) on Dec 04, 2016 at 12:54 UTC
Yes, I have tried that, the issue i run into is DBD::mysql::st execute failed: Duplicate entry '00000510011339735_1015422747869973' for key 'PRIMARY' at /home/rdscopy.pl line 104. DBD::mysql::st execute failed: Duplicate entry '00000510011339735_1015422747869973' for key 'PRIMARY' at /home/rdscopy.pl line 104. Line 104 is: $sth_insert->execute(@insert) or die $sth_insert->errstr;	[reply]
Re^3: perl DBI statement by poj (Abbot) on Dec 04, 2016 at 13:32 UTC
You can't insert a record with the same primary key as an existing record. If the destination table is empty then it looks like the source table does not have the same primary key contraint as the destination table. poj	[reply]
Re^4: perl DBI statement by cbtshare (Monk) on Dec 04, 2016 at 13:44 UTC
Re^5: perl DBI statement by huck (Prior) on Dec 04, 2016 at 15:42 UTC
Some notes below your chosen depth have not been shown here
Re: perl DBI statement by choroba (Cardinal) on Dec 04, 2016 at 08:42 UTC
BTW, you seem to already know how to work with placeholders. Why do you interpolate $time instead of using the technique? `my $table_results= $dbh2->prepare("SELECT * FROM $feed_table WHERE ent +ry_time >= ?"); $table_results->execute($time);` [download] ($q=q:Sq=~/;[c](.)(.)/;chr(-\|\|-\|5+lengthSq)`"S\|oS2"`map{chr \|+ord }map{substrSq`S_+\|`\|}3E\|-\|`7**2-3:)=~y+S\|`+$1,++print+eval$q,q,a, [download]	[reply] [d/l] [select]
Re^2: perl DBI statement by cbtshare (Monk) on Dec 04, 2016 at 13:55 UTC
thank you, I have made the changes	[reply]
Re: perl DBI statement by Marshall (Canon) on Dec 04, 2016 at 18:33 UTC
In this and in the previous thread on this subject, you have been asked to show the table structure of both tables (the CREATE statement). You can ask MySQL for that information. From the MySQL command line, the SHOW CREATE TABLE function will display the CREATE TABLE function that is required to make a duplicate of this particular table. Do that for both of your tables. It appears that something is wrong with your Destination table in regards to the primary key. Ok, you see: `Duplicate entry '1638749309730588_1796064657332385' for key 'PRIMARY'` Could it be that a previous INSERT actually worked? And the reason that this INSERT does not work is that there is already a row with that same PRIMARY KEY?	[reply] [d/l]
Re^2: perl DBI statement by cbtshare (Monk) on Dec 04, 2016 at 23:30 UTC
thank you for your response, it was my oversight which turned this into a bigger issue that it was.I was connecting to the same host twice.	[reply]
Re: perl DBI statement by cbtshare (Monk) on Dec 04, 2016 at 13:53 UTC
I enabled tracing and got the following, so it does seem to be doing the correct thing, but it still wont insert it because of the duplicate key error >count_params statement INSERT INTO FACEBOOK_281938224734 (id_code, en +try_time, parent_id_code, author_name, author_code, author_url, autho +r_image_url, entry_url, entry_types, status_code, entry_text, entry_d +ata, last_update, pull_time, queue_code) VALUES (?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?) <- dbd_st_prepare <- prepare= ( DBI::st=HASH(0x2715148) ) [1 items] at rdscopy.pl li +ne 92 -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x2714c50)~0x27 +14fb0) -> dbd_st_fetch dbd_st_fetch for 270acc0, chopblanks 0 dbd_st_fetch result set details imp_sth->result=2796450 mysql_num_fields=15 mysql_num_rows=11 mysql_affected_rows=11 dbd_st_fetch for 270acc0, currow= 1 <- dbd_st_fetch, 15 cols <- fetchrow_array= ( '1638749309730588_179606465732333322385' '201 +6-11-18 23:08:41' undef 'Brdef+Meqtl' '1638749323230323239730588' 'ht +tps://www.facebook.com/1638749323309730323588' 'http://graph.facebook +.com/163874933230973323230588/picture' 'https://www.facebook.com/br32 +efmdsdtl/photos/a.1638973239786374207.1073741828.1638749309733230588/ +179606463232357332385/?type=3' 'REMOTE MENTION' 'MOD' 'Merci+%C3%A0+n +os+partenfdfaires+Vinfdfs+Phifdflippe+Dansasdurand%2C+Geordsdges+Dubo +eddfdeuf+Casasnada%2C+Oldass+Stydsle+Pildsdssner+et+Mol32son+Coors+Ca +nada+%21' '{"pictures":["https:\/\/scontent.xx.fbcdn.net\/v\/t1.0-9\/ +p720x720\/15036683_1796064323657332385_7424094774323638414550_n.jpg?o +h=dd7f1b67541992213b5a5741844a9bce&oe=58BB5ABA"],"is_hidden":false,"a +ttachments":{"data":[{"description":"Merci \u00e0 nos partenaires Vin +s Philippe Dandurand, Georges Duboeuf Canadsaada, dOssld Stdyldsde Pi +lsndsder et Moldsdsson Coors Canaeweda !","description_tags":[{"id":" +152972788101632","length":23,"name":"Vinasds Philsdsdsippe Danduran32 +323d","offset":24,"type":"page"},{"id":"2490763333338560306","length" +:22,"name":"Geewerges Duwewef Caneweeada","offset":49,"type":"page"}, +{"id":"281938224734","length":17,"name":"Old Style Pilsner","offset": +73,"type":"page"}],"media":{"image":{"height":720,"src":"https:\/\/sc +ontent.xx.fbcdn.net\/v\/t1.0-9\/p720x720\/15036683_17933233332385_742 +4094774638414550_n.jpg?oh=dd7f1b67541992213231844a9bce&oe=58BB5ABA"," +width":720}},"target":{"id":"1796064657332385","url":"https:\/\/www.f +acebook.com\/brefmtwqwl\/photos\/a.1638979ewe4207.1073ewe828.16387ew3 +09730588\/179606...' '2016-11-18 23:31:10' '2016-11-18 23:32:56' unde +f ) [15 items] row1 at rdscopy.pl line 100 -> execute for DBD::mysql::st (DBI::st=HASH(0x2715148)~0x270b6e0 ' +1638749sas30588_17960646sasa85' '2016-11-18 23:08:41' undef 'Brqqef+M +twwl' '163874930sas588' 'https://www.facebook.com/1638732323730588' ' +http://graph.facebook.com/16323a30588/picture' 'https://www.facebook. +com/bresasfmtl/photos/a.163897932374207.1073323828.1638749323588/1796 +0332357332385/?type=3' 'REMOTE MENTION' 'MOD' 'Merci+%C3%A0+nos+parte +nasasires+Visns+Phaassilippe+Dandsaurand%2C+Georsasages+Duboeasauf+Ca +naasada%2C+Olasad+Stydsdsle+Pilsnasaer+et+Molsosasan+Coor323s+Canada+ +%21' '{"pictures":["https:\/\/scontent.xx.fbcdn.net\/v\/t1.0-9\/p720x +720\/15033283_179606432332385_7424094774632350_n.jpg?oh=dd7f1b6754199 +2213b5a5741844a9bce&oe=58BB5ABA"],"is_hidden":false,"attachments":{"d +ata":[{"description":"Merci \u00e0 nos partenaires Vinsdss Philippsss +de Danduraasasnd, Geaaorgaases Dssaoeuf sssanada, Oasld Stasasyle Pil +snasaer et Molsdsdson Cosdsors Candsdsdada !","description_tags":[{"i +d":"1529727823232","length":23,"name":"Vsds Phdsdsppe Dandurand","off +set":24,"type":"page"},{"id":"249076338522306","length":22,"name":"Ge +sdsdes Dudsdsuf Canada","offset":49,"type":"page"},{"id":"2813333734" +,"length":17,"name":"Old Style Pilsner","offset":73,"type":"page"}]," +media":{"image":{"height":720,"src":"https:\/\/scontent.xx.fbcdn.net\ +/v\/t1.0-9\/p720x720\/1533683_17963332385_7424094333414550_n.jpg?oh=d +d7f1b6754133333b5a5741844a9bce&oe=58BB5ABA","width":720}},"target":{" +id":"179606465733335","url":"https:\/\/www.facebook.com\/brdsdstl\/ph +otos\/a.1638933374207.10333828.1339730588\/179606...' '2016-11-18 23: +31:10' '2016-11-18 23:32:56' undef) Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph Called: dbd_bind_ph -> dbd_st_execute for 2715190 >- dbd_st_free_result_sets <- dbd_st_free_result_sets RC -1 <- dbd_st_free_result_sets mysql_st_internal_execute MYSQL_VERSION_ID 50614 >parse_params statement INSERT INTO FACEBOOK_281933334734 (id_code, en +try_time, parent_id_code, author_name, author_code, author_url, autho +r_image_url, entry_url, entry_types, status_code, entry_text, entry_d +ata, last_update, pull_time, queue_code) VALUES (?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?) Binding parameters: INSERT INTO FACEBOOK_281938224734 (id_code, entry_ +time, parent_id_code, author_name, author_code, author_url, author_im +age_url, entry_url, entry_types, status_code, entry_text, entry_data, + last_update, pull_time, queue_code) VALUES ('1638749333388_179606463 +33385', '2016-11-18 23:08:41', NULL, 'Bddssf+Mddl', '163874332322388' +, 'https://www.facebook.com/16387433333388', 'http://graph.facebook.c +om/16387423333730588/picture', 'https://www.facebook.com/bdsstl/photo +s/a.163893233207.1073741828.16387493232588/179623385/?type=3', 'REMOT +E MENTION', 'MOD', 'Merci+%C3%A0+nos+partenaires+Vins+Philippe+Dandsd +durand%2C+Georgdsdses+Duboesdsuf+Candsdsasdda%2C+Old+St3dsdyle+Pilsns +dser+et+Molson+Coors+Canada+%21', '{\"pictures\":[\"https:\\/\\/scont +ent.xx.fbcdn.net\\/v\\/t1.0-9\\/p720x720\\/150323683_17923385_7424032 +3550_n.jpg?oh=dd7f1b67541992213b5a5741844a9bce&oe=58BB5ABA\"],\"is_hi +dden\":false,\"attachments\":{\"data\":[{\"description\":\"Merci \\u0 +0e0 nos partenaires Vins Phfdpe Dandfurand, Georgfdfada, Odfd Pilr !\ +",\"description_tags\":[{\"id\":\"152972788101632\",\"length\":23,\"n +ame\":\"Vins Phfdpe Danfdnd\",\"offset\":24,\"type\":\"page\"},{\"id\ +":\"2490765450306\",\"length\":22,\"name\":\"Geofdes Dubff Canada\",\ +"offset\":49,\"type\":\"page\"},{\"id\":\"28193825434\",\"length\":17 +,\"name\":\"Old Style Pilsner\",\"offset\":73,\"type\":\"page\"}],\"m +edia\":{\"image\":{\"height\":720,\"src\":\"https:\\/\\/scontent.xx.f +bcdn.net\\/v\\/t1.0-9\\/p720x720\\/1503er83_1796064re43332385_7424094 +75454414550_n.jpg?oh=dd7f1b67541992213b534349bce&oe=58BreBA\",\"width +\":720}},\"target\":{\"id\":\"1796064rer332385\",\"url\":\"https:\\/\ +\/www.facebook.com\\/brefdfmtl\\/photos\\/a.16434786374207.104341828. +1638749309730588\\/1796064657332385\\/?type=3\"},\"title\":\"Timeline + Photos\",\"type\":\"photo\",\"url\":\"https:\\/\\/www.facebook.com\\ +/brefmtl\\/photos\\/a.1638974344207.107374434828.163874930343588\\/17 +960643432385\\/?type=3\"}]}}', '2016-11-18 23:31:10', '2016-11-18 23: +32:56', NULL) --> do_error Duplicate entry '1638749309730588_1796064657332385' for key 'PRIMARY' +error 1062 recorded: Duplicate entry '1638749309730588_17960646573323 +85' for key 'PRIMARY' <-- do_error IGNORING ERROR errno 1062 <- dbd_st_execute returning imp_sth->row_num 18446744073709551614 !! ERROR: 1062 'Duplicate entry '1638749309730588_1796064657332385 +' for key 'PRIMARY'' (err#0) <- execute= ( undef ) [1 items] at rdscopy.pl line 102 DBD::mysql::st execute failed: Duplicate entry '1638749309730588_17960 +64657332385' for key 'PRIMARY' at /home//rdscopy.pl line 102. DBD::mysql::st execute failed: Duplicate entry '1638749309730588_17960 +64657332385' for key 'PRIMARY' at /home/ardscopy.pl line 102. -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x2714fb0)~INNER) [download]	[reply] [d/l]