Yes. You are right! That's the problem and it's not :)) I have tested it with very dummy test (see attachment mysqli.php.gz) and it works like a charm (against stormer provided before). So I have discovered that the problem is in framework.
The problem is that result cursor is never closed. Here's what happens in
sample application provided by me in one of previous messages. We have
following code:
$news = new Automobili_Model_Table_News();
$news->fetchAll($news->select());
In other words it can be written like this (to better understand):
/** Zend_Db_Table_Abstract */
$news = new Automobili_Model_Table_News();
/** Zend_Db_Table_Select */
$select = $news->select();
$news->fetchAll($select);
The most interesting part here is fetchAll() which internally
calls protected method _fetch() which is clean enough:
1503 protected function _fetch(Zend_Db_Table_Select $select)
1504 {
1505 $stmt = $this->_db->query($select);
1506 $data = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
1507 return $data;
1508 }
Now, what we have here. This code creates a new object ($stmt) of
Zend_Db_Statement_Mysqli calls it's fetchAll() and returns it's result
(array of rows). And that's where all problems began. Statement
grabs results with standard mysqli functions, but it does not close
cursor automatically!
After I spend another hour trying to implement some easy workarounds,
I found that there are no "easy" workaround at all. Except making some
modifications to Zend_Db_Adapter_Mysqli and Zend_Db_Adapter_Statement.
Of course there's one easy workaround (and I'm pretty sure that some of
developers will say that it's a feature) - you can first, replace that
small code
for rows retrievement with something like this:
$news = new Automobili_Model_Table_News();
$select = $news->select();
$stmt = $news->getAdapter()->query($select);
$this->view->assign('rows', $stmt->fetchAll(Zend_Db::FETCH_OBJ));
$stmt->closeCursor();
$stmt->close();
and then add postDispatch hook with closing db handler:
Zend_Db_Table_Abstract::getDefaultAdapter()->closeConnection();
Unfortunatelly even this caused sample application (from previous post)
to hang server up after 130 requests. I'm gonna try to make some changes
to the Zend_Db_Adapter_Mysqli and Zend_Db_Statement_Mysqli today
or tomorrow and will share my investigation results :))
2010/5/25 Саша Стаменковић <[email protected]>:
> Zend_Db_Statement in setFetchMode() calls $this->closeCursor(); only in
> default case for $mode, why not in other cases?!
>
> Regards,
> Saša Stamenković
>
>
> On Tue, May 25, 2010 at 4:37 PM, Aleksey Zapparov <[email protected]>
> wrote:
>>
>> I'll prepare a dummy test with direct mysqli opertating - without using
>> Zend Framework at all - just to make sure that it's not a framework's
>> error. I'm pretty sure it's not but still want to have solid approvements.
>>
>>
>> 2010/5/25 Aleksey Zapparov <[email protected]>:
>> > Forgotten trace and profiler info.
>> >
>> > 2010/5/25 Aleksey Zapparov <[email protected]>:
>> >> Hello everybody again,
>> >>
>> >> Unfortunately I forgot to add CC to group, so we were discussing
>> >> problem
>> >> with Саша privately :)) But he mentioned that there left only two of
>> >> us,
>> >> so I'm repeating abridged summary of discussion.
>> >>
>> >> First of all, I successfully repeated error mentioned by Саша. It do
>> >> not
>> >> related with database engine (so I was able to successfully repeat it
>> >> with both MyISAM and InnoDB).
>> >>
>> >> I was keeping track of connections, while storming a server. And indeed
>> >> there were only ONE connection to the database. But after a 'storm' (in
>> >> fact 5-10 rapidly repeated requests was enough) server became down.
>> >> I'll
>> >> explain in details a little bit later. Here's dummy stormer in Ruby I
>> >> was using to reproduce an error:
>> >>
>> >>
>> >> require 'rubygems'
>> >> require 'httpclient'
>> >>
>> >> client = HTTPClient.new
>> >> uri = 'http://localhost/zfw'
>> >> status = 'ACTIVE'
>> >>
>> >> (1..128).each do
>> >> status = ('ACTIVE' == status) ? 'INACTIVE' : 'ACTIVE'
>> >> client.post(uri, { 'status' => status }
>> >> end
>> >>
>> >>
>> >> So after running this stormer, assuming 'http://localhost/zfw' is an
>> >> index action of index controller of our application which selects rows
>> >> from database (there were only 32 rows total in database on testing),
>> >> index action started throw Zend_Db_Adapter_Mysqli_Exception all the
>> >> time (until I have restarted Apache2 server). This exception had empty
>> >> message. Here's a trace:
>> >>
>> >> #0 /var/www/zfw-app/library/Zend/Db/Adapter/Abstract.php(304):
>> >> Zend_Db_Adapter_Mysqli->_connect()
>> >> #1 /var/www/zfw-app/library/Zend/Db/Adapter/Mysqli.php(194):
>> >> Zend_Db_Adapter_Abstract->getConnection()
>> >> #2 /var/www/zfw-app/library/Zend/Db/Table/Abstract.php(823):
>> >> Zend_Db_Adapter_Mysqli->describeTable('automobili_news', NULL)
>> >> #3 /var/www/zfw-app/library/Zend/Db/Table/Abstract.php(862):
>> >> Zend_Db_Table_Abstract->_setupMetadata()
>> >> #4 /var/www/zfw-app/library/Zend/Db/Table/Abstract.php(969):
>> >> Zend_Db_Table_Abstract->_setupPrimaryKey()
>> >> #5 /var/www/zfw-app/library/Zend/Db/Table/Select.php(100):
>> >> Zend_Db_Table_Abstract->info()
>> >> #6 /var/www/zfw-app/library/Zend/Db/Table/Select.php(78):
>> >> Zend_Db_Table_Select->setTable(Object(Automobili_Model_Table_News))
>> >> #7 /var/www/zfw-app/library/Zend/Db/Table/Abstract.php(1005):
>> >> Zend_Db_Table_Select->__construct(Object(Automobili_Model_Table_News))
>> >> #8 /var/www/zfw-app/application/controllers/IndexController.php(14):
>> >> Zend_Db_Table_Abstract->select()
>> >> #9 /var/www/zfw-app/library/Zend/Controller/Action.php(513):
>> >> IndexController->indexAction()
>> >> #10
>> >> /var/www/zfw-app/library/Zend/Controller/Dispatcher/Standard.php(289):
>> >> Zend_Controller_Action->dispatch('indexAction')
>> >> #11 /var/www/zfw-app/library/Zend/Controller/Front.php(954):
>> >>
>> >> Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http),
>> >> Object(Zend_Controller_Response_Http))
>> >> #12
>> >> /var/www/zfw-app/library/Zend/Application/Bootstrap/Bootstrap.php(97):
>> >> Zend_Controller_Front->dispatch()
>> >> #13 /var/www/zfw-app/library/Zend/Application.php(366):
>> >> Zend_Application_Bootstrap_Bootstrap->run()
>> >> #14 /var/www/zfw-app/public/index.php(26): Zend_Application->run()
>> >> #15 {main}
>> >>
>> >>
>> >> For those who are interested in details I have attached an xdebug
>> >> trace as an attachment (trace.xt.gz) and xdebug profiler data
>> >> (cachegrind.out.gz).
>> >>
>> >> After all I have switched config to use pdo_mysql instead of mysqli and
>> >> was able to run my (previously described) stormer without any problems
>> >> even with 1024 requests. So I guess there's something wrong with mysqli
>> >> adapter (of PHP).
>> >>
>> >>
>> >> 2010/5/25 Саша Стаменковић <[email protected]>:
>> >>> Zend_Db_Statement ин setFetchMode() calls $this->closeCursor(); only
>> >>> in
>> >>> default case fro $mode ?!
>> >>>
>> >>> Regards,
>> >>> Saša Stamenković
>> >>>
>> >>>
>> >>> On Tue, May 25, 2010 at 11:40 AM, Саша Стаменковић
>> >>> <[email protected]>
>> >>> wrote:
>> >>>>
>> >>>> Cache can be the temporary fix.
>> >>>>
>> >>>> Regards,
>> >>>> Saša Stamenković
>> >>>>
>> >>>>
>> >>>> On Tue, May 25, 2010 at 11:35 AM, Саша Стаменковић
>> >>>> <[email protected]>
>> >>>> wrote:
>> >>>>>
>> >>>>> I found where the problem was!
>> >>>>> $newsTable->publishNews($ids);
>> >>>>> foreach ($newsTable->fetchNewsByIds($ids) as $news) {
>> >>>>> $news->publishOnTwitter();
>> >>>>> }
>> >>>>> Row have this publish on twitter method, which shouldn't have
>> >>>>> nothing to
>> >>>>> do with the problem - WRONG! It has. When I post it on twitter, a
>> >>>>> great
>> >>>>> amount of traffic is generated, people are opening concrete news and
>> >>>>> break
>> >>>>> my limit of 15 connections.
>> >>>>> Looks like I need more connections, heh.
>> >>>>> Regards,
>> >>>>> Saša Stamenković
>> >>>>>
>> >>>>>
>> >>>>> On Tue, May 25, 2010 at 10:57 AM, Саша Стаменковић
>> >>>>> <[email protected]>
>> >>>>> wrote:
>> >>>>>>
>> >>>>>> BTW, my limit is not
>> >>>>>> mysqli.max_links = 15
>> >>>>>> its a property of mysql.user table, MAX_USER_CONNECTIONS.
>> >>>>>> http://dev.mysql.com/doc/refman/5.1/en/user-resources.html
>> >>>>>>
>> >>>>>> Regards,
>> >>>>>> Saša Stamenković
>> >>>>>>
>> >>>>>>
>> >>>>>> On Mon, May 24, 2010 at 11:44 PM, Aleksey Zapparov
>> >>>>>> <[email protected]>
>> >>>>>> wrote:
>> >>>>>>>
>> >>>>>>> Hello,
>> >>>>>>>
>> >>>>>>> Was not able to wait until tomorow to test on FreeBSD as it was
>> >>>>>>> really
>> >>>>>>> interesting for will it work or not. And it does. Here's mysqli
>> >>>>>>> config
>> >>>>>>> of my
>> >>>>>>> php.ini on FreeBSD:
>> >>>>>>>
>> >>>>>>> mysqli.max_links = 15
>> >>>>>>> mysqli.default_port = 3306
>> >>>>>>> mysqli.default_socket =
>> >>>>>>> mysqli.default_host =
>> >>>>>>> mysqli.default_user =
>> >>>>>>> mysqli.default_pw =
>> >>>>>>> mysqli.reconnect = Off
>> >>>>>>>
>> >>>>>>> You can see it's working at: http://sandbox.ixti.ru/zfw/ (it will
>> >>>>>>> be
>> >>>>>>> available
>> >>>>>>> at least until 27th of May 2010).
>> >>>>>>>
>> >>>>>>>
>> >>>>>>> 2010/5/24 Aleksey Zapparov <[email protected]>:
>> >>>>>>> > Hello,
>> >>>>>>> >
>> >>>>>>> > I guess you are doing something wrong. I have just build up a
>> >>>>>>> > little
>> >>>>>>> > app from
>> >>>>>>> > scratch with zf tool (attachment app.tar.gz) which simply
>> >>>>>>> > "batch"
>> >>>>>>> > updates
>> >>>>>>> > 32 rows with new status - very dumy logic in controller:
>> >>>>>>> >
>> >>>>>>> > $news = new Automobili_Model_Table_News();
>> >>>>>>> > $ids = range(1,32);
>> >>>>>>> >
>> >>>>>>> > $news->update(
>> >>>>>>> > array('status' => $status),
>> >>>>>>> > $news->getAdapter()->quoteInto('id IN (?)', $ids,
>> >>>>>>> > Zend_Db::INT_TYPE)
>> >>>>>>> > );
>> >>>>>>> >
>> >>>>>>> > And it works good for me at least on my GNU/Linux.
>> >>>>>>> > Here's my php.ini (section of MySQLi):
>> >>>>>>> >
>> >>>>>>> > mysqli.max_persistent = 15
>> >>>>>>> > mysqli.allow_persistent = Off
>> >>>>>>> > mysqli.max_links = 15
>> >>>>>>> > mysqli.cache_size = 2000
>> >>>>>>> > mysqli.default_port = 3306
>> >>>>>>> > mysqli.default_socket =
>> >>>>>>> > mysqli.default_host =
>> >>>>>>> > mysqli.default_user =
>> >>>>>>> > mysqli.default_pw =
>> >>>>>>> > mysqli.reconnect = Off
>> >>>>>>> >
>> >>>>>>> >
>> >>>>>>> > I have a FreeBSD running host so tomorow I'm gonna check this
>> >>>>>>> > app
>> >>>>>>> > against
>> >>>>>>> > it. Anyway you can try my dummy app by yourself (I've removed
>> >>>>>>> > some
>> >>>>>>> > portion
>> >>>>>>> > from your News table class (which was referring to another
>> >>>>>>> > model) to
>> >>>>>>> > be able
>> >>>>>>> > run this code).
>> >>>>>>> >
>> >>>>>>> > Attached files are:
>> >>>>>>> > app.tar.gz - Application itself
>> >>>>>>> > dump.sql.gz - MySQL dump of table (I have used to test)
>> >>>>>>> >
>> >>>>>>> >
>> >>>>>>> > 2010/5/24 Саша Стаменковић <[email protected]>:
>> >>>>>>> >> Okay, I'm using one connection, one db, one adapter, but still,
>> >>>>>>> >> I
>> >>>>>>> >> have
>> >>>>>>> >> problems. I'm pretty sure I'm using it right, because I'm using
>> >>>>>>> >> it
>> >>>>>>> >> like it
>> >>>>>>> >> says in the doc.
>> >>>>>>> >> The problem is, I can exec up to 15 queries in the row, and
>> >>>>>>> >> this
>> >>>>>>> >> quoteInto
>> >>>>>>> >> with array param is hitting my limits.
>> >>>>>>> >> I can send you my code on private mail Thomas.
>> >>>>>>> >>
>> >>>>>>> >> Regards,
>> >>>>>>> >> Saša Stamenković
>> >>>>>>> >>
>> >>>>>>> >>
>> >>>>>>> >> On Mon, May 24, 2010 at 9:27 PM, Thomas D.
>> >>>>>>> >> <[email protected]>
>> >>>>>>> >> wrote:
>> >>>>>>> >>>
>> >>>>>>> >>> Hi,
>> >>>>>>> >>>
>> >>>>>>> >>> Саша Стаменковић wrote:
>> >>>>>>> >>> > Sure, when you have unlimited number of db operation over
>> >>>>>>> >>> > a period of time. I'll come up with my own offline quoting.
>> >>>>>>> >>>
>> >>>>>>> >>> Seems like you are missing one fact all over the time:
>> >>>>>>> >>> That quoting would use a connection to a database server,
>> >>>>>>> >>> isn't a
>> >>>>>>> >>> problem,
>> >>>>>>> >>> because Zend_Db_* would use one connection across every
>> >>>>>>> >>> component.
>> >>>>>>> >>> Only if
>> >>>>>>> >>> you are working with multiple databases, it might be a
>> >>>>>>> >>> problem,
>> >>>>>>> >>> because you
>> >>>>>>> >>> would have one adapter per database (=nAdapter * 1 Connection
>> >>>>>>> >>> = n
>> >>>>>>> >>> connections)...
>> >>>>>>> >>>
>> >>>>>>> >>> So again:
>> >>>>>>> >>> When you are working with just *one* database, everything
>> >>>>>>> >>> should
>> >>>>>>> >>> work
>> >>>>>>> >>> fine.
>> >>>>>>> >>> If not, *you* are doing something wrong.
>> >>>>>>> >>>
>> >>>>>>> >>> Doing your own quoting is everything but not safe. You should
>> >>>>>>> >>> use
>> >>>>>>> >>> the
>> >>>>>>> >>> adapter's escape function, if your application should be safe.
>> >>>>>>> >>>
>> >>>>>>> >>>
>> >>>>>>> >>> --
>> >>>>>>> >>> Regards,
>> >>>>>>> >>> Thomas
>> >>>>>>> >>>
>> >>>>>>> >>>
>> >>>>>>> >>
>> >>>>>>> >>
>> >>>>>>> >
>> >>>>>>> >
>> >>>>>>> >
>> >>>>>>> > --
>> >>>>>>> > Sincerely yours,
>> >>>>>>> > Aleksey V. Zapparov A.K.A. ixti
>> >>>>>>> > FSF Member #7118
>> >>>>>>> > Mobile Phone: +34 617 179 344
>> >>>>>>> > Homepage: http://www.ixti.ru
>> >>>>>>> > JID: [email protected]
>> >>>>>>> >
>> >>>>>>> > *Origin: Happy Hacking!
>> >>>>>>> >
>> >>>>>>>
>> >>>>>>>
>> >>>>>>>
>> >>>>>>> --
>> >>>>>>> Sincerely yours,
>> >>>>>>> Aleksey V. Zapparov A.K.A. ixti
>> >>>>>>> FSF Member #7118
>> >>>>>>> Mobile Phone: +34 617 179 344
>> >>>>>>> Homepage: http://www.ixti.ru
>> >>>>>>> JID: [email protected]
>> >>>>>>>
>> >>>>>>> *Origin: Happy Hacking!
>> >>>>>>
>> >>>>>
>> >>>>
>> >>>
>> >>>
>> >>
>> >>
>> >>
>> >> --
>> >> Sincerely yours,
>> >> Aleksey V. Zapparov A.K.A. ixti
>> >> FSF Member #7118
>> >> Mobile Phone: +34 617 179 344
>> >> Homepage: http://www.ixti.ru
>> >> JID: [email protected]
>> >>
>> >> *Origin: Happy Hacking!
>> >>
>> >
>> >
>> >
>> > --
>> > Sincerely yours,
>> > Aleksey V. Zapparov A.K.A. ixti
>> > FSF Member #7118
>> > Mobile Phone: +34 617 179 344
>> > Homepage: http://www.ixti.ru
>> > JID: [email protected]
>> >
>> > *Origin: Happy Hacking!
>> >
>>
>>
>>
>> --
>> Sincerely yours,
>> Aleksey V. Zapparov A.K.A. ixti
>> FSF Member #7118
>> Mobile Phone: +34 617 179 344
>> Homepage: http://www.ixti.ru
>> JID: [email protected]
>>
>> *Origin: Happy Hacking!
>
>
--
Sincerely yours,
Aleksey V. Zapparov A.K.A. ixti
FSF Member #7118
Mobile Phone: +34 617 179 344
Homepage: http://www.ixti.ru
JID: [email protected]
*Origin: Happy Hacking!
mysqli.php.gz
Description: GNU Zip compressed data
