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