MySQL Query Help

[sychn]

Hi, m8s,

I tried to change the storage engine to InnoDB of my tables, but some of my tables are large and it returns 500 error.

Does anyone know how to do it by 1000 lines per round?

ALTER TABLE core_search_index ENGINE=InnoDB;

Thanks!

 

[MADMAN32395]

500 error? are you doing this through the SQL Toolbox or something else?

I personally use mySQL Workbench to get stuff done; easier than having to worry about timeouts or various other webserver errors.

[ASTRAPI]

Just run it from ssh

[sychn]

Thank you m8s, I tried to make a command list to batch excute it.

[media]

what is SSH (command line) code for this????
I have same problem I have access to command line but i need to know what code to run?

Thanks

[Afrodude]

1 hour ago, media said:

what is SSH (command line) code for this????
I have same problem I have access to command line but i need to know what code to run?

Thanks

 

ALTER TABLE databasename.tablename ENGINE=InnoDB;

[media]

I was able to convert all my myisam tables to innoDB

Here is a complete code for ssh 

mysql -u [username] -p

Enter your password.

Again, without the brackets, enter the command:

use [databasename];

Enter the command:

show tables;

The run this:

alter table [table_name] ENGINE=InnoDB;

[Iwooo]

Is it safe to upgrade to InnoDB from Myisam? 

[Miss_B]

2 hours ago, Iwooo said:

Is it safe to upgrade to InnoDB from Myisam? 

Yes, it is safe. However, make a backup of your database before the conversion. 

[bfarber]

On 10/3/2020 at 4:16 AM, Iwooo said:

Is it safe to upgrade to InnoDB from Myisam? 

Yes and it's strongly recommended. That said, you may wish to verify with your webhost that their MySQL configuration is optimized for InnoDB.

 

[media]

Yes it is safe, but I went thru some problems... Here is one of them

Quote

2020-09-18 9:23:28 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

and i fixed it. I have repaired the DB using mysqlchecks

 

[IP-Gamers]

On 10/3/2020 at 11:16 AM, Iwooo said:

Is it safe to upgrade to InnoDB from Myisam? 

I needed to transfer tables from innoDB to myISAM, no problems were found.

[Rick Vasquez]

https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

You should use pt-osc if you don't want to take downtime while doing this.