👋🏾 Looking to join our SuiteCRM Community Working Group? 💌 Open invitation throughout January. Check out all the details here 👈

Change the Database engine MyISAM to InnoDB? 👈

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

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

Thanks!
Kind regards
PowerQuest

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:

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

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.

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

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

Kind regards
PowerQuest

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

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

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.

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

Do you have this change in your upgraded system?

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.

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.

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

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