Tried running:
> mysql -e "REPAIR TABLE mysql.proc;"
|
+------------+--------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+------------+--------+----------+----------+
|
| mysql.proc | repair | status | OK |
|
+------------+--------+----------+----------+
|
And then "mysql_upgrade" again.
Failed with the same errors, and additionally with:
sys.x$innodb_buffer_stats_by_schema
|
Error : 'information_schema.ibp.TABLE_NAME' isn't in GROUP BY
|
error : Corrupt
|
sys.x$innodb_buffer_stats_by_table
|
Error : 'information_schema.ibp.TABLE_NAME' isn't in GROUP BY
|
error : Corrupt
|
Tried this from scratch, but getting the same results.
I've also compared a "SHOW CREATE TABLE proc" between a new installation and the upgraded installation, and both of them look like the same.
There are indeed changes between 10.5 and 10.8 on that table, but after "mysql_upgrade", they look the same.
So I don't really know why is it saying "table is probably corrupted".
Opening the table with phpMyAdmin also doesn't show any signs of being corrupted.
If I try to create a procedure, like:
DELIMITER //
|
|
|
CREATE PROCEDURE simpleproc (OUT param1 INT) |
BEGIN |
SELECT COUNT(*) FROM db.table; |
END; |
//
|
DELIMITER ;
I do get this error:
#1728 - Cannot load from mysql.proc. The table is probably corrupted
Works well on the 10.5 installation.
I've just tried upgrading from 10.5 to 10.6 (instead of jumping right to 10.8), and the exact same thing is happening with mysql_upgrade.
Alright - I've just realized that the log file contains helpful information!
The log is long/repetitive, but here's a condensed excerpt of the problems:
2022-06-21 10:55:43 10 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
|
2022-06-21 10:55:43 10 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
|
2022-06-21 10:55:43 10 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'name' at position 1 to have character set 'utf8mb3' but found character set 'utf8mb4'.
|
2022-06-21 10:55:43 10 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'definer' at position 3 to have character set 'utf8mb3' but found character set 'utf8mb4'.
|
2022-06-21 10:55:43 10 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'character_set_client' at position 18 to have character set 'utf8mb3' but found character set 'utf8mb4'.
|
2022-06-21 10:55:43 10 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'collation_connection' at position 19 to have character set 'utf8mb3' but found character set 'utf8mb4'.
|
2022-06-21 10:55:43 10 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'db_collation' at position 20 to have character set 'utf8mb3' but found character set 'utf8mb4'.
|
2022-06-21 10:55:43 10 [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
|
2022-06-21 10:55:43 10 [ERROR] Incorrect definition of table mysql.proc: expected the type of column 'db' at position 0 to have character set 'utf8mb3' but found character set 'utf8mb4'.
|
This looks like it's mostly because of this setting I added:
old_mode = ""
|
(default is UTF8_IS_UTF8MB3)
I commented it out, and now everything went fine with mysql_upgrade.
There is still this issue though:
2022-06-21 11:31:16 10 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
|
2022-06-21 11:31:16 10 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
|
But when I look at the table, it looks fine, so maybe this error was triggered before mysql_upgrade fixed it.
I encountered the same problem while running `mariadb-upgrade` with `old_mode=""` in my server configurations files.
I removed `old_mode=""` and ran `mariadb-upgrade` without any error.
I restarted MariaDB with `old_mode=""` and all seem to work correctly.
Root cause:
The failure is caused by the default UTF8 character set selection controlled by `old_mode` variable.
- MariaDB supports a number of character sets including utf8, utf8mb3, and utf8mb4
https://mariadb.com/kb/en/unicode/
From MariaDB 10.6, utf8 is an alias for utf8mb3, but this can changed to ut8mb4 by changing the default value of the old_mode system variable.
- MariaDB will verify the table column definition before reading the mysql.proc and mysql.event tables.
Function open_proc_table_for_read checks the table definition before reading it.
The expected character of the table and columns is hard-coded as utf8mb3.
https://github.com/MariaDB/server/blob/a49e394399859b23ba609d0058b498cfa876cac4/sql/sp.cc#L486,L498
https://github.com/MariaDB/server/blob/a49e394399859b23ba609d0058b498cfa876cac4/sql/sp.cc#L125,L140
- During the upgrade script, SQL commands like following will be executed to convert the character set of mysql tables (proc, event, etc) to utf8:
ALTER TABLE proc CONVERT TO CHARACTER SET utf8
However the real character set it used (utf8mb3 or utf8mb4) depends on the configuration of old_mode.- If the server is running with default value of old_mode=UTF8_IS_UTF8MB3:
ALTER TABLE proc CONVERT TO CHARACTER SET utf8
will be equal to
ALTER TABLE proc CONVERT TO CHARACTER SET utf8mb3.
Then during the open_proc_table_for_read the table's character set is utf8mb3 which is expected and pass. - If the server is running with customized value for example old_mode=NO_DUP_KEY_WARNINGS_WITH_IGNORE:
ALTER TABLE proc CONVERT TO CHARACTER SET utf8
will be equal to
ALTER TABLE proc CONVERT TO CHARACTER SET utf8mb4.
Then open_proc_table_for_read will fail because the expected character set doesn't match.
- If the server is running with default value of old_mode=UTF8_IS_UTF8MB3:
Reproduce:
The easiest way to reproduce is:
- Start a 10.6+ instance with old_mode=NO_DUP_KEY_WARNINGS_WITH_IGNORE in my.cnf.
- Run:
mysql_upgrade -uxxx -pxxxx -f
Fix:
Though we could update the upgrade scripts to force using utf8mb3, we probably will be migrating to utf8mb4 someday.
Maybe the function of table/column definition check for proc/event tables could be smarter to support all utf8/utf8mb3/utf8mb4?
haidong Will help on working on a fix for this. 
Patch: https://github.com/MariaDB/server/commit/a9080af3b819c28a62a6f237d8b14d60b3f205b2
Hi rucha174,
I think this part of the patch is not correct:
diff --git a/sql/table.cc b/sql/table.cc
|
index 163bb798962f9..2eb2e4b79af81 100644
|
--- a/sql/table.cc
|
+++ b/sql/table.cc
|
@@ -5316,7 +5316,8 @@ Table_check_intact::check(TABLE *table, const TABLE_FIELD_DEF *table_def) |
error= TRUE;
|
}
|
else if (field_def->cset.str && |
- strcmp(field->charset()->cs_name.str, field_def->cset.str)) |
+ strncmp(field->charset()->cs_name.str, field_def->cset.str, |
+ field->charset()->cs_name.length-1))
|
{
|
report_error(0, "Incorrect definition of table %s.%s: " |
"expected the type of column '%s' at position %d " |
Shouldn't the last parameter to strncmp() be field_def.cset.length ? So it is 6 because length of "utf8mb" is 6. Although field->charset()->cs_name.length-1 also evaluates to 6 for "utf8mb3" and "utf8mb4". But this is only a coincidence. This expression seems meaningless for me.
Also I think the new test needs --source include/mysql_upgrade_preparation.inc in the beginning.
Otherwise from a glance it will fail with embedded server or without performance_schema compiled. See inside mysql_upgrade_preparation.inc for details.
Hi rucha174,
the patch https://github.com/MariaDB/server/commit/a29b1e5761a3d18c8167e3aeca4a9fde575ee351 is OK to push. Thanks!
Hi!
Could you consider posting https://github.com/MariaDB/server/commit/a29b1e5761a3d18c8167e3aeca4a9fde575ee351 as a pull request on https://github.com/MariaDB/server/pulls so that the anybody can contribute with review?
Also if you have a branch instead of a single commit id, you can amend the and force push on Github new versions of the branch so that all reviewers are commenting the latest version and not just one single permanent commit?
Publishing this as a PR would also have the benefit that anybody can validate that the CI passed.
FYI also TheLinuxJedi
Hi otto,
Whilst I agree that I would love for this to happen, this would probably need to be discussed as part of a longer conversation of around process changes.
serg : please review:
https://github.com/MariaDB/server/commit/b7daf593f41d70db5ea026493cb30806b1ace775
b7daf593f41d70db5ea026493cb30806b1ace775 is ok to push