question

AbdulHayee-6944 avatar image
0 Votes"
AbdulHayee-6944 asked ·

Mysql config parameter innodb page size to be increased in Azure mysql db

Hi i am new to Azure and planning on shifting our servers and mysql databases Now to fully migrate our mysql databases it is required that the following parameter is added in mysql my.cnf
innodb_page_size=64K (Default is 16k)

Now after adding we have to delete everything in data dictionary which is in /var/lib/mysql

and then run the following command
mysqld --initialize

These are the above steps we take to increase innodb_page_size of mysql. I just wanted to ask will i be able to do the above in mysql azure databases? I know i can do that in a linux VM but i want to do it databases provided by azure.

azure-database-mysql
· 1
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NavtejSaini-MSFT avatar image NavtejSaini-MSFT ·

@AbdulHayee-6944 We are checking this and we will get back to you.

1 Vote 1 ·

2 Answers

NavtejSaini-MSFT avatar image
0 Votes"
NavtejSaini-MSFT answered ·

@AbdulHayee-6944

Our team has conveyed:

The innodb_page_size is read_only server parameter in Azure Database for MySQL Single Server and Azure Database for MySQL Flexible Server and cannot be changed because innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward.

Please convey your use case on why you want to update this server parameter? As conveyed by the team,,migration should not be a problem if this parameter value is different but we would still advise to create test environment and check that.

Regards
Navtej S


· 2 ·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NavtejSaini-MSFT avatar image NavtejSaini-MSFT ·

@AbdulHayee-6944 Please let us know if you need any further help.

0 Votes 0 ·
AbdulHayee-6944 avatar image AbdulHayee-6944 ·

Just as i suspected i am getting the following error while restoring, this is due to inno db page size parameter which is of low value

ERROR 1118 (42000) at line 1982: 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.
Operation failed with exitcode 1
14:23:23 Import of C:\Users\abdulhayee\Desktop\trash\inowdbuat_02Mar2021.sql has finished with 1 errors



73590-image.png



0 Votes 0 ·
image.png (34.8 KiB)
NavtejSaini-MSFT avatar image
0 Votes"
NavtejSaini-MSFT answered ·

@AbdulHayee-6944

Here is the response from our Product Team regarding this:

"This issue is documented here and please find the response below:
Error: 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.
Limitation: This error happens when you're migrating to Azure Database for MySQL using the InnoDB storage engine and any table row size is too large (>8126 bytes).
Workaround: Update the schema of the table that has a row size greater than 8126 bytes. We don't recommend changing the strict mode because the data will be truncated. Changing the page_size isn't supported."

Please let us know if any further issues.

Regards
Navtej S



· 3 ·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AbdulHayee-6944 avatar image AbdulHayee-6944 ·

Can you please guide me to documentation or any source which can help me how to do the below:

Workaround: Update the schema of the table that has a row size greater than 8126 bytes.

0 Votes 0 ·
NavtejSaini-MSFT avatar image NavtejSaini-MSFT AbdulHayee-6944 ·

@AbdulHayee-6944 There are several Stack Overflow and MySQL forum questions that discuss this here - Document1. Document2. Document3.

Please do raise issue on Mysql forum for better help. Hope this helps.

Regards
Navtej S


0 Votes 0 ·
NavtejSaini-MSFT avatar image NavtejSaini-MSFT NavtejSaini-MSFT ·

@AbdulHayee-6944

Were you able to check this and move forward with the migration. Please do let us know if you need further help.

Regards
Navtej S

0 Votes 0 ·