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

[PoC2]

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 October 22, 2020 by PoC2

[CheersnGears]

Do you have ssh access on your server?

[PoC2]

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.

[CheersnGears]

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. 

[PoC2]

Does changing a table's ROW_FORMAT have any impact on the data or indexes (like losing them all)?

Some guidance would be appreciated.

[kmk]

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.

[Sonya*]

I have the same warning using MySQL v5.5.5-10.0.38-MariaDB 🤨

[Nathan Explosion]

13 hours ago, PoC2 said:

what tables

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'

 

 

[Sonya*]

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 🤔

[Sonya*]

I have only two options for the ROW_FORMAT: COMPACT and REDUNDANT. No DYNAMIC.

[PoC2]

Thank you for the very helpful replies so far.

I have now submitted this as a support ticket (and directed staff to this thread) and will report back on what they say.

[PoC2]

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.

 

[bfarber]

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.

[kmk]

@bfarber my hosting provider is already upgraded MySQL to 5.7.32

But run the support we still find this recommendation 

 

[opentype]

4 minutes ago, kmk said:

@bfarber my hosting provider is already upgraded MySQL to 5.7.32

But run the support we still find this recommendation 

Running 5.7 doesn’t change the row format. 

[Mercury5]

I proceeded to ask my webhost about this and they gave me a link to stackoverflow site to do it myself. They have faith where as I do not. I have no idea and wouldn't like to attempt such a thing.

 

 

 

[PoC2]

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.

[bfarber]

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.

[PoC2]

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.

 

[bfarber]

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.

[rionerotnt]

I use this query SQL on each InnoDB table

 

SET storage_engine=MYISAM;
ALTER TABLE table_name ENGINE = MyISAM;

 

 

Edited October 28, 2020 by rionerotnt

[GregoryRasputin]

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 🙂

[Thomas Emme]

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*

[Thomas Emme]

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 November 10, 2020 by Thomas Emme

[bfarber]

Yes, your host was mistaken - I'm glad you got it sorted!