MySQL Bugs: #87531: sql.execute() will not raise OperationalError under specific condition

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

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.

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

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.

Hello Shahriyar,

Thank you for the report.

Thanks,
Chiranjeevi.

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.

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.

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.

Thank you for confirming, closing this now.