Server Support is telling me civicrm database is half innodb and half isam

[GeekFamily]

We have been trying to update to 4.4.5 but can't get a good backup so are hesitant to do the update.  Our server support company is telling us that our civicrm database is half innodb and half isam - but unfortunately I'm don't even know how to check for that much less convert it.  From what I'm reading about this topic - I'm not even sure why the database is even working??  it is supposed to be on Innodb - correct?

We are running drupal 7.28 with civicrm 4.4.4 on an Apache Server inside of cPanel.  Any help and/or pointers in the right direction of where to go with this would be MUCH appreciated.

[Hershel]

Yes, all the tables should be InnoDB for CiviCRM. You can click on phpMyAdmin to view your tables and see what type each is. You can also convert them to InnoDB:

http://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html

[GeekFamily]

One more part of the story.  Looking into this further - it seems we have a number of "non-standard" tables - myrelationships (with random numbers) and civicrmexporttemp - they are all empty.  These are the tables that are isam.  So - can we just delete these?

[Hershel]

Not sure why you have those, but you could certainly make a backup first and then delete them, yes.

[GeekFamily]

OK - I did make a backup and then delete those myISAM tables but now civi is recreating them as well as some additional tables that are marked as "Memory"??

I wasn't having this problem in of my other civi installs but now I have found those same "myrelationships-randomnumber" tables in 2 of my other installations - and they are indeed myISAM type instead of InnoDB and "mysecondaryrelationships-randomnumber" that are being created as myISAM type as well.

Could it be some module or extention I have installed or??

[GeekFamily]

Side note: we are now on Drupal 7.30, civicrm 4.4.6

[Hershel]

The full table name is mysecondaryrelationships-randomnumber or it's civicrm_mysecondaryrelationships-randomnumber ?

[joanne]

I have those tables in my install also ( although they are Innodb not Myisam).  The names a just myrelationshipsXXXXX and mysecondaryrelationshipsXXXX.. (13 digits).  They are not prefixed by civicrm_ .

Still, the name of them suggests something to do with setting Allow second-degree relationship permissions to Yes.

[petednz]

what extensions do you have on??

https://civicrm.org/extensions/relationship-permissions-acls seems a likely reason to have those extra tables

[Eileen]

The second degree relations table did have a release that created tables instead of temporary tables.

It *may* be the last downloadable release - not sure. I stopped publishing for automatic them because civicrm doesn't support dependencies - however you can grab the latest & it works on 4.4.6. Here is the information about it

https://civicrm.org/extensions/relationship-permissions-acls

There is a branch of this extension that is not available for automatic download because automatic download doesn't support dependencies or sub-point requirements. However you can use the 1.2 branch if you ensure you have these things covered

1.2 branch features

- ability to set some relationship types to always be permissioned (this doesn't retrospectively change them but does cause save on any relationships to have the permission flag going forwards)

- second degree permissions. (If the second degree permissions is checked then on admin/misc screen). For example if you have a organisation called 'Chapter' and it has a permissioned relationship over an organisation called 'branch' then anyone with a permissioned relationship over the chapter will have permissions to access anyone the branch has a permissioned relationship over. There is no theoretical limit to the number of relationshipsbut only organisations and households pass on their permissions. This is because you cannot login as an organisation / household so there is no other purpose served by a household having permission over someone. However, individuals may throw up some more nuances - so for now they are excluded.

 

Installing 1.2

1) install entity settings (through the UI)

2) ensure you have a 4.4.6+ (but not 4.5 at this stage )

grab the extension code from

https://github.com/eileenmcnaughton/nz.co.fuzion.relatedpermissions/tree...

[GeekFamily]

Sorry for not answering this sooner - my ADHD sometimes gets the best of me.

This is happening in 2 of my databases:
On one I have:

• Any Telephone or Email Search
• CiviCRM Report Error
• US County Loader
• Event Calendar
• Civimobile
• Export to Excl
• Display the groups in the contact summary
• Normalise the data entered by your constituents
• Prevent users to overwrite their record
• quick lookup for autocomplete in the navigation
• send event conf
• CiviConfigure
• CSV GUI Import to api
• Extended Report
• Related Permissions Module
• CiviDiscount Module Extension
• CiviVolunteer
• Offline Recurring Paymetn
• CiviBooking
• CiviCRM Contact Calendar

[GeekFamily]

So short answer should have been - yes, it looks like I have related permissions installed.  I'm sorry to sound so dumb - but how do I go about fixing this?  And I just upgraded to 4.5 - so I'm not sure Eileen's fix below would work?

[Eileen]

So, you can install entitysettings safely - it won't do anything without other modules - it just stores settings related to entities and adds them to the admin pages.

 If running the latest version of relatedpermissions causes any problems it will only affect the page where you configure relationship types - so you can upgrade to the latest version of relatedpermissions, check if that page works & flip back if there are problems - there are no DB changes.

You should also remove all those myrelationships tables from your DB - it is OK to delete them at any time - they will be recreated if needed (& only as temp tables on the latest version)

[GeekFamily]

The full table name is mysecondaryrelationships-randomnumber or it's civicrm_mysecondaryrelationships-randomnumber ?

Just FYI - because I realized I didn't answer this question - it does not have the civicrm in front of it and the rest of my "regular" tables do.

Trying Eileen's suggestion shortly.

[GeekFamily]

Goodness - I have SOOO much to learn.  OK - I managed to get Entity Settings Installed but I get to the GitHub page above (I'm assuming this is a manual extension install) - do I just ftp those files into the nz.co.fuzion.relatedpermissions directory ? Or do I have to do something to actually install?

Thanks in advance for all your help!