SQL Error: 1054 (Unknown column 'nl_q.nl_id')

2009-02-01
2013-05-29
  • Nobody/Anonymous

    I just updated to the latest version (1.8.4) and everything seemed to work fine. But then I got a SQL Error after clicking "Create new delivery job" and in the following dialogue "safe".  The former version (1.8.2) worked fine on my "Hosteurope" Webpack (Shared Hosting). PHP Version 5.2.6.

    I even tried a new clean install, but the same error!

    So here is the error code:

    -----

    SQL Error: 1054 (Unknown column 'nl_q.nl_id' in 'on clause')

    Invalid SQL:
                /* insert new adr_id for selected newsletter in newsletter history */
                /* adresses can refer to multiple groups adr_grp.id via adr_grp_ref.adr_id and adr_grp_ref.grp_id (inner join) */
                INSERT INTO
                    nl_h
                    (
                        q_id, #qeue id
                        #2 AS q_id,
                        host_id, #host id
                        #1 AS host_id,
                        nl_id, #newsletter id
                        #1 AS nl_id,
                        grp_id, #adress group id
                        adr_id, #adress id
                        status, #history status (1=new)
                        created, #creation date
                        sent, #send date
                        siteid #the siteid
                    )
                SELECT
                    /* we also could use prefined values from php array h, but we use matches */
                    /* q_id, host_id from q, nl_id from nl.id, adr_id from adr.id, and grp_id from grp.id */
                    /* status, created is taken from array, sent is empty (not sent yet) and siteid as defined in Tellmatic */
                    nl_q.id AS q_id,
                    nl_q.host_id AS host_id,
                    nl.id AS nl_id,
                    adr_grp.id AS grp_id,
                    adr.id AS adr_id,
                    1 AS status,
                    '2009-02-01 22:21:30' AS created,
                    '' as sent,
                    'tellmatic' as siteid
                FROM adr
                    /* join adresstable adr.id with groups references table adr_grp_ref.adr_id */
                    INNER JOIN adr_grp_ref
                        ON adr.id = adr_grp_ref.adr_id
                    /* join groupstable adr_grp.id with groups references table adr_grp_ref.grp_id  */
                    INNER JOIN adr_grp
                        ON adr_grp_ref.grp_id = adr_grp.id
                    /* join newsletter nl.id with queuetable nl_q.nl_id */
                    INNER JOIN nl
                        ON nl_q.nl_id = nl.id
                    /* join newsletterqueue nl_q.grp_id with groupstable adr_grp.id */
                    INNER JOIN nl_q
                        ON adr_grp.id = nl_q.grp_id
                    /* left join now adresstable adr.id with temporary historytable/recipientstable nl_h_tmp_tellmatic_2_1_1_724.adr_id */
                    LEFT JOIN nl_h_tmp_tellmatic_2_1_1_724 ON adr.id = nl_h_tmp_tellmatic_2_1_1_724.adr_id
                WHERE
                    /* Address is active */
                    adr.aktiv=1
                    /* for siteid */
                    AND adr.siteid='tellmatic'
                    AND (
                                /* less errors */
                                adr.errors<=5
                                /* or NULL */
                                OR adr.errors IS NULL
                            )
                    AND (
                                /* filter adr with status */
                                 adr.status=1
                                OR adr.status=2
                                OR adr.status=3
                                OR adr.status=4
                                OR adr.status=10
                                OR adr.status=12
                            )
                    /* for selected group */
                    AND adr_grp.id=1
                    AND adr_grp_ref.grp_id=1
                    AND nl_q.grp_id=1
                    AND (
                            /* q status must be 1 new or 2 started */
                            nl_q.status=1
                            OR nl_q.status=2
                            )
                    /* group must be active */
                    AND adr_grp.aktiv=1
                    /* for selected newsletter */
                    AND nl.id=1
                    AND nl_q.nl_id=1
                    /* for siteid */
                    AND adr_grp.siteid='tellmatic'
                    AND adr_grp_ref.siteid='tellmatic'
                    AND nl.siteid='tellmatic'
                    AND nl_q.siteid='tellmatic'
                    AND
                    /* only addresses not included in temporary table */
                    nl_h_tmp_tellmatic_2_1_1_724.adr_id IS NULL
    -----

    What´s the problem here, because it is not possible to send Mails!?

    Thanks a lot and Greeting!

     
    • vizzy

      vizzy - 2009-02-01

      Hi,

      > SQL Error: 1054 (Unknown column 'nl_q.nl_id' in 'on clause')

      the Code is in Class_Q.inc.php around line 717:

      /* join newsletter nl.id with queuetable nl_q.nl_id */
          INNER JOIN ".TM_TABLE_NL."
          ON ".TM_TABLE_NL_Q.".nl_id = ".TM_TABLE_NL.".id

      and is parsed as:

      /* join newsletter nl.id with queuetable nl_q.nl_id */
      INNER JOIN nl 
      ON nl_q.nl_id = nl.id

      which means you have no table prefix, which is ok.

      i can't imagine why the column doesn#t exists for you anymore. there were no changes regarding to this.
      maybe your hoster lost some data after a crash?
      look at the database using console or phpmyadmin or so, and check if nl_id exists in table "nl_q".

       
    • Nobody/Anonymous

      Hi,

      thx for that fast reply. With my hoster there are no crashes, furthermore I tried it several times with new installations and even created a new database, so no data can´t be lost normally. Also the entry "nl_id" exists in table "nl_q"!

       
    • vizzy

      vizzy - 2009-02-01

      sorry, i have no clue yet why that should happen :(

       
    • Nobody/Anonymous

      ok, perhaps you´ll find a solution soon or a little workaround (e.g. comment out something which is not so important for me, so that the wrong SQL Request don´t stop the sending process anymore), would be perfect, because otherwise I have to install again the older version and fix some bugs there manually, because with that version I couldn´t send Mails either because the SMTP AUTH didnt allow "-", which is fixed in the latest version and so on.

       
      • vizzy

        vizzy - 2009-02-02

        sorry, i think i can't find a solution for a problem that does not exists for me. i tried installing and updating several times, but can't reproduce it.
        till nl_q.nl_id exists and there is no lost data or index due to a crash, i can't image why that happens. i think the problem is related to another problem, maybe depending on your server. maybe i'm wrong, but for now i can't reproduce it.
        i will try that again on another server the next days.

        v.

         
    • Nobody/Anonymous

      Ok, I can imagine that it is difficult to find out.
      The script is installed on a normal shared hosting package (Hosteurope, Webpack M 2.0), perhaps the standard script parameters have to be changed (PHP-RegisterGlobals, etc.)? Safe mode is off, which cannot be changed because it is standard with this kind of hosting.

      Thanks!

       
    • vizzy

      vizzy - 2009-02-02
       
    • vizzy

      vizzy - 2009-02-02

      i finally found it.

      Hi,

      now after some tesing and endless installations on different systems i found that he problem is mysql related. since a specific mysql5 version, the join syntax changed!

      Please open include/Class_Q.inc.php:

      change

        710             FROM ".TM_TABLE_ADR."
        711                 /* join adresstable adr.id with groups references table adr_grp_ref.adr_id */
        712                 INNER JOIN ".TM_TABLE_ADR_GRP_REF."
        713                     ON ".TM_TABLE_ADR.".id = ".TM_TABLE_ADR_GRP_REF.".adr_id
        714                 /* join groupstable adr_grp.id with groups references table adr_grp_ref.grp_id  */
        715                 INNER JOIN ".TM_TABLE_ADR_GRP."
        716                     ON ".TM_TABLE_ADR_GRP_REF.".grp_id = ".TM_TABLE_ADR_GRP.".id
        717                 /* join newsletter nl.id with queuetable nl_q.nl_id */
        718                 INNER JOIN ".TM_TABLE_NL."
        719                     ON ".TM_TABLE_NL_Q.".nl_id = ".TM_TABLE_NL.".id
        720                 /* join newsletterqueue nl_q.grp_id with groupstable adr_grp.id */
        721                 INNER JOIN ".TM_TABLE_NL_Q."
        722                     ON ".TM_TABLE_ADR_GRP.".id = ".TM_TABLE_NL_Q.".grp_id
        723                 /* left join now adresstable adr.id with temporary historytable/recipientstable $tmp_tablename.adr_id */
        724                 LEFT JOIN ".$tmp_tablename." ON ".TM_TABLE_ADR.".id = ".$tmp_tablename.".adr_id

      to

        710             FROM (".TM_TABLE_ADR.")
        711                 /* join adresstable adr.id with groups references table adr_grp_ref.adr_id */
        712                 INNER JOIN ".TM_TABLE_ADR_GRP_REF."
        713                     ON (".TM_TABLE_ADR.".id = ".TM_TABLE_ADR_GRP_REF.".adr_id)
        714                 /* join groupstable adr_grp.id with groups references table adr_grp_ref.grp_id  */
        715                 INNER JOIN ".TM_TABLE_ADR_GRP."
        716                     ON (".TM_TABLE_ADR_GRP_REF.".grp_id = ".TM_TABLE_ADR_GRP.".id)

        720                 /* join newsletterqueue nl_q.grp_id with groupstable adr_grp.id */
        721                 INNER JOIN ".TM_TABLE_NL_Q."
        722                     ON (".TM_TABLE_ADR_GRP.".id = ".TM_TABLE_NL_Q.".grp_id)

        717                 /* join newsletter nl.id with queuetable nl_q.nl_id */
        718                 INNER JOIN ".TM_TABLE_NL."
        719                     ON (".TM_TABLE_NL_Q.".nl_id = ".TM_TABLE_NL.".id)
        723                 /* left join now adresstable adr.id with temporary historytable/recipientstable $tmp_tablename.adr_id */
        724                 LEFT JOIN ".$tmp_tablename." ON ".TM_TABLE_ADR.".id = ".$tmp_tablename.".adr_id

      Details
      http://dev.mysql.com/doc/refman/5.0/en/join.html

      ps: i only have a php4/mysql4 system for developing now. sorry for that.

      v.

       
    • Nobody/Anonymous

      Perfect, it works, thanks a lot!!

       

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks