How do I fix MySQL "row size too large" errir?

Question

I'm attempting to create a table with 585 rows. I'm executing this query in my python program. Here is my query: https://pastebin.com/y7VSTeGZ

When I execute the query, it gives me an error message: Error: '1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.'

After doing some digging, I found this on stack overflow: https://stackoverflow.com/a/25373052

It sounds like the solution lies within the my.ini text file, specifically the innodb_log_file_size value. However, it looks like Azure limits access to these files.

It seems like a pretty simple request. I can create 500 columns in Excel no problem - why can I not create 500 columns in MySQL?

Thanks so much.

Answer 1

@Maritz de Villiers Thank you for reaching out.

If you receive an error similar to "Row size too large (> 8126)", you may want to turn OFF the parameter innodb_strict_mode. The server parameter innodb_strict_mode can't be modified globally at the server level because if row data size is larger than 8k, the data is truncated without an error, which can lead to potential data loss. We recommend modifying the schema to fit the page size limit.

This parameter can be set at a session level using init_connect. To set innodb_strict_mode at session level, refer to setting parameter not listed.

Please read more Server parameters in Azure Database for MySQL - Flexible Server.

Please don't forget to mark as accept answer if the reply answers your question. If not, please comment below so we can continue to work on the same.

Regards,

Oury