I've used Perl on and off for some 9 years now, but have not had the chance to use it together with MySQL until now. I have a script which reads through a flat ASCII database and adds records using INSERT to a MySQL database. The problem is, it misses some of the records (maybe 20% of them). The same records are skipped each time I try to do this. And the records being skipped are not duplicates; they have a unique key which isn't in the MySQL database.

Here are the relevant lines of code in my Perl script:

$db->do("INSERT INTO news (url, header, country, country2, date, priority, userid, source, formoreinfo, language, regreqd, keywords, image, actnowcampaigncode, state) VALUES ('$url', '$header', '$country1', '$country2', $date, $priority, '$correspondent', '$source', '$formoreinfo', '$language', '$regreqd', '$keywords', '$image', '$actnowcampaigncode', '$state')");

The unique key in table 'news' is the first field, url.

I cannot figure out why certain records are being skipped. And looking over and over at the records being skipped, I cannot see how they differ from other records which are being added.

For example, here are three records in the flat ASCII database -- the middle one is skipped, the other two are successfully added:

http://montreal.cbc.ca/regional/servlet/View?filename=qc_hema20030513|
+Quebec blood workers threaten to strike|Canada||15-May-2003|2|dblacka
+dder|CBC||English|No|||
http://www.izf.net/izf/AFP/francais/topics/togo/030513165556.mvvte7lp.
+html|Libération d'un journaliste après quatre mois de détention|Togo|
+|15-May-2003|2|jacquot|izf||French|No|||
http://pei.cbc.ca/regional/servlet/View?filename=pe_uniontalks20030414
+|PEI union and Polar Foods end contract talks|Canada||15-May-2003|2|d
+blackadder|CBC||English|No|||

[download]

Any ideas? Thanks.

In reply to Perl DBI adding some, not all, records to MySQL database by ericlee

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.