Suggestion: Allow SQL engine to be specified in conf_global.

[Wolfie]

Current host has InnoDB as the default engine which is great.  However, I'm looking to switch to a different hosting company and their default is MyISAM, which causes issues.  I see in the code that the supported engines are checked and the "default" one is chosen as the default to be used.  However, the administrator of the community should be able to specify the preferred engine that gets used (if it's supported, of course).  If it's not supported, then go with the server's default engine.

$INFO['sql_engine'] = (MyISAM or InnoDB).

 

 

 

 

[Wolfie]

I'm thinking something like this, though I'm sure one of the devs can tweak it to be cleaner/better.

System\Db\Db.php

	public function defaultEngine()
	{
		$default = "";
		$result = $this->forceQuery( "SHOW ENGINES" );

		while( $engine = $result->fetch_assoc() )
		{
			if( mb_strtoupper( $engine['Support'] ) == 'YES' and isset( $INFO ) and $engine['Engine'] == mb_strtoupper( $INFO['sql_engine'] ) )
			{
				return $engine['Engine'];
			}
			if( mb_strtoupper( $engine['Support'] ) == 'DEFAULT' )
			{
				$default = $engine['Engine'];
			}
		}
		
		return $default != "" ? $default : ( $this->_innoDbSupportsFulltextIndexes() ? 'InnoDB' : 'MyISAM' );
	}

 

[teraßyte]

That actually used to be the case at some point, but IPS now suggests always using InnoDB, which is more reliable than MyISAM compared to the past. Even the ACP Support page in 4.x will give you a warning if there are non-InnoDB tables.

[Wolfie]

The problem is that some hosting companies set the default to MyISAM and there's no way to select InnoDB as preferred, thus the suggestion.  That way if it's supported but not default, it can still be chosen.  Of course there could be a situation where someone would prefer to use MyISAM (not sure why) and this would allow that.

I don't know the technical aspects between the two, only that InnoDB is supposed to be faster and more efficient.  The fact that IPS recommends it is another reason I prefer it.  They know what works best for the software, so I'll try to follow that when possible.

 

Of course, an alternative is to add in an optional constant for preferring InnoDB when available, so it will do the above, but specifically look for InnoDB vs a user chosen engine.

Edited June 17, 2023 by Wolfie

[Charles]

If your host is defaulting to MyISAM, you need a new host 🙂

InnoDB has been the default since MySQL 5.5 release in July 2010. I would say if your host is 13 years behind the times then you need to move along.

[Wolfie]

It's the lowest cost alternative I could find to what I'm using now.  My current host upped the price on me so I need to switch, and I looked at many options.

[Randy Calvert]

The cheapest solution is typically not really a good solution.  It means you gotta deal with the crap like bad and unoptimized configurations.

In my experience the equation is GOOD, FAST, and CHEAP. But you can only pick TWO. 

[Wolfie]

8 hours ago, Randy Calvert said:

In my experience the equation is GOOD, FAST, and CHEAP. But you can only pick TWO. 

If I had money to burn, then I'd stick with my current hosting.  It's not a case of wanting to pinch pennies, but a need.  What I picked actually isn't the cheapest, but cheaper than my current and matched enough of my requirements for me to choose them.  There were others that I skipped over completely due to being "too good to be true" or not providing features or options that I wanted.

I already contacted the new hosting provider to ask if they have any plans to change the default to InnoDB since it's recommended, the standard, and the default as of v5.5 (they're on 8.0.33).  So far it's a no.  There is an option I could follow, but I would lose something in the process, so I have to consider a workaround if I go that route.  Of course, if this option gets added, it would solve the problem not only for me, but would also give other community administrators the ability to choose the preferred engine if going with the server default isn't desired for whatever reason.

[Marc Stridgen]

Its quite unlikely we would begin adding something to work around hosting companies not having the proper capabilities, to be honest. Using a self hosted environment, you should be looking for your host to meet the requirements your software requires, rather than looking for a workaround.

[Wolfie]

On 6/19/2023 at 8:57 AM, Marc Stridgen said:

Using a self hosted environment, you should be looking for your host to meet the requirements your software requires, rather than looking for a workaround.

InnoDB isn't a requirement for the IPS Suite though, much less it being the default engine.  It's a simple thing to add with a great benefit, so I'm hoping the devs will at least consider it.

[Marc Stridgen]

7 minutes ago, Wolfie said:

InnoDB isn't a requirement for the IPS Suite though, much less it being the default engine.  It's a simple thing to add with a great benefit, so I'm hoping the devs will at least consider it.

Sorry, I should say recommendation. However these are still hosting related items, not software

[Wolfie]

True, but allowing the admin to tell the software what engine they'd prefer it to use (even if it's not the default) is a software related item.  😉

[Matt]

Imagine a world where you no longer have to fight hosts, databases and server admin. A world where you can focus purely on building your community instead of hours of your life managing a server.

https://invisioncommunity.com/services/switch-to-invision/

[Charles]

9 hours ago, Wolfie said:

InnoDB isn't a requirement for the IPS Suite though, much less it being the default engine.  It's a simple thing to add with a great benefit, so I'm hoping the devs will at least consider it.

We are not adding support for MyISAM when MySQL itself defaults to InnoDB. You should either switch to our cloud services or get a competent host. It is so written.

[Wolfie]

I think there's been a misunderstanding.  I'm all for InnoDB over MyISAM and not the other way around.  The feature request is to allow a community owner to select a preferred engine to use (in my case, InnoDB) if it's supported vs the default engine of the server.  The software does support MyISAM but that's not what I'm asking for.  InnoDB is the preferred/recommended engine (as you have indicated), but some hosts unfortunately don't have it as the default for whatever reason.  Also, there may be situations where a person may prefer an engine that isn't typically recommended (for who knows why), and it would allow them to easily use it.

If nothing else, then defaulting to InnoDB if it's supported before going with the default engine of the server would also work.  It's the recommended one anyway, so why fallback to MyISAM if it's not necessary?

[Randy Calvert]

The feedback you’re getting is that this is a server level setting. You should be setting it in your MySQL my.cnf.  

The server sets the default engine type, not the software. 

[Ryan Ashbrook]

The problem is the two engines are vastly different, and it’s becoming increasingly difficult to support both.

Oracle themself recommends InnoDB.

https://blogs.oracle.com/mysql/post/still-using-myisam-it-is-time-to-switch-to-innodb

[Wolfie]

4 hours ago, Randy Calvert said:

The server sets the default engine type, not the software. 

I'm aware of that, but I'm not asking for the software to change the server's default setting.  Both MyISAM and InnoDB are supported.  The request is to tell IPS (the suite software) to use a preferred engine if it's supported by the server instead of using the engine labeled as default by the server.  Either that or to always use InnoDB if it's available even if it's not the default.

If you look in the Db.php file, you'll see that it looks for the default engine and then uses that (if one is marked as the default).  The change I'm suggesting is to let the admin choose which to use (such as InnoDB), so long as it's supported by the server.  If not, then it falls back to the default engine.

 

1 hour ago, Ryan Ashbrook said:

The problem is the two engines are vastly different, and it’s becoming increasingly difficult to support both.

If you know and are allowed to say, will InnoDB be required in a future version of the suite (such as IPS5)?  If so, then that would make this request/suggestion a moot point.

[Matt]

We have made a conscious decision to be less involved in helping people use budget hosting by adding features to compensate. For example, adding the default option, then adding methods to trim long indexes under the 767 limit, and testing, etc would probably cost around $10,000 in development costs - and we'd only do that to enable people to continue using hosts that don't give you the correct tools.

We have added a lot of code and functionality in the past to allow a variety of hosting environments but we really want to be more firm in what we will support moving forwards.

This may mean people need to speak with their hosts, or even switch hosts that support our needs (like our own cloud service).

[PatrickRQ]

7 hours ago, Matt said:

We have made a conscious decision to be less involved in helping people use budget hosting by adding features to compensate. For example, adding the default option, then adding methods to trim long indexes under the 767 limit, and testing, etc would probably cost around $10,000 in development costs - and we'd only do that to enable people to continue using hosts that don't give you the correct tools.

We have added a lot of code and functionality in the past to allow a variety of hosting environments but we really want to be more firm in what we will support moving forwards.

This may mean people need to speak with their hosts, or even switch hosts that support our needs (like our own cloud service).

Not directly related to the topic, however.. I have few MyISAM tables, still from IPB 3.x times when it was advised to covert large tables to MyISAM to resolve e.g. large topics performance issue. After migration to IPB3 they remained like this. Is there any risk or any potential loss if I will covert them back to InnoDB?

Edited June 23, 2023 by PatrickRQ

[Randy Calvert]

39 minutes ago, PatrickRQ said:

Not directly related to the topic, however.. I have few MyISAM tables, still from IPB 3.x times when it was advised to covert large tables to MyISAM to resolve e.g. large topics performance issue. After migration to IPB3 they remained like this. Is there any risk or any potential loss if I will covert them back to InnoDB?

It's a pretty reliable/safe operation.  I've done it multiple times personally.  (I had a few sites that came all the way back from 1.x!) As a best practice, it's recommended you take a backup of the database before any changes just to be 100% safe.  

I would PERSONALLY recommend using SSH to do the change instead of phpMyAdmin so that you don't run into any timeout issues.  If the table is small enough though, phpMyAdmin might work.  

Edited June 23, 2023 by Randy Calvert

[Wolfie]

13 hours ago, Matt said:

For example, adding the default option, then adding...

I mean the function to get the default Engine is already in Db.php.  If this will be getting removed with InnoDB becoming a requirement, then this suggestion is moot.

 

13 hours ago, Matt said:

We have added a lot of code and functionality in the past to allow a variety of hosting environments but we really want to be more firm in what we will support moving forwards.

So does this mean that MyISAM will be losing support (such as in IPS5)?  Again, if so, meaning that InnoDB will be required/forced, then (IMO) it solves the issue anyways.  Would be my preference, to be honest.

 

6 hours ago, PatrickRQ said:

Is there any risk or any potential loss if I will covert them back to InnoDB?

Always a risk no matter what you do.  It's why backing up the database and files is always recommended before upgrading your community.  So backup your database and then follow @Randy Calvert's advice and do it via SSH if possible.

 

5 hours ago, Randy Calvert said:

I would PERSONALLY recommend using SSH to do the change instead of phpMyAdmin so that you don't run into any timeout issues.  If the table is small enough though, phpMyAdmin might work.

Agreed.  SSH/console is much MUCH faster and less likely to cause issues.  Any issues encountered are likely ones that would happen anyway.

1. Backup (export) database.

2. Run query to get list of tables using MyISAM, sorted largest to smallest (storage usage).

3. Update very large tables individually, then the rest of the tables in groups.

4. Done.