Bug #77090 Inject default specification per database into CREATE/ALTER commands
Submitted: 19 May 2015 11:56
Reporter: Andrii Nikitin Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[19 May 2015 11:56] Andrii Nikitin
The idea is to configure flexibility of CREATE and ALTER commands and let DBA choose default global and per database specifications .
Implementation of this FR should cover many current and future FRs

How to repeat:

Suggested fix:
Idea of structure may be:

create table default_specification
	db varchar comment '* means global scope',
	object varchar comment 'object for which CREATE or ALTER command is used. For tables may be ENGINE',
	specification varchar,
	value varchar,
	primary key (db, engine, specification)

Whenever corresponding SQL command is executed, MySQL automatically chooses default configuration as specified in the table (if any).

example uses:

1. Let innodb tables in 'test' database to be created in individual tablespaces and have Compact ROW_FORMAT (Bug #8674):
'test' 'INNODB' 'tablespace' 'innodb_file_per_table'
'test' 'INNODB' 'ROW_FORMAT' 'Compact'

2. Let all DATABASES be latin1 default:
* 'DATABASE' 'character set' 'latin1'

3. Let procedures to have definer= INVOKER by default
* 'PROCEDURE' 'definer' 'invoker'

4. Let global default engine be InnoDB and myisam in 'test' database:
'*' 'TABLE' 'engine' 'innodb'
'test' 'TABLE' 'engine' 'myisam'

5. Let default engine for temporary tables be InnoDB :
'*' 'TEMPORARY TABLE' 'engine' 'innodb'

Presence of such universal feature will deprecate variables (which are read during CREATE / ALTER):