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

[Genestoy]

I have done the

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

And all 247 rows are "COMPACT" can anybody provide a SQL command to change all of them as I don't want to have to do every single row manually

Also should the forum be taken offline when doing this? Also I have no ssh access

Thanks

Edited November 11, 2020 by Genestoy

[ZakRhyno]

Do all rows have to be dynamic? Are just the ones that are compact have to be dynamic?

[bfarber]

You are free to administer your server as you see fit. I will just say that we have seen tables run into row length issues when the tables use COMPACT row format.

[CoffeeCake]

On 10/28/2020 at 3:36 AM, rionerotnt said:

I use this query SQL on each InnoDB table

 

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

This seems to have been missed in the back and forth. You should really not use MyISAM. That is not an appropriate solution for this recommendation. 

Instead, as @bfarber pointed out, ignore the recommendation or change to dynamic row format with InnoDB.

https://mariadb.com/kb/en/choosing-the-right-storage-engine/

[Thomas_B]

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, 2021 by Thomas_B

[Nathan Explosion]

What version of mySQL/MariaDB?

[Genestoy]

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, 2021 by Genestoy

[Nathan Explosion]

14 minutes ago, Genestoy said:

All of a sudden

That's because the task to check things ran...

[Genestoy]

4 minutes ago, Nathan Explosion said:

That's because the task to check things ran...

Thanks Nathan, that explains it!

[Thomas_B]

19 hours ago, Nathan Explosion said:

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, 2021 by Thomas_B

[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

[acarlsson]

I have 202 tables that has to be changed. Is there any way to do them all at once? 😬

SQL: v5.5.5-10.1.47-MariaDB

phpMyAdmin 5.0.3

Edited March 19, 2021 by acarlsson

[CoffeeCake]

7 hours ago, acarlsson said:

phpMyAdmin 5.0.3

Don't use phpMyAdmin for this or any other major SQL operation. Connect via SSH and use the command line client.

You can script out all 202 tables into a single command that you send at once.

[AlessandroTax]

I followed all the steps reported here and I was able to change the tables to InnoDB and Dynamic format. Unique difference is that I am still running MySQL version 5.6.

Now I have an issue where the website sometimes is super fast and sometimes it takes really long to load pages, forums, etc. Sometimes it sais the website is not reacheable and, after 3-4 page refresh, it works.

Any idea to solve it?