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 <[email protected]
> <mailto:[email protected]>>
>
> 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 <[email protected]
> <mailto:[email protected]>
> > <mailto:[email protected] <mailto:[email protected]>>>
> >
> > 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
> >
> >
>
>
