Rewrite lookup via MySQL table

Tue Aug 24 07:32:21 MSD 2010

On Tue, Aug 24, 2010 at 8:22 AM, btafoya <nginx-forum at nginx.us> wrote:
> Any chance I can use a MySQL database table to do a lookup to return the
> url to rewrite to?
>
> Let me explain the situation first...
>
> Magento utilizes rewrite rules heavily but does the majority of the
> rewrite within the Front controller. The problem is that it is VERY
> in-efficient and a store with more than 10,000 products is severely
> delay. But, all information is referenced in a table 'core_url_rewrite'
> so my though it so do the lookup first in Nginx and hand off to Magento
> with the non-seo url.
>

Here's a sample configuration that has been tested on my laptop.

Consider you seo uri is /baz, the true uri is /foo/bar. And I have the
following table in my local mysql "test" database:

   create table my_url_map(id serial, url text, seo_url);
   insert into my_url_map(url, seo_url)values('/foo/bar', '/baz');

And I build my nginx 0.8.41 this way:

    ./configure \
                --add-module=/path/to/ngx_devel_kit \
                --add-module=/path/to/set-misc-nginx-module \
                --add-module=/path/to/ngx_http_auth_request_module-0.2 \
                --add-module=/path/to/echo-nginx-module \
                --add-module=/path/to/lua-nginx-module \
                --add-module=/path/to/drizzle-nginx-module \
                --add-module=/path/to/rds-json-nginx-module

Also, I have lua 5.1.4 and the lua-yajl library installed to my system.

And here's the central part in my nginx.conf:

    upstream backend {
        drizzle_server 127.0.0.1:3306 dbname=test
             password=some_pass user=monty protocol=mysql;
        drizzle_keepalive max=300 mode=single overflow=ignore;
    }

    lua_package_cpath '/path/to/your/lua/yajl/library/?.so';

    server {
          ...

        location /conv-mysql {
            internal;
            set_quote_sql_str $seo_uri $query_string; # to prevent sql injection
            drizzle_query "select url from my_url_map where seo_url=$seo_uri";
            drizzle_pass backend;
            rds_json on;
        }

        location /conv-uid {
            internal;
            content_by_lua_file 'html/foo.lua';
        }

        location /jump {
            internal;
            rewrite ^ $query_string? redirect;
        }

        # your SEO uri
        location /baz {
            set $my_uri $uri;
            auth_request /conv-uid;

            echo_exec /jump $my_uri;
        }

Then let's access /baz from the client side:

    $ curl -i localhost:1984/baz
    HTTP/1.1 302 Moved Temporarily
    Server: nginx/0.8.41 (without pool)
    Date: Tue, 24 Aug 2010 03:28:42 GMT
    Content-Type: text/html
    Content-Length: 176
    Location: http://localhost:1984/foo/bar
    Connection: keep-alive

    <html>
    <head><title>302 Found</title></head>
    <body bgcolor="white">
    <center><h1>302 Found</h1></center>
    <hr><center>nginx/0.8.41 (without pool)</center>
    </body>
    </html>

We can optimize this further by introducing ngx_srcache, ngx_memc and
memcached clusters with connection pooling to cache our mysql result
sets. But I'd keep this sample config minimal for now :) If anybody is
interested, just follow up and ask here :)

Note that 0.8.42+ won't work for this config, and the request will
hang for reasons that I don't know yet. We do not have the resource to
track all the aggressive changes in recent nginx core and that's no
fun at all.

Cheers,
-agentzh