kallithea Changeset - 7b0aafc6b7ca

Changeset - 7b0aafc6b7ca

Parent rev.

Child rev.

[Not reviewed]

default

0 2 0

Mads Kiilerich (mads) - 6 years ago 2020-06-15 17:27:19
mads@kiilerich.com

Grafted from: 5273e61453eb

mysql: create database with explicit UTF-8 character set and collation

A spin-off from Issue #378.

In MySQL, the character sets for server, database, tables, and connection are
set independently. Ideally, they should all use UTF-8, but systems tend to use
latin1 as default encoding, for example:

character_set_server = latin1
collation_server = latin1_swedish_ci

Databases would thus by default be created as:

character_set_database = latin1
collation_database = latin1_swedish_ci

To make things work consistently anyway, we have so far specified the utf8mb4
charset explicitly when creating tables, but there is no corresponding simple
option for specifying the collation for tables. We need a better solution.

If necessary and possible, the system charset and collation should be set to
UTF-8. Some systems already have these defaults default - see
https://mariadb.com/kb/en/differences-in-mariadb-in-debian-and-ubuntu/ .
The defaults can be changed as described on
https://mariadb.com/kb/en/setting-character-sets-and-collations/#example-changing-the-default-character-set-to-utf-8
to give something like:

character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

Databases will then by default be created as:

character_set_database = utf8mb4
collation_database = utf8mb4_unicode_ci

and there is thus no longer any need for specifying the charset when creating
tables.

To be reasonably resilient across all systems without relying on system
defaults, we will now start specifying the charset and collation when creating
the database, but drop the specification of charset when creating tables.

For existing databases, it is recommended to change encoding (and collation) by
altering the database and each of the tables inside it as described on
https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8 .

Note the use of utf8mb4_unicode_ci instead of utf8mb4_general_ci - see
https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci .

For investigation of these issues, consider the output from:
show variables like '%char%';
show variables like '%collation%';
show create database `KALLITHEA_DB_NAME`;
SELECT * FROM information_schema.SCHEMATA WHERE schema_name = "KALLITHEA_DB_NAME";
SELECT * FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "KALLITHEA_DB_NAME";

2 files changed with 1 insertions and 2 deletions:

kallithea/lib/db_manage.py

kallithea/model/db.py

0 comments (0 inline, 0 general)

kallithea/lib/db_manage.py

➞

Show inline comments

@@ @@ -76,49 +76,49 @@ class DbManage(object): @@
         """
         Create a auth database
         """
         log.info("Any existing database is going to be destroyed")
         if self.tests:
             destroy = True
         else:
             destroy = self._ask_ok('Are you sure to destroy old database ? [y/n]')
         if not destroy:
             print('Nothing done.')
             sys.exit(0)
         if destroy:
             # drop and re-create old schemas
             url = sqlalchemy.engine.url.make_url(self.dburi)
             database = url.database
             # Some databases enforce foreign key constraints and Base.metadata.drop_all() doesn't work
             if url.drivername == 'mysql':
                 url.database = None  # don't connect to the database (it might not exist)
                 engine = sqlalchemy.create_engine(url)
                 with engine.connect() as conn:
                     conn.execute('DROP DATABASE IF EXISTS `%s`' % database)
                     conn.execute('CREATE DATABASE `%s`' % database)
+                    conn.execute('CREATE DATABASE `%s` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' % database)
             elif url.drivername == 'postgresql':
                 from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
                 url.database = 'postgres'  # connect to the system database (as the real one might not exist)
                 engine = sqlalchemy.create_engine(url)
                 with engine.connect() as conn:
                     conn.connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
                     conn.execute('DROP DATABASE IF EXISTS "%s"' % database)
                     conn.execute('CREATE DATABASE "%s"' % database)
             else:
                 # known to work on SQLite - possibly not on other databases with strong referential integrity
                 Base.metadata.drop_all()
         checkfirst = not override
         Base.metadata.create_all(checkfirst=checkfirst)
         # Create an Alembic configuration and generate the version table,
         # "stamping" it with the most recent Alembic migration revision, to
         # tell Alembic that all the schema upgrades are already in effect.
         alembic_cfg = alembic.config.Config()
         alembic_cfg.set_main_option('script_location', 'kallithea:alembic')
         alembic_cfg.set_main_option('sqlalchemy.url', self.dburi)
         # This command will give an error in an Alembic multi-head scenario,
         # but in practice, such a scenario should not come up during database
         # creation, even during development.

kallithea/model/db.py

➞

Show inline comments

@@ @@ -146,49 +146,48 @@ class BaseDbModel(object): @@
     @classmethod
     def get_or_404(cls, id_):
         try:
             id_ = int(id_)
         except (TypeError, ValueError):
             raise HTTPNotFound
         res = cls.query().get(id_)
         if res is None:
             raise HTTPNotFound
         return res
     @classmethod
     def delete(cls, id_):
         obj = cls.query().get(id_)
         Session().delete(obj)
     def __repr__(self):
         return '<DB:%s>' % (self.__class__.__name__)
 _table_args_default_dict = {'extend_existing': True,
                             'mysql_engine': 'InnoDB',
                             'mysql_charset': 'utf8mb4',
                             'sqlite_autoincrement': True,
+                           }
 class Setting(Base, BaseDbModel):
     __tablename__ = 'settings'
     __table_args__ = (
         _table_args_default_dict,
+    )
     SETTINGS_TYPES = {
         'str': safe_bytes,
         'int': safe_int,
         'unicode': safe_str,
         'bool': asbool,
         'list': functools.partial(aslist, sep=',')
+    }
     DEFAULT_UPDATE_URL = ''
     app_settings_id = Column(Integer(), primary_key=True)
     app_settings_name = Column(String(255), nullable=False, unique=True)
     _app_settings_value = Column("app_settings_value", Unicode(4096), nullable=False)
     _app_settings_type = Column("app_settings_type", String(255), nullable=True) # FIXME: not nullable?
     def __init__(self, key='', val='', type='unicode'):

0 comments (0 inline, 0 general)