Backend.sql() throws error if db user has only read permissions

[cpcloud]

Discussed in #9343

^{Originally posted by nextchamp-saqib June 10, 2024}
Is there any other way to make Backend.sql() or Table.sql() work if the db user doesn't have write permissions?

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
Cell In[2], line 2
      1 with ds._get_ibis_backend() as db:
----> 2     res = db.sql("select 1")
      3     print(res)

File ~/frappe-bench/env/lib/python3.10/site-packages/ibis/backends/sql/__init__.py:200, in SQLBackend.sql(self, query, schema, dialect)
    198 query = self._transpile_sql(query, dialect=dialect)
    199 if schema is None:
--> 200     schema = self._get_schema_using_query(query)
    201 return ops.SQLQueryResult(query, ibis.schema(schema), self).to_expr()

File ~/frappe-bench/env/lib/python3.10/site-packages/ibis/backends/mysql/__init__.py:193, in Backend._get_schema_using_query(self, query)
    190 table = util.gen_name(f"{self.name}_metadata")
    192 with self.begin() as cur:
--> 193     cur.execute(f"CREATE TEMPORARY TABLE {table} AS {query}")
    194     try:
    195         return self.get_schema(table)

File ~/frappe-bench/env/lib/python3.10/site-packages/pymysql/cursors.py:153, in Cursor.execute(self, query, args)
    149     pass
    151 query = self.mogrify(query, args)
--> 153 result = self._query(query)
    154 self._executed = query
    155 return result

File ~/frappe-bench/env/lib/python3.10/site-packages/pymysql/cursors.py:322, in Cursor._query(self, q)
    320 conn = self._get_db()
    321 self._clear_result()
--> 322 conn.query(q)
    323 self._do_get_result()
    324 return self.rowcount

File ~/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py:558, in Connection.query(self, sql, unbuffered)
    556     sql = sql.encode(self.encoding, "surrogateescape")
    557 self._execute_command(COMMAND.COM_QUERY, sql)
--> 558 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    559 return self._affected_rows

File ~/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py:822, in Connection._read_query_result(self, unbuffered)
    820 else:
    821     result = MySQLResult(self)
--> 822     result.read()
    823 self._result = result
    824 if result.server_status is not None:

File ~/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py:1200, in MySQLResult.read(self)
   1198 def read(self):
   1199     try:
-> 1200         first_packet = self.connection._read_packet()
   1202         if first_packet.is_ok_packet():
   1203             self._read_ok_packet(first_packet)

File ~/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py:772, in Connection._read_packet(self, packet_type)
    770     if self._result is not None and self._result.unbuffered_active is True:
    771         self._result.unbuffered_active = False
--> 772     packet.raise_for_error()
    773 return packet

File ~/frappe-bench/env/lib/python3.10/site-packages/pymysql/protocol.py:221, in MysqlPacket.raise_for_error(self)
    219 if DEBUG:
    220     print("errno =", errno)
--> 221 err.raise_mysql_exception(self._data)

File ~/frappe-bench/env/lib/python3.10/site-packages/pymysql/err.py:143, in raise_mysql_exception(data)
    141 if errorclass is None:
    142     errorclass = InternalError if errno < 1000 else OperationalError
--> 143 raise errorclass(errno, errval)

OperationalError: (1044, "Access denied for user '_93e0db92_read_only'@'%' to database '_93e0db92'")

[cpcloud]

Turns out that you can't get access to the logical type of a column, like UUID or INET, using the MySQL protocol.

If we're okay with some loss in fidelity when reading those types in, then we can fix this issue.