Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5332

Per-database options

        Details

        • Type: Task
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Fix Version/s: 10.1
        • Component/s: None
        • Labels:
          None

          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

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Task - A task that needs to be done. MDEV-307 Add functionality for database comments

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Open

                  Activity

                  Hide
                  Permalink
                  serg Sergei Golubchik added a comment - - edited

                  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.

                  Show
                  serg Sergei Golubchik added a comment - - edited 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.

                    People

                    • Assignee:
                      Unassigned
                      Reporter:
                      bar Alexander Barkov
                    • Votes:
                      4 Vote for this issue
                      Watchers:
                      6 Start watching this issue

                      Dates

                      • Created:
                        Updated: