MySQL Analyseproblem

[TDS]

Hallo,

ich hab ein Problem

Und zwar habe ich eine Tabelle:
int id (primary, auto_increment)
int user_id
bigint ip
timestamp time

Nun möchte ich gern alle Datensätze haben die innerhalb von 30 Minuten (time) die gleiche IP-Adresse (ip) aber unterschiedliche User-ID (user_id) haben. Ich habe es mit Sub-Selects probiert - ohne Erfolg. Dann habe ich mit join angefangen, bloß irgendwie geht das bei mir nicht auf nur eine Tabelle bezogen.

Kann mir jemand weiterhelfen?

Aktueller Stand (ich weiß, nur Quark):

$db_order  = '
    SELECT A.user_id, B.user_id, A.ip, B.ip, A.time, B.time FROM
    '.$portal_config['db']['tables']['log_points'].' as A LEFT JOIN '.$portal_config['db']['tables']['log_points'].' AS B
    ON A.ip = B.ip AND A.user_id != B.user_id
    HAVING DATE_SUB(A.time, INTERVAL 60 SECONDS) > 0  
    GROUP BY A.ip
    LIMIT 10
'; 

[Slava]

Nicht getestet!

LEFT JOIN auf INNER JOIN aendern

ON A.ip = B.ip AND A.user_id <> B.user_id
and DATE_ADD(A.time, INTERVAL 30 MINUTE) > B.time and A.time<B.time
LIMIT .....
damit muss du dann die passende datensätze finden.

[TDS]

Aktuelle Query-Anfrage:

SELECT A.user_id AS id1, B.user_id AS id2, A.ip AS ip1, B.ip AS ip2, A.time AS time1, B.time AS time2
FROM portal_log_points AS A
INNER JOIN portal_log_points AS B ON A.ip = B.ip
AND A.user_id <> B.user_id
AND DATE_ADD( A.time, INTERVAL 30
MINUTE ) > B.time
AND A.time < B.time
LIMIT 10

Ergebnis:
phpMyAdmin

Das Problem ist das annähernd zur gleichen Zeit ein User mit 3 Usern gleichzeitig die IP genutzt haben soll. Und der User Nr. 1 bin ich - sehr unwahrscheinlich

[Slava]

Das Problem ist das annähernd zur gleichen Zeit ein User mit 3 Usern gleichzeitig die IP genutzt haben soll. Und der User Nr. 1 bin ich - sehr unwahrscheinlich

Ich könnte mit query helfen, für die Daten bin ich nicht verantwortlich

[TDS]

Ich könnte mit query helfen, für die Daten bin ich nicht verantwortlich

Ich kann dir gern die Daten als SQL Dump geben. Musst nur sagen an welche Mailadresse.

[kuddeldaddeldu]

Hi,

stelle den Dump doch als Anhang hier rein, dann können alle draufschauen. Wenn Du die echten Daten nicht veröffentlichen willst, dann erstelle einen Satz fiktive Beispieldaten, an denen man das Problem nachvollziehen kann.

LG

[TDS]

Okay, hier der komplette Dump (entpackt ~120MB):

www.tds-net.de/portal_log_points.sql.gz

[Slava]

man braucht kein dump.
Qeury sagt eben, dass es tatsächlich zu den gleichen ip mehrere user_ids gibt.
Vermutlich werden die falsche Daten abgespeichert oder die mehrere User kommen über Proxie auf dein Server rein.

[kuddeldaddeldu]

Ich werde hier auch bestimmt keinen 120MB Dump importieren (das ist doch nicht Dein Ernst...) und dann mit einem Theta Selfjoin darauf rumrödeln...

Schau halt einfach mal in Deine Daten.

LG

[TDS]

Ich weiß das AOL über Proxy geht bzw. IP-Sharing betreibt. Aber die user_id Nr. 1 ist meine und ich habe eine unique IP. Also kann das Ergebnis (Query) nicht richtig stimmen. Was braucht ihr denn noch außer den Dump?
Ich kann heute Abend sonst eine Beispiel-Tabelle erstellen mit Einträgen die für's Testen geeignet sind.

Falsche Daten gibt es nicht. Jede User-ID ins eineindeutig. Und die Ip wird so ausgelesen:

function Dot2LongIP($ip)
{
    if (!$ip)
        return 0;
    $ips = split("\.", $ip);
    $result = ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
    return $result;
}

function get_ip()
{
    global $_SERVER;

    if (isSet($_SERVER)) {
        if (isSet($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $realip = $_SERVER['HTTP_X_FORWARDED_FOR'];
        } elseif (isSet($_SERVER['HTTP_CLIENT_IP'])) {
        $realip = $_SERVER['HTTP_CLIENT_IP'];
        } else {
        $realip = $_SERVER['REMOTE_ADDR'];
        }
    }
    else
    {
        if ( getenv( 'HTTP_X_FORWARDED_FOR' ) ) {
        $realip = getenv( 'HTTP_X_FORWARDED_FOR' );
        } elseif ( getenv( 'HTTP_CLIENT_IP' ) ) {
        $realip = getenv( 'HTTP_CLIENT_IP' );
        } else {
        $realip = getenv( 'REMOTE_ADDR' );
        }
    }
    return $realip;
} 

[kuddeldaddeldu]

Hi,

Aber die user_id Nr. 1 ist meine und ich habe eine unique IP. Also kann das Ergebnis (Query) nicht richtig stimmen.

Der Punkt ist, dass wir annehmen, bzw. behaupten, dass nicht die Query, sondern Deine Daten nicht stimmen. Nur, um Dir das zu zeigen, bräuchten wir überhaupt einen Dump. Allerdings hätte Dir dabei klar sein müssen, dass sich niemand wegen eines Forenthreads einen 120MB Dump in die DB jagt. Wieviele Datensätze mögen das sein? Ich möchte nicht wissen, wie lange die Abfrage da läuft...

Ich kann heute Abend sonst eine Beispiel-Tabelle erstellen mit Einträgen die für's Testen geeignet sind.

Gut, mach das. Du könntest natürlich auch einfach mal mit einer simplen Abfrage nach user_id, ip und time in der Datenbank schauen, ob es die angezweifelten Datensätze nicht doch gibt...

LG

[TDS]

Genau das will ich ja heute abend rausbekommen.
Schnell nen Script zum debuggen mit mehr ausgaben und ein paar Datensätze die stimmen müssten.
Ich meld mich heute abend nochmal.

[TDS]

Wir versprochen hier der Dump:

CREATE TABLE IF NOT EXISTS `portal_log_points` (
`id` int(11) unsigned NOT NULL auto_increment,
`user_id` int(11) unsigned NOT NULL default '0',
`ref_id` int(11) unsigned NOT NULL default '0',
`no` tinyint(4) NOT NULL default '0',
`message` varchar(100) default NULL,
`points` double NOT NULL default '0',
`ref_points` double NOT NULL default '0',
`ref_back` double NOT NULL default '0',
`ref_back_percent` tinyint(4) NOT NULL default '0',
`ip` bigint(20) NOT NULL default '0',
`tan` varchar(50) default NULL,
`time` datetime default NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `ref_id` (`ref_id`),
KEY `no` (`no`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `portal_log_points`
--

INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(1, 1, 0, 0, 'Banner', 5, 0, 0, 0, 3232235796, '11.08.2009 20:51,35-1-Banner', '2009-08-11 20:51:35');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(2, 1, 0, 0, 'Banner', 1, 0, 0, 0, 3232235796, '11.08.2009 20:51,35-1-Banner', '2009-08-11 20:51:35');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(3, 1, 0, 0, 'Banner', 5, 0, 0, 0, 3232235796, '11.08.2009 20:51,40-1-Banner', '2009-08-11 20:51:40');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(4, 1, 0, 0, 'Banner', 2, 0, 0, 0, 3232235796, '11.08.2009 20:51,45-1-Banner', '2009-08-11 20:51:45');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(5, 4, 557, 0, 'Forced-Banner', 0.50625, 0.01875, 0.00625, 25, 3232235796, '11.08.2009 20:52,57-4-Forced-Banner', '2009-08-11 20:52:57');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(6, 4, 557, 0, 'Forced-Banner', 0.50625, 0.01875, 0.00625, 25, 3232235796, '11.08.2009 20:52,59-4-Forced-Banner', '2009-08-11 20:52:59');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(7, 4, 557, 0, 'Klick-Jackpot', 76.1393925, 2.8199775, 0.9399925, 25, 3232235796, '11.08.2009 20:53,04-4-Klick-Jackpot', '2009-08-11 20:53:04');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(8, 4, 557, 0, 'Forced-Banner', 0.50625, 0.01875, 0.00625, 25, 3232235796, '11.08.2009 20:53,04-4-Forced-Banner', '2009-08-11 20:53:04');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(9, 4, 557, 0, 'Banner', 2.025, 0.075, 0.025, 25, 3232235796, '11.08.2009 20:53,20-4-Banner', '2009-08-11 20:53:20');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(10, 1, 0, 0, 'Banner', 5, 0, 0, 0, 3232235795, '11.08.2009 19:51,33-1-Banner', '2009-08-11 20:51:33');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(11, 1, 0, 0, 'Banner', 5, 0, 0, 0, 3232235795, '11.08.2009 19:51,35-1-Banner', '2009-08-11 20:51:35');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(12, 1, 0, 0, 'Banner', 1, 0, 0, 0, 3232235795, '11.08.2009 19:51,35-1-Banner', '2009-08-11 20:51:35');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(13, 1, 0, 0, 'Banner', 5, 0, 0, 0, 3232235795, '11.08.2009 19:51,40-1-Banner', '2009-08-11 20:51:40');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(14, 1, 0, 0, 'Banner', 2, 0, 0, 0, 3232235795, '11.08.2009 19:51,45-1-Banner', '2009-08-11 20:51:45');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(15, 4, 557, 0, 'Forced-Banner', 0.50625, 0.01875, 0.00625, 25, 3232235797, '11.08.2009 20:52,57-4-Forced-Banner', '2009-08-11 21:52:57');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(16, 4, 557, 0, 'Forced-Banner', 0.50625, 0.01875, 0.00625, 25, 3232235797, '11.08.2009 20:52,59-4-Forced-Banner', '2009-08-11 21:52:59');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(17, 4, 557, 0, 'Klick-Jackpot', 76.1393925, 2.8199775, 0.9399925, 25, 3232235797, '11.08.2009 20:53,04-4-Klick-Jackpot', '2009-08-11 21:53:04');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(18, 4, 557, 0, 'Forced-Banner', 0.50625, 0.01875, 0.00625, 25, 3232235797, '11.08.2009 20:53,04-4-Forced-Banner', '2009-08-11 21:53:04');
INSERT INTO `portal_log_points` (`id`, `user_id`, `ref_id`, `no`, `message`, `points`, `ref_points`, `ref_back`, `ref_back_percent`, `ip`, `tan`, `time`) VALUES(19, 4, 557, 0, 'Banner', 2.025, 0.075, 0.025, 25, 3232235797, '11.08.2009 20:53,20-4-Banner', '2009-08-11 21:53:20');

Und hier das Testscript (bitte anpassen):

<?
$db_order  = '
    SELECT A.user_id as id1, B.user_id as id2, A.ip, B.ip, A.time as time1, B.time as time2
    FROM
    '.$portal_config['db']['tables']['log_points'].' as A
    INNER JOIN
    '.$portal_config['db']['tables']['log_points'].' AS B
    ON
    A.ip = B.ip AND A.user_id != B.user_id and
    DATE_ADD(A.time, INTERVAL 30 MINUTE) > B.time and A.time < B.time
    LIMIT 10
';
$db_res = $mysql->query($db_order, NULL, __FILE__, __LINE__);
?>
<table border="0" cellspacing="0" cellpadding="2" align="center" width="90%">
<?
$count = false;

while ($info = @mysql_fetch_array($db_res))
{
    if ($count == false)
    {
        echo '<tr style="background-color: silver">';
        $keys = array_keys($info);
        for($i =0; $i < count($keys); $i++)
            echo '<td valign="top" style="font-weight: bold;">'.$keys[$i].'</td>'; 
        echo '</tr>
        ';
        $count = true;
    }
    echo '<tr>';
    foreach ($info as $i)
        echo '<td valign="top">'.$i.'</td>';
    echo '</tr>
    ';
}

?>
</table>

[kuddeldaddeldu]

So, und was hat die Abfrage jetzt ergeben, was Du bei den Daten nicht erwartet hättest?

LG

[TDS]

So, und was hat die Abfrage jetzt ergeben, was Du bei den Daten nicht erwartet hättest?

LG

Ähm, ja *schäm* Funktioniert!

Hab mal PHP analysiert. Wenn normale IP-Adressen genommen werden ist alles kein Problem, nur manchmal steht dort "IP (noch eine IP)". Und das obwohl schon Proxy und Co. gefiltert werden soweit es geht. Ab und an werden auch ganz krumme Sachen übermittelt. Wieso kann so etwas passieren?