[Bug]: Issue when saving to MySQL

[NormanTUD]

What happened?

I am using from ax.storage.sqa_store.save import save_experiment as save_experiment_to_db to save to a MySQL-db.

I consistently get this error message:

(pymysql.err.OperationalError) (1118, 'Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs')
[SQL: 
CREATE TABLE parameter_v2 (
        domain_type SMALLINT NOT NULL, 
        experiment_id INTEGER, 
        id INTEGER NOT NULL AUTO_INCREMENT, 
        generator_run_id INTEGER, 
        name VARCHAR(100) NOT NULL, 
        parameter_type SMALLINT NOT NULL, 
        is_fidelity BOOL, 
        target_value VARCHAR(4096), 
        digits INTEGER, 
        log_scale BOOL, 
        lower FLOAT, 
        upper FLOAT, 
        choice_values VARCHAR(4096), 
        is_ordered BOOL, 
        is_task BOOL, 
        dependents VARCHAR(4096), 
        fixed_value VARCHAR(4096), 
        PRIMARY KEY (id), 
        FOREIGN KEY(experiment_id) REFERENCES experiment_v2 (id), 
        FOREIGN KEY(generator_run_id) REFERENCES generator_run_v2 (id)
)

]

Changing the line target_value: Column[TParamValue | None] = Column(JSONEncodedObject) to target_value: Column[TParamValue | None] = Column(JSONEncodedObject().with_variant(Text, "mysql")) in storage/sqa_store/sqa_classes.py solved this for me for parameter_v2.

But this should probably done for other values as well, and more tested, also with other storage engines that may have different limitations, and other DB fields.

Would be greatly appreciated if this could be fixed properly.

Please provide a minimal, reproducible example of the unexpected behavior.

--

Please paste any relevant traceback/logs produced by the example provided.

Ax Version

1.0.0

Python Version

3.11.2

Operating System

Debian

(Optional) Describe any potential fixes you've considered to the issue outlined above.

No response

Pull Request

None

Code of Conduct

• [x] I agree to follow Ax's Code of Conduct

[mgrange1998]

Hi @NormanTUD, thanks for the bug report! We'll investigate into a proper fix on our end, glad you were able to unblock with the fix you listed for the time being.

[degerahmet]

@lena-kashtelyan Hey Lena, I’d like to contribute to this repository. I’d like to start with this issue, if you’re open to contributors from outside Meta.

[mgrange1998]

Please follow the https://ax.dev/docs/recipes/experiment-to-sqlite/ guide for saving objects to a SQLite database, which does not have the "Row size too large" constraint.

[NormanTUD]

Thanks but that certainly doesn't help for my complex use case, where I offer a large application that uses Ax for hyperparameter optimization only as a background service, and where the user should have the ability to push data into other databases as he desires.