/ Register

Get the most out of your Centmin Mod LEMP stack
Become a Member

MariaDB character and collation option

Discussion in 'MariaDB & General MySQL news & discussions' started by Sunka, May 25, 2020.

Previous Thread Next Thread
Loading...
  1. May 25, 2020 #1
    Sunka

    Sunka Well-Known Member

    1,150
    323
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +521
    Local Time:
    9:17 PM
    Nginx 1.17.9
    MariaDB 10.3.22
    MySQL version 10.3.23 (10.3.23-MariaDB)

    I have question.

    This is mysqld part in my.cnf
    Code (Text):
    [mysqld]

local-infile=0
ignore_db_dirs=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock


    Could I just add/change this
    Code (Text):
    character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci


    So it will be:
    Code (Text):
    [mysqld]

local-infile=0
ignore_db_dirs=lost+found
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock


    And then restart mysql?
    Would it hurt in some way my database?
     
  2. May 26, 2020 #2
    eva2000

    eva2000 Administrator Staff Member

    49,887
    11,487
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +17,843
    Local Time:
    5:17 AM
    Nginx 1.21.x
    MariaDB 10.x
    Generally should be fine but changing from UTF8 to UTF8MB4 will reduce max InnoDB index length so need to be aware if you have not so smart database schema's in use MariaDB - Index column size too large. The maximum column size is 767 bytes.

    If using utf8mb4 read official documentation for why you need to carefully go through all databases and ensure the max key prefix index sizes are within InnoDB limits outlined at MySQL :: MySQL 5.6 Reference Manual :: 14.8.8 Limits on InnoDB Tables

    Latest 123.09beta01 already adds to /etc/my.cnf the relevant settings to support larger InnoDB index prefixes as outlined at MySQL :: MySQL 5.6 Reference Manual :: 10.1.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets
    Code (Text):
    egrep 'format|large' /etc/my.cnf | uniq 
innodb_large_prefix=1
innodb_file_format = Barracuda

    But still you need to be consciously aware of the differences if you run into them.

    and example of where a database schema/structure can run into such issues if not properly accounting for UTF8MB4 instead of UTF8 character set and reduced index length limits MariaDB - Error importing sql database: Row size too large (> 8126)

     
  3. May 26, 2020 #3
    Sunka

    Sunka Well-Known Member

    1,150
    323
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +521
    Local Time:
    9:17 PM
    Nginx 1.17.9
    MariaDB 10.3.22
    hmm
    Code (Text):
    # egrep 'format|large' /etc/my.cnf | uniq
# binlog_format=ROW
#innodb_file_format = Barracuda
innodb_default_row_format = dynamic
# binlog_format=ROW
#innodb_file_format = Barracuda
innodb_default_row_format = dynamic
# binlog_format=ROW
     
  4. May 27, 2020 #4
    eva2000

    eva2000 Administrator Staff Member

    49,887
    11,487
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +17,843
    Local Time:
    5:17 AM
    Nginx 1.21.x
    MariaDB 10.x
    interesting innodb_large_prefix=1 was added to default my.cnf templates 5+ years ago centminmod/centminmod
     
Previous Thread Next Thread
Loading...

.