gluon.contrib.pymysql.err.Error AssertionError('Protocol error, expecting EOF',)

[Gilad Hoshmand]

Hi all,

I built a join query which breaks up a field that has '|' char as separator of values to multiple rows with single values (ie. field value = '1|2|3' , rows will be multiplied with single_value_field = 1 , single_value_field = 2 etc..)

Filters some rows ('Having ... ' )

Then join with a table that I need to get values from.

I tested it on mysql 5.7 and it works fine.

I'm using db.executesql(...) to execute the query because I find it hard to make with the DAL but I'm getting an error.

The query is:

SELECT 

 *

FROM

    (SELECT * , SUBSTRING_INDEX(SUBSTRING_INDEX(field1, '|', numbers.n), '|', - 1) AS single_value_field

    FROM

        (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers

    INNER JOIN table1 ON CHAR_LENGTH(field1) - CHAR_LENGTH(REPLACE(field1, '|', '')) >= numbers.n - 1

    HAVING (field1 IS NOT NULL

        AND field1 != '||'

        AND single_value_field IS NOT NULL

        AND single_value_field != '')) new_table_alias

        JOIN

    table2 ON (table2.id = single_value_field)

    WHERE (table2.field2 = '' OR table2.field2 is null)

My Error arises on db.executesql(...)  only when I include the last Join clause: 

        JOIN

    table2 ON (table2.id = single_value_field)

If it is omitted - no problems.

Full error(private bits renamed as in query):

<class 'gluon.contrib.pymysql.err.Error'> (<type 'exceptions.AssertionError'>, AssertionError('Protocol error, expecting EOF',))

Version

web2py™	Version 2.14.6-stable+timestamp.2016.05.10.00.21.47

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20.

Traceback (most recent call last): File "/path/gluon/restricted.py", line 227, in restricted exec ccode in environment File "/path/controllers/myController.py", line 93, in <module> File "/path/gluon/globals.py", line 417, in <lambda> self._caller = lambda f: f() File "/path/controllers/myController.py", line 15, in index rows = db.executesql(query); File "/path/gluon/packages/dal/pydal/base.py", line 1019, in executesql adapter.execute(query) File "/path/gluon/packages/dal/pydal/adapters/base.py", line 1388, in execute return self.log_execute(*a, **b) File "/path/gluon/packages/dal/pydal/adapters/base.py", line 1382, in log_execute ret = self.get_cursor().execute(command, *a[1:], **b) File "/path/gluon/contrib/pymysql/cursors.py", line 117, in execute self.errorhandler(self, exc, value) File "/path/gluon/contrib/pymysql/connections.py", line 200, in defaulterrorhandler raise Error(errorclass, errorvalue) Error: (<type 'exceptions.AssertionError'>, AssertionError('Protocol error, expecting EOF',))

Thank you all!

[Gilad Hoshmand]

Solved.

The problem was that I was addressing 'single_value_field' without the context of the derived table.

Changed:

 JOIN

   table2 ON (table2.id = single_value_field)

To:

JOIN
   table2 ON (table2.id = new_table_alias.single_value_field)