Welcome Guest, Not a member yet? Register   Sign In
Two Left Joins = Connection Reset Error
#1

[eluser]vbrtrmn[/eluser]
I've got a query which I use two left joins and several inner joins, it consistently returns a connection reset error in firefox and no error message in the apache logs.

If I remove the 'left' arguments the same error does not occur, if I run the SQL from $this->db->last_query() directly in the DB adding the LEFT joins back in, it comes back properly (either in phpMyAdmin or commandline).

The "inner" arguments don't need to be passed, I put them in there to experiment.

Code:
$this->db->select('votes.uid AS voterUID, comments.uid AS voteeUID, voteTypes.id AS voteTypeID, userratingVoter.level AS voterLevel, userratingVotee.level AS voteeLevel');

$this->db->from('votes');

# Join the comments table on comments.id = votes.cid
$this->db->join('comments', 'comments.id = votes.cid', 'inner');

# Join the userauth table for admin WHERE clause below
$this->db->join('userauth',  'userauth.uid = votes.uid', 'inner');

# Join the votesTypes table for the WHERE IN clause below
$this->db->join('voteTypes', 'voteTypes.id = votes.voteid', 'inner');

# Join the userrating table twice once for the Voter once for the Votee
$this->db->join('userrating AS userratingVotee', 'userratingVotee.uid = comments.uid', 'left');
$this->db->join('userrating AS userratingVoter', 'userratingVoter.uid = votes.uid', 'left');

# Where in valid Vote Types
# Valid Vote Types: helpful, interesting, correct, incorrect
#                   5        4            2        7
$validVoteTypes = array(5,4,2,7);
$this->db->where_in('votes.voteid', $validVoteTypes);

# Where vote is active.
$this->db->where('votes.active',1);

# Where timestamp is greater than the last run timestamp.
$this->db->where('votes.timestamp >',$lastrun);

# Only standard user votes count, may have to update this if we get other authids
$this->db->where('userauth.authid',2);

Broken up SQL from $this->db->last_query()... works in phpMyAdmin and from commandline.

Code:
SELECT `votes`.`uid` AS voterUID, `comments`.`uid` AS voteeUID, `voteTypes`.`id` AS voteTypeID, `userratingVoter`.`level` AS voterLevel, `userratingVotee`.`level` AS voteeLevel

FROM (`votes`)

INNER JOIN `comments` ON `comments`.`id` = `votes`.`cid`
INNER JOIN `userauth` ON `userauth`.`uid` = `votes`.`uid`
INNER JOIN `voteTypes` ON `voteTypes`.`id` = `votes`.`voteid`

LEFT JOIN `userrating` AS userratingVotee ON `userratingVotee`.`uid` = `comments`.`uid`
LEFT JOIN `userrating` AS userratingVoter ON `userratingVoter`.`uid` = `votes`.`uid`

WHERE `votes`.`voteid` IN (5, 4, 2, 7)
AND `votes`.`active` = 1
AND `votes`.`timestamp` > '2012-03-01 00:00:00'
AND `userauth`.`authid` = 2

Further if I use a standard query instead of the active record syntax, it produces the same results.

Code:
$sql = "SELECT `votes`.`uid` AS voterUID, `comments`.`uid` AS voteeUID, `voteTypes`.`id` AS voteTypeID, `userratingVoter`.`level` AS voterLevel, `userratingVotee`.`level` AS voteeLevel FROM (`votes`) INNER JOIN `comments` ON `comments`.`id` = `votes`.`cid` INNER JOIN `userauth` ON `userauth`.`uid` = `votes`.`uid` INNER JOIN `voteTypes` ON `voteTypes`.`id` = `votes`.`voteid` LEFT JOIN `userrating` AS userratingVotee ON `userratingVotee`.`uid` = `comments`.`uid` LEFT JOIN `userrating` AS userratingVoter ON `userratingVoter`.`uid` = `votes`.`uid` WHERE `votes`.`voteid` IN (5, 4, 2, 7) AND `votes`.`active` = 1 AND `votes`.`timestamp` > '2012-03-01 00:00:00' AND `userauth`.`authid` = 2";

$query = $this->db->query($sql);

If I use a standard JOIN instead of LEFT JOIN for either the error does not occur, though it doesn't return the data I need.

Server Info:

Apache/2.2.14 (Ubuntu)
PHP/5.3.2
MySQL 5.1.63
CodeIgniter 2.1.2

application/config/database.php

Code:
$active_group = 'default';
$active_record = TRUE;

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'some_user';
$db['default']['password'] = 'some_pass';
$db['default']['database'] = 'some_db';
$db['default']['dbdriver'] = 'mysqli';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;
#2

[eluser]yacman[/eluser]
If the raw sql produces the same disconnect, then you need to check your database logs. Most likely you are running into a memory or disk space issue that kills the connection due timeout.

Can you run the sql from the mysql command prompt and see the execution time or how much data is being generated?
#3

[eluser]vbrtrmn[/eluser]
I have no problem running from the commandline or phpMyAdmin or from Perl.

I get about 49 rows back, each row has 5 columns, which currently look like the following (where the 1's are other single digit numbers).

Code:
1      1      1      null     null
1      1      1      null     null
1      1      1      null     null
1      1      1      null     null

#4

[eluser]yacman[/eluser]
In your application config, you are using the 'mysqli' driver. Try changing that to 'mysql' instead and see if this resolves the issue.

Code:
$db['default']['dbdriver'] = 'mysql';
#5

[eluser]CroNiX[/eluser]
Try turning off pconnect as well. It causes strange problems sometimes.
#6

[eluser]vbrtrmn[/eluser]
Tried all combinations.

Failed:

Code:
$db['default']['dbdriver'] = 'mysql';
$db['default']['pconnect'] = FALSE;

Failed:

Code:
$db['default']['dbdriver'] = 'mysql';
$db['default']['pconnect'] = TRUE;

Failed (original):

Code:
$db['default']['dbdriver'] = 'mysqli';
$db['default']['pconnect'] = TRUE;

Failed:

Code:
$db['default']['dbdriver'] = 'mysqli';
$db['default']['pconnect'] = FALSE;

Checked mysql log, nothing pertinent. Still nothing in apache log. In the CI log there are only DEBUG messages, no errors.




Theme © iAndrew 2016 - Forum software by © MyBB