[Maria-discuss] Loading a backup created from 10.6.4 into newly installed 10.6.4 causes mysql.proc to appear corrupt
Failing process: Install mariadb from ppa using apt (happens through salt, fwiw) Download last night's backup from a 10.6.4 server, which is a replica from a galera cluster. run the script attached restore_mariabackup.sh
Once this is done the database itself works, for our application, but any client trying to access anything that requires mysql.proc gets an error similar to:
*Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: error : CorruptSep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: sys.x$schema_table_statistics*
*Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: Error : Cannot load from mysql.proc. The table is probably corrupted *
I just built two new servers, one that builds mariadb and then overwrites the data from a backup and one that just installs mariadb. The two different files base_install.proc.sql and backupedup.proc.sql. The error output from sudo mysql_upgrade on the server that received the backup is in mysql_upgrade.out
There are more differences in the proc table if i dump the data as well, but i think much of it is driven by the utf8_mb4 rather than 3.
Tried to keep this sucient and use attachments. This process has worked for me for until i upgraded to 10.6.4. Please ask anything you need. I think i've been using this similar process to build dev machines since about 10.4
Thanks Jeff
Sorry a diff between the two schema's, to make this a bit simpler, since I tend to think this is the fault, but i'm not sure why it was not upgraded and why it doesn't matter on 10.6.4 (i can think/guess, but i'm not a maria dev)
*diff base_install.proc.sql backedup.proc.sql5c5< -- Server version 10.6.4-MariaDB-1:10.6.4+maria~focal---> -- Server version 10.6.4-MariaDB-1:10.6.4+maria~focal-log26c26< `db` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '',---> `db` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',37c37< `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '',---> `definer` varchar(384) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',41,44c41,44< `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,< `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,< `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,< `db_collation` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,---> `comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,> `character_set_client` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,> `collation_connection` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,> `db_collation` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,48c48< ) ENGINE=Aria DEFAULT CHARSET=utf8mb3 PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Stored Procedures';---> ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Stored Procedures';60c60< -- Dump completed on 2021-09-30 21:10:49---> -- Dump completed on 2021-09-30 21:12:29*
On Thu, Sep 30, 2021 at 5:55 PM Jeff Dyke jeff.dyke@gmail.com wrote:
Failing process: Install mariadb from ppa using apt (happens through salt, fwiw) Download last night's backup from a 10.6.4 server, which is a replica from a galera cluster. run the script attached restore_mariabackup.sh
Once this is done the database itself works, for our application, but any client trying to access anything that requires mysql.proc gets an error similar to:
*Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: error : CorruptSep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: sys.x$schema_table_statistics*
*Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: Error : Cannot load from mysql.proc. The table is probably corrupted *
I just built two new servers, one that builds mariadb and then overwrites the data from a backup and one that just installs mariadb. The two different files base_install.proc.sql and backupedup.proc.sql. The error output from sudo mysql_upgrade on the server that received the backup is in mysql_upgrade.out
There are more differences in the proc table if i dump the data as well, but i think much of it is driven by the utf8_mb4 rather than 3.
Tried to keep this sucient and use attachments. This process has worked for me for until i upgraded to 10.6.4. Please ask anything you need. I think i've been using this similar process to build dev machines since about 10.4
Thanks Jeff
sorry, that diff was horrible online, hopefully it shows up better in email clients.
On Thu, Sep 30, 2021 at 6:01 PM Jeff Dyke jeff.dyke@gmail.com wrote:
Sorry a diff between the two schema's, to make this a bit simpler, since I tend to think this is the fault, but i'm not sure why it was not upgraded and why it doesn't matter on 10.6.4 (i can think/guess, but i'm not a maria dev)
*diff base_install.proc.sql backedup.proc.sql5c5< -- Server version 10.6.4-MariaDB-1:10.6.4+maria~focal---> -- Server version 10.6.4-MariaDB-1:10.6.4+maria~focal-log26c26< `db` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '',---> `db` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',37c37< `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '',---> `definer` varchar(384) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',41,44c41,44< `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,< `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,< `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,< `db_collation` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,---> `comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,> `character_set_client` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,> `collation_connection` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,> `db_collation` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,48c48< ) ENGINE=Aria DEFAULT CHARSET=utf8mb3 PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Stored Procedures';---> ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Stored Procedures';60c60< -- Dump completed on 2021-09-30 21:10:49---> -- Dump completed on 2021-09-30 21:12:29*
On Thu, Sep 30, 2021 at 5:55 PM Jeff Dyke jeff.dyke@gmail.com wrote:
Failing process: Install mariadb from ppa using apt (happens through salt, fwiw) Download last night's backup from a 10.6.4 server, which is a replica from a galera cluster. run the script attached restore_mariabackup.sh
Once this is done the database itself works, for our application, but any client trying to access anything that requires mysql.proc gets an error similar to:
*Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: error : CorruptSep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: sys.x$schema_table_statistics*
*Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: Error : Cannot load from mysql.proc. The table is probably corrupted *
I just built two new servers, one that builds mariadb and then overwrites the data from a backup and one that just installs mariadb. The two different files base_install.proc.sql and backupedup.proc.sql. The error output from sudo mysql_upgrade on the server that received the backup is in mysql_upgrade.out
There are more differences in the proc table if i dump the data as well, but i think much of it is driven by the utf8_mb4 rather than 3.
Tried to keep this sucient and use attachments. This process has worked for me for until i upgraded to 10.6.4. Please ask anything you need. I think i've been using this similar process to build dev machines since about 10.4
Thanks Jeff
participants (1)
-
Jeff Dyke