You are not logged in.

#1 2019-03-15 09:13:05

mkheaven
Member
Registered: 2018-01-17
Posts: 15

MariaDB - Installation of system tables failed!

Hello everybody!

I need some MySQL db to use from python3, so I followed wiki https://wiki.archlinux.org/index.php/MariaDB,
but got stuck on just the second step:

# mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
2019-03-15 11:44:41 6 [ERROR] Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 100136, now running 100313. Please use mysql_upgrade to fix this error
2019-03-15 11:44:41 6 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALU
ERROR: 1136  Column count doesn't match value count at row 1
2019-03-15 11:44:41 0 [ERROR] Aborting


Installation of system tables failed!  Examine the logs in
/var/lib/mysql for more information.

The problem could be conflicting information in an external
my.cnf files. You can ignore these by doing:

    shell> /usr/bin/mysql_install_db --defaults-file=~/.my.cnf

You can also try to start the mysqld daemon with:

    shell> /usr/bin/mysqld --skip-grant-tables --general-log &

and use the command line tool /usr/bin/mysql
to connect to the mysql database and look at the grant tables:

    shell> /usr/bin/mysql -u root mysql
    mysql> show tables;

Try 'mysqld --help' if you have problems with paths.  Using
--general-log gives you a log in /var/lib/mysql that may be helpful.

The latest information about mysql_install_db is available at
https://mariadb.com/kb/en/installing-system-tables-mysql_install_db
You can find the latest source at https://downloads.mariadb.org and
the maria-discuss email list at https://launchpad.net/~maria-discuss

Please check all of the above before submitting a bug report
at http://mariadb.org/jira

Read that and tried mysql_upgrade:

# mysql_upgrade --verbose
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)
FATAL ERROR: Upgrade failed

I also can't find error logs here, only some binaries:

# tree -L 2 /var/lib/mysql/
/var/lib/mysql/
├── aria_log.00000001
├── aria_log_control
├── ib_buffer_pool
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── mysql
│   ├── columns_priv.frm
│   ├── columns_priv.MYD
│   ├── columns_priv.MYI
│   ├── column_stats.frm
│   ├── column_stats.MYD
│   ├── column_stats.MYI
│   ├── db.frm
│   ├── db.MYD
│   ├── db.MYI
│   ├── event.frm
│   ├── event.MYD
│   ├── event.MYI
│   ├── func.frm
│   ├── func.MYD
│   ├── func.MYI
│   ├── general_log.CSM
│   ├── general_log.CSV
│   ├── general_log.frm
│   ├── gtid_slave_pos.frm
│   ├── gtid_slave_pos.ibd
│   ├── help_category.frm
│   ├── help_category.MYD
│   ├── help_category.MYI
│   ├── help_keyword.frm
│   ├── help_keyword.MYD
│   ├── help_keyword.MYI
│   ├── help_relation.frm
│   ├── help_relation.MYD
│   ├── help_relation.MYI
│   ├── help_topic.frm
│   ├── help_topic.MYD
│   ├── help_topic.MYI
│   ├── host.frm
│   ├── host.MYD
│   ├── host.MYI
│   ├── index_stats.frm
│   ├── index_stats.MYD
│   ├── index_stats.MYI
│   ├── innodb_index_stats.frm
│   ├── innodb_index_stats.ibd
│   ├── innodb_table_stats.frm
│   ├── innodb_table_stats.ibd
│   ├── plugin.frm
│   ├── plugin.MYD
│   ├── plugin.MYI
│   ├── proc.frm
│   ├── proc.MYD
│   ├── proc.MYI
│   ├── procs_priv.frm
│   ├── procs_priv.MYD
│   ├── procs_priv.MYI
│   ├── proxies_priv.frm
│   ├── proxies_priv.MYD
│   ├── proxies_priv.MYI
│   ├── roles_mapping.frm
│   ├── roles_mapping.MYD
│   ├── roles_mapping.MYI
│   ├── servers.frm
│   ├── servers.MYD
│   ├── servers.MYI
│   ├── slow_log.CSM
│   ├── slow_log.CSV
│   ├── slow_log.frm
│   ├── tables_priv.frm
│   ├── tables_priv.MYD
│   ├── tables_priv.MYI
│   ├── table_stats.frm
│   ├── table_stats.MYD
│   ├── table_stats.MYI
│   ├── time_zone.frm
│   ├── time_zone_leap_second.frm
│   ├── time_zone_leap_second.MYD
│   ├── time_zone_leap_second.MYI
│   ├── time_zone.MYD
│   ├── time_zone.MYI
│   ├── time_zone_name.frm
│   ├── time_zone_name.MYD
│   ├── time_zone_name.MYI
│   ├── time_zone_transition.frm
│   ├── time_zone_transition.MYD
│   ├── time_zone_transition.MYI
│   ├── time_zone_transition_type.frm
│   ├── time_zone_transition_type.MYD
│   ├── time_zone_transition_type.MYI
│   ├── transaction_registry.frm
│   ├── transaction_registry.ibd
│   ├── user.frm
│   ├── user.MYD
│   └── user.MYI
├── mysql-bin.000001
├── mysql-bin.000002
├── mysql-bin.000003
├── mysql-bin.index
├── mysql-bin.state
├── performance_schema
│   └── db.opt
└── test

3 directories, 101 files
# mysql -V
mysql  Ver 15.1 Distrib 10.3.13-MariaDB, for Linux (x86_64) using readline 5.1

Is it a bug?

Offline

#2 2019-03-17 23:24:33

mkheaven
Member
Registered: 2018-01-17
Posts: 15

Re: MariaDB - Installation of system tables failed!

I tried mariadb-git form AUR and it worked, so I guess the fix is coming.


Also found, that

$ mysql -u root -p

only works from root or sudo - probably wiki needs an update.

It seemed all right, queries were working, but...

I changed the default character set to UTF8: (folowing official wiki)

[client]
...
default-character-set=utf8mb4
...
[mysql]
...
default-character-set=utf8mb4
...
[mysqld]
...
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
...

and then I tried upgrading and got this:

$ sudo mysql_upgrade -u root -p

Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1267 (HY000) at line 7: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 59: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 168: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 179: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 195: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 322: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 393: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 400: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 407: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'
FATAL ERROR: Upgrade failed

I searched for a while and found similar problems with solutions, but they all were coming to altering the tables and queries and none of them were related to upgrade...

For now I only found one solution: comment out everything related to character set I've set before, then upgrade. Confusing...

Offline

#3 2019-03-17 23:59:14

loqs
Member
Registered: 2014-03-06
Posts: 17,195

Re: MariaDB - Installation of system tables failed!

The default charset and encoding should be utfmb4/utf8mb4_unicode_ci https://git.archlinux.org/svntogit/pack … 5d448b#n96

Last edited by loqs (2019-03-17 23:59:42)

Offline

Board footer

Powered by FluxBB