[MDEV-5332] Per-database options Created: 2013-11-25  Updated: 2015-10-30

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 4
Labels: None

Issue Links:
Relates
relates to MDEV-307 Add functionality for database comments Closed

 Description   

MySQL/MariaDB uses the file DATADIR/dbname/db.opt to store
the database default character set and collation.
We could also store more per-database options in this file.
For example, a default storage engine would be convenient:

CREATE DATABASE db1 DEFAULT_STORAGE_ENGINE=InnoDB;
 
CREATE DATABASE db2 DEFAULT_STORAGE_ENGINE=MyISAM;

Almost all system variables that can have both global and session
scope could be set per-database.

Some useful examples:

  • AUTOCOMMIT
  • DATETIME_FORMAT
  • DATE_FORMAT
  • DEFAULT_WEEK_FORMAT
  • DEFAULT_TMP_STORAGE_ENGINE
  • LC_MESSAGES
  • LOW_PRIORITY_UPDATES
  • SQL_MODE
  • TX_ISOLATION

Some global-only variables could also possibly be set per-database:

  • FT_STOPWORD_FILE
  • FT_QUERY_EXPANSION_LIMIT
  • FT_MAX_WORD_LEN
  • FT_BOOLEAN_SYNTAX
  • FT_MIN_WORD_LEN

 Comments   
Comment by Sergei Golubchik [ 2014-04-21 ]

Let's distinguish between table attributes that can be set per database and session variables. Table attributes, like ENGINE or CHARACTER SET or CHECKSUM. When it's set on a database, it means that all tables, created in this database, get this. For example,

ALTER DATABASE db1 DEFAULT_STORAGE_ENGINE=MyISAM, CHECKSUM=YES;
 
CREATE TABLE db1.t1 (a int);

this means that t1 table will use myisam storage engine and will have checksums enabled.

On the other hand, system variables set per database merely set session variable values based on the current database. For example,

INSERT db1.t1 VALUES (1);
 
ALTER DATABASE db1 SQL_MODE='ANSI_QUOTES';
 
SELECT "a" FROM db1.t1; -- will print "a", because SQL_MODE is '', though the table is in db1
 
USE db1; -- now SQL_MODE is ANSI_QUOTES
 
SELECT "a" FROM db1.t1; -- will print 1, because SQL_MODE is ANSI_QUOTES

These are two different functionalities, I'm not sure that we want both or that we want to mix both in one "per database options" feature.

Generated at Thu Feb 08 07:03:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.