CODEWITHSUNDEEP
mysql
Sam M
Sam M

Reputation: 4166

show create table, specifying the engine keyword

My production database is MySql 5.1.66. My development version is 5.5.28. When I run the statement show create table in production, the table DDL script outputs using the keyword TYPE to denote the storage engine. But that keyword was deprecated and replaced by ENGINE a while ago, so I can't create the table in version 5.5 without modifying the script output by version 5.1. Is there some sort of parameter to show create table that will cause the output to use the ENGINE keyword instead of TYPE?

Example output from 5.1:

CREATE TABLE `acr` (
  `IncidentNum` varchar(25)
) TYPE=InnoDB;

What it needs to run in 5.5:

CREATE TABLE `acr` (
  `IncidentNum` varchar(25)
) ENGINE=InnoDB;

Upvotes: 1

Views: 500

Answers (1)

eggyal
eggyal

Reputation: 125925

You can use the no_table_options value in mysqldump's --compatible argument:

  • --compatible=name

    Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5.1.7, “Server SQL Modes”.

    This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, --compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.

    This option requires a server version of 4.1.0 or higher. With older servers, it does nothing.

As documented under Server SQL modes:

Full List of SQL Modes

The following list describes all supported modes:

[ deletia ]

Upvotes: 1

Related Questions