Can't make sql_bridge to work

[12u...@gmail.com]

Hi,

into etc/sql_bridge.config, I have correct options :

    {lookup,    emploi},
    {host,      "127.0.0.1"},
    {user,      "emploi"},
    {pass,      "emploi"},
    {port,      5432},

I added an io:format("Args = ~p", [Args] just before the connection (line 24) into sql_bridge_epgsql_worker.erl that show everything is correct, but it crash when trying to connect to Pg :

JYJYJYJYJYJY - Args = [{database,"emploi"},
                       {hostname,"127.0.0.1"},
                       {username,"emploi"},
                       {password,"emploi"},
                       {port,5432}]=PROGRESS REPORT==== 7-Dec-2021::00:10:54.890534 ===
    supervisor: {local,inet_gethost_native_sup}
    started: [{pid,<0.4471.0>},{mfa,{inet_gethost_native,init,[[]]}}]

=PROGRESS REPORT==== 7-Dec-2021::00:10:54.890668 ===
    supervisor: {local,kernel_safe_sup}
    started: [{pid,<0.4470.0>},
              {id,inet_gethost_native_sup},
              {mfargs,{inet_gethost_native,start_link,[]}},
              {restart_type,temporary},
              {significant,false},
              {shutdown,1000},
              {child_type,worker}]

=CRASH REPORT==== 7-Dec-2021::00:10:54.899630 ===
  crasher:
    initial call: poolboy:init/1
    pid: <0.4466.0>
    registered_name: []
    exception error: no match of right hand side value
                     {error,
                         {{badmatch,{error,{unsupported_auth_method,unknown}}},
                          [{sql_bridge_epgsql_worker,init,1,
                               [{file,
                                    "/home/niff/ERLANG/SITES_NITROGEN/emploi/lib/sql_bridge/src/sql_bridge_epgsql_worker.erl"},
                                {line,27}]},
                           {gen_server,init_it,2,
                               [{file,"gen_server.erl"},{line,423}]},
                           {gen_server,init_it,6,
                               [{file,"gen_server.erl"},{line,390}]},
                           {proc_lib,init_p_do_apply,3,
                               [{file,"proc_lib.erl"},{line,226}]}]}}
      in function  poolboy:new_worker/1 (src/poolboy.erl, line 300)
      in call from poolboy:prepopulate/3 (src/poolboy.erl, line 329)
      in call from poolboy:init/3 (src/poolboy.erl, line 168)
      in call from gen_server:init_it/2 (gen_server.erl, line 423)
      in call from gen_server:init_it/6 (gen_server.erl, line 390)
    ancestors: [sql_bridge_sup,<0.167.0>]
    message_queue_len: 0
    messages: []
    links: [<0.168.0>,<0.4467.0>]
    dictionary: []
    trap_exit: true
    status: running
    heap_size: 987
    stack_size: 29
    reductions: 318
  neighbours:

=CRASH REPORT==== 7-Dec-2021::00:10:54.899435 ===
  crasher:
    initial call: sql_bridge_epgsql_worker:init/1
    pid: <0.4468.0>
    registered_name: []
    exception error: no match of right hand side value
                     {error,{unsupported_auth_method,unknown}}
      in function  sql_bridge_epgsql_worker:init/1 (/home/niff/ERLANG/SITES_NITROGEN/emploi/lib/sql_bridge/src/sql_bridge_epgsql_worker.erl, line 27)
      in call from gen_server:init_it/2 (gen_server.erl, line 423)
      in call from gen_server:init_it/6 (gen_server.erl, line 390)
    ancestors: [<0.4467.0>,emploi,sql_bridge_sup,<0.167.0>]
    message_queue_len: 1
    messages: [{'EXIT',<0.4469.0>,normal}]
    links: [<0.4467.0>]
    dictionary: []
    trap_exit: true
    status: running
    heap_size: 610
    stack_size: 29
    reductions: 335
  neighbours:

JYJYJYJYJYJY - Args = [{database,"emploi"},
                       {hostname,"127.0.0.1"},
                       {username,"emploi"},
                       {password,"emploi"},
                       {port,5432}]=ERROR REPORT==== 7-Dec-2021::00:10:54.904750 ===

I don't see where it could come from :/

Trying to directly connect with epgsql from nitrogen console doesn't work either (which seems "normal" providing it fails through sql_bridge) :

(emp...@127.0.0.1)5> {ok, Conn} = epgsql:connect("127.0.0.1", "emploi", "emploi", [{database, "emploi"},{port,5432}]).
** exception error: no match of right hand side value {error,{unsupported_auth_method,unknown}}

I added -eval "sql_bridge:start()." into vm.args, so it starts automatically with the console.

Sooo, I'm lost - I could directly use epgsql but it would cause problems between binaries and strings that sql_bridge is supposed to handle gracefully - where did I goofed ?

Jean-Yves

[Jesse Gumm]

Interesting, I'm not sure yet - I use mysql on the daily, but I rarely bounce into pgsql, so maybe something has changed that I'm not aware of.

What version of Postgres are you running?

-Jesse

--
You received this message because you are subscribed to the Google Groups "Nitrogen Project / The Nitrogen Web Framework for Erlang" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nitrogenweb...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nitrogenweb/1a1b5673-4e4c-41e3-9b56-de51adec5999n%40googlegroups.com.

--

Jesse Gumm
Owner, Sigma Star Systems
414.940.4866 || sigma-star.com || @jessegumm

[12u...@gmail.com]

Hi Jesse,

my system is a Debian bullseye (stable) + deb-multimedia (stable) with some external packages :

* erlang-solutions erlang multiple pkgs - Version: 24.1.7-1

* apt.postgresql.org pkgs - Version: 14.1-1.pgdg110+1

I modified the following epgsql files:

* epgsql_fdatetime.erl - changed function name from floor() to myfloor() to avoid a collision w/ BIF

* epgsql_binary.erl - where I zapped the stacktrace

O_o! Some new news:

* apparently, sql_bridge uses your own version of epgsql: into rebar.config.script : {epgsql,  {git, "git://github.com/choptastic/epgsql", {branch, master}}}, which doesn't work, event trying to directly connect with epgsql:connect(),

* subsequently, I made a: git clone https://github.com/epgsql/epgsql, issued a make that downloaded rebar3 and compiled like a charm - after that, {ok, C} = epgsql:connect("localhost", "emploi", "emploi", [{database, "emploi"}, {port, 5432}]). worked right instantly,

So, the questions are:

* can I replace the epgsql version downloaded by sql_bridge and replace it with the git master

* and shall I rectify rebar.config.script accordingly or does it not matter ?

Jean-Yves

[12u...@gmail.com]

Changing to the regular epgsql git into sql_bridge doesn't work (throws a lot of errors)

So I'm stuck:(

[Jesse Gumm]

Interesting,

There are a handful of changes I've made from mainline epgsql.  Looking at my repo, I'm 300+ commits behind mainline, but i have about 5 or so commits I've made to make sure sql_bridge actually works.

The trick might be trying to merge my changes with mainline to see if that works out.

-Jesse

To view this discussion on the web visit https://groups.google.com/d/msgid/nitrogenweb/7e1d92ea-ffd1-4ebd-9e7e-551af9afa926n%40googlegroups.com.

[12u...@gmail.com]

> trying to merge my changes with mainline

I don't know how to do that :/

At this time, I added several io:format/2 and found that the PostgresSQL connection is ok

It possibly fails because it can't start poolboy, then loop and crash trying to open another connection, so the run look like that:

BEFORE CALLING: sql_bridge_utils:start_poolboy_pool(DB, WorkerArgs, sql_bridge_epgsql_worker)

OK: epgsql:connect(Host, Username, Password, Opts)

OK: epgsql:connect(C, Host, Username, Password, Opts)

OK: sql_bridge_utils:start_poolboy_pool/3 BEFORE poolboy:child_spec/3sql_bridge_utils:start_poolboy_pool/3

??: sql_bridge_utils:start_poolboy_pool/3 AFTER poolboy:child_spec/3    (doesn't seems to crash here…)

LOOP

=PROGRESS REPORT==== 7-Dec-2021::16:17:26.179757 ===
    supervisor: {local,inet_gethost_native_sup}
    started: [{pid,<0.5000.0>},{mfa,{inet_gethost_native,init,[[]]}}]

=PROGRESS REPORT==== 7-Dec-2021::16:17:26.179957 ===
    supervisor: {local,kernel_safe_sup}
    started: [{pid,<0.4999.0>},

              {id,inet_gethost_native_sup},
              {mfargs,{inet_gethost_native,start_link,[]}},
              {restart_type,temporary},
              {significant,false},
              {shutdown,1000},
              {child_type,worker}]

AFTER CALLING: sql_bridge_utils:start_poolboy_pool(DB, WorkerArgs, sql_bridge_epgsql_worker)

BEFORE CALLING: sql_bridge_utils:start_poolboy_pool(DB, WorkerArgs, sql_bridge_epgsql_worker)

OK: epgsql:connect(Host, Username, Password, Opts)

BAD: epgsql:connect(C, Host, Username, Password, Opts)=CRASH REPORT==== 7-Dec-2021::16:17:26.186369 ===

  crasher:
    initial call: sql_bridge_epgsql_worker:init/1

    pid: <0.4997.0>

    registered_name: []
    exception error: no match of right hand side value
                     {error,{unsupported_auth_method,unknown}}
      in function  sql_bridge_epgsql_worker:init/1 (/home/niff/ERLANG/SITES_NITROGEN/emploi/lib/sql_bridge/src/sql_bridge_epgsql_worker.erl, line 27)
      in call from gen_server:init_it/2 (gen_server.erl, line 423)
      in call from gen_server:init_it/6 (gen_server.erl, line 390)

    ancestors: [<0.4996.0>,emploi,sql_bridge_sup,<0.167.0>]
    message_queue_len: 1
    messages: [{'EXIT',<0.4998.0>,normal}]
    links: [<0.4996.0>]

    dictionary: []
    trap_exit: true
    status: running
    heap_size: 610
    stack_size: 29

    reductions: 394
  neighbours:

=CRASH REPORT==== 7-Dec-2021::16:17:26.186540 ===
  crasher:
    initial call: poolboy:init/1      <= I'M GONNA INVESTIGATE FROM HERE
    pid: <0.4995.0>

    registered_name: []
    exception error: no match of right hand side value
                     {error,
                         {{badmatch,{error,{unsupported_auth_method,unknown}}},
                          [{sql_bridge_epgsql_worker,init,1,
                               [{file,
                                    "/home/niff/ERLANG/SITES_NITROGEN/emploi/lib/sql_bridge/src/sql_bridge_epgsql_worker.erl"},
                                {line,27}]},
                           {gen_server,init_it,2,
                               [{file,"gen_server.erl"},{line,423}]},
                           {gen_server,init_it,6,
                               [{file,"gen_server.erl"},{line,390}]},
                           {proc_lib,init_p_do_apply,3,
                               [{file,"proc_lib.erl"},{line,226}]}]}}
      in function  poolboy:new_worker/1 (src/poolboy.erl, line 300)
      in call from poolboy:prepopulate/3 (src/poolboy.erl, line 329)
      in call from poolboy:init/3 (src/poolboy.erl, line 168)
      in call from gen_server:init_it/2 (gen_server.erl, line 423)
      in call from gen_server:init_it/6 (gen_server.erl, line 390)
    ancestors: [sql_bridge_sup,<0.167.0>]
    message_queue_len: 0
    messages: []

    links: [<0.168.0>,<0.4996.0>]

    dictionary: []
    trap_exit: true
    status: running
    heap_size: 987
    stack_size: 29
    reductions: 318
  neighbours:

[12u...@gmail.com]

I now meet another problem using pgapp which is just epgsql+poolboy - textbox_autocomplete is throwing a bad_generator error from a proplist build from a DB query:

=INFO REPORT==== 9-Dec-2021::23:56:05.696236 ===
{error,postback_request,
    {url,"nitrogen.defcon1.lan/offer/create"},
    {error,
        {bad_generator,ok},
        [{offer_create,'-autocomplete_enter_event/2-lc$^1/1-1-',2,
             [{file,
                  "/home/niff/ERLANG/SITES_NITROGEN/emploi/site/src/offer_create.erl"},
              {line,806}]},
         {offer_create,autocomplete_enter_event,2,
             [{file,
                  "/home/niff/ERLANG/SITES_NITROGEN/emploi/site/src/offer_create.erl"},
              {line,806}]},
         {element_textbox_autocomplete,event,1,
             [{file,"src/elements/forms/element_textbox_autocomplete.erl"},
              {line,57}]},
         {wf_core,run_websocket,1,[{file,"src/wf_core.erl"},{line,81}]},
         {nitrogen,ws_message_catched,1,[{file,"src/nitrogen.erl"},{line,69}]},
         {nitrogen,ws_message,3,[{file,"src/nitrogen.erl"},{line,59}]},
         {cowboy_simple_bridge_anchor,websocket_handle,2,
             [{file,
                  "src/cowboy_bridge_modules/cowboy_simple_bridge_anchor.erl"},
              {line,70}]},
         {cowboy_websocket,handler_call,6,
             [{file,"src/cowboy_websocket.erl"},{line,482}]}]}}

this is the code involved:

autocomplete_enter_event(SearchTerm, _Tag) ->
    %% If the session VAR is undefined, fill it, otherwise, return it
    Proplist = case wf:state(towns_proplist) of
        undefined ->
            %% Retrieve all towns for autocompletion from DB (only the list of columns)
            {ok, _, ListTownsIds} = e_db:get_all_towns()
, ?PRINT(ListTownsIds)
            %% Convert it to a map, for we'll need to retrieve "id", which is the
            %% town's name shorcut we'll need when saving this offer
            %% (key=full name, value=shortcut=="id" col)
            %% Retrieve Ex:     maps:find(<<"Nantes">>, MapTowns.
            %%                  {ok,<<"nan">>}
            , wf:state(map_towns, maps:from_list(ListTownsIds))
, ?PRINT(wf:state(map_towns))
            %% Build the proplist
            %% FUCK ! ALL BINARIES w/ DIACRITICS HAVE WRONG (ISO-8859-1|15 ?) CHARS :(((
            %% IT WAS BECAUSE THIS FILE HADN'T THE UTF8 HEADR (SEE LINE# 1) !
            , wf:state(towns_proplist,
                [[{id,string:lowercase(X)},{label,X},{value,X}] || {X, _} <- ListTownsIds]);
            %, wf:state(towns_proplist,
            %    [[{id,string:lowercase(unicode:characters_to_binary(X,utf8))},{label,unicode:characters_to_binary(X,utf8)},{value,unicode:characters_to_binary(X,utf8)}] || {X, _} <- ListTownsIds]);
            %wf:state(towns_proplist,
            %    [[{id,unicode:characters_to_binary(string:lowercase(X))},{label,unicode:characters_to_binary(X)},{value,unicode:characters_to_binary(X)}] || {X, _} <- ListTownsIds]);
        _ ->
            wf:state(towns_proplist)
    end
, ?PRINT(wf:state(towns_proplist))
    %% Let's filter the list based on the SearchTerm
    , Data = [LangRec || LangRec <- Proplist, does_search_match(LangRec, SearchTerm)]  <<< LINE# 806
, ?PRINT(Data)
   
    %% Encode the Data into json for the autocomplete event. As you can see
    %% from the base_data function below, it expects each record to have an
    %% "id", a "label", and a "value". "id" is a short-hand identifier. "label"
    %% is what will be displayed in the dropdown, and "value" will be passed
    %% along with the "id", then decoded from json and sent to the
    %% autocomplete_select_event function as a proplist.
    , wf:json_encode(Data).


does_search_match(LangRec, SearchTerm0) ->
% OK, LA Fn CI-DESSUS ENVOIE UN PAR UN LES ÉLÉMENTS (LISTE DE TUPLES) ICI
%?PRINT(LangRec),
    %% We extract the label from the passed Lang "Rec" (which is just a proplist)
    Label0 = proplists:get_value(label, LangRec)
    %% And convert it ot lower case
    , Label = string:to_lower(wf:to_unicode_list(Label0))
    %% and also convert the SearchTerm to lowercase
    , SearchTerm = string:to_lower(SearchTerm0)
    %% Then see if SearchTerm is contained anywhere in the Label
    , string:str(Label, SearchTerm) > 0.


autocomplete_select_event(SelectedElement, _Tag) ->
    %% SelectedElement is a proplist
    %% Let's tell the user what we selected!
    wf:flash(proplists:get_value(<<"value">>, SelectedElement))
    , ok.

The problem seems to be tied to a character problem (oh nooo! I thought there wasn't any anymore:( as ?PRINT(ListTownsIds) displays a bad list with odd characters:

=INFO REPORT==== 9-Dec-2021::23:56:05.695021 ===
DEBUG: <0.24257.4>
offer_create:784
"ListTownsIds"
  [{<<"Basse-Goulaine">>,<<"basgou">>},
   {<<"Bouaye">>,<<"bouaye">>},

…

   {<<"St-Léger les Vignes">>,<<"stlegv">>},
   {<<"St-Sébastien sur Loire">>,<<"stseb">>},
   {<<"Thouaré sur Loire">>,<<"thoua">>},

…

Note that the same thing done from the Nitrogen console returns a ferpect ouput.

The query is pretty simple:

get_all_towns() ->
    pgapp:squery("SELECT town,id FROM tab.town ORDER BY town").

which normally returns (done manually from the nitrogen console):

(emp...@127.0.0.1)95> e_db:get_all_towns().
{ok,[{column,<<"town">>,varchar,1043,-1,52,0,39351,2},
     {column,<<"id">>,varchar,1043,-1,10,0,39351,1}],
    [{<<"Basse-Goulaine">>,<<"basgou">>},
     {<<"Bouaye">>,<<"bouaye">>},

…

     {<<"St-Léger les Vignes"/utf8>>,<<"stlegv">>},
     {<<"St-Sébastien sur"/utf8...>>,<<"stseb">>},
     {<<"Thouaré sur "/utf8...>>,<<"thoua">>},

…

So, once again I'm stuck  - where am I wrong?

Jean-Yves

[Bunny Lushington]

On Dec 9, 2021, at 17:38, 12u...@gmail.com <12u...@gmail.com> wrote:

I now meet another problem using pgapp which is just epgsql+poolboy - textbox_autocomplete is throwing a bad_generator error from a proplist build from a DB query:

You’re attempting to use ‘ok’ as a list in your comprehension on line 806.  You might have pgapp issues but that’s not what this error is about.

b.

[12u...@gmail.com]

Thanks Bunny, I found that but forgot to close it here.