Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

OK, I'm flummoxed.(i've asked this question over on Stack too - but I need to get it fixed so I'm asking here too - any help is GREATLY appreciated) I'm trying to execute a query on a database (locally) and I keep getting a connection reset error. I've been using the method below in a generic DAO class to build a query string and pass to Zend_Db API.

public function insert($params) {
    $loop = false;
    $keys = $values = '';
    foreach($params as $k => $v){
        if($loop == true){
            $keys   .= ',';
            $values .= ',';
        }
        $keys   .= $this->db->quoteIdentifier($k);
        $values .= $this->db->quote($v);
        $loop = true;
    }

    $sql = "INSERT INTO " . $this->table_name . " ($keys) VALUES ($values)";

    //formatResult returns an array of info regarding the status and any result sets of the query
    //I've commented that method call out anyway, so I don't think it's that
    try {
        $this->db->query($sql);
        return $this->formatResult(array(
                true,
                'New record inserted into: '.$this->table_name
        ));
    }catch(PDOException $e) {
        return $this->formatResult($e);
    }
}

So far, this has worked fine - the errors have been occurring since we generated new tables to record user input. The insert string looks like this:

INSERT INTO tablename(`id`,`title`,`summary`,`description`,`keywords`,`type_id`,`categories`) VALUES ('5539','Sample Title','Sample content','
\'Lorem ipsum dolor sit amet, consectetur adipiscing elit. In et pellentesque mauris. Curabitur hendrerit, leo id ultrices pellentesque, est purus mattis ligula, vitae imperdiet neque ligula bibendum sapien. Curabitur aliquet nisi et odio pharetra tincidunt. Phasellus sed iaculis nisl. Fusce commodo mauris et purus vehicula dictum. Nulla feugiat molestie accumsan. Donec fermentum libero in risus tempus elementum aliquam et magna. Fusce vitae sem metus. Aenean commodo pharetra risus, nec pellentesque augue ullamcorper nec. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Nullam vel elit libero. Vestibulum in turpis nunc.\'','this,is,a,sample,array',1,'category title') 

Here are the parameters it's getting before assembling the query (var_dump):

array
    'id' => string '1' (length=4)
    'title' => string 'Sample Title' (length=12)
    'summary' => string 'Sample content' (length=14)
    'description' => string '<p>'Lorem ipsum dolor sit amet, consectetur adipiscing elit. In et pellentesque mauris. Curabitur hendrerit, leo id ultrices pellentesque, est purus mattis ligula, vitae imperdiet neque ligula bibendum sapien. Curabitur aliquet nisi et odio pharetra tincidunt. Phasellus sed iaculis nisl. Fusce commodo mauris et purus vehicula dictum. Nulla feugiat molestie accumsan. Donec fermentum libero in risus tempus elementum aliquam et magna. Fusce vitae sem metus. Aenean commodo pharetra risus, nec pellentesque augue'... (length=677)
    'keywords' => string 'this,is,a,sample,array' (length=22)
    'type_id' => int 1
    'categories' => string 'category title' (length=43)

The next port of call was checking the limits on the table, since it seems to insert if the length of "description" is around the 300 mark (it varies between 310 - 330). The field limit is set to VARCHAR(1500) and the validation on this field won't allow anything past bigger than 1200 with HTML, 800 without.

The real kicker is that if I take this sql string and execute it via the command line, it works fine - so I can't for the life of me figure out what's wrong.

I've tried extending the server parameters i.e. http://stackoverflow.com/questions/1964554/unexpected-connection-reset-a-php-or-an-apache-issue

So, in a nutshell, I'm stumped. Any ideas?

share|improve this question

3 Answers 3

If the generated SQL works when you copy/paste it, I'd be sorely tempted to say it's an issue with your code.

I have a similar DB class, which inserts a record into a log table if an SQL error occurs. I once modified the routine and it did something similar to which you describe, because it generated an SQL error, which called the function, which generated an SQL error, which called the function.... you get the idea :)

What does your $this->db->query($sql); function do? It might be worth looking to see if it does something similar, or just does a straight mysql_query

share|improve this answer
    
That's a call to the Zend API. I've executed the same script on the live server and it works fine, so I reckon it's something to do with my local server. –  sunwukung May 21 '10 at 11:51
    
OK - for kicks, could you replace $this->db->query($sql) with simply mysql_query($sql) to see if it still crashes? –  Ben Pilbrow May 21 '10 at 11:57
    
yup, that works... –  sunwukung May 21 '10 at 12:08
    
Right, well if that works without crashing then it's definitely a code issue. I've noticed the insert function returns something - is the code which handles that return maybe the culprit? –  Ben Pilbrow May 21 '10 at 12:11
    
I've taken the sql statement it generates and moved it into a test file, and the reset is still occurring, so it's not the returning method. Cheers for your help, I'll do some profiling/logging to check what's happening –  sunwukung May 21 '10 at 13:12
up vote 1 down vote accepted

OK, for anyone else experiencing this problem - the issue is to do with Zend_Db_Statement - or rather the preg library that this component relies on. It's been flagged in the Zend issue tracker: http://framework.zend.com/issues/browse/ZF-8399, but its not strictly a problem with the Zend lib. You may or may not experience this problem depending on your server environment. In my case, this error occurred on:

Win XP, Wamp 2 PHP 5.3

It can be resolved by using

db->getConnection>exec($sql)

which effectively executes the sql directly via the adapter (PDO etc). Alternatively, you can modify your server to rectify segfaulting in PCRE itself (though I confess I'm not entirely sure how you'd go about this). Hope that helps anyone else with the same issue

share|improve this answer
    
Thank you so very much, especially for the link to framework.zend.com. This type of things destroy productivity in programming, and you alleviated this situation so much. Thanks! –  user57502 Oct 18 '10 at 20:22

Have you tried looking at the MySQL error log? I have also seen this error when MySQL client and server versions are mismatched (5,5.x client to 5.0,x server etc) and you are executing a query that is not valid in the server version, but is valid with the current client version. If that is the case I would check to make sure the version of the Zend API you are using is compatible with the MySQL server version you are using. The MySQL error log on the server should at least zero you into the server crashing or the client.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.