for the last few months i've been writing some scripts that require database access, and in the process have run into many of the day-to-day issues attendant to using perl with mysql and postgresql. i've generally had to track these down (although they were often in some bit of documentation somewhere) and it occurred to me that we would be doing ourselves and others following the same path a service if we were to summarize our assembled wisdom. i'm sure that there are those who have higher levels of enlightenment to shed on some of these items ... we could probably assemble a skeleton with a great deal of utility. in that spirit, here are a few things for which i've learned to watch.

mysql-

--installs with "skip networking" specified in /etc/mysql/my.cnf ... a little thing that can hang you for a bit if you don't know it.

--watch disk space ... especially with logging turned on mysql can eat it up pretty quickly, and then simply start failing insert statements with curiously inconsistent error statements.

--dbd driver sensitive to memory leaks when performing a large number of operations on very large tables. these problems may manifest themselves in sections not specifically dealing with database access.

--quirky insert statement syntax ... i.e., "insert into <table_name> set column1=$whatever, column2=$whaterver2 ... etc.";

--access control system offers finely-grained control, but reminds me of reverse polish notation in that if you don't work with it reasonably frequently it'll drive you crazy each time you deal with it. if someone has a good heuristic for application here i'm sure a great many would regard that as a blessing.

--o'reilly book (MySQL and mSQL) has a few errors ... e.g., isamchk identified as repair utility, rather than myisamchk. not being critical, just heading off the time someone might spend trying to figure out why it doesn't work.

postgresql

--insert statement very sensitive to quoting. use $dbh->quote() whenever applicable or the statement will flat bomb out. parser just doesn't like it.

--i've had some difficulty with a select max statement when the operant column is character but holds numbers. for some columns the statement will work and for others not, and the reason for that is not presently apparent to me.

--trying to keep numeric strings appropriately aligned by stuffing white space in the beginning of the string doesn't apparently work after insertion, even after quoting. i've had to stuff with zeros ... maybe not the best solution, but it sure is nice to get back records you've put in when you do a select statement. <grin>

i know i have others that i'm not thinking of right now ... perhaps other would like to make a running thread of this? i'm not particularly interested in advocacy of one or the other, each had a slighly different niche. if we do this right our comments should also map those niches for individuals trying to make a decision regarding which to choose.

Edit by tye

In reply to mysql and postgresql gotchas and tips by ralphie

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.