Change the Database engine MyISAM to InnoDB? šŸ‘ˆ

SuiteCRM Forum - English Language Installation & Upgrade Help
1

Iā€™m planning to change the DB engine type from the old myisam to innodb after reading that article post below

due to I have issues with not being able to upgrade suiteCRM because it throws a databse error, (MySQL error 1071: Specified key was too long; max key length is 1000 bytes):

Now with that said.:
1. ) Is there any database table that should NOT be changed to InnoDB and must stay as MyISAM?

2.) Anything that I should think about before doing this?

Thanks in advance for the help.
Kind regards
PowerQuest

2

Anyone here that could help me with clarifying this for me, please?

Thanks!
Kind regards
PowerQuest

3

Hi @PowerQuest,

Sorry for the delay in replying.

Iā€™m using InnoDB by default on all my instances, so you should be able to use it.

Iā€™m not sure if there are issue when migrating from one to the other.

Regarding the (MySQL error 1071: Specified key was too long; max key length is 1000 bytes):

I think this might be a known issue in that version of the db.

Could you check the following StackOverflow answer? It may help I think:

4

Thanks for the reply @clemente.raposo ; I appreciate you. :facepunch:

Well from my research it seems that InnoDB allows more characters than the older format MyISAM so I am hoping that could solve the issue or at least some of it. Right now I cannot upgrade because it throws that ā€œdatabase error se suitcrm.logā€ which then contains the 1071 error. So Iā€™m stuck between a rock and a hard place really.

Thanks for the link, I will check that out.

Well if anyone knows if I should convert all DB tables to InnoDB or maybe leave a few specific tables as MyISAM for any specific reason than I would be very happy for that help. :smiley:

Kind regards
PowerQuest

5

Iā€™ve never heard of leaving only some tables in one systemā€¦

That 1000 chars error is just a quirk, it can be solved. There are numerous threads here in the forums with this issue, and I believe I remember seeing this also on Github.

6

Thanks @pgr for confirming this. :+1:
I just want to be sure so that I do not mess up anything when I do the DB conversion as it is my live production CRM. :wink:

Kind regards
PowerQuest

7

Well apparently the solution is to convert to InnoDB, unless you anything else you need to do too? :thinking:

Kind regards
PowerQuest

8

The index could be built differently. We donā€™t need to hit that 1000 chars limit.

9

So what does that mean? Is it a possibility that by changing to InnoDB the DB/suiteCRM could be corrupted?

And if changing to InnoDB is not the solution the issue then what is the solution of the 1000 chars limit then?

Thanksā€¦

Kind regards
PowerQuest

10

I would explain more fully if I knew how, but I donā€™t :frowning:

I just seem to remember someone did a more thorough investigation of this issue, perhaps here, perhaps on GitHub, maybe this one

ā†’ https://github.com/salesagility/SuiteCRM/issues/7509

The index is built from a few fields, and these fields were increased in length a couple of years ago. But there are other ways of generating the index. This will probably require something to be tweaked in core SuiteCRM. But it shouldnā€™t be too difficult to work around by removing the index and replacing it with something equivalent.

11

Ok thanks @pgr

Hmm not sure what to do from here and how to go about this as it seems more and more complicated. :thinking:

I just want to fix this obviously so I can upgrade my system but I feel more and more confused about it.

Kind regards
PowerQuest

12

Do you have this change in your upgraded system?

13

Thank you pgr for your reply and your kindess.

No, and i cannot see how to apply that fix either?

All I have done is to install the update package and then things went south with those errors. I cannot even run the rebuild in the admin section because that generates the ā€œdatabase error please see suitecrm.logā€.

Current version:
image
and Iā€™m stuck on that.

14

Just apply the fix manually and try a QR&R and see if it works.

I donā€™t know why you donā€™t have it in 7.11.22ā€¦ on GitHub it seems to be there.

15

Hi pgr and thanks for your reply. :smirk:

Do you mean the one with changing the tables to InnoDB?

Sorry I donā€™t know what you mean with "QR&R ". Could you be kind and please explain what that means?

Thanks in advance. :+1:
Kind regards
PowerQuest

16

QR&R = Quick Repair and Rebuild

Apply the fix = I meant change the code according to that GitHub commit, and see if it makes the error (about the1000 chars) go away

17

IĀ“ll check it again then. Lets see how it goes and post here how it turned outā€¦

Thanks! :facepunch:

Kind regards
PowerQuest

18

Hmmmm :thinking:
To be honest IĀ“m not sure how to do this:
image
I guess I have to do something/run querys in MariaDB/MySql (?), but I donĀ“t know what commands etc that is needed to make this happenā€¦

Thanks in advance.
Kind regards
PowerQuest

19

What I meant was simply to apply this change to file by editing it manually:

ā†’ https://github.com/salesagility/SuiteCRM/pull/7510/files

Then run a Quick Repair and Rebuild

20

Ah ok, now i understand. So basically update the vardefs.php file on those line and replace it with that new code?

Awesome, thank you so much @pgr ! ::+1:

Iā€™ll do this and post an update then about how it turned out.

Kind regards
PowerQuest