Bug #77090 Inject default specification per database into CREATE/ALTER commands

• Submitted: 19 May 2015 11:56
• Reporter: Andrii Nikitin
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[19 May 2015 11:56] Andrii Nikitin

Description:
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:
FR

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):
innodb_file_per_table
default_storage_engine
default_tmp_storage_engine
delay_key_write
max_heap_table_size
myisam_data_pointer_size