fida aljounaidi
Wed, 13 Jan 2010 15:01:57 -0800
YEs i thinks so So that, i would like to know how to increase those parameters.
Thanks another time for your help.
Can i know what do you use instead of DBD::mysql ?
Thanks
2010/1/13 Martin Evans <martin.ev...@easysoft.com>
> fida aljounaidi wrote:
> > Helo
> > Thanks for your answer
> >
> > The example is with this function
> >
> > my $objects=$dbh->selectall_arrayref($sql, {Slice=>{}}, @$values);
> >
> > with $dbh= DBI->connect($dsn, $user, $pass, $attributes)
> > $dsn= "dbi:mysql:host=$host;database=$dbname"
> > "dbi:mysql:host=$host;database=$dbname"
> > $attributes={ RaiseError => 0,
> > AutoCommit => 1,
> > PrintError => 0,
> > ShowErrorStatement => 1,
> > HandleError => \&_handle_error,
> > TaintIn => 1,
> > LongReadLen =>10485760,
> > FetchHashKeyName => 'NAME',
> > )
> > };
> > $sql=$sql = "SELECT $cols FROM $table";
> > if (defined $where) {
> > $sql .= " WHERE $where ";
> > }
> > $sql .= " ORDER BY $order";
> >
> > $sql .= " $postamble" if $postamble
> >
> > $where = in (id1,id2,.......id500)
> > here are 2 log files with trace (trace function) from the execution of
> > selectall_arrayref()
> >
> > Te first one is successfully ended -dbi.log-work), the
> > (dbi.log-notwork).
> >
> > home this is more clear.
> >
> > how did you proceed for this kind of problem ?
> >
> > Thanks a lot
>
> I don't use DBD::mysql anymore so I'm not best placed to help beyond
> making general comments. From the log you identify as failed I do not
> see any failure in the log - it ends with:
>
> 1 -> fetchrow_hashref for DBD::mysql::st
> (DBI::st=HASH(0x28b33d0)~INNER) thr#603010
> 2 -> FETCH for DBD::mysql::st (DBI::st=HASH(0x28b33d0)~INNER 'NAME')
> thr#603010
> -> dbd_st_FETCH_attrib for 027d58c0, key NAME
> 2T <- FETCH= [ 'id' 'name' 'description' 'isbuggroup' 'userregexp'
> 'isactive' 'icon_url' ] at DBI.pm line 1988
> 2 -> fetch for DBD::mysql::st (DBI::st=HASH(0x28b33d0)~INNER) thr#603010
> -> dbd_st_fetch
> dbd_st_fetch for 01048660, chopblanks 0
> dbd_st_fetch result set details
> imp_sth->result=028dff70
> mysql_num_fields=7
> mysql_num_rows=856
> mysql_affected_rows=856
> dbd_st_fetch for 01048660, currow= 857
> dbd_st_fetch, no more rows to fetch
> --> dbd_st_finish
> >- dbd_st_free_result_sets
> <- dbd_st_free_result_sets RC -1
> <- dbd_st_free_result_sets
>
> <-- dbd_st_finish
>
> suggesting there are "no more rows to fetch". You could perhaps help
> others help you by showing:
>
> a) the output errors you see from your script.
> b) a full portion of the code involved with actual SQL.
> c) versions of DBI, DBD::mysql and mysql client and server.
> d) describing better what you mean by "second one is ended with freeing
> parameters before the end of exection" as I don't understand what you mean.
>
> If you can reduce your code to the smallest example possible which fails
> it helps narrow things down even more.
>
> I notice significant differences between the operation of the perl
> script between the working example and the non-working example e.g., the
> working one issues selects with parameters initially then is followed by
> selects with ids in the where (like the non-working one) but is not
> followed by the huge select in the non-working one e.g.,
>
> both do:
>
> SELECT
> groups.id,groups.name
> ,groups.description,groups.isbuggroup,groups.userregexp,groups.isactive,groups.icon_url
> FROM groups WHERE id IN (?,?,?,?,?,?,?,?,?,?,?,?,?) ORDER BY
> isbuggroup, name
>
> SELECT
> groups.id,groups.name
> ,groups.description,groups.isbuggroup,groups.userregexp,groups.isactive,groups.icon_url
> FROM groups WHERE id IN
> ('6','11','3','7','9','12','2','8','1','4','13','10','5') ORDER BY
> isbuggroup, name
>
> etc
>
> then the working one does:
>
> SELECT
> groups.id,groups.name
> ,groups.description,groups.isbuggroup,groups.userregexp,groups.isactive,groups.icon_url
> FROM groups WHERE id IN (?) ORDER BY isbuggroup, name'
> HASH(0x27d5e50) '8')
>
> and the non-working one does:
>
> SELECT
> groups.id,groups.name
> ,groups.description,groups.isbuggroup,groups.userregexp,groups.isactive,groups.icon_url
> FROM groups WHERE id IN
>
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?...'
> HASH(0x2852970) '1588' '443' '1648' '959' '1481' '882' '1065' '1488'
> '1050' '737' '560' '1178' '580' '2070' '1387' '1031' '512' '1932' '437'
> '1273' '463' '451' '1364' '1312' '1363' '1257' '722' '634' '1263' '578'
> '1989' '1597' '695' '1553' '978' '1244' '948' '325' '889' '1654' '572'
> '350' '675' '1969' '1398' '1810' '540' '382' '337' '1448' '982' '833'
> '571' '1190' '1573' '825' '389' '917' '1660' '926' '548' '1703' '1345'
> '859' '1083' '1293' '1110' '876' '3024' '966' '1322' '992' '933' '706'
> '1172' '1518' '1259' '789' '1608' '2046' '2052' '627' '1308' '594'
> '1974' '1934' '1916' '1135' '3026' '1722' '23' '364' '388' '1068' '1778'
> '1425' '1667' '950' '486' '1678' '1445' '1354' '509' '569' '1015' '976'
> '8' '716' '1048' '1821' '1972' '1381' '1000' '940' '1760' '2054' '778'
> '743' '685' '21' '1728' '523' '1270' '1460' '586' '2066' '410' '1755'
> '1138' '690' '1168' '1498' '835' '2941' '536' '1267' '379' '1767' '1688'
> '901' '1356' '342' '862' '295' '341' '480' '1022' '1832' '683' '1215'
> '1560' '558' '417' '881' '3036' '1554' '897' '312' '573' '370' '1963'
> '1530' '1734' '638' '734' '2952' '1723' '1522' '506' '2047' '1197'
> '2950' '1580' '914' '380' '1279' '824' '661' '1395' '1075' '1935' '1471'
> '1814' '581' '906' '1349' '1321' '1365' '2966' '1407' '678' '496' '605'
> '373' '1975' '1779' '1568' '773' '1699' '644' '1820' '1752' '1309' '301'
> '1758' '1622' '1103' '3031' '1450' '1260' '568' '874' '652' '791' '1708'
> '493' '1143' '1591' '1961' '1539' '796' '1261' '1352' '329' '525' '1390'
> '272' '1646' '1469' '775' '919' '400' '534' '20' '1310' '888' '1372'
> '1825' '1627' '718' '1604' '1507' '1239' '1019' '475' '1614' '349' '672'
> '764' '1140' '1743' '1556' '1181' '1525' '803' '515' '1731' '1242'
> '2996' '751' '1383' '606' '1766' '1414' '1855' '1746' '1245' '724'
> '1400' '432' '666' '1980' '519' '860' '1676' '1195' '1063' '17' '1776'
> '1295' '907' '650' '333' '1601' '710' '1656' '895' '323' '1664' '1157'
> '545' '2938' '588' '1684' '646' '2964' '756' '446' '1944' '617' '1420'
> '1220' '1582' '352' '1982' '1006' '1599' '1331' '1149' '1594' '426'
> '542' '1533' '1435' '961' '956' '498' '629' '1288' '2951' '2988' '574'
> '1540' '920' '1284' '1701' '2064' '1160' '1937' '806' '1592' '1496'
> '533' '359' '1682' '1229' '1122' '1001' '1275' '1108' '556' '1598'
> '1053' '414' '1770' '843' '815' '1491' '1338' '1209' '1447' '1510' '394'
> '514' '610' '692' '1605' '931' '663' '476' '854' '1994' '1519' '1198'
> '969' '1988' '1626' '1409' '766' '3020' '1175' '1272' '274' '1579'
> '1663' '877' '659' '1611' '1508' '1326' '322' '645' '1128' '635' '2989'
> '1155' '1566' '1039' '1485' '788' '709' '310' '589' '1361' '614' '699'
> '687' '278' '490' '387' '1707' '1302' '1112' '626' '1777' '2044' '1294'
> '354' '1437' '461' '3029' '583' '1562' '1358' '782' '1824' '809' '332'
> '9' '1486' '1099' '1217' '2056' '1740' '591' '745' '603' '1920' '565'
> '1964' '1950' '850' '891' '937' '1004' '1619' '1193' '1404' '1013'
> '1069' '1543' '1681' '368' '424' '872' '1693' '1106' '1836' '994' '1786'
> '584' '849' '492' '1550' '1421' '1523' '665' '1333' '292' '865' '1184'
> '904' '1675' '429' '1306' '1577' '649' '1979' '2972' '1328' '642' '648'
> '1091' '1929' '1116' '1737' '1948' '1764' '1958' '1908' '795' '812'
> '1725' '1589' '1718' '1609' '585' '1059' '327' '1970' '1392' '939' '944'
> '1080' '1780' '1281' '674' '913' '280' '957' '471' '361' '465' '622'
> '1612' '1189' '1509' '894' '1748' '1416' '1819' '858' '1298' '1459'
> '1984' '1643' '412' '385' '954' '502' '307' '1286' '1671' '1237' '2041'
> '1467' '830' '2998' '747' '2058' '1101' '704' '1563' '326' '1462' '1378'
> '1165' '1942' '619' '1717' '335' '1017' '1029' '1634' '1057' '538' '621'
> '1788' '1957' '554' '680' '552' '831' '1602' '521' '1147' '1960' '1923'
> '780' '880' '1441' '1433' '1666' '1206' '2936' '440' '786' '1926' '1117'
> '299' '1008' '1289' '377' '1997' '1027' '1282' '602' '922' '841' '1044'
> '1680' '1953' '1787' '1139' '1319' '827' '1240' '1742' '701' '867'
> '1976' '564' '1695' '817' '905' '1552' '1967' '668' '1749' '302' '774'
> '866' '768' '1928' '910' '693' '503' '1636' '593' '1483' '402' '1940'
> '1097' '338' '893' '1641' '1996' '1347' '576' '761' '2962' '616' '870'
> '1384' '1036' '1078' '1253' '2065' '1726' '1265' '1512' '1644' '1526'
> '1464' '1114' '1818' '1494' '962' '1837' '1906' '1730' '281' '628'
> '1223' '1403' '2977' '1672' '1316' '1051' '1418' '630' '1304' '518'
> '1478' '1655' '1211' '1379' '711' '1791' '1593' '1542' '1642' '478'
> '398' '1314' '2071' '1720' '1428' '942' '955' '1376' '537' '1474' '18'
> '912' '1930' '1423' '599' '1393' '609' '587' '1125' '1167' '1595' '1443'
> '741' '998' '1186' '1808' '1564' '714' '3032' '1789' '601' '313' '822'
> '1610' '2051' '551' '529' '3023' '753' '697' '1343' '3001' '726' '987'
> '1324' '2984' '857' '707' '1010' '964' '1665' '1909' '935' '1782' '1093'
> '321' '624' '1199' '1340' '656' '1495' '729' '771' '1590' '814' '1652'
> '845' '1231' '1412' '884' '1249' '973' '1938' '1705' '863' '749' '1291'
> '829' '319' '915' '2960' '1586' '1986' '1521' '1922' '404' '1897' '613'
> '516' '1493' '2060' '974' '1066' '1852' '702' '639' '952' '344' '1613'
> '1710' '577' '1227' '1118' '357' '1812' '852' '643' '1769' '1637' '1673'
> '457' '821' '1475' '1794' '297' '963' '1187' '1721' '676' '1251' '1431'
> '277' '1784' '879' '938' '1212' '1830' '980' '1727' '1477' '1183' '1651'
> '1081' '1527' '505' '929' '1094' '1736' '1569' '419' '958' '995' '837'
> '1925' '625' '592' '304' '631' '547' '1616' '1583' '1544' '1538' '406'
> '1640' '499' '1369' '1204' '1524' '793' '1895' '1578' '1696' '2038'
> '654' '570' '909' '1124' '2048' '618' '1829' '421' '1163' '1998' '1761'
> '1918' '1367' '1024' '1060' '1070' '1853' '972' '1170' '473' '1896'
> '1647' '597' '1686' '1515' '408' '528' '1691' '2040')
>
> Perhaps, as I said, you've just hit a limit on the number of bound
> parameters.
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > 2010/1/8 Martin Evans <martin.ev...@easysoft.com
> > <mailto:martin.ev...@easysoft.com>>
> >
> > fida aljounaidi wrote:
> > >
> > > Hi
> > > thanks for your answer
> > > I mean a request that should return a lots of row. Its exactly a
> large
> > > parameters under the Where In(id1,id2,...,id1000) clause.
> > > I've tried with LongReadLen but it doesn't work.
> >
> > You may have reached a limit on the size of SQL handled if you do
> this
> > sort of thing. Personally, I'd not do what you are doing as I doubt
> it
> > is very efficient and I'd find another way to do this.
> >
> > > i've put a trace() function to have the log. I've noticed a freeing
> > > parameters request before the end of selectall_arrayref()
> execution.
> > > Not an array reference come after this freeing parameters.
> > >
> > > I'm thinking on setting up a paging of a result?
> > > what do you think about that
> > > Fida
> >
> > But we have not indication that the result-set size is the problem
> yet
> > so paging would make no difference.
> >
> > As I said, it may be worth posting a small example as we cannot tell
> > from your description what may be going wrong.
> >
> > Martin
> > --
> > Martin J. Evans
> > Easysoft Limited
> > http://www.easysoft.com
> >
> > > 2010/1/8 Martin Evans <martin.ev...@easysoft.com
> > <mailto:martin.ev...@easysoft.com>
> > > <mailto:martin.ev...@easysoft.com <mailto:
> martin.ev...@easysoft.com>>>
> > >
> > > fida aljounaidi wrote:
> > > > Hi
> > > >
> > > > I have a problem whith one large select request.
> > > >
> > > > A connection is opened by dbi perl but ended without giving
> any
> > > result.
> > > > Th perl script end with an error like
> > > >
> > > > "Not an ARRAY reference"
> > > >
> > > > When i observed mysql log, i found that the connection
> > opened is ended
> > > > without any result.
> > > > When i execute sql query directly into the db server, it
> > succeed.
> > > >
> > > > i didn't find anyt ips about that.
> > > > However i found a kind of definition of buffersize on
> > > dbi:teradata. (a kind
> > > > of parameter to define into dbi->connect instruction.
> > > >
> > > > Any idea about that?
> > > >
> > > > Thanks a lot.
> > > >
> > >
> > > What do you mean by "one large select request" - do you mean
> > it should
> > > return lots of rows or a row contains a column that is very
> large?
> > >
> > > See LongReadLen for retrieving large columns.
> > >
> > > We'd have to see a small self contained example to diagnose
> > where the
> > > "Not an ARRAY reference" is coming from.
> > >
> > > Martin
> > > --
> > > Martin J. Evans
> > > Easysoft Limited
> > > http://www.easysoft.com
> > >
> > >
> >
> >
>