foreach error on 10.0.22-MariaDB

[pleiadess]

Executing this:

CALL foreach(
"SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'devices'",
"SELECT username, group, acl_group_id FROM ${1}.users");

MySQL Version : 10.0.21-MariaDB ---> works fine

MySQL Version : 10.0.22-MariaDB ---> error:

1 queries executed, 0 success, 1 errors, 0 warnings

Query: CALL foreach( "SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'devices'", "SELECT username, `grou...

Error Code: 1644
QueryScript error: [Unsupported token: "startg"] at 7: "SELECT
username, group, acl_group_id FROM abm.users "

[alexkipkalo]

Similar issue on my side:

CALL common_schema.foreach(
"SELECT table_schema FROM INFORMATION_SCHEMA.TABLES WHERE table_name = '<table_name>' ",
"UPDATE ${1}.<table_name> set ...... WHERE id = ");

show warnings ;
Error Code: 1644 ;
Message : QueryScript error: [Unsupported token: "sleep-"] at 7: "UPDATE ... ";

MySQL version : Percona 5.6.23-72.1 -- no error ;
MySQL version : Percona 5.6.27-rel75 -- error ;

[alexkipkalo]

Do you know what it might be ?

[tersmitten]

I think we have the same error, see.

Do you have any suggestions @shlomi-noach?

[ikewalker]

I've tested this in 5.7 and gotten similar errors.

Looking more closely at 5.6, I tested each version of community server between 5.6.22 and 5.6.27, and 5.6.27 was the first version that started throwing the error.

So it appears to be a change between 5.6.26 and 5.6.27 that broke QueryScript.

[shlomi-noach]

I can also confirm:

master [localhost] {msandbox} (common_schema) > call run("foreach ($s: select TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'samplet') {select * from :${s}.samplet}" );
ERROR 1644 (91100): QueryScript error: [Unsupported token: "startet"] at 8: "foreach ($s: select TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE t
master [localhost] {msandbox} (common_schema) > call run("foreach ($s: select TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'samplet') {select * from :${s}.samplet}" );
ERROR 1644 (91100): QueryScript error: [Unsupported token: "

I'm looking into this. I cannot see an obvious hint in http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-27.html

[shlomi-noach]

master [localhost] {msandbox} (common_schema) > call run('select * from _sql_tokens');
ERROR 1644 (91100): QueryScript error: [Unsupported token: "startt"] at 7: "select * from _sql_tokens"
master [localhost] {msandbox} (common_schema) > call run('select * from _sql_tokens');
ERROR 1644 (91100): QueryScript error: [Unsupported token: "

[tersmitten]

We're also experiencing the problem in mysql 5.5 (5.5.46)

[shlomi-noach]

Seems like MySQL introduced a bug in local routine variables.

These variables:

    declare first_token text;
    declare first_state text;

Are assigned via:

SELECT level, token, state FROM _sql_tokens WHERE id = id_from INTO statement_level, first_token, first_state;

With these legit values, select, alpha (first row):

+-----------+------------+----+-------+-------+--------+------------+
| server_id | session_id | id | start | level | token  | state      |
+-----------+------------+----+-------+-------+--------+------------+
|         1 |         19 |  1 |     7 |     0 | select | alpha      |
|         1 |         19 |  2 |     8 |     0 |        | whitespace |
|         1 |         19 |  3 |     9 |     0 | 1      | integer    |
|         1 |         19 |  4 |     9 |     0 |        | start      |
+-----------+------------+----+-------+-------+--------+------------+

and their values are immediately corrupted.

I'm looking to see whether I can overcome this bug, but it is very disconcerting as this coding style is all over the place.

[shlomi-noach]

This seems to work around the problem:

      SELECT level, token, state FROM _sql_tokens WHERE id = id_from INTO @_statement_level, @_first_token, @_first_state;

      set statement_level=@_statement_level;
      set first_token=@_first_token;
      set first_state=@_first_state;

[shlomi-noach]

O-M-G this is everywhere. This bug is creepy and happens on multiple occasions of SELECT... INTO <local-variable>. I'm replacing all occurrences, but I'm doing so hastily at this time.
I will need to step back and do this orderly. Meanwhile I can throw in a hopefully mostly fixed deployment file.

[shlomi-noach]

Please find new sql file in https://github.com/shlomi-noach/common_schema/releases/tag/v2.3

[tersmitten]

Thanks @shlomi-noach

[shlomi-noach]

Is it confirmed the problem is solved on mentioned platforms?

[ikewalker]

I can confirm that the problem is solved on all mentioned platforms that I tested. The one I did not test is MariaDB 1.0.22.

I tested the following versions:

MySQL Community Server 5.5.46
Percona Server 5.6.27
Percona Server 5.7.10-3
MySQL Community Server 5.7.11

I was able to install common_schema 2.3 on all of those versions with no errors, and I was able to successfully run the following QueryScript test:

call common_schema.run("
foreach ($t: select TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'mysql')
{select count(*) from mysql.:${t} }
" );

[genuss]

I can confirm installation works too. Thank you for the fix. One little thing I mentioned is weird symbols in sql-file of release. Take a look for instance on line 13466.

[tersmitten]

Server version: 5.5.48-37.8 Percona Server (GPL), Release 37.8, Revision c8ae1b6

also works

[shlomi-noach]

@genuss thanks - can you kindly open a new issue for the strange chars? Probably my mac's version of html2text

[shlomi-noach]

thanks all, closing this issue