[MySQL] PHP geeft ander resultaat dan handmatig

[JaFFoG]

Ik heb een webapplicatie die op basis van username en password een gebruiker in de database (MySQL) selecteert. Dit gebeurt op de volgende manier:

(Query is vereenvoudigd)

PHP:

            $query = sprintf("
                SELECT        usr.id
                            , usr.username
                            , usr.password
                            , CONCAT(usr.firstname, ' ', usr.lastname) AS 'title'
                FROM        users usr
                
                INNER JOIN  users_in_usergroups uiu
                    ON      uiu.usr_id = usr.id
                INNER JOIN  usergroups usg
                    ON      usg.id = uiu.usg_id
                INNER JOIN  rights_in_usergroups riu
                    ON      riu.usg_id = usg.id
                INNER JOIN  rights rgt
                    ON      rgt.id = riu.rgt_id
                    
                WHERE       usr.username = '%s'
                AND         usr.password = '%s'
                AND         usr.active = 1
                AND         usg.active = 1
                "
                
                , $username
                , $password
            );

Het probleem is dat PHP een rij zou moeten retourneren. Als ik de string in $query print naar de browser en deze in mijn MySQL tool (SQLyog) uitvoer, dan krijg ik één rij terug: de beoogde rij. In PHP krijg ik niks terug.
Haal ik echter de INNER JOIN's en alles in het WHERE-statement weg uit de query in PHP, dan retourneert hij wel een berg data.

Wat ik heb gecontroleerd:

• Gebruik ik dezelfde MySQL server? Ja
• Gebruik ik dezelfde database? Ja
• Verbind ik met de MySQL server met dezelfde user/pass? Ja
• Doet de query het zelf wel? Ja (geeft in PHP 0 rijen, in SQLyog 1 rij)
• Is mijn code verder foutloos? Ja

Wat waarschijnlijk nog relevant is, is het feit dat dit vanmorgen nog zonder problemen werkte, maar ik vanmiddag de inhoud van de database (de database zelf dus NIET) heb overschreven i.v.m. grootschalige wijzigingen in de structuur. De tabellen in kwestie zijn echter niet gewijzigd in de structuur (alleen opnieuw aangemaakt dus).
"Overschrijven is sneller" dacht ik, en "het is toch maar een ontwikkeldatabase". If I've ever regret something, this is it.

Verder maak ik bij al mijn tabellen gebruik van het type InnoDB.

Mijn vraag: heeft iemand hier een idee waar het nog meer aan zou kunnen liggen?

[ Voor 1% gewijzigd door JaFFoG op 13-08-2008 15:31 . Reden: Inhoud DB overschreven, DB zelf niet! ]

[TheRookie]

hoe controleer je in je php script of je een rij terugkrijgt ?

PHP:

$result=mysql_query($query) or die(mysql_error());
$numRows = mysql_numrows($result);

of iets dergelijks ?

@sky-:

Als ik de string in $query print naar de browser en deze in mijn MySQL tool (SQLyog) uitvoer, dan krijg ik één rij terug

[ Voor 27% gewijzigd door TheRookie op 13-08-2008 14:57 . Reden: mysql_ niet mssql_ ]

[sky-]

Heb je je query als eens geëchod ?

PHP:

<?php
            $query = sprintf("
                SELECT          usr.id
                            , usr.username
                            , usr.password
                            , CONCAT(usr.firstname, ' ', usr.lastname) AS 'title'
                FROM        users usr
                
                INNER JOIN    users_in_usergroups uiu
                    ON        uiu.usr_id = usr.id
                INNER JOIN    usergroups usg
                    ON        usg.id = uiu.usg_id
                INNER JOIN    rights_in_usergroups riu
                    ON        riu.usg_id = usg.id
                INNER JOIN    rights rgt
                    ON        rgt.id = riu.rgt_id
                    
                WHERE        usr.username = '%s'
                AND            usr.password = '%s'
                AND            usr.active = 1
                AND            usg.active = 1
                "
                
                , $username
                , $password
            );

echo $query;
?>

Wat krijg je dan ? Zijn alle beide waardes etc ingevuld ?

Aha, niet gezien. Sorry :x

[JaFFoG]

TheRookie schreef op woensdag 13 augustus 2008 @ 14:56:
hoe controleer je in je php script of je een rij terugkrijgt ?

PHP:

$result=mysql_query($query) or die(mysql_error());
$numRows = mysql_numrows($result);

of iets dergelijks ?

Via mysql_num_rows() ja.

sky- schreef op woensdag 13 augustus 2008 @ 14:56:
Heb je je query als eens geëchod ?

Zoals TheRookie al aangaf: ja. Lees nog maar even mijn eerste post goed door.

Wat krijg je dan ? Zijn alle beide waardes etc ingevuld ?

Beide waardes zijn goed ingevuld, en de query klopt.

[GlowMouse]

Werken andere queries wel, en hoe zit het met character encoding?

[ Voor 7% gewijzigd door GlowMouse op 13-08-2008 15:11 ]

[Verwijderd]

Gebruik je in PHP ook dezelfde user (met dus dezelfde rechten) als in je mysqltool voor de dbconnectie?

[TheRookie]

Nog een (waarschijnlijk overbodige) vraag, neem aan dat error_reporting op E_ALL staat ?
Zou je, voor het 'gemak' ook de regels waarin je de query runt en de resultaten ophaalt kunnen posten ?

Heb je ook al naar de source v/d pagina waarop je de query echo't gekeken, of print(<pre>$query</pre>"); gedaan ?
Misschien dat er iets qua formatting of bijzondere tekens roet in het eten gooit ?

[Verwijderd]

Waarschijnlijk ook overbodig maar je weet maar nooit, heb je na die wijzigingen van je database de server opnieuw gestart (dus niet alleen mysql)?

[storeman]

Er kan een verschil zitten door de driver, ik heb het afgelopen week ondervonden met een

code:

SELECT * FROM table WHERE field IN ( SELECT a FROM b WHERE c=c )

Ik mocht niet meerdere velden ophalen om de een of andere reden terwijl phpmyadmin het allemaal prima vond, vond Zend_Framework (gebruikt de PDO driver in mijn geval) het toch niet tof vond.

[Voutloos]

[quote]JaFFoG schreef op woensdag 13 augustus 2008 @ 14:50:
• Is mijn code verder foutloos? JaLaat de relevante code maar zien dan. Blijkbaar toch niet zo foutloos mbt foutafhandeling, error reporting, controleren van alle return values van system calls (bv. mysql* funcs) etc.

[BCC]

JaFFoG schreef op woensdag 13 augustus 2008 @ 14:50:

Wat waarschijnlijk nog relevant is, is het feit dat dit vanmorgen nog zonder problemen werkte, maar ik vanmiddag de database heb overschreven i.v.m. grootschalige wijzigingen in de structuur. De tabellen in kwestie zijn echter niet gewijzigd in de structuur (alleen opnieuw aangemaakt dus).
"Overschrijven is sneller" dacht ik, en "het is toch maar een ontwikkeldatabase". If I've ever regret something, this is it.

Je verbind PHP met hetzelfde username/password met je MYSQL als je MYSQL commandline, maar gelden alle rechten ook voor alle hosts? Het klinkt alsof je geen rechten meer hebt om te selecten op de database oid.

offtopic:
Ik hoop dat je implementatie iets anders is, want je hebt met deze constructie een waanzinnig veiligheidslek. Mijn username is namelijk"'OR 1='1"

[ Voor 4% gewijzigd door BCC op 13-08-2008 15:26 ]

[JaFFoG]

GlowMouse schreef op woensdag 13 augustus 2008 @ 15:11:
Werken andere queries wel, en hoe zit het met character encoding?

Een andere query werkt inderdaad ook niet lekker. Deze staat hieronder:

PHP:

    $query = sprintf("
        SELECT        COALESCE(til.translation, til_def.translation) AS 'text'
        FROM            texts_in_languages til_def
        
        LEFT OUTER JOIN texts_in_languages til
            ON          til.tts_code = til_def.tts_code
            AND         til.lgg_id = %2\$d
        
        WHERE           til_def.tts_code = '%3\$s'
        AND             til_def.lgg_id = %1\$d
        "
        , DEFAULT_LANGUAGE
        , $lgg_id
        , $key
    );

Ook deze query heb ik geprint en getest in SQLyog, met hetzelfde resultaat als de eerste: in SQLyog wel een rij geretourneerd, in PHP niks.

Verwijderd schreef op woensdag 13 augustus 2008 @ 15:11:
Gebruik je in PHP ook dezelfde user (met dus dezelfde rechten) als in je mysqltool voor de dbconnectie?

Ja. Staat ook in mijn startpost.

TheRookie schreef op woensdag 13 augustus 2008 @ 15:12:
Nog een (waarschijnlijk overbodige) vraag, neem aan dat error_reporting op E_ALL staat ?
Zou je, voor het 'gemak' ook de regels waarin je de query runt en de resultaten ophaalt kunnen posten ?

error_reporting staat inderdaad op E_ALL.

Code staat hieronder (uiteraard gigantisch versimpeld, aangezien ik met objecten werk en anders drie complete bestanden moet posten).

PHP:

$result = mysql_query($query) or trigger_error("blabla"); # Even verkort...
if (mysql_num_rows($result) === 0) {
  echo "niks";
} else {
  echo "wel iets";
}

Heb je ook al naar de source v/d pagina waarop je de query echo't gekeken, of print(<pre>$query</pre>"); gedaan ?
Misschien dat er iets qua formatting of bijzondere tekens roet in het eten gooit ?

Heb het met <pre> gedaan, die gekopiëerd en gedraaid in SQLyog. Speciale tekens is ook het enige wat ik kan bedenken, maar ik heb niks kunnen vinden. De encoding van de tabellen is exact hetzelfde als vanmorgen en aan de web- en databaseserver is verder niks gewijzigd vandaag, op welk vlak dan ook.

Verwijderd schreef op woensdag 13 augustus 2008 @ 15:17:
Waarschijnlijk ook overbodig maar je weet maar nooit, heb je na die wijzigingen van je database de server opnieuw gestart (dus niet alleen mysql)?

Nee, dit heb ik niet gedaan. Is dit nodig dan? Na het toevoegen van tabellen aan een database is toch geen restart nodig?

[JaFFoG]

storeman schreef op woensdag 13 augustus 2008 @ 15:23:
Er kan een verschil zitten door de driver, (...).

Dat zou kunnen, maar in deze tabellen is niks gewijzigd, op welk vlak (datatypes, encoding, tabeltype, foreign- en primary keys, etc.) dan ook.

Voutloos schreef op woensdag 13 augustus 2008 @ 15:23:
[...]
Laat de relevante code maar zien dan. Blijkbaar toch niet zo foutloos mbt foutafhandeling, error reporting, controleren van alle return values van system calls (bv. mysql* funcs) etc.

Die staat er inmiddels. Maar als je mijn startpost (goed) had gelezen dan wist je al dat de query wel geaccepteerd en uitgevoerd wordt, maar hij gewoon 0 rijen terug geeft.

BCC schreef op woensdag 13 augustus 2008 @ 15:25:
Verbind PHP met hetzelfde username/password met je MYSQL als je MYSQL commandline? Het klinkt alsof je geen rechten meer hebt om te selecten op de database oid.

offtopic:
Ik hoop dat je implementatie iets anders is, want je hebt met deze constructie een waanzinnig veiligheidslek. Mijn username is namelijk"'OR 1='1"

Ja. Staat ook in mijn startpost. En de implementatie is uiteraard anders, maar flink versimpeld (ook dat staat er bij).
Wat rechten betreft: de database zélf is niet overschreven, alleen de inhoud. (Alle tabellen zijn verwijderd en opnieuw aangemaakt met alle daarbij horende relaties.) Ik snap dat dit misschien verwarrend of onduidelijk is uitgelegd in mijn startpost, heb het inmiddels verduidelijkt.

[ Voor 12% gewijzigd door JaFFoG op 13-08-2008 15:33 ]

[Verwijderd]

JaFFoG schreef op woensdag 13 augustus 2008 @ 15:26:
[...]

Nee, dit heb ik niet gedaan. Is dit nodig dan? Na het toevoegen van tabellen aan een database is toch geen restart nodig?

In principe niet nodig voor die tabellen, maar ik weet niet waar je op doelde met 'de database heb overschreven i.v.m. grootschalige wijzigingen in de structuur'. Kwam bij mij iig over als mysqlfiles gekopieerd van server1 naar server2 zeg maar, en dan is het slim mysql wel even te restarten.

edit
Dat was het dus niet, duidelijk

Geen andere pc of server waar je het op kan uittesten? Weet je in ieder geval wat je kan uitsluiten

[ Voor 12% gewijzigd door Verwijderd op 13-08-2008 15:35 ]

[JaFFoG]

Verwijderd schreef op woensdag 13 augustus 2008 @ 15:33:
[...]

In principe niet nodig voor die tabellen, maar ik weet niet waar je op doelde met 'de database heb overschreven i.v.m. grootschalige wijzigingen in de structuur'. Kwam bij mij iig over als mysqlfiles gekopieerd van server1 naar server2 zeg maar, en dan is het slim mysql wel even te restarten.

Ah oke. Bedankt voor de tip, maar dit is niet het geval. Ik heb een datamodel in MySQL Workbench waaruit ik SQL heb gegenereerd. Deze heb ik uitgevoerd op de betreffende database, wat goed gegaan lijkt te zijn. Alle tabellen staan er netjes in, de relaties kloppen, encoding, tabeltypes, etc. Queries gaan ook goed in SQLyog, maar PHP geeft voor exact dezelfde queries andere resultaten.

[Verwijderd]

JaFFoG schreef op woensdag 13 augustus 2008 @ 15:36:
[...]
...maar PHP geeft voor exact dezelfde queries andere resultaten.

Begrijp ik nu dat dit niet de enige query is waar iets mis mee is?

[GlowMouse]

Je negeert de helft van mijn post: hoe zit het met de character encoding? Werkt dezelfde query zonder where wel goed bijvoorbeeld.

[JaFFoG]

Verwijderd schreef op woensdag 13 augustus 2008 @ 15:41:
[...]

Begrijp ik nu dat dit niet de enige query is waar iets mis mee is?

Ja, dat begrijp je goed. Ook de andere query die ik gepost heb geeft wél problemen in PHP en niét in SQLyog.

[ Voor 13% gewijzigd door JaFFoG op 13-08-2008 16:32 ]

[JaFFoG]

GlowMouse schreef op woensdag 13 augustus 2008 @ 15:44:
Je negeert de helft van mijn post: hoe zit het met de character encoding? Werkt dezelfde query zonder where wel goed bijvoorbeeld.

Zonder WHERE werkt de query ook niet. Ik moet de INNER JOIN's én het WHERE statement weghalen. In de caracter encoding van zowel de browser als op de servers is niks gewijzigd.

[JaFFoG]

Heb inmiddels uitvoerig overleg gehad met de systeembeheerder hier en het lijkt als volgt te omschrijven:

1. Lege database
2. Maak tabellen aan met daarin bij één tabel ook een INSERT
3. Deze wordt door zowel PHP als SQLyog getoond
4. Voer nog een INSERT uit op die zelfde tabel
5. Het record uit de tweede INSERT wordt niet door PHP getoond, wel door SQLyog (het eerste record nog wel)

We hebben de MySQL server instellingen gecontroleerd en daar staat caching uit, dus het lijkt met PHP te maken te hebben. Een instelling van PHP misschien, of iets met de mysql driver die PHP gebruikt. Maar zoals ik al heb gezegd is daar niks in gewijzigd vandaag.

[rrrandy]

JaFFoG schreef op woensdag 13 augustus 2008 @ 16:44:
Heb inmiddels uitvoerig overleg gehad met de systeembeheerder hier en het lijkt als volgt te omschrijven:

1. Lege database
2. Maak tabellen aan met daarin bij één tabel ook een INSERT
3. Deze wordt door zowel PHP als SQLyog getoond
4. Voer nog een INSERT uit op die zelfde tabel
5. Het record uit de tweede INSERT wordt niet door PHP getoond, wel door SQLyog (het eerste record nog wel)

We hebben de MySQL server instellingen gecontroleerd en daar staat caching uit, dus het lijkt met PHP te maken te hebben. Een instelling van PHP misschien, of iets met de mysql driver die PHP gebruikt. Maar zoals ik al heb gezegd is daar niks in gewijzigd vandaag.

Gebruik je innodb? Heb je wel een commit gedaan?

[Voutloos]

Laat nou eens de échte mysql_query aanroep inc vervolg zien, want je doet gewoon wél iets fout. Je reageert echt slecht op debug hints. Foutloze code bestaat niet, en 99.9% kans dat je voor niets de systeembeheerder met een probleem hebt verveeld.

[JaFFoG]

Het is inmiddels opgelost!

Het probleem zat 'm niet in de applicatie, ook niet in PHP, ook niet in de drivers, ook niet in MySQL of zijn databases of zijn tabellen of zijn rechten... Het probleem zat in SQLyog.

In SQLyog voerde ik een aantal INSERT's uit op een tabel. Deze liet vervolgens zien dat er gegevens bij zijn gekomen. In PHP waren deze gegevens niet te zien. Dit kwam omdat de gegevens eigenlijk helemaal niet in de tabel stonden. SQLyog is naar blijkt onbetrouwbaar. Zelfs na refreshen van de database en alle daaronder vallende objecten en opnieuw query'en van de tabel gaf SQLyog aan dat de gegevens wél in de tabel stonden toen dit niet het geval was. Ook handmatig query'en gaf aan dat de gegevens wél in de tabel stonden toen dit niet het geval was. Blijkbaar gebruikt SQLyog zélf een soortement van cache, wat onbetrouwbare resultaten oplevert.

Vreemd genoeg werkten CREATE TABLE, CREATE INTERFACE en USE statements wel, alsook INSTER statements direct na een CREATE TABLE.

De oplossing is SQLyog opnieuw opstarten.

rrrandy schreef op woensdag 13 augustus 2008 @ 17:07:
[...]
Gebruik je innodb? Heb je wel een commit gedaan?

Neen, echter doe ik dit nooit en gebruik altijd InnoDB. Dit heeft nog nooit problemen opgeleverd (en blijkt ook nu het probleem niet te zijn). Toch bedankt.

Voutloos schreef op woensdag 13 augustus 2008 @ 17:11:
Laat nou eens de échte mysql_query aanroep inc vervolg zien, want je doet gewoon wél iets fout. (...) Foutloze code bestaat niet, en 99.9% kans dat je voor niets de systeembeheerder met een probleem hebt verveeld.

Dat foutloze code niet bestaat ben ik tot op zekere hoogte met je eens. Alleen ben ik van mening dat voor deze uiteenzetting geldt "als de code goed draait dan is het nu goed". Daarmee bedoel ik dat wanneer de code geen fouten veroorzaakt, dat het probleem hooguit in de relevante code (de code die puur gebruikt wordt voor het uitvoeren van de query en het uitlezen/testen van het resultaat) kan zitten. Dat blijkt in deze dus ook niet het geval te zijn.

Of ik de systeembeheerder heb zitten vervelen - hij meent zelf van niet - maakt mij in alle eerlijkheid niet uit, als het maar opgelost wordt. Bovendien ben ik niet direct naar hem gegaan, maar heb ik eerst uren zelf een oplossing gezocht en heb hier een topic geopend na mijn digitale speurtocht. Pas toen ook dát geen soelaas leek te bieden (en het gewoon wel erg lang begon te duren voor ik verder kon) heb ik de systeembeheerder aangesproken.

Je reageert echt slecht op debug hints.

Noem eens een concreet voorbeeld? Volgens mij heb ik overal op gereageerd en ben ik duidelijk geweest in de resultaten (waarvan bij een aantal van de hier geopperde suggesties het resultaat al in de startpost staan beschreven, maar dat terzijde).

[BCC]

SQLyog draait dus alles in een transactie? Rrandy had dus gelijk met z'n commit.

[ Voor 31% gewijzigd door BCC op 13-08-2008 19:03 ]

[JaFFoG]

BCC schreef op woensdag 13 augustus 2008 @ 19:03:
SQLyog draait dus alles in een transactie? Rrandy had dus gelijk met z'n commit.

Daar heeft het inderdaad alle schijn van, maar dit is niet zo. Bovendien verklaart het niet waarom ik dit nooit eerder heb gehad en ook nu niet meer heb. Ik heb het programma niet recentelijk geupdate, gebruik het dagelijks en had er vanmorgen nog geen last van. Na het opnieuw opstarten van SQLyog was dit ook niet meer zo. Ook heb ik in de sessie waarin de INSERT's niet bleken te werken geen instellingen gewijzigd (dit doe ik überhaupt nooit, hooguit bij de eerste keer installeren, maar ik heb de software al geruime tijd geïnstalleerd staan).

[ Voor 3% gewijzigd door JaFFoG op 13-08-2008 19:50 ]

[BCC]

Heb je niet zelf per ongeluk een transactie gestart?

[JaFFoG]

BCC schreef op woensdag 13 augustus 2008 @ 22:39:
Heb je niet zelf per ongeluk een transactie gestart?

Ik zou niet weten hoe. Heb de MySQL documentatie er nog even op nageslagen maar ik kan me niet heugen ergens START TRANSACTION; of BEGIN; te hebben getypt. (Sterker nog, ik wist niet eens wat de MySQL syntax hiervoor was tot ik het opzocht.)

Het enige wat ik nog kan bedenken is dat de SQL gegenereerd uit het datamodel ergens dit statement bevat. Maar die heb ik nagezocht en ook dat is niet het geval.

Laten we het maar op de kaboutertjes houden dan.