ubuntu 22.04 + zabbix 6.4 + mariadb 11.3.2 出現 Server sent charset (0) unknown to the client. Please, report to the developers

 http://localhost/setup.php 出現下面的錯誤訊息

Server sent charset (0) unknown to the client. Please, report to the developers

 編輯 /etc/mysql/mariadb.conf.d/50-server.cnf

刪除下面這行重啟即可

character-set-collat​​ions = utf8mb4=uca1400_ai_ci

Reference: https://stackoverflow.com/questions/78036671/mariadb-11-3-2-php-server-sent-charset-0-unknown-to-the-client-please-rep

Ubuntu 安裝 Mariadb 11.1 出現 Skipping acquire of configured file 'main/binary-i386/Packages' as repository 'https://tw1.mirror.blendbyte.net/mariadb/repo/11.1/ubuntu focal InRelease' doesn't support architecture 'i386

按照下面網頁的方式安裝

https://mariadb.org/download/?t=repo-config&d=20.04+%22focal%22&v=11.1&r_m=blendbyte

結果在執行 apt update 的時候跳出錯誤訊息

Skipping acquire of configured file 'main/binary-i386/Packages' as repository 'https://tw1.mirror.blendbyte.net/mariadb/repo/11.1/ubuntu focal InRelease' doesn't support architecture 'i386

依照下面網頁的寫法去改寫

https://askubuntu.com/questions/741410/skipping-acquire-of-configured-file-main-binary-i386-packages-as-repository-x

/etc/apt/sources.list.d/mariadb.list

# MariaDB 11.1 repository list - created 2024-01-03 05:46 UTC
# https://mariadb.org/download/
# deb.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# deb [signed-by=/etc/apt/keyrings/mariadb-keyring.pgp] https://deb.mariadb.org/11.1/ubuntu focal main
deb [arch=amd64 signed-by=/etc/apt/keyrings/mariadb-keyring.pgp] https://tw1.mirror.blendbyte.net/mariadb/repo/11.1/ubuntu focal main
# deb-src [signed-by=/etc/apt/keyrings/mariadb-keyring.pgp] https://tw1.mirror.blendbyte.net/mariadb/repo/11.1/ubuntu focal main

mariadb 10.0 使用 grant 指令時出現 Access denied for user 'root'@'localhost' (using password: YES)

工作環境中有 mysql 和 mariadb 10 混用

今天用下面這條命令要新增使用者、mysql 沒有問題、mariadb 卻一直出現錯誤
mysql> grant ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.%' IDENTIFIED BY 'password';

原本以為和之前文章 MariaDB 使用 root 登入後、新增使用者出現 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 錯誤訊息 一樣

但是照著做卻發現一切正常

後來認真 google 以後才發現這篇 解決mariadb grant ERROR 1045 (28000): Access denied for user

原來是語法不一樣，後來使用下面這個語法就沒問題了、也就是 "ALL PRIVILEGES" 和 "*.*" 不能一起用

mysql> grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER on *.* to 'user'@'192.168.1.%' IDENTIFIED BY 'password';

MariaDB 使用 root 登入後、新增使用者出現 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 錯誤訊息

使用 cli 模式或是 phpmyadmin 新增使用者、都出現 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 錯誤訊息

MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO 'tt123'@'192.168.0.%' IDENTIFIED BY PASSWORD '*36F6ABC284EDCE8CBB4D071795AF023333785872';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

一開始照下面文章的方式處理、但是都無效
解決 MYSQL 登入時，ERROR 1045 (28000) using password: NO 的方法
myqsql 登入問題- iT 邦幫忙::一起幫忙解決難題，拯救IT 人的一天 - iThome

後來發現log裡面出現錯誤訊息，跑了分析以後出現
[root@tt0001 ~]# mysqlcheck -a -p mysql
Enter password:
mysql.column_stats Table is already up to date
mysql.columns_priv Table is already up to date
mysql.db Table is already up to date
mysql.event Table is already up to date
mysql.func Table is already up to date
mysql.gtid_slave_pos OK
mysql.help_category Table is already up to date
mysql.help_keyword Table is already up to date
mysql.help_relation Table is already up to date
mysql.help_topic Table is already up to date
mysql.host Table is already up to date
mysql.index_stats Table is already up to date
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
mysql.ndb_binlog_index Table is already up to date
mysql.plugin Table is already up to date
mysql.proc Table is already up to date
mysql.procs_priv Table is already up to date
mysql.proxies_priv Table is already up to date
mysql.roles_mapping Table is already up to date
mysql.servers Table is already up to date
mysql.table_stats Table is already up to date
mysql.tables_priv Table is already up to date
mysql.time_zone Table is already up to date
mysql.time_zone_leap_second Table is already up to date
mysql.time_zone_name Table is already up to date
mysql.time_zone_transition Table is already up to date
mysql.time_zone_transition_type Table is already up to date
mysql.user Table is already up to date

但是去 ls /var/lib/mysql/mysql 又有出現檔案
最後按照這篇操作 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
先 drop 後再手動把檔案移開，最後跑 mysql_upgrade 就好了

[root@tt0001 tools]# mysql_upgrade -p
Enter password:
MySQL upgrade detected
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.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
mysql.ndb_binlog_index 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.user OK

Repairing tables
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
Upgrading from a version before MariaDB-10.1
Phase 2/7: Installing used storage engines
Checking for tables with unknown storage engine
Phase 3/7: Fixing views from mysql
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1932 (42S02) at line 586: Table 'mysql.innodb_index_stats' doesn't exist in engine
ERROR 1932 (42S02) at line 590: Table 'mysql.innodb_table_stats' doesn't exist in engine
ERROR 1932 (42S02) at line 593: Table 'mysql.innodb_table_stats' doesn't exist in engine
FATAL ERROR: Upgrade failed
[root@tw01 tools]# mysql_upgrade -p
Enter password:
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.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.ndb_binlog_index 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.user OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1813 (HY000) at line 64: Tablespace for table '`mysql`.`innodb_table_stats`' exists. Please DISCARD the tablespace before IMPORT.
ERROR 1813 (HY000) at line 68: Tablespace for table '`mysql`.`innodb_index_stats`' exists. Please DISCARD the tablespace before IMPORT.
ERROR 1146 (42S02) at line 586: Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1146 (42S02) at line 590: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02) at line 593: Table 'mysql.innodb_table_stats' doesn't exist
FATAL ERROR: Upgrade failed
[root@tw01 tools]#
[root@tw01 tools]# mysql_upgrade -p
Enter password:
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.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.ndb_binlog_index 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.user OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK