Support testing of additional Storage engines

[robins]

Support testing of additional Storage engines (this commit focuses on MyISAM / CSV / Memory storage engines).

Am new to MySQL, so let me know if I need to work more at it. Some of the commits was about me learning MySQL and the Storage engine limitations. Hoping this'd be useful for someone.

Note:

• Blackhole was not included (actually added and subsequently removed) owing to lack of MariaDB support
  • Aria was not added for similar reason.
• Some testing (for e.g. DML on CSV table) was avoided owing to limitations or conflicting engine / FDW requirements.

Barring RDS specific expected failures, nothing else stood out while testing in the following .

• RDS MySQL 5.6 / 5.7 / 8.0
• Aurora MySQL 5.7 / 8.0
• RDS MariaDB 10.3 / 10.5

[surajkharage19]

Hi @robins,

Thanks for the patches. Your proposal is not directly testing the mysql_fdw features as such but testing the MySQL storage engine. While we may not get this committed but we are curious to understand why you think that it will be good to have these kinds of test cases. This will help us understand any use cases that we are not aware of.

A kind request to merge patches like typos etc in the single patch as far as possible, this avoids having to look at multiple patches.

[robins]

Ack on merging. This was an exercise in learning MySQL as well as how to work with PRs. Will keep in mind for the future.

About the utility of tests, I think they would be a good addition. MySQL storage engines are not merely an implementation difference but are logically visible to the end-user - with different limitations. Then I think it would be good to test whether the features for each engine works as expected (from FDW).

I am not an avid MySQL User, so correct me if I misunderstand, but server options such "Safe Update mode" require a Key column for UPDATE. This effectively renders a CSV table useless for UPDATEs. Now although MySQL_FDW + CSV storage engine don't go well with each other ... but I hope you get the point. If an SQL (triggered on a specific Server) can Succeed or Fail depending on the Storage Type of the underlying table, IMHO it makes sense to have a test that can bring that out.