[ISSUE] Can't select JSON column from mysql when using AcraServer

[TheAliAbbasi]

Describe the bug
When selecting from a MySQL table that contains a JSON column, AcraServer fails with:

Can't handle length encoded string binary value
found unknown FieldType in MySQL response packet

To Reproduce
Steps to reproduce the behavior:

1. Start cossacklabs/acra-server:0.96.0 with MySQL 8.1 backend.
2. Create a table with a JSON column:

CREATE TABLE test_json (
    id INT PRIMARY KEY AUTO_INCREMENT,
    payload JSON
);
INSERT INTO test_json (payload) VALUES ('{"foo":"bar"}');

Run SELECT * FROM test_json; through AcraServer.

Observe error in AcraServer logs:

error="found unknown FieldType in MySQL response packet"
Expected behavior
AcraServer should transparently proxy the JSON column values (leave them untouched) without failing.

Acra configuration files
For AcraServer:
- [ ] configuration file or CLI params that you use to start AcraServer;
- [ ] encryptor_config.yaml if used.
AcraServer started via Docker official image:

docker run --rm -it \
  -p 9393:9393 \
  cossacklabs/acra-server:0.96.0 \
  --mysql_db_host=<mysql-host> \
  --mysql_db_port=3306 \
  --client_id=my_client \
  --keys_dir=/keys \
  --tls_auth=0 \
  --debug

Configuration of encryptor:

database_settings:
  mysql:
    case_sensitive_table_identifiers: false

defaults:
  crypto_envelope: acrablock
  data_type: str

schemas:
  - table: users
    # List columns in real DB order so SELECT * / INSERT VALUES (...) work
    columns:
      - id
      - tiny_id
      - name
      - family
      - mobile
      - mobile_enc
      - emergency_number_one
      - emergency_number_one_enc
      - emergency_number_two
      - emergency_number_two_enc
      - email
      - username
      - password
      - tel
      - tel_enc
      - address
      - address_enc
      - birthdate
      - gender
      - national_number
      - national_number_enc
      - picture
      - cardno
      - remember_token
      - created_at
      - updated_at
      - from_app
      - domain
      - push_token
      - inviter_id
      - company_id
      - contract_id
      - revoked_from_company_at
      - is_ban
      - is_fake
      - wants_first_reservation
      - pod_id
      - about_me
      - living_city
      - living_province
      - tracker_id
      - support_status
      - reminded_at
      - reminded_count
      - financial_blacklist
      - wants_settle_instantly
      - recency
      - frequency
      - meta_data


    encrypted:
      - column: mobile_enc
        searchable: true

      - column: emergency_number_one_enc

      - column: emergency_number_two_enc

      - column: tel_enc

      - column: address_enc

      - column: national_number_enc      

For AcraTranslator:
- [ ] configuration file or CLI params that you use to start AcraTranslator.

Environment (please complete the following information):

• Acra version: [e.g. 0.90.0]
• Database server and its version: [e.g. PostgreSQL 11, MySQL 7 ..]
• Installed components:
  • AcraServer
  • AcraTranslator
• Data-in-transit encryption between Acra and the client-side application:
  • TLS
  • AcraConnector
  • no transport encryption
• Installation way:
  • via Docker
  • via package manager

Additional context
Add any other context about the problem here.

[Zhaars]

Hello, @TheAliAbbasi

Thank you for reporting this issue!

We were able to reproduce it on our side. Unfortunately, the root cause is that the current open-source version of Acra does not fully support the MySQL binary protocol, and we plan to extend protocol support in the near future.

As an alternative, depending on the client you use to connect to the database through Acra, you may be able to use the text protocol instead of the binary protocol. This can work around the current limitation in this case.

If this limitation is critical for your use case, we recommend considering the Enterprise Edition, which provides more comprehensive MySQL protocol support.

Thank you for bringing this to our attention!

[TheAliAbbasi]

Hi @Zhaars,
Thank you for your response. After talking to my team we came to the conclusion that we can hold this task until you release your new version. How long do you think it would get until your new release comes out?

I encountered another case where mass update query is not getting parsed either, take a look at this query as an example:

UPDATE yourTableName set yourColumnName=case when yourColumnName=Value1 then anyUpdatedValue1
when yourColumnName=Value2 then anyUpdatedValue2
when yourColumnName=Value3 then anyUpdatedValue3
when yourColumnName=Value4 then anyUpdatedValue4
else yourColumnName
end;

I was trying to use this to mass encrypt our old fields when I encountered this situation. This is just an FYI
I haven't tried upsert query, but that might have the same issue

Thanks in advance,
Ali Abbasi