Jump to content

IPS 4.5.4: InnoDB tables are using Compact row format...


PoC2

Recommended Posts

A warning popped up during the upgrade to 4.5.4:

Quote

You have one or more InnoDB tables that are using the 'Compact' row format... we recommend changing it to 'Dynamic'...

Any ideas  how we identify exactly which tables those might be?

Many thanks.

Edited by PoC2
Link to comment
Share on other sites

I've seen your post and bash script. Thank you for sharing that. 👍

I wondered if anyone else has had success or if the staff here have anything to say, regarding changes and what tables – since they're requesting it be done to our databases.

Link to comment
Share on other sites

20 minutes ago, PoC2 said:

I've seen your post and bash script. Thank you for sharing that. 👍

I wondered if anyone else has had success or if the staff here have anything to say, regarding changes and what tables – since they're requesting it be done to our databases.

I only wrote it a couple hours ago, so I don't think anyone else has had a chance to try it. But it ran on my site with no issues. 

Link to comment
Share on other sites

My hosting provider say that is bug of MySQL 5.6

I researched the matter further and it appears that there is a bug with MySQL 5.6 which does not allow the option of changing the default row format variable. The only way of changing the default row format is to specify this for each individual database table.

You can see more information on this bug at the link below:

https://bugs.mysql.com/bug.php?id=58123

It appears that this matter has been resolved in MySQL 5.7 and the default row format can be modified via the global MySQL configuration file.

As such, what I can suggest is that we upgrade the server's MySQL version to 5.7.

Please note, however, that we are not able to confirm if all of your websites will be able to work correctly under the new MySQL version. The upgrade process cannot be reverted, as such, before we proceed, you should consult with a developer as to whether your websites will continue working with version 5.7.

Should you decide to proceed with the upgrade, you can contact us at any time so that we can perform it for you, after which we will be able to modify the default row format variable.

Link to comment
Share on other sites

8 minutes ago, Nathan Explosion said:

Run this in PHPMYADMIN to identify the tables:


SELECT table_name, table_schema, ROW_FORMAT
FROM information_schema.tables
WHERE engine = 'InnoDB' AND TABLE_SCHEMA = '<YOUR DATABASE NAME>' AND ROW_FORMAT = 'COMPACT'

 

 

I have tried, ALL tables in database are affected 🤔

Link to comment
Share on other sites

Reply received from Invision folks (very speedy and helpful).

Essentially as  @kmk suggested, upgrade your MySQL beyond 5.5 (to something like 5.7+) to resolve the issue. There should be no issues with information loss/re-indexing.

So that is what I'll do.

 

Link to comment
Share on other sites

Database tables using COMPACT row format only allow a certain amount of space per row, and many clients were encountering database errors when tables were being adjusted upgrading to 4.5.4. We recommend using DYNAMIC row format which allows for more efficient use of space and has been the default for several MySQL versions now. It is effectively the only way to resolve the errors that some have encountered.

Link to comment
Share on other sites

Update.

I updated our server from MariaDB 10.1 to 10.3 and then had the IPS Db convert rows from 'Compact' to 'Dynamic'. As a result the AdminCP notification has gone and its green ticks all the way.

I guess you're bound to encounter hiccups when your database is nearly 20 years old and has gone through 4 major generations.

Link to comment
Share on other sites

2 hours ago, PoC2 said:

Update.

I updated our server from MariaDB 10.1 to 10.3 and then had the IPS Db convert rows from 'Compact' to 'Dynamic'. As a result the AdminCP notification has gone and its green ticks all the way.

I guess you're bound to encounter hiccups when your database is nearly 20 years old and has gone through 4 major generations.

This is really what it comes down to. As the backend evolves, sometimes changes like this become warranted, even if not necessary (which is why this is a recommendation and not a requirement in the checking tools). Just like MyISAM storage engine was the preferred engine for years, but not we strongly recommend InnoDB for just about everyone.

Link to comment
Share on other sites

41 minutes ago, bfarber said:

Just like MyISAM storage engine was the preferred engine for years, but not we strongly recommend InnoDB for just about everyone.

Weirdly for some reason our IPS DB rows are a mix of those. I don't know why.

42 minutes ago, bfarber said:

(which is why this is a recommendation and not a requirement in the checking tools)

Except alarm bells do begin to ring when it also includes a warning that future upgrades may not work (and we don't know when 4.5.5? 4.6? etc.); we don't have that information.

I think the main issue is clarity. It isn't/wasn't clear whether all table rows should be Dynamic? Some? What DB software version is recommended? etc. I expect IPS don't want to say because of potential liability issues, but the lack of info can be a problem.

 

Link to comment
Share on other sites

We do post minimum recommendations for MySQL and PHP.

For the "Dynamic" row format, we would recommend moving all tables to this format. But it's not a requirement, and if you aren't having issues you do not necessarily need to make any changes.

The note about future versions supporting certain things is a generic message just to indicate why we make recommendations.

Link to comment
Share on other sites

  • 2 weeks later...
On 10/23/2020 at 9:04 AM, Nathan Explosion said:

Run this in PHPMYADMIN to identify the tables:


SELECT table_name, table_schema, ROW_FORMAT
FROM information_schema.tables
WHERE engine = 'InnoDB' AND TABLE_SCHEMA = '<YOUR DATABASE NAME>' AND ROW_FORMAT = 'COMPACT'

 

 

Thanks, this helped me find the tables, of which there were 19.
I did this command "ALTER TABLE ipbdownloads_files_pending ROW_FORMAT=DYNAMIC;"  - Not sure if there was a away to do them all at once, but I did them singely and now they are all Dynamic 🙂

Link to comment
Share on other sites

I contacted my web host (my site being on a shared server) and they said they couldn't change to tables to Dynamic without altering things for every site on that server. Not what I was intending at all. Something tells me my host is wrong though... especially if the post above was successful in altering individual tables to Dynamic. Unless he's hosting his own?

Will spend some time identifying which of my DB tables are not InnoDB powered and try manually resetting them. *After DB backup made*

Link to comment
Share on other sites

Update: Success. Previous "compact" tables are now showing as "dynamic". 

I entered the following as an SQL query through PHPMyAdmin:

SELECT table_name, table_schema, ROW_FORMAT
FROM information_schema.tables
WHERE engine = 'InnoDB' AND TABLE_SCHEMA = '<YOUR DATABASE NAME>' AND ROW_FORMAT = 'COMPACT'

... then opened a new window to the same DB SQL query page, and copied the following query (adjusting the <compact_table_name> as needed :

ALTER TABLE <compact_table_name> ROW_FORMAT=DYNAMIC;

After doing this one at a time for the half-dozen or so entries I had to work on, I re-ran the original query (to identify compact row_formats) and came up with nothing. 

Edit: And the Administrator Notification pointing to "compact" row_format issues, is gone.

Edited by Thomas Emme
Link to comment
Share on other sites

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...