MySQL values returned as arrays of ASCII integers

[clovis-maniguet]

Versions

• k6 v0.36.0 ((devel), go1.17.7, linux/amd64)
• Dockerized [email protected]

What

On table of 926 rows named filled_table;

SELECT *

export default function () {
  const results = sql.query(db, 'SELECT * FROM filled_table;');
  check(results, {
    'is length 926': (r) => r.length === 926
  });
}

% ./k6 run script.js

          /\      |‾‾| /‾‾/   /‾‾/
     /\  /  \     |  |/  /   /  /
    /  \/    \    |     (   /   ‾‾\
   /          \   |  |\  \ |  (‾)  |
  / __________ \  |__| \__\ \_____/ .io

WARN[0000] Module 'k6/x/sql' is using deprecated APIs that will be removed in k6 v0.38.0, for more details on how to update it see https://k6.io/docs/extensions/guides/create-an-extension/#advanced-javascript-extension
  execution: local
     script: script.js
     output: -

  scenarios: (100.00%) 1 scenario, 1 max VUs, 10m30s max duration (incl. graceful stop):
           * default: 1 iterations for each of 1 VUs (maxDuration: 10m0s, gracefulStop: 30s)


running (00m00.0s), 0/1 VUs, 1 complete and 0 interrupted iterations
default ✓ [======================================] 1 VUs  00m00.0s/10m0s  1/1 iters, 1 per VU

     ✓ is length 926

     checks...............: 100.00% ✓ 1         ✗ 0
     data_received........: 0 B     0 B/s
     data_sent............: 0 B     0 B/s
     iteration_duration...: avg=8.29ms min=8.29ms med=8.29ms max=8.29ms p(90)=8.29ms p(95)=8.29ms
     iterations...........: 1       98.862097/s

SELECT count

export default function () {
  const results = sql.query(db, 'SELECT count(*) FROM filled_table;');
  console.log(JSON.stringify(results))
  check(results, {
    'is length 926': (r) => r.length === 926
  });
}

% ./k6 run script.js

          /\      |‾‾| /‾‾/   /‾‾/
     /\  /  \     |  |/  /   /  /
    /  \/    \    |     (   /   ‾‾\
   /          \   |  |\  \ |  (‾)  |
  / __________ \  |__| \__\ \_____/ .io

WARN[0000] Module 'k6/x/sql' is using deprecated APIs that will be removed in k6 v0.38.0, for more details on how to update it see https://k6.io/docs/extensions/guides/create-an-extension/#advanced-javascript-extension
  execution: local
     script: script.js
     output: -

  scenarios: (100.00%) 1 scenario, 1 max VUs, 10m30s max duration (incl. graceful stop):
           * default: 1 iterations for each of 1 VUs (maxDuration: 10m0s, gracefulStop: 30s)

INFO[0000] [{"count(*)":[57,50,54]}]                    source=console

running (00m00.0s), 0/1 VUs, 1 complete and 0 interrupted iterations
default ✓ [======================================] 1 VUs  00m00.0s/10m0s  1/1 iters, 1 per VU

     ✗ is length 926
      ↳  0% — ✓ 0 / ✗ 1

     checks...............: 0.00% ✓ 0          ✗ 1
     data_received........: 0 B   0 B/s
     data_sent............: 0 B   0 B/s
     iteration_duration...: avg=4.88ms min=4.88ms med=4.88ms max=4.88ms p(90)=4.88ms p(95)=4.88ms
     iterations...........: 1     148.411992/s

SELECT TABLE_ROWS

export default function () {
  const results = sql.query(db, 'SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_NAME = "filled_table";');
  console.log(JSON.stringify(results))
  check(results, {
    'is length 926': (r) => r.length === 926
  });
}

% ./k6 run script.js

          /\      |‾‾| /‾‾/   /‾‾/
     /\  /  \     |  |/  /   /  /
    /  \/    \    |     (   /   ‾‾\
   /          \   |  |\  \ |  (‾)  |
  / __________ \  |__| \__\ \_____/ .io

WARN[0000] Module 'k6/x/sql' is using deprecated APIs that will be removed in k6 v0.38.0, for more details on how to update it see https://k6.io/docs/extensions/guides/create-an-extension/#advanced-javascript-extension
  execution: local
     script: script.js
     output: -

  scenarios: (100.00%) 1 scenario, 1 max VUs, 10m30s max duration (incl. graceful stop):
           * default: 1 iterations for each of 1 VUs (maxDuration: 10m0s, gracefulStop: 30s)

INFO[0000] [{"TABLE_ROWS":[57,50,54]}]                    source=console

running (00m00.0s), 0/1 VUs, 1 complete and 0 interrupted iterations
default ✓ [======================================] 1 VUs  00m00.0s/10m0s  1/1 iters, 1 per VU

     ✗ is length 926
      ↳  0% — ✓ 0 / ✗ 1

     checks...............: 0.00% ✓ 0          ✗ 1
     data_received........: 0 B   0 B/s
     data_sent............: 0 B   0 B/s
     iteration_duration...: avg=4.88ms min=4.88ms med=4.88ms max=4.88ms p(90)=4.88ms p(95)=4.88ms
     iterations...........: 1     148.411992/s

Observation

Logging the response of both SELECT count(*) and SELECT TABLE_ROWS show us the same responses.

[{"count(*)":[57,50,54]}]
// OR
[{"TABLE_ROWS":[57,50,54]}]

[imiric]

Hi, thanks for reporting this!

It's a known issue, unrelated to count(*), but with how values are returned from MySQL/MariaDB. See the example, and the discussion in PR #9.

For some reason I'm not clear on yet, values from MySQL are always returned as an array of ASCII integers. This is why the example uses String.fromCharCode(...row.key) to convert it into a string.

This is the case for all column types, AFAICS. 😞 So if you need to use the value as a number, you'd have to do parseInt(String.fromCharCode(...row.key)).

This only happens on MySQL and not for the other RDBMSs. I tried experimenting with different character sets and collation (e.g. CREATE TABLE (...) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;), and adding charset=utf8mb4 to the connection string, but it didn't have an effect.

So to summarize, you're actually getting the correct value: [57,50,54] are the ASCII codes for 926, and you need to convert them in your script. E.g.:

export default function () {
  const results = sql.query(db, 'SELECT count(*) c FROM filled_table;');
  for (const row of results) {
    console.log(`${String.fromCharCode(...row.c)}`);
  }
}

I'll keep this issue open as it should be fixed specifically for MySQL. I'm not sure if something can be configured on the MySQL side to change this behavior, or if we'll have to manually inspect the column types and use reflection to return them correctly, but it needs more investigation.

[imiric]

Hey @clovis-maniguet, I'd prefer to keep this issue open, if you don't mind.

Even though there's a workaround for it, it should eventually be fixed. We just haven't had the bandwidth to look into it in more depth, but any help is greatly appreciated.