MySQL " identify storage engine statement"

Question

This IS NOT a Homework question! While building my current student database project I realized that I may want to identify comprehensive information about a database design in the future. More-so if I am fortunate enough to get a job in this field and were handed a database project how could I break down certain elements for identification...

In all of my previous designs I have been using MySQL Community Server (GPL) 5.1.42, I thought (duh) that I was using the MyISAM based on most of my text-book instruction and MySQL 5.0 Reference Manual :: 13 Storage Engines :: 13.1 The MyISAM Storage Engine

I determined that this was in fact incorrect for this version and the use of "SHOW ENGINES" at the console... No problem, figured out why they have "versions" the need to pay attention to what version is being used, and the need for a means to determine what I am about to mess up "if" I do not pay attention to detail...

Q1. Specifically what statement will identify the version used by someone elses initial database creation? (since I created my own databases I know what version I used)

Q2. Specifically what statement will identify the storage engine that the developer used when creating the database. (I specified a particular database in my collection then tried SHOW Engine, did not work, then tried to just get the metadata from one table in that database:

mysql> SELECT duck_cust, table_type, engine -> FROM INFORMATION_SCHEMA.tables -> WHERE table_schema = 'tp' -> ORDER BY table_type ASC, table_name DESC;

as this was not really what I wanted (and did not work) I am looking for some direction from the pros...

Q3. (If you really have the inclination to continue helping) If I were to access a database from an earlier/later "version" are there backward/forward compatibility issues for maintaining/updating data between versions? Please and Thank you in advance for your time and efforts! sammysmall

Answer 1

This worked fine when I tested it:

SELECT table_name, table_type, engine, version FROM INFORMATION_SCHEMA.tables  
WHERE table_schema = 'tp' ORDER BY table_type ASC, table_name DESC;

I don't know what duck_cust means in your example query. That's not a column in INFORMATION_SCHEMA.tables, so that is certain to make your query give an error.

I've had very little trouble with MySQL upgrading from one version to the next. But I still make a database backup before upgrading the software, just to be safe.

You should be in the habit of making backups regularly anyway.