Preventing a 'lua entry thread aborted: runtime error:' and continue anyway

[in...@ecsystems.nl]

Because of this issue: https://github.com/openresty/lua-resty-mysql/issues/14

I have a persistent sql link inside Lua (nginx) and try a simple "SELECT version()" to see if the link is still alive, however when it isn't alive Lua aborts (as it should), the question is: is it possible to tell Lua to ignore the error and continue anyway ? (because after the first failure I do a re-connect, if that fails it may abort normally)

[in...@ecsystems.nl]

Find it !  http://www.lua.org/pil/8.4.html

Which actually works :)

    function sqlconntest()
      if unexpected_condition then error() end
      local cur = assert (luaconMYSQL:execute ("SELECT version()"))
    end

[...]

          if not pcall(sqlconntest) then
            sqlreconn() -- auto reconnect when sql link has gone
          end

[Yichun Zhang (agentzh)]

Hello!

On Fri, Aug 15, 2014 at 10:51 AM, info wrote:
> I have a persistent sql link inside Lua (nginx) and try a simple "SELECT
> version()" to see if the link is still alive, however when it isn't alive
> Lua aborts (as it should), the question is: is it possible to tell Lua to
> ignore the error and continue anyway ? (because after the first failure I do
> a re-connect, if that fails it may abort normally)
>

This is strange. The lua-resty-mysql library always return the error
instead of throwing out a Lua exception. See, for example,

https://github.com/openresty/lua-resty-mysql#query

Will you provide the full raw nginx error message with the prefix
"'lua entry thread aborted: runtime error: "? I gather it should be
something else in your Lua code due to improper error handling on your
side.

> function sqlconntest()
> if unexpected_condition then error() end
> local cur = assert (luaconMYSQL:execute ("SELECT version()"))
> end

This code snippet does not look like that you're using
lua-resty-mysql. If it's indeed the case, then you need to ensure that
the mysql driver you're using will not block the nginx event loop.

Regards,
-agentzh

[in...@ecsystems.nl]

Heya,

On Friday, August 15, 2014 9:00:39 PM UTC+2, agentzh wrote:

Hello!

On Fri, Aug 15, 2014 at 10:51 AM,  info wrote:
Will you provide the full raw nginx error message with the prefix
"'lua entry thread aborted: runtime error: "? I gather it should be
something else in your Lua code due to improper error handling on your
side.

The connections are initiated in init_by_lua so that each worker has its own persistent connection,
then I close the connections externally simulating a mysql link failure, then you get:

2014/08/15 20:36:14 [error] 3476#3688: *4 lua entry thread aborted: runtime error: rewrite_by_lua:19: LuaSQL: Error executing query. MySQL: MySQL server has gone away

The below pcall solution just auto reconnects and makes sure the sql test does not abort runtime.
 

>     function sqlconntest()
>       if unexpected_condition then error() end
>       local cur = assert (luaconMYSQL:execute ("SELECT version()"))
>     end

This code snippet does not look like that you're using
lua-resty-mysql. If it's indeed the case, then you need to ensure that
the mysql driver you're using will not block the nginx event loop.

 Its not blocking, the 1.1 release of ngxLuaDB will have the full working sample.

[Yichun Zhang (agentzh)]

Hello!

On Fri, Aug 15, 2014 at 12:20 PM, info wrote:
> The connections are initiated in init_by_lua so that each worker has its own
> persistent connection,
> then I close the connections externally simulating a mysql link failure,

This approach looks inefficient. You really need a proper connection
pool here :) I wonder why you don't just use the lua-resty-mysql
library and the built-in connection pool.

> then you get:
>
> 2014/08/15 20:36:14 [error] 3476#3688: *4 lua entry thread aborted: runtime
> error: rewrite_by_lua:19: LuaSQL: Error executing query. MySQL: MySQL server
> has gone away
>

This is your MySQL driver then it has nothing to do with
lua-resty-mysql. I wonder why you referenced that #14 ticket for
lua-resty-mysql in the first place.

> The below pcall solution just auto reconnects and makes sure the sql test
> does not abort runtime.
>

pcall and Lua exceptions are relatively expensive and also cumbersome
to use. And it's better to return errors rather than raising an
exception. And that's why lua-resty-mysql and ngx_lua's Lua API try to
avoid Lua exceptions wherever possible :)

>
> Its not blocking, the 1.1 release of ngxLuaDB will have the full working
> sample.
>

I cannot find the source code of your "ngxLuaDB" thing through a quick
web search. Is it making use of ngx_lua's cosocket API or the
subrequest API? If not, then I can assure you that it must be blocking
nginx's event loop unless you introduce your own OS thread pools
(which have their own overhead like context switching and thread
synchronization).

Regards,
-agentzh

[in...@ecsystems.nl]

Heya,

On Friday, August 15, 2014 9:29:01 PM UTC+2, agentzh wrote:

Hello!

On Fri, Aug 15, 2014 at 12:20 PM,  info wrote:
> The connections are initiated in init_by_lua so that each worker has its own
> persistent connection,
> then I close the connections externally simulating a mysql link failure,

This approach looks inefficient. You really need a proper connection
pool here :) I wonder why you don't just use the lua-resty-mysql
library and the built-in connection pool.

This is Windows you know :) things work a bit differently.
 

This is your MySQL driver then it has nothing to do with
lua-resty-mysql. I wonder why you referenced that #14 ticket for
lua-resty-mysql in the first place.

Only because the issue is the same (how to test for a failed link).
 

I cannot find the source code of your "ngxLuaDB" thing through a quick
web search. Is it making use of ngx_lua's cosocket API or the
subrequest API? If not, then I can assure you that it must be blocking
nginx's event loop unless you introduce your own OS thread pools
(which have their own overhead like context switching and thread
synchronization).

ngxLuaDB is a redis+++ replacement for Windows,
http://forum.nginx.org/read.php?2,252488

The mysql example shows how each worker gets its data, the failed link thing is a GeoIP example I'm working on.

I've run tests on the mysql sample with 1.000 clients each with 1.000 requests on 2 workers and can't see any blocking going on.

Tests on the GeoIP sample with a lua_shared_dict SQL cache on 4 workers, 10.000 clients each with 5.000 requests and pcall's does a nice 2k/sec rate, so if there is something blocking I can't see it :)

[Yichun Zhang (agentzh)]

Hello!

On Fri, Aug 15, 2014 at 1:32 PM, info wrote:
>
> This is Windows you know :) things work a bit differently.
>

But basic things and concepts still remain the same :)

>
> Only because the issue is the same (how to test for a failed link).
>

Then you should make it clearer :) For ngx_lua's builtin connection
pool, there is actually a mechanism that automatically removes a
connection when dubious new read events happen on that connection such
that it cannot get reused by future requests. So the chance that you
get a broken connection from the pool is much smaller for relatively
idle servers. Also, the getreusedtimes() result can also be used on
the Lua land to actively give up connections that have been reused for
certain amount of times (like 10K times or 100K times). Basically,
passive error handling should be good enough here.

On that lua-resty-mysql ticket, an active ping query involves extra
overhead in an extra MySQL round trip (meaning more system calls, more
computation on the MySQL server side, and etc). I don't think it
should be recommended :)

>
> ngxLuaDB is a redis+++ replacement for Windows,
> http://forum.nginx.org/read.php?2,252488
>

Where can I found the source code?

> The mysql example shows how each worker gets its data, the failed link thing
> is a GeoIP example I'm working on.
> I've run tests on the mysql sample with 1.000 clients each with 1.000
> requests on 2 workers and can't see any blocking going on.
>

You should test the following mysql query with a *single* nginx worker
process configured:

select sleep(1);

And check how many requests per second you can have with certain
concurrency level (say, 100) for a single nginx worker.

If it's nonblocking, then for the concurrency level of 100 (like ab's
-c100 option), you should get somewhere near 100 req/sec. The actual
number measured can be a bit smaller than 100 req/sec, but should not
be far away. If it's blocking, then you can only get 1 req/sec (yes,
no kidding). If you're getting a rate larger than 100 req/sec, then
you must be incorrectly caching the query result inside nginx, which
defeats the purpose of this test :)

I've tested lua-resty-mysql with a *single* nginx worker and the
"select sleep(1)" MySQL query, and the command "ab -c100 -n 200 -k
localhost/t" gives the result after 2.037 seconds:

Requests per second: 98.20 [#/sec] (mean)

Quite close to the value in theory, 100.00 #/sec. So it is not
blocking nginx event loop. On the other hand, if it is blocking, then
the "ab" command will take way longer to complete and will eventually
print the something like 0.95 req/sec.

> Tests on the GeoIP sample with a lua_shared_dict SQL cache on 4 workers,
> 10.000 clients each with 5.000 requests and pcall's does a nice 2k/sec rate,
> so if there is something blocking I can't see it :)
>

Try testing the case that pcall actually catches some error :) And
compare that with simply returning and checking an error string ;)

Also, I cannot really parse your numbers like "10.000". Are you using
the dot (.) as the decimal point as in 3.14, or just as "," to
visually isolate zeros (as in 25,000,000)?

Again, you could do much better than 2k/sec with true I/O multiplexing
because even when you block the nginx event loop, you can still get 2k
req/sec by just strictly serially accessing MySQL. The 2k req/sec rate
alone cannot prove anything. You need to carefully design an
experiment. For example, you should really use the "select sleep(1)"
MySQL query above to quickly test if your MySQL queries are blocking
the nginx event loop :)

Finally, as I've stated in my previous mail, the "nonblocking magic"
cannot happen if you're not using cosockets nor subrequests when
running Lua code doing socket I/O atop the official ngx_lua module :)

Regards,
-agentzh

[in...@ecsystems.nl]

Heya!

On Friday, August 15, 2014 11:08:34 PM UTC+2, agentzh wrote:

Hello!

On Fri, Aug 15, 2014 at 1:32 PM,  info wrote:
>
> This is Windows you know :) things work a bit differently.

But basic things and concepts still remain the same :)

As I found out today, read on cause this is gonna be a big reply :)
 

that it cannot get reused by future requests. So the chance that you
get a broken connection from the pool is much smaller for relatively
idle servers. Also, the getreusedtimes() result can also be used on

Have a look at the example below, maybe you can give some pointers about this.
 

> ngxLuaDB is a redis+++ replacement for Windows,
> http://forum.nginx.org/read.php?2,252488
>

Where can I found the source code?

There ain't any source, nginx for Windows has Lua and lua-nginx-module build in, ngxLuaDB are DLL's that are compiled against Luajit so that Windows functionality becomes available inside nginx, its like .so modules can be added, it works the same with a .dll
 

You should test the following mysql query with a *single* nginx worker
process configured:

    select sleep(1);

You were right (however I hate having to say that :)) I read your pdf about the redis presentation.
 

Try testing the case that pcall actually catches some error :) And
compare that with simply returning and checking an error string ;)

Well it didn't slow things down, its just an extra query which should always work.
 

Finally, as I've stated in my previous mail, the "nonblocking magic"
cannot happen if you're not using cosockets nor subrequests when
running Lua code doing socket I/O atop the official ngx_lua module :)

See my tested example below :)

Before I paste my work of today I have 2 issues,
1: I would like to make a persistent connection via init_by_lua but when trying I get this error:
2014/08/16 20:45:35 [error] 1516#3236: init_by_lua error: .\openresty\mysql.lua:462: no request found
stack traceback:
    [C]: in function 'tcp'
    .\openresty\mysql.lua:462: in function 'new'
    init_by_lua:5: in main chunk

2: local row = cjson.encode(res) returns for example [{"cn":"Canada"}] from the database,
how do I get the plain value Canada from this table ??
 
And now the paste which is tested non blocking !


worker_processes  2;
#error_log  logs/error.log debug;
error_log  logs/error.log;
pid        logs/nginx.pid;
events {
  worker_connections  8192;
}

http {
  log_format  main  '[$time_local] $remote_addr:$remote_port - $remote_user "$request" '
                    '$status $body_bytes_sent "$http_referer" '
                    '"$http_user_agent" "$http_x_forwarded_for" $upstream_cache_status';
  access_log  logs/access.log  main;

# Initialize a shared Lua cache for SQL queries
  lua_shared_dict SqlQueries 10m;

  server {
    listen        80;
    server_name   localhost;
    root          html;

    # include debugbylua.lua;
    location / {
      index  index.html index.htm;
      try_files $uri $uri/ =404;
    }

    # Get GeoIP in a MySQL database: http://vincent.delau.net/php/geoip.html
    # The old GeoliteIP csv: http://dev.maxmind.com/geoip/legacy/geolite/
    # curl -i "http://127.0.0.1/geoiptest?ip=192.10.224.5"
    # curl -i "http://127.0.0.1/geoiptest?ip=192.210.224.5"
    # blocking test, set worker_processes to 1 before the 'ab' test and disable cache
    # ab -n 200 -c 100 -k -i "http://127.0.0.1:80/geoiptest?ip=192.210.224.5"
    location /geoiptest {
      content_by_lua '
        local args = ngx.req.get_uri_args()
        local reqip
        function ip2long(curIP)
          local _,_,a,b,c,d = string.find(curIP, "(%d+).(%d+).(%d+).(%d+)")
          if (tonumber(a) and tonumber(b) and tonumber(c) and tonumber(d) ) then
            return a*16777216 + b*65536 + c*256 + d
          else
            return 0
          end
        end
        ngx.header.content_type = "text/plain"
        if not args["ip"] then args["ip"] = "123.45.67.89" end
        reqip = ip2long(args["ip"])
        local Query = ngx.shared.SqlQueries
        local QResult = Query:get(reqip) -- disable this line disables the cache
        ngx.say("IP request "..args["ip"].." to GeoIP "..reqip.."\\n")
        if not QResult then
          local mysql = require "openresty.mysql"
          local cjson = require "openresty.ljson"
          local db, err = mysql:new()
          if not db then
              ngx.say("failed to instantiate mysql: ", err)
              return
          end
          db:set_timeout(3000) -- 3 sec
          local ok, err, errno, sqlstate = db:connect{
              host = "127.0.0.1", port = 3306, database = "GeoIP",
              user = "user", password = "password",
              max_packet_size = 1024 * 1024 }
          if not ok then
              ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
              return
          end
--          restt, err, errno, sqlstate = db:query("SELECT sleep(1)", 10) -- Tested, non-blocking!
          local res, err, errno, sqlstate = db:query("SELECT cn, cn FROM ip NATURAL JOIN cc WHERE "..reqip.." BETWEEN start AND end LIMIT 1", 10)
          local row = cjson.encode(res)
          if not row then row = "Unknown" end
          ngx.say (row.." from database\\n")
          Query:set(reqip, row) -- stick results in cache
        else
          ngx.say(QResult.." from cache\\n")
        end
      ';
    }

  } # server block end

} # http end


Comments are welcome :)

[Yichun Zhang (agentzh)]

Hello!

On Sat, Aug 16, 2014 at 12:43 PM, info wrote:
>
> There ain't any source, nginx for Windows has Lua and lua-nginx-module build
> in, ngxLuaDB are DLL's that are compiled against Luajit so that Windows
> functionality becomes available inside nginx, its like .so modules can be
> added, it works the same with a .dll
>

DLLs must be generated from something, right? I don't think you create
the DLL binaries by hand in a binary editor :)

I used to work in Windows environment for nontrivial C/C++
development. DLLs are similar to Linux's DSO.

>
> Well it didn't slow things down, its just an extra query which should always
> work.
>

An extra query means an extra round-trip to the MySQL server, which
takes time. Not to mention the extra data copying, system calls,
context switches, and the overhead involved in parsing and generating
MySQL packets according to its wire protocol. How can this be free?
This is in fact huge and can be comparable to simple MySQL queries
hitting MySQL's query cache :)

> Before I paste my work of today I have 2 issues,
> 1: I would like to make a persistent connection via init_by_lua but when
> trying I get this error:
> 2014/08/16 20:45:35 [error] 1516#3236: init_by_lua error:
> .\openresty\mysql.lua:462: no request found

This error message already makes it clear that you need a request
object for that Lua call (i.e., the "new" method). init_by_lua does
not have a request context.

Also, check out the code example in lua-resty-mysql's manual. Never
use lua-resyt-mysql (or other cosockets-based libraries) in contexts
other than the request handlers or timer handlers (timer works because
it has a fake request). See the "Limitations" section in
lua-resty-mysql's manual [1].

If you want to reuse connections, then use the connection pool
mechanism (i.e., the set_keepalive method). Do not recycle the wrapper
Lua objects directly because these wrapper objects' lifetime are
limited to the request handler creating them.

> 2: local row = cjson.encode(res) returns for example [{"cn":"Canada"}] from
> the database,
> how do I get the plain value Canada from this table ??
>

Just manipulate the "res" Lua table directly, e.g.,

ngx.say("res.cdn = ", res.cn)

cjson.encode() is to serialize Lua values to JSON-encoded strings.
Isn't that obvious enough?

> And now the paste which is tested non blocking !
>

Not bad :)

But how did you miss the set_keepalive() call from the code sample in
lua-resty-mysql's example [2]? In your code snippet, the mysql
connections are always shut down when your resty.mysql object is GC'd
because you do not put your current connection into the connection
pool via set_keepalive() when you finish using it.

One last and but not least suggestion is to read the documentation
more carefully :)

Regards,
-agentzh

[1] https://github.com/openresty/lua-resty-mysql#limitations
[2] https://github.com/openresty/lua-resty-mysql#synopsis

[Yichun Zhang (agentzh)]

Hello!

On Sat, Aug 16, 2014 at 1:11 PM, Yichun Zhang (agentzh) wrote:
>> 2: local row = cjson.encode(res) returns for example [{"cn":"Canada"}] from
>> the database,
>> how do I get the plain value Canada from this table ??
>>
>
> Just manipulate the "res" Lua table directly, e.g.,
>
> ngx.say("res.cdn = ", res.cn)
>

Sorry, the "cn" Lua table is actually an array containing a hash-table
according to its JSON content. So it should really be like this:

ngx.say("res[1].cn = ", res[1].cn) -- the "cn" field value in the
first row of the resultset

Regards,
-agentzh

[in...@ecsystems.nl]

Heya!

On Saturday, August 16, 2014 10:11:36 PM UTC+2, agentzh wrote:

Hello!

DLLs must be generated from something, right? I don't think you create
the DLL binaries by hand in a binary editor :)

Sometimes a bin editor comes in handy :) but you can find sources from here for example:
http://www.scilua.org
http://lua-users.org/wiki/LoadLibrary
http://www.gammon.com.au/forum/?id=6324
http://luabinaries.sourceforge.net/
https://code.google.com/p/luaforwindows/
 

> 2: local row = cjson.encode(res) returns for example [{"cn":"Canada"}] from
> the database,
> how do I get the plain value Canada from this table ??
>

Just manipulate the "res" Lua table directly, e.g.,

    ngx.say("res.cdn = ", res.cn)

cjson.encode() is to serialize Lua values to JSON-encoded strings.
Isn't that obvious enough?

I would not be asking if it was obvious :) but this works:
          local row = (res[1].cn)
 

> And now the paste which is tested non blocking !
>

Not bad :)

Can you repeat that :)
 

But how did you miss the set_keepalive() call from the code sample in
lua-resty-mysql's example [2]? In your code snippet, the mysql

I didn't miss it, it didn't work and caused some weird results, I just tried it again and when used before a db:query it does weird things, it works correctly when set after the first db:query
 

One last and but not least suggestion is to read the documentation
more carefully :)

Real men don't read manuals :)

Now lets see if I can get this to work for ODBC....

[Yichun Zhang (agentzh)]

Hello!

On Sat, Aug 16, 2014 at 1:51 PM, info wrote:
> Sometimes a bin editor comes in handy :) but you can find sources from here
> for example:
> http://www.scilua.org
> http://lua-users.org/wiki/LoadLibrary
> http://www.gammon.com.au/forum/?id=6324
> http://luabinaries.sourceforge.net/
> https://code.google.com/p/luaforwindows/
>

Okay, you are using these classic Lua libraries. Then all these things
doing I/O will be blocking without doubt. And that's why people have
been working on various lua-resty-* libraries for OpenResty.

> I would not be asking if it was obvious :) but this works:
> local row = (res[1].cn)
>

Then you're not familiar with web development at all :)

>
> I didn't miss it, it didn't work and caused some weird results, I just tried
> it again and when used before a db:query it does weird things, it works
> correctly when set after the first db:query
>

Always check the return values of the set_keepalive() call. The error
string returned in case of errors can usually tell you what you do
wrong.

One common mistake in the context of lua-resty-mysql is that you have
not consumed all the resultsets from your previous query (which is
indicated by the "again" err return value). See

https://github.com/openresty/lua-resty-mysql#query

Always do proper error handling and log any error messages to save
debugging time.

>
> Real men don't read manuals :)
>

IMHO, this is not something you should be proud of. I'd say it is as
unprofessional as writing some software without any docs. And this
also means a much higher chance of wasting your time and my time.

> Now lets see if I can get this to work for ODBC....
>

Good luck :) I'm always interested in a lua-resty-odbc library based
on ngx_lua's cosockets :)

Regards,
-agentzh

[in...@ecsystems.nl]

Heya,

On Saturday, August 16, 2014 11:27:56 PM UTC+2, agentzh wrote:

One common mistake in the context of lua-resty-mysql is that you have
not consumed all the resultsets from your previous query (which is
indicated by the "again" err return value). See

Would this be a 'good' way to get all the results in one table:

          restmp = {} res = {} err = "again"
          while err and string.lower(err) == "again" do
            restmp, err, errno, sqlstate = db:query("SELECT .......
            for k,v in ipairs(restmp) do
              table.insert(res, v)
            end
          end
 

[Yichun Zhang (agentzh)]

Hello!

On Sun, Aug 17, 2014 at 4:18 AM, info wrote:
> Would this be a 'good' way to get all the results in one table:
>
> restmp = {} res = {} err = "again"
> while err and string.lower(err) == "again" do
> restmp, err, errno, sqlstate = db:query("SELECT .......
> for k,v in ipairs(restmp) do
> table.insert(res, v)
> end
> end
>

This snippet looks a bit confusing (due to the explicitly initialized
"again" err) and also defeats the purpose of streaming processing of
the "again" pulling design. Furthermore,

1. string.lower() is completely unnecessary and also a bit expensive
(because it creates a new string object).

2. table.insert() is expensive because it always fetch the length of
the table (similar to the '#' operator) which is an O(n) operation.

3. It's wrong to assume that the query result is always an array-like
table (for example, SQL DDL queries do not return a resultset).

Regards,
-agentzh

[Yichun Zhang (agentzh)]

Hello!

On Sun, Aug 17, 2014 at 4:18 AM, <in...@ecsystems.nl> wrote:
> restmp = {} res = {} err = "again"
> while err and string.lower(err) == "again" do
> restmp, err, errno, sqlstate = db:query("SELECT .......
> for k,v in ipairs(restmp) do
> table.insert(res, v)
> end
> end
>

Also, this is completely wrong because in case of "again" err, you
should call read_result() instead of query() because query() is
essentially send_query() + read_result().

I think the documentation already makes it very clear and it even
provides a complete example. Why don't you spend a few minutes on it?
I will not reply to you for such obvious things to save my own time
for something more meaningful.

Regards,
-agentzh

[in...@ecsystems.nl]

On Monday, August 18, 2014 12:23:02 AM UTC+2, agentzh wrote:

 
I will not reply to you for such obvious things to save my own time
for something more meaningful.

Regards,
-agentzh

Being rude does not suit your personality or competence.