/xhr/units/ returns 500 Error (1054, "Unknown column 'pootle_store_store.pootle_path' in 'where clause'")

[knok]

Version: 2.5.1

I tried to install pootle, it seems almost work fine, but I can't find translate tab.

The browser show "Server Error" message, so I traced web browser's developer tool.

The following is the request and responce.

http://mysite.example/xhr/units/?page=1&path=%2Fprojects%2Fterminology%2F&pager=true

GET /xhr/units/?page=1&path=%2Fprojects%2Fterminology%2F&pager=true HTTP/1.1 Host: pootle.knok.ddns.daionet.gr.jp:11080 Connection: keep-alive Authorization: Basic a25vazpJZWJhaXQzaQ== Accept: application/json, text/javascript, /; q=0.01 X-Requested-With: XMLHttpRequest User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/33.0.1750.146 Safari/537.36 DNT: 1 Referer: http://mysite.example/projects/terminology/translate/ Accept-Encoding: gzip,deflate,sdch Accept-Language: ja,en-US;q=0.8,en;q=0.6 Cookie: sessionid=6a6c0b71d7c32d2b527b84e1c0976538; csrftoken=urHoYP4Nf7uNiJEsAv3MKc4qCeOooh7M

HTTP/1.1 500 INTERNAL SERVER ERROR Date: Tue, 11 Mar 2014 06:24:46 GMT Server: Apache/2.2.22 (Debian) Content-Language: en Content-Encoding: gzip Vary: Accept-Language,Cookie,Accept-Encoding X-Frame-Options: SAMEORIGIN Set-Cookie: csrftoken=urHoYP4Nf7uNiJEsAv3MKc4qCeOooh7M; expires=Tue, 10-Mar-2015 06:24:46 GMT; Max-Age=31449600; Path=/ Content-Length: 1838 Connection: close Content-Type: text/html; charset=utf-8

The response body is too large, so I copied the main error message:

Database Error

Pootle can't connect to the database, the database is notinitialized yet, or a schema upgrade is needed before continuing.

Please read the Pootle documentation for installationand upgrade instructions. Contact the server administrator if you feel this isa mistake.

The reported error was: (1054, "Unknown column 'pootle_store_store.pootle_path' in 'where clause'")

However, pootle_store_store table has pootle_path column:

$ mysql -u pootle -p pootle -e "describe pootle_store_store;" |more Field Type Null Key Default Extra id int(11) NO PRI NULL auto_increment file varchar(255) NO MUL NULL pending varchar(255) NO NULL tm varchar(255) NO NULL parent_id int(11) NO MUL NULL translation_project_id int(11) NO MUL NULL pootle_path varchar(255) NO UNI NULL name varchar(128) NO NULL sync_time datetime NO NULL state int(11) NO MUL NULL

I can't find why such error cased. I suspect it should be django's problem, but I can't find similar problem by other people.

$ pip list argparse (1.2.1) cssmin (0.2.0) distribute (0.7.3) Django (1.4.10) django-assets (0.8) django-taggit (0.11.1) django-voting (0.1) lxml (3.3.3) MySQL-python (1.2.5) pip (1.5.4) Pootle (2.5.1) setuptools (3.1) South (0.7.6) translate-toolkit (1.11.0) webassets (0.8) wsgiref (0.1.2)

[knok]

Created attachment 971

Pootle setup typescript file.

I retried to clean install, and use sqlite3 backend. The error message was a little bit changed, but it seems almost same means.

The reported error was: no such column: pootle_store_store.pootle_path

I attach a typescript file of my runnning setup commands.

[knok]

From mysql log, I can find the SQL expression it causes error.

mysql> SELECT COUNT(*) FROM pootle_store_unit WHERE (pootle_store_unit.state > -100 AND pootle_store_store.pootle_path LIKE '/%/ve/%' AND pootle_store_store.pootle_path NOT LIKE '/templates/%') -> ; ERROR 1054 (42S22): Unknown column 'pootle_store_store.pootle_path' in 'where clause'

However, I don't know where code executes the SQL expression.

[knok]

I think it should be pootle/apps/pootle_store/models.py:

def get_for_path(self, pootle_path, profile):
    """Returns units that fall below the `pootle_path` umbrella.

    :param pootle_path: An internal pootle path.
    :param profile: The user profile who is accessing the units.
    """
    lang, proj, dir_path, filename = split_pootle_path(pootle_path)

    units_qs = super(UnitManager, self).get_query_set().filter(
        state__gt=OBSOLETE,
    )

    # /projects/<project_code>/translate/*
    if lang is None and proj is not None:
        units_qs = units_qs.extra(
            where=[
                '`pootle_store_store`.`pootle_path` LIKE %s',
                '`pootle_store_store`.`pootle_path` NOT LIKE %s',
            ], params=[''.join(['/%/', proj ,'/%']), '/templates/%']
        )

What SQL engines expects a expression in the code?

[knok]

I tried latest git version, so the issue is cleared. From the MySQL query log, the following expression is called:

               58 Query     SELECT `pootle_store_unit`.`id`, `pootle_store_u

nit.store_id,pootle_store_unit.index,pootle_store_unit.unitid,poo tle_store_unit.unitid_hash,pootle_store_unit.source_f,pootle_store_uni t.source_hash,pootle_store_unit.source_wordcount,pootle_store_unit.s ource_length,pootle_store_unit.target_f,pootle_store_unit.target_wordc ount,pootle_store_unit.target_length,pootle_store_unit.developer_comme nt,pootle_store_unit.translator_comment,pootle_store_unit.locations, pootle_store_unit.context,pootle_store_unit.state,pootle_store_unit. mtime,pootle_store_unit.submitted_by_id,pootle_store_unit.submitted_o n,pootle_store_unit.commented_by_id,pootle_store_unit.commented_on, pootle_store_store.id,pootle_store_store.file,pootle_store_store.pen ding,pootle_store_store.tm,pootle_store_store.parent_id,pootle_stor e_store.translation_project_id,pootle_store_store.pootle_path,pootle_s tore_store.name,pootle_store_store.sync_time,pootle_store_store.stat eFROMpootle_store_unitINNER JOINpootle_store_storeON (pootle_store_un it.store_id=pootle_store_store.id) WHERE (pootle_store_unit.state> -100 AND (pootle_store_store.pootle_pathLIKE '/%/terminology/%') AND (po otle_store_store.pootle_pathNOT LIKE '/templates/%')) ORDER BYpootle_store _store.pootle_pathASC,pootle_store_unit.index` ASC LIMIT 18

I think the latest pootle requires django 1.5.5, so it should be work fine.

[julen]

This sounds like https://github.com/evernote/pootle/issues/4 which was worked around with an ugly hack, but in the end it was Django ORM's fault.

The code in master doesn't paginate the list of units anymore, so the issue shouldn't be present as you have already checked.

[knok]

Thank you for your information. I'll check the ticket and try to reproduce on 2.5.1.

[knok]

Yes, the following workaround works fine. https://github.com/evernote/pootle/commit/4ef1df17a45c34b0f5cd212689d0f3335df0eefc

[unho]

The commit in https://github.com/evernote/pootle/issues/4 was picked into the stable/2.5.1 branch as https://github.com/translate/pootle/commit/c75eeecd.