[Cartoweb-users] Accounting error SQL

Wed Jul 2 11:27:47 EDT 2008

postgresql-server-8.2.6-0.1
openSUSE 10.3 64 bits rpm

in the postgresql.conf there
#escape_string_warning = on
It's commented but perharps would say on by default.

Standard log except perharps
log_statement = 'ddl'

Oliver Christen wrote:
> I tried here in a postgres 8.1 with the default log level and couldnt
> reproduce the notice you reported.
> 
> can you tell me which version of postgres you are using and also which
> log level you have defined in postgres ?
> 
> 
> 
> 
>> Just a little note more
>>
>> I think to make a patch for this module,
>>
>> We need to abstract the table name in db mode and dr_direct.
>>
>> For db_direct, is there a .sql somewhere which describe the table
>> structure ?
>> There's none is head documentation. nor in the default cartoweb cvs.
>>
>>
>>
>> Bruno Friedmann wrote:
>>> Rahh I've found
>>>
>>> This is the ligne inserted like found in pg_log.
>>>
>>> 2008-07-02 14:03:46 CEST cwdev carto_dev LOG:  instruction : INSERT
>>> INTO cw_accounting (date, info) VALUES (now(),
>>> 'general.client_version=\"1\";general.mapid=\"geojb.geojb\";general.time=\"1215000223\";general.ua=\"Mozilla/5.0
>>> (X11; U; Linux
>>> x86_64; en-US; rv:1.9.0) Gecko/2008061600 SUSE/3.0-1.1
>>> Firefox/3.0\";general.ip=\"127.0.0.1\";general.sessid=\"0JyYwADYAGDQJEHYjRxRE2\";general.direct_access=\"1\";layers.server_version=\"1\";layers.layers=\"COUVERTUREDUSOL,COUVERTUREDUSOL_SYMBOLE,COUVERTUREDUSOL_SYMBOLEA,COUVERTUREDUSOL_TEXTE,PARCELLE_NUMEROCENTRE,PARCELLE,PARCELLE_LIGNE,PL,BATIMENTS,BATIMENT_NUMERO,PFA12,PFP12,PFA3,PFP3,ODELPOINT,ODELLIGNE,ODELSURFACE,ODELPROJET,COUVERTUREDUSOL_LIGNE,LOCALISATION_RUE,LOCALISATION_LIEU,LOCALISATION_NOMLOCAL\";layers.switch_id=\"default\";images.server_version=\"0\";images.mainmap.width=\"600\";images.mainmap.height=\"420\";location.server_version=\"0\";location.bbox=\"550000,203017.53,620000,251982.47\";location.scale=\"441681.2554\";general.elapsed_time=\"2.178224\";layers.client_version=\"0\";layers.visible_layers=\"COUVERTUREDUSOL\"')
>>>
>>>
>>> In fact the trouble is the following. We don't need the \ before " as
>>> we are enclosed in a ' ' string.
>>> If we want to keep this we need to escape all in a postgres way (
>>> need to insert a E just before the beginning ' )
>>>
>>> As I don't know what would be expected by the log parser, I don't
>>> know how to patch it ( suppress the addslashes ) or add the E
>>>  before the sql statement.
>>>
>>> If you have an idea, I can create the patch for the sources.
>>> I simply comment line 187 in common/Accounting.php
>>>             // $accountingPacket = addslashes($accountingPacket);
>>> and it work like a charm ( in point of view of postgres ).
>>>
>>>
>>>
>>>
>>> Oliver Christen wrote:
>>>> well, the data comes from pretty much every parts of Cartoweb.
>>>> all line with $this->account(...) will log data.
>>>>
>>>> I would say simply output in some files the sql generated in
>>>> Accounting.php and then search for \
>>>>
>>>> then based on the string being included we can eventually know where it
>>>> came from.
>>>>
>>>> Sylvain is not available at the moment.
>>>>
>>>> regards
>>>> Oliver
>>>>
>>>> I mean there's none of our data inside the table, only the accounting
>>>> plugin is writing to it.
>>>> After the insert, the data is filtered by postgresql so there's no \r\n
>>>> inside the table.
>>>>
>>>> So I must found where the plugin take it's data, and prepare the sql.
>>>> So I would check the Accounting.php file if I found something.
>>>>
>>>> Perharps Sylvain has a more precise id ?
>>>>
>>>>
>>>> Oliver Christen wrote:
>>>>> "user or us" ?
>>>>> im sorry but I dont understand what you mean.
>>>>>
>>>>> the sql for accounting is in cartoweb3/common/Accounting.php if thats
>>>>> what you are looking for.
>>>>>
>>>>> regards
>>>>> Oliver
>>>>>
>>>>>
>>>>>> Thanks Oliver, i will dump the db it should be easy to find the \r\n
>>>>>> into the sql file.
>>>>>>
>>>>>> But also we need to understand where we can escape properly the
>>>>>> string
>>>>>> into accounting plugin
>>>>>> As it is this plugin that lead to this error. And there's no "user or
>>>>>> us" data insert.
>>>>>>
>>>>>>
>>>>>> Oliver Christen wrote:
>>>>>>> Hi
>>>>>>>
>>>>>>> it seems to be linked to some backslashs in the data inserted
>>>>>>> into the
>>>>>>> database, usualy when a backslash is present but no special
>>>>>>> character is
>>>>>>> positionned after that. one usual example are file path on windows:
>>>>>>> c:\foo\bar
>>>>>>>
>>>>>>> it should be interesting to know what is the string that
>>>>>>> triggered that
>>>>>>> postgres notice, maybe you can log that too ?
>>>>>>>
>>>>>>> that's all I can say based on the message
>>>>>>>
>>>>>>> regards
>>>>>>> Oliver
>>>>>>>
>>>>>>>> Dear CW team,
>>>>>>>>
>>>>>>>> We encounter a constant log in postgresql server if we activate the
>>>>>>>> accounting plugin.
>>>>>>>>
>>>>>>>> This is what we found on the pg log server ( sorry it's french LC )
>>>>>>>>
>>>>>>>> 2008-07-01 15:36:16 CEST cwdev carto_dev ATTENTION:  utilisation
>>>>>>>> non
>>>>>>>> standard d'un échappement dans une chaîne littérale au
>>>>>>>> caractère 55
>>>>>>>> 2008-07-01 15:36:16 CEST cwdev carto_dev ASTUCE :  Utilisez la
>>>>>>>> syntaxe
>>>>>>>> de la chaîne d'échappement pour les échappements,
>>>>>>>> c'est-à-dire E'\r\n'.
>>>>>>>>
>>>>>>>> I've try to see where it could be in the source but doesn't found
>>>>>>>> someting.
>>>>>>>>
>>>>>>>> If you could drive me where to search.
>>>>>>>>
>>>>>>>> -- 
>>>>>>>>
>>>>>>>>     Bruno Friedmann
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> Cartoweb-users mailing list
>>>>>>>> Cartoweb-users at lists.maptools.org
>>>>>>>> http://lists.maptools.org/mailman/listinfo/cartoweb-users
>>>>>>>>
>>>>>>
>>>>>> -- 
>>>>>>
>>>>>>     Bruno Friedmann
>>>>>>
>>>>>> Ioda-Net Sàrl   - www.ioda-net.ch
>>>>>>  2830 Vellerat - Switzerland
>>>>>>
>>>>>>  Tél : ++41 32 435 7171
>>>>>>  Fax : ++41 32 435 7172
>>>>>>  gsm : ++41 78 802 6760
>>>>>>
>>>>>> C'est Facile et Cool d'Évoluer en ligne : www.cfcel.com
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> Cartoweb-users mailing list
>>>>>> Cartoweb-users at lists.maptools.org
>>>>>> http://lists.maptools.org/mailman/listinfo/cartoweb-users
>>>>>>
>>>>
>>>
>>>
>>
>>
>> -- 
>>
>>     Bruno Friedmann
>>
>> Ioda-Net Sàrl   - www.ioda-net.ch
>>  2830 Vellerat - Switzerland
>>
>>  Tél : ++41 32 435 7171
>>  Fax : ++41 32 435 7172
>>  gsm : ++41 78 802 6760
>>
>> C'est Facile et Cool d'Évoluer en ligne : www.cfcel.com
>>
>>
>> _______________________________________________
>> Cartoweb-users mailing list
>> Cartoweb-users at lists.maptools.org
>> http://lists.maptools.org/mailman/listinfo/cartoweb-users
>>
> 

-- 

     Bruno Friedmann

Ioda-Net Sàrl   - www.ioda-net.ch
  2830 Vellerat - Switzerland

  Tél : ++41 32 435 7171
  Fax : ++41 32 435 7172
  gsm : ++41 78 802 6760

C'est Facile et Cool d'Évoluer en ligne : www.cfcel.com