Is it possible to bypass the limitation on MySQL Azure?

user_6461717 0
Viewed 276

I am using Azure MySQL version 5.6

When i am trying to import a large MySQL dump file from linux enviroment into the PaaS of Azure (Azure Database for MySQL servers) using this command :

pv DBFILE.sql | mysql -u username@mysqlserver -h mysqlservername.mysql.database.azure.com -pPassword DBNAME

I am getting this message:

"The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size."

Is there any way to bypass this error?

I read on the Microsoft documentation that "innodb_log_file_size" is not configurable. Can i split this large dump file into smaller ones and import them all together? Does it make any difference?

azure mysql
1 Answers

The size of the dump file is not the problem. It won't help to split it up.

The problem is that the size of one BLOB or TEXT value on at least one row is greater than 1/10th the size of the innodb log file. You can't split the data to be less than a single BLOB or TEXT value.

According to the Azure documentation you linked to, the value of innodb_log_file_size is fixed at 256MB. This means you cannot import any row with a BLOB or TEXT value of more than 25.6MB. At least you can't import it to an InnoDB table.

The reason is that the redo log file has a fixed size, and the size of the log file creates a limit on the number of modified pages in the InnoDB buffer pool (not one-to-one though, because the format of redo log file records is not the same as pages in the buffer pool). It's kind of an arbitrary ratio, but the purpose of the limit on BLOB/TEXT values is meant to avoid a giant BLOB wrapping around and overwriting part of itself in a small redo log, which would leave the MySQL server in a state that could not recover from a crash. In MySQL 5.5, this was just a recommended limit. In MySQL 5.6, it became enforced by InnoDB, so an INSERT of a BLOB or TEXT that was too large would simply result in an error.

Amazon RDS used to have a similar restriction years ago. They only supported a fixed size for innodb_log_file_size, I recall it was 128MB. It was not configurable.

I was at an AWS event years ago in San Francisco, and I found an opportunity to talk to the Amazon RDS Product Manager in the hall between sessions. I gave him feedback that leaving this setting at a relatively small value without the ability to increase it was too limiting. It meant that one could only insert BLOB/TEXT of 12.8MB or less.

I'm sure I was not the only customer to give him that feedback. A few months later, an update to RDS allowed that variable to be changed. But you must restart the MySQL instance to apply the change, just like if you run MySQL yourself.

I'm sure that Azure will discover the same thing, and get an earful of feedback from their customers.

Bill_Karwin_208600
Related