I'm using both MySQL 4.0.12 and MySQL 4.0.2-alpha.
My problem is that, after a while, LEFT JOINs start giving me the wrong
answer. I have a repro case that involves running my server for quite a
while, but I haven't extracted out the minimal case for the server.
In any case, this is the query that's causing trouble:
SELECT DISTINCT p.name, p.description, p.server_name, p.host_name,
p.domain_name, cp.active, t.roles, c.name
FROM peer p, community c, community_peer cp
LEFT JOIN transport t ON (t.oid_sender = cp.oid)
WHERE cp.oid_peer = p.oid
AND cp.oid_community = c.oid
AND c.name = 'zulu' order by 2
Early in a session, the t.roles column is filled in correctly with values.
After the server has been running for a while, t.roles returns all NULLs.
Restarting (but not reloading the data) causes the server to start working
again.
Here's the result late in the session:
mysql> select p.name, p.host_name, cp.active, t.roles, c.name
-> FROM peer p, community c, community_peer cp
-> LEFT JOIN transport t ON (t.oid_sender = cp.oid)
-> where cp.oid_peer = p.oid and cp.oid_community = c.oid and c.name =
'zulu'
-> ;
+-----------------+----------------+--------+-------+------+
| name | host_name | active | roles | name |
+-----------------+----------------+--------+-------+------+
| zulu pub | zulu | 0 | NULL | zulu |
| zulu_cm | mshulman-gx110 | 1 | NULL | zulu |
| David | david | 0 | NULL | zulu |
| mshulman_laptop | mshulman | 0 | NULL | zulu |
+-----------------+----------------+--------+-------+------+
4 rows in set (0.00 sec)
Note that all of the "roles" are NULL.
Then, I stop the MySQL Service and restart it, and re-issue the query.
+-----------------+----------------+--------+-------+------+
| name | host_name | active | roles | name |
+-----------------+----------------+--------+-------+------+
| David | david | 0 | 1 | zulu |
| mshulman_laptop | mshulman | 0 | NULL | zulu |
| zulu pub | zulu | 0 | 1 | zulu |
| zulu_cm | mshulman-gx110 | 1 | 2 | zulu |
+-----------------+----------------+--------+-------+------+
4 rows in set (0.05 sec)
The EXPLAINS tell the whole story. Sorry about the formatting.
When the query gives me the wrong answer, EXPLAIN looks like this:
mysql> explain select p.name, p.host_name, cp.active, t.roles, c.name
-> FROM peer p, community c, community_peer cp
-> LEFT JOIN transport t ON (t.oid_sender = cp.oid)
-> where cp.oid_peer = p.oid and cp.oid_community = c.oid and c.name =
'zulu';
+-------+--------+----------------------------+-----------------+---------+-
------------+------+-------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+-------+--------+----------------------------+-----------------+---------+-
------------+------+-------------+
| t | system | sender_index | NULL | NULL |
NULL | 1 | |
| c | ALL | PRIMARY | NULL | NULL |
NULL | 1 | Using where |
| cp | ref | peer_index,community_index | community_index | 36 |
c.oid | 1 | |
| p | eq_ref | PRIMARY | PRIMARY | 36 |
cp.oid_peer | 1 | |
+-------+--------+----------------------------+-----------------+---------+-
------------+------+-------------+
4 rows in set (0.00 sec)
Note that it thinks that "t" is a system (or const) table, and that it has
to do a table scan of "c".
Then, after restarting the service:
mysql> explain select p.name, p.host_name, cp.active, t.roles, c.name
-> FROM peer p, community c, community_peer cp
-> LEFT JOIN transport t ON (t.oid_sender = cp.oid)
-> where cp.oid_peer = p.oid and cp.oid_community = c.oid and c.name =
'zulu';
+-------+--------+----------------------------+--------------+---------+----
---------+------+-------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-------+--------+----------------------------+--------------+---------+----
---------+------+-------------+
| c | ALL | PRIMARY | NULL | NULL |
NULL | 1 | Using where |
| cp | ALL | peer_index,community_index | NULL | NULL |
NULL | 4 | Using where |
| p | eq_ref | PRIMARY | PRIMARY | 36 |
cp.oid_peer | 1 | |
| t | ref | sender_index | sender_index | 36 |
cp.oid | 1 | |
+-------+--------+----------------------------+--------------+---------+----
---------+------+-------------+
4 rows in set (0.00 sec)
My questions:
1. I need to use a left join because a record might not be in the
"transport" table. Is there another way to write this query?
2. Are there known issues with LEFT JOINs? I see scattered references on
devshed. Would it be helpful to have a full repro case for this problem?
Thanks in advance!
-ms
