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, 20204 yr by PoC2

 

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'

 

 

 

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 🙂

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, 20204 yr by Thomas Emme

I'm not having any joy applying the DYNAMIC row conversion on my seven COMPACT tables. For example, when I execute this:

ALTER TABLE ibf_core_follow_count_cache ROW_FORMAT=DYNAMIC;

 

It reports my InnoDB file format is wrong.  I get two warnings (and the row format remains in the compact mode):

Warning: #1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
Warning: #1478 InnoDB: assuming ROW_FORMAT=COMPACT.

So then I added these two statements to the my.cnf file on my paid hosting site:

 innodb_file_format=BARRACUDA
 innodb_large_prefix=1

Repeated the ALTER TABLE statement and still no joy.

So I'm seeking advice on how to do the COMPACT to DYNAMIC row conversion on my tables.

- Thomas

Edited January 5, 20214 yr by Thomas_B

I am having a issue maybe someone can help with--

I log into my cpanel a few days ago and discover I am running MariaDB 10.1

I call my host asking when they will upgrade it to MariaDB 10.3

They tell me not soon but I can move to one of their newer servers that already has MariaDB 10.3 for free

I authorize the migration to the new server

It is completed and DNS propagated

I check in my cpanel and all rows have now been set to DYNAMIC (with innodb same as I was running on 10.1 and Database client version: libmysql - 5.6.43 )

BUT my ACP still says I am running COMPACT and running MariaDB 10.1

What gives?

Thanks

 

UPDATE: All of a sudden my ACP is now showing MariaDB 10.3 and all rows are DYNAMIC and the notification is GONE!!!

Edited January 6, 20214 yr by Genestoy

 

What version of mySQL/MariaDB?

mySQL 5.6.

It's a hosted server. Tech support says that mySQL will be upgraded to 5.7 in a few weeks. But they cautioned that what I want to do is probably not going to work; The account is on a shared server and changing to the Barracuda file format would probably require a global setting that would affect all shared users.

But others have commented that they were told a similar story by their host. And yet they were still able to change to DYNAMIC row format. So I need advice on how to make this magic happen.

- Thomas

Edited January 7, 20214 yr by Thomas_B

*SOLVED*

Yesterday my host upgraded to mySQL 5.7, which included changing to the Barracuda file format (previously Antelope). So now the SQL commands were successfully executed and my "compact" database tables have been converted to "dynamic."

So I'd like to report a happy ending to my adventure. A big Thank You to the forum members for the advice and providing the SQL commands to do it.

- Thomas