Hello everybody, I have reported this as a MySQLi bug at: http://bugs.php.net/bug.php?id=51909 Please if somebody think (or know) that I was doing something wrong upon my tests feel free to point it out - I'll try them with your proposed solution and will change my mind if I was wrong ;))
Пожалуйста, Саша! :)) * * You are welcome, Саша! :)) 2010/5/25 Саша Стаменковић <[email protected]>: > Oh my, if I would have no limits on shared hosting, we would never find out! > Switched to PDO. > Спасибо Алексей! > > Regards, > Saša Stamenković > > > On Tue, May 25, 2010 at 10:01 PM, Aleksey Zapparov <[email protected]> > wrote: >> >> Suddenly I have changed my mind ;)) It's not a problem of framework at >> all. >> It's a problem in mysqli itself. Seems like my browser cached data, so >> I've >> found that mysqli.php (from previous message) is hanging server too (!) >> >> Anyway I think Zend_Db_Statement_Mysqli needs a simple destructor: >> >> public function __destruct() >> { >> $this->close(); >> } >> >> Unfortunately it will not help anyway. I'm going to bug report to PHP as >> it's >> a problem with mysqli extension. >> >> >> 2010/5/25 Aleksey Zapparov <[email protected]>: >> > 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! >> > >> >> >> >> -- >> 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!
