Bug #87531 sql.execute() will not raise OperationalError under specific condition
Submitted: 24 Aug 15:32 Modified: 3 Nov 8:10
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:8.0.4 OS:CentOS (7)
Assigned to:

[24 Aug 15:32] Shahriyar Rzayev
Description:
From client:

mysql> select * from my_collection;
ERROR 3142 (HY000): The JSON binary value contains invalid data.
mysql> select * from my_collection_view;
ERROR 3142 (HY000): The JSON binary value contains invalid data.

mysql> show create table my_collection\G
*************************** 1. row ***************************
       Table: my_collection
Create Table: CREATE TABLE `my_collection` (
  `doc` json DEFAULT NULL
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

mysql> show create view my_collection_view\G
*************************** 1. row ***************************
                View: my_collection_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`bakux`@`localhost` SQL SECURITY DEFINER VIEW `my_collection_view` AS select `my_collection`.`doc` AS `doc` from `my_collection`
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)

But from Connector/Python - Using XPlugin interface selecting from view did not raise an OperationalError and failed:

./../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_select_from_view FAILED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_select_from_table PASSED

=============================================================================== FAILURES ================================================================================
___________________________________________________________________ TestXPlugin.test_select_from_view ___________________________________________________________________

self = <myrocks_mysqlx_plugin_test.test_module01.TestXPlugin instance at 0x3052878>
return_plugin_obj = <myrocks_mysqlx_plugin.myrocks_mysqlx_plugin.MyXPlugin instance at 0x2f765f0>

    def test_select_from_view(self, return_plugin_obj):
        with pytest.raises(OperationalError) as er:
>            return_plugin_obj.select_from_view("my_collection_view")
E            Failed: DID NOT RAISE <class 'mysqlx.errors.OperationalError'>

../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py:63: Failed

How to repeat:
For full code portions please refer to:

https://github.com/Percona-QA/percona-qa/blob/master/myrocks-tests/myrocks_mysqlx_plugin/m...

https://github.com/Percona-QA/percona-qa/blob/master/myrocks-tests/myrocks_mysqlx_plugin_t...

Basically I have used following functions:

def create_view_from_collection(self, view_name):
        # Creating view from collection
        print "Trying to create view based on MyRocks collection"
        try:
            command = "create view {}.{} as select * from {}.{}".format(self.schema_name, view_name, self.schema_name, self.collection_name)
            sql = self.session.sql(command)
            sql.execute()
        except Exception as e:
            raise
        else:
            return 0

    def select_from_view(self, view_name):
        # Running select; Should raise an error
        print "Trying to select from view [Should raise an OperationalError]"
        try:
            command = "select * from {}.{}".format(self.schema_name, view_name)
            sql = self.session.sql(command)
            sql.execute()
        except Exception as e:
            raise mysqlx.errors.OperationalError("The JSON binary value contains invalid data")
        else:
            return 0

    def select_from_table(self):
        # Running select; Should raise an error
        print "Trying to select from view [Should raise an OperationalError]"
        try:
            command = "select * from {}.{}".format(self.schema_name, self.collection_name)
            sql = self.session.sql(command)
            sql.execute()
        except Exception as e:
            raise mysqlx.errors.OperationalError("The JSON binary value contains invalid data")
        else:
            return 0

And then tried run this inside pytest tests.
[25 Aug 13:04] Shahriyar Rzayev
I dig a bit and found that, this was due to different error handling in my situation.
Let me clarify a bit.

If you are going to run such select with non-existing table:

E               OperationalError: Table 'generated_columns_test.jsdgfjsdhgf' doesn't exist

Because sql.execute() failed here.

But to get: 
"OperationalError: The JSON binary value contains invalid data."

I should run fetch_all():

def select_from_table_dummy(self):
        # Running select; Should raise an error
        print "Trying to select from table [Should raise an OperationalError]"
        try:
            command = "select * from {}.{}".format(self.schema_name, self.collection_name)
            sql = self.session.sql(command)
            cursor = sql.execute()
            cursor.fetch_all()
        except Exception as e:
            raise
        else:
            return 0

So it means, the sql.execute() successfully run the sql statement and gave me mysqlx.SqlResult object back and only after trying to fetch data(fetch_all()) it  figured out there was an error? It should be Error != Result
[29 Aug 3:47] Jose Israel Gomez Delgado
Duplicated as described.

modified the method to print some debug messages...

    def select_from_table(self):
        # Running select; Should raise an error
        print "Trying to select from view [Should raise an OperationalError]"
        try:
            command = "select * from {0}.{1}".format(self.schema_name, self.collection_name)
            print("command: {0}".format(command))
            sql = self.session.sql(command)
            cursor = sql.execute()
            print(cursor.fetch_all())
            print("not OK.")
        except Exception as e:
            print("exception occurred")
            raise mysqlx.errors.OperationalError("The JSON binary value contains invalid data")
        else:
            return 0

It prints
Trying to select from view [Should raise an OperationalError]
command: select * from TEST.no_exist
[]
not OK.

the fetch returns and empty list even if the table does not exists.
[31 Aug 12:18] Chiranjeevi Battula
Hello Shahriyar,

Thank you for the report.

Thanks,
Chiranjeevi.
[14 Sep 18:46] Jose Israel Gomez Delgado
Thanks for your bug report.

I found the reason why the error is not raised.

The method create_collection is being invoke in the MyXPlugin class' constructor, on this line:
        self.schema.create_collection(self.collection_name, reuse=True)

The create_collection creates in the current schema a new collection with the specified name, more info can be found here: https://dev.mysql.com/doc/dev/connector-python/mysqlx.Schema.html?highlight=create_collect...

The create_collection method creates a table in the database, in order to create an object that represents the collection but without create it in the database the method get_collection can be used (get_collection by default does not check if the table exists in the database). The collection will not be created in the database until the schema.create_collection is invoked.

Please consider that methods from the collection object that require the preexistence of the collection in the database may raise an error if the schema.create_collection is not invoked first to create such collection.

Please let us know if this helps.
[18 Sep 13:03] Shahriyar Rzayev
Please see my new reports here:

https://bugs.mysql.com/bug.php?id=87776
https://bugs.mysql.com/bug.php?id=87777

I think those things are related.

For eg. cursor.fetch_all() returns list of Row object but not list of values from select.
[3 Oct 11:26] Shahriyar Rzayev
I got your point at this moment. 
But to be honest current implementation of API is difficult to catch, especially with lacking documentation.

This report can be closed safely.
[3 Nov 8:10] Chiranjeevi Battula
Thank you for confirming, closing this now.