We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Prepared statement queries return maximum of 100,000 records per thread

First off, Phalcon is amazing - thank you so much!

I worked on an experiment with it last week and found an interesting problem. If I prepare the sql statement (passing in params), I only get back 100,000 rows per instance/thread of Phalcon. I tried making a loop setting limits and offsets, but you still only get 100,000 rows total returned. (for example, I could request 25,000 rows 4 times, or 10,000 rows 10 times, or 50,000 rows 2 times, but no more then that). This also seems to be a maximum when calling Model::count();

Passing in raw sql with with the parameters hard coded in works just fine.

I was able to work around it, but it was driving me a little nuts.

Thanks again for the amazing work - awesome job!



98.9k

You can add a logger to the connection to watch which SQL stataments are being send to the connection:

https://docs.phalcon.io/en/latest/reference/db.html#logging-sql-statements



103
edited May '14

Hi!

I am using prepared statements with bound parameters (using ?) on MySql DB. I'm trying to log these statements but I can't see the actual values sent to the DB...only question marks.

Tried with $connection->getRealSQLStatement but no chance

Any idea please ? Thank you



98.9k

This way:

$di->set('db', function() {

    $connection = new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host"          => 'localhost',
        "username"      => 'root',
        "password"      => '',
        "dbname"        => 'test'
    ));

    $eventsManager = new \Phalcon\Events\Manager();

    $logger = new \Phalcon\Logger\Adapter\File(APP_PATH . "/app/logs/db.log");

    //Listen all the database events
    $eventsManager->attach('db', function($event, $connection) use ($logger) {
        if ($event->getType() == 'beforeQuery') {
            $sqlVariables = $connection->getSQLVariables();
            if (count($sqlVariables)) {
                $logger->log($connection->getSQLStatement() . ' ' . join(', ', $sqlVariables), Logger::INFO);
            } else {
                $logger->log($connection->getSQLStatement(), Logger::INFO);
            }
        }
    });

    //Assign the eventsManager to the db adapter instance
    $connection->setEventsManager($eventsManager);

    return $connection;
});


103

Well thank you for your reply but this isn't exactly what I wanted.. the parameters are not replacing the question marks, they are just added at the end

Is there a way to log the actual query sent to server ? without any "?"