{"action":"created","issue":{"url":"https://api.github.com/repos/openresty/lua-resty-mysql/issues/60","repository_url":"https://api.github.com/repos/openresty/lua-resty-mysql","labels_url":"https://api.github.com/repos/openresty/lua-resty-mysql/issues/60/labels{/name}","comments_url":"https://api.github.com/repos/openresty/lua-resty-mysql/issues/60/comments","events_url":"https://api.github.com/repos/openresty/lua-resty-mysql/issues/60/events","html_url":"https://github.com/openresty/lua-resty-mysql/pull/60","id":228548137,"number":60,"title":"add charset supported","user":{"login":"wilhelmliao","id":16433590,"avatar_url":"https://avatars0.githubusercontent.com/u/16433590?v=3","gravatar_id":"","url":"https://api.github.com/users/wilhelmliao","html_url":"https://github.com/wilhelmliao","followers_url":"https://api.github.com/users/wilhelmliao/followers","following_url":"https://api.github.com/users/wilhelmliao/following{/other_user}","gists_url":"https://api.github.com/users/wilhelmliao/gists{/gist_id}","starred_url":"https://api.github.com/users/wilhelmliao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wilhelmliao/subscriptions","organizations_url":"https://api.github.com/users/wilhelmliao/orgs","repos_url":"https://api.github.com/users/wilhelmliao/repos","events_url":"https://api.github.com/users/wilhelmliao/events{/privacy}","received_events_url":"https://api.github.com/users/wilhelmliao/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":3,"created_at":"2017-05-14T15:15:40Z","updated_at":"2017-05-29T19:21:28Z","closed_at":"2017-05-29T19:21:28Z","pull_request":{"url":"https://api.github.com/repos/openresty/lua-resty-mysql/pulls/60","html_url":"https://github.com/openresty/lua-resty-mysql/pull/60","diff_url":"https://github.com/openresty/lua-resty-mysql/pull/60.diff","patch_url":"https://github.com/openresty/lua-resty-mysql/pull/60.patch"},"body":""},"comment":{"url":"https://api.github.com/repos/openresty/lua-resty-mysql/issues/comments/304718405","html_url":"https://github.com/openresty/lua-resty-mysql/pull/60#issuecomment-304718405","issue_url":"https://api.github.com/repos/openresty/lua-resty-mysql/issues/60","id":304718405,"user":{"login":"agentzh","id":56241,"avatar_url":"https://avatars2.githubusercontent.com/u/56241?v=3","gravatar_id":"","url":"https://api.github.com/users/agentzh","html_url":"https://github.com/agentzh","followers_url":"https://api.github.com/users/agentzh/followers","following_url":"https://api.github.com/users/agentzh/following{/other_user}","gists_url":"https://api.github.com/users/agentzh/gists{/gist_id}","starred_url":"https://api.github.com/users/agentzh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/agentzh/subscriptions","organizations_url":"https://api.github.com/users/agentzh/orgs","repos_url":"https://api.github.com/users/agentzh/repos","events_url":"https://api.github.com/users/agentzh/events{/privacy}","received_events_url":"https://api.github.com/users/agentzh/received_events","type":"User","site_admin":false},"created_at":"2017-05-29T19:21:28Z","updated_at":"2017-05-29T19:21:28Z","body":"@wilhelmliao Merged with the following extra patch. Thanks!\r\n\r\n```diff\r\ndiff --git a/README.markdown b/README.markdown\r\nindex 0ee94aa..e5dd69b 100644\r\n--- a/README.markdown\r\n+++ b/README.markdown\r\n@@ -92,7 +92,9 @@ Synopsis\r\n                     database = \"ngx_test\",\r\n                     user = \"ngx_test\",\r\n                     password = \"ngx_test\",\r\n-                    max_packet_size = 1024 * 1024 }\r\n+                    charset = \"utf8\",\r\n+                    max_packet_size = 1024 * 1024,\r\n+                }\r\n \r\n                 if not ok then\r\n                     ngx.say(\"failed to connect: \", err, \": \", errcode, \" \", sqlstate)\r\n@@ -202,6 +204,14 @@ The `options` argument is a Lua table holding the following keys:\r\n * `password`\r\n \r\n     MySQL account password for login (in clear text).\r\n+* `charset`\r\n+\r\n+    the character set used on the MySQL connection, which can be different from the default charset setting.\r\n+The following values are accepted: `big5`, `dec8`, `cp850`, `hp8`, `koi8r`, `latin1`, `latin2`,\r\n+`swe7`, `ascii`, `ujis`, `sjis`, `hebrew`, `tis620`, `euckr`, `koi8u`, `gb2312`, `greek`,\r\n+`cp1250`, `gbk`, `latin5`, `armscii8`, `utf8`, `ucs2`, `cp866`, `keybcs2`, `macce`,\r\n+`macroman`, `cp852`, `latin7`, `utf8mb4`, `cp1251`, `utf16`, `utf16le`, `cp1256`,\r\n+`cp1257`, `utf32`, `binary`, `geostd8`, `cp932`, `eucjpms`, `gb18030`.\r\n * `max_packet_size`\r\n \r\n     the upper limit for the reply packets sent from the MySQL server (default to 1MB).\r\ndiff --git a/lib/resty/mysql.lua b/lib/resty/mysql.lua\r\nindex 3030887..ff77ee8 100644\r\n--- a/lib/resty/mysql.lua\r\n+++ b/lib/resty/mysql.lua\r\n@@ -55,51 +55,53 @@ local SERVER_MORE_RESULTS_EXISTS = 8\r\n -- 16MB - 1, the default max allowed packet size used by libmysqlclient\r\n local FULL_PACKET_SIZE = 16777215\r\n \r\n--- add by Wilhelm Liao @ 2017-05-14\r\n--- SELECT CHARACTER_SET_NAME, ID FROM information_schema.collations WHERE IS_DEFAULT = 'Yes' ORDER BY id;\r\n+-- the following charset map is generated from the following mysql query:\r\n+--   SELECT CHARACTER_SET_NAME, ID\r\n+--   FROM information_schema.collations\r\n+--   WHERE IS_DEFAULT = 'Yes' ORDER BY id;\r\n local CHARSET_MAP = {\r\n-  _default  = 0,\r\n-  big5      = 1,\r\n-  dec8      = 3,\r\n-  cp850     = 4,\r\n-  hp8       = 6,\r\n-  koi8r     = 7,\r\n-  latin1    = 8,\r\n-  latin2    = 9,\r\n-  swe7      = 10,\r\n-  ascii     = 11,\r\n-  ujis      = 12,\r\n-  sjis      = 13,\r\n-  hebrew    = 16,\r\n-  tis620    = 18,\r\n-  euckr     = 19,\r\n-  koi8u     = 22,\r\n-  gb2312    = 24,\r\n-  greek     = 25,\r\n-  cp1250    = 26,\r\n-  gbk       = 28,\r\n-  latin5    = 30,\r\n-  armscii8  = 32,\r\n-  utf8      = 33,\r\n-  ucs2      = 35,\r\n-  cp866     = 36,\r\n-  keybcs2   = 37,\r\n-  macce     = 38,\r\n-  macroman  = 39,\r\n-  cp852     = 40,\r\n-  latin7    = 41,\r\n-  utf8mb4   = 45,\r\n-  cp1251    = 51,\r\n-  utf16     = 54,\r\n-  utf16le   = 56,\r\n-  cp1256    = 57,\r\n-  cp1257    = 59,\r\n-  utf32     = 60,\r\n-  binary    = 63,\r\n-  geostd8   = 92,\r\n-  cp932     = 95,\r\n-  eucjpms   = 97,\r\n-  gb18030   = 248\r\n+    _default  = 0,\r\n+    big5      = 1,\r\n+    dec8      = 3,\r\n+    cp850     = 4,\r\n+    hp8       = 6,\r\n+    koi8r     = 7,\r\n+    latin1    = 8,\r\n+    latin2    = 9,\r\n+    swe7      = 10,\r\n+    ascii     = 11,\r\n+    ujis      = 12,\r\n+    sjis      = 13,\r\n+    hebrew    = 16,\r\n+    tis620    = 18,\r\n+    euckr     = 19,\r\n+    koi8u     = 22,\r\n+    gb2312    = 24,\r\n+    greek     = 25,\r\n+    cp1250    = 26,\r\n+    gbk       = 28,\r\n+    latin5    = 30,\r\n+    armscii8  = 32,\r\n+    utf8      = 33,\r\n+    ucs2      = 35,\r\n+    cp866     = 36,\r\n+    keybcs2   = 37,\r\n+    macce     = 38,\r\n+    macroman  = 39,\r\n+    cp852     = 40,\r\n+    latin7    = 41,\r\n+    utf8mb4   = 45,\r\n+    cp1251    = 51,\r\n+    utf16     = 54,\r\n+    utf16le   = 56,\r\n+    cp1256    = 57,\r\n+    cp1257    = 59,\r\n+    utf32     = 60,\r\n+    binary    = 63,\r\n+    geostd8   = 92,\r\n+    cp932     = 95,\r\n+    eucjpms   = 97,\r\n+    gb18030   = 248\r\n }\r\n \r\n local mt = { __index = _M }\r\n@@ -553,7 +555,7 @@ function _M.connect(self, opts)\r\n \r\n     local charset = CHARSET_MAP[opts.charset or \"_default\"]\r\n     if not charset then\r\n-      return nil, \"charset '\" .. opts.charset .. \"' is not supported\"\r\n+        return nil, \"charset '\" .. opts.charset .. \"' is not supported\"\r\n     end\r\n \r\n     local pool = opts.pool\r\ndiff --git a/t/charset.t b/t/charset.t\r\nnew file mode 100644\r\nindex 0000000..2db4ac7\r\n--- /dev/null\r\n+++ b/t/charset.t\r\n@@ -0,0 +1,221 @@\r\n+# vim:set ft= ts=4 sw=4 et:\r\n+\r\n+use Test::Nginx::Socket::Lua;\r\n+use Cwd qw(cwd);\r\n+\r\n+repeat_each(2);\r\n+\r\n+plan tests => repeat_each() * (3 * blocks());\r\n+\r\n+my $pwd = cwd();\r\n+\r\n+our $HttpConfig = qq{\r\n+    resolver \\$TEST_NGINX_RESOLVER;\r\n+    lua_package_path \"$pwd/lib/?.lua;$pwd/t/lib/?.lua;;\";\r\n+    lua_package_cpath \"/usr/local/openresty-debug/lualib/?.so;/usr/local/openresty/lualib/?.so;;\";\r\n+};\r\n+\r\n+$ENV{TEST_NGINX_RESOLVER} = '8.8.8.8';\r\n+$ENV{TEST_NGINX_MYSQL_PORT} ||= 3306;\r\n+$ENV{TEST_NGINX_MYSQL_HOST} ||= '127.0.0.1';\r\n+$ENV{TEST_NGINX_MYSQL_PATH} ||= '/var/run/mysql/mysql.sock';\r\n+\r\n+#log_level 'warn';\r\n+\r\n+#no_long_string();\r\n+no_shuffle();\r\n+check_accum_error_log();\r\n+\r\n+run_tests();\r\n+\r\n+__DATA__\r\n+\r\n+=== TEST 1: connect db using charset option (utf8)\r\n+--- http_config eval: $::HttpConfig\r\n+--- config\r\n+    location /t {\r\n+        content_by_lua_block {\r\n+            local ljson = require \"ljson\"\r\n+            local mysql = require \"resty.mysql\"\r\n+            local db = mysql:new()\r\n+\r\n+            db:set_timeout(1000) -- 1 sec\r\n+\r\n+            local ok, err, errno, sqlstate = db:connect({\r\n+                path     = \"$TEST_NGINX_MYSQL_PATH\",\r\n+                database = \"ngx_test\",\r\n+                user     = \"ngx_test\",\r\n+                password = \"ngx_test\",\r\n+                charset  = \"utf8\",\r\n+                pool     = \"my_pool\"})\r\n+\r\n+            if not ok then\r\n+                ngx.say(\"failed to connect: \", err, \": \", errno, \" \", sqlstate)\r\n+                return\r\n+            end\r\n+\r\n+            -- generate test data\r\n+            local res, err, errno, sqlstate = db:query(\"DROP TABLE IF EXISTS cats\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            res, err, errno, sqlstate = db:query(\"CREATE TABLE cats (id serial PRIMARY KEY, name VARCHAR(128)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            -- add new record with '愛麗絲' by utf8 encoded.\r\n+            res, err, errno, sqlstate = db:query(\"INSERT INTO cats(name) VALUES (0xe6849be9ba97e7b5b2)\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            res, err, errno, sqlstate = db:query(\"SELECT * FROM cats\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            db:close()\r\n+\r\n+            ngx.say(ljson.encode(res))\r\n+        }\r\n+    }\r\n+--- request\r\n+GET /t\r\n+--- response_body\r\n+[{\"id\":\"1\",\"name\":\"愛麗絲\"}]\r\n+--- no_error_log\r\n+[error]\r\n+\r\n+\r\n+\r\n+=== TEST 2: connect db using charset option (big5)\r\n+--- http_config eval: $::HttpConfig\r\n+--- config\r\n+    location /t {\r\n+        content_by_lua_block {\r\n+            local ljson = require \"ljson\"\r\n+            local mysql = require \"resty.mysql\"\r\n+            local db = mysql:new()\r\n+\r\n+            db:set_timeout(1000) -- 1 sec\r\n+\r\n+            local ok, err, errno, sqlstate = db:connect({\r\n+                path     = \"$TEST_NGINX_MYSQL_PATH\",\r\n+                database = \"ngx_test\",\r\n+                user     = \"ngx_test\",\r\n+                password = \"ngx_test\",\r\n+                charset  = \"big5\",\r\n+                pool     = \"my_pool\"})\r\n+\r\n+            if not ok then\r\n+                ngx.say(\"failed to connect: \", err, \": \", errno, \" \", sqlstate)\r\n+                return\r\n+            end\r\n+\r\n+            -- generate test data\r\n+            local res, err, errno, sqlstate = db:query(\"DROP TABLE IF EXISTS cats\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            res, err, errno, sqlstate = db:query(\"CREATE TABLE cats (id serial PRIMARY KEY, name VARCHAR(128)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            -- add new record with '愛麗絲' by utf8 encoded.\r\n+            res, err, errno, sqlstate = db:query(\"INSERT INTO cats(name) VALUES (0xe6849be9ba97e7b5b2)\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            res, err, errno, sqlstate = db:query(\"SELECT * FROM cats\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            db:close()\r\n+\r\n+            ngx.say(ljson.encode(res))\r\n+        }\r\n+    }\r\n+--- request\r\n+GET /t\r\n+--- response_body eval\r\n+qq/[{\"id\":\"1\",\"name\":\"\\x{b7}R\\x{c4}R\\x{b5}\\x{b7}\"}]\\n/\r\n+--- no_error_log\r\n+[error]\r\n+\r\n+\r\n+\r\n+=== TEST 3: connect db using charset option (gbk)\r\n+--- http_config eval: $::HttpConfig\r\n+--- config\r\n+    location /t {\r\n+        content_by_lua_block {\r\n+            local ljson = require \"ljson\"\r\n+            local mysql = require \"resty.mysql\"\r\n+            local db = mysql:new()\r\n+\r\n+            db:set_timeout(1000) -- 1 sec\r\n+\r\n+            local ok, err, errno, sqlstate = db:connect({\r\n+                path     = \"$TEST_NGINX_MYSQL_PATH\",\r\n+                database = \"ngx_test\",\r\n+                user     = \"ngx_test\",\r\n+                password = \"ngx_test\",\r\n+                charset  = \"gbk\",\r\n+                pool     = \"my_pool\"})\r\n+\r\n+            if not ok then\r\n+                ngx.say(\"failed to connect: \", err, \": \", errno, \" \", sqlstate)\r\n+                return\r\n+            end\r\n+\r\n+            -- generate test data\r\n+            local res, err, errno, sqlstate = db:query(\"DROP TABLE IF EXISTS cats\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            res, err, errno, sqlstate = db:query(\"CREATE TABLE cats (id serial PRIMARY KEY, name VARCHAR(128)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            -- add new record with '愛麗絲' by utf8 encoded.\r\n+            res, err, errno, sqlstate = db:query(\"INSERT INTO cats(name) VALUES (0xe6849be9ba97e7b5b2)\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            res, err, errno, sqlstate = db:query(\"SELECT * FROM cats\")\r\n+            if not res then\r\n+                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n+                return\r\n+            end\r\n+\r\n+            db:close()\r\n+\r\n+            ngx.say(ljson.encode(res))\r\n+        }\r\n+    }\r\n+--- request\r\n+GET /t\r\n+--- response_body eval\r\n+qq/[{\"id\":\"1\",\"name\":\"\\x{90}\\x{db}\\x{fb}\\x{90}\\x{bd}z\"}]\\n/\r\n+--- no_error_log\r\n+[error]\r\ndiff --git a/t/sanity.t b/t/sanity.t\r\nindex 7f397a6..f57134d 100644\r\n--- a/t/sanity.t\r\n+++ b/t/sanity.t\r\n@@ -1321,67 +1321,3 @@ success\r\n --- no_error_log\r\n [error]\r\n --- timeout: 10\r\n-\r\n-\r\n-\r\n-=== TEST 20: connect db using charset option\r\n---- http_config eval: $::HttpConfig\r\n---- config\r\n-    location /t {\r\n-        content_by_lua_block {\r\n-            local ljson = require \"ljson\"\r\n-            local mysql = require \"resty.mysql\"\r\n-            local db = mysql:new()\r\n-\r\n-            db:set_timeout(1000) -- 1 sec\r\n-\r\n-            local ok, err, errno, sqlstate = db:connect({\r\n-                path     = \"$TEST_NGINX_MYSQL_PATH\",\r\n-                database = \"ngx_test\",\r\n-                user     = \"ngx_test\",\r\n-                password = \"ngx_test\",\r\n-                charset  = \"utf8\",\r\n-                pool     = \"my_pool\"})\r\n-\r\n-            if not ok then\r\n-                ngx.say(\"failed to connect: \", err, \": \", errno, \" \", sqlstate)\r\n-                return\r\n-            end\r\n-\r\n-            -- generate test data\r\n-            local res, err, errno, sqlstate = db:query(\"DROP TABLE IF EXISTS cats\")\r\n-            if not res then\r\n-                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n-                return\r\n-            end\r\n-\r\n-            res, err, errno, sqlstate = db:query(\"CREATE TABLE cats (id serial PRIMARY KEY, name VARCHAR(128)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\")\r\n-            if not res then\r\n-                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n-                return\r\n-            end\r\n-            \r\n-            -- add new record with '愛麗絲' by utf8 encoded.\r\n-            res, err, errno, sqlstate = db:query(\"INSERT INTO cats(name) VALUES (0xe6849be9ba97e7b5b2)\")\r\n-            if not res then\r\n-                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n-                return\r\n-            end\r\n-            \r\n-            res, err, errno, sqlstate = db:query(\"SELECT * FROM cats\")\r\n-            if not res then\r\n-                ngx.say(\"bad result: \", err, \": \", errno, \": \", sqlstate, \".\")\r\n-                return\r\n-            end\r\n-            \r\n-            db:close()\r\n-\r\n-            ngx.say( ljson.encode(res) )\r\n-        }\r\n-    }\r\n---- request\r\n-GET /t\r\n---- response_body\r\n-[{\"id\":\"1\",\"name\":\"愛麗絲\"}]\r\n---- no_error_log\r\n-[error]\r\n```"}}
{"actor":{"display_login":"agentzh"}}