Diferencias

Muestra las diferencias entre dos versiones de la página.

--- linux:howtos:mariadb-104-mariabackup-partial-backup [10/07/2020 17:38] – - Imported by DokuWiki Advanced Plugin Juanga Covas
+++ linux:howtos:mariadb-104-mariabackup-partial-backup [09/09/2021 06:07] (actual) – Juanga Covas
@@ Línea 13: / Línea 13: @@
   ALTER TABLE dbname.dbtable FORCE;
 Then you can grab again a backup (or manually transport the InnoDB table), ''%%--prepare%%'' and ''%%--export%%'' again, and IMPORT TABLESPACE should work now. Note that this seems **not being replicated** to any slaves.
+A simple way to rebuild the metadata for all tables (so we fix them), note that it's simple but not quick, depending on your table data:
+Getting a list of InnoDB tables on a given "dbname", with the commands to "FORCE" (fix them)
+  mysql --batch --silent --skip-column-names -e \
+  "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' LOCK=EXCLUSIVE, FORCE;') FROM information_schema.tables \
+  WHERE table_schema = 'dbame' AND engine = 'InnoDB'"
+Passing the command list to mysql:
+  mysql --batch --silent --skip-column-names -e \
+  "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' LOCK=EXCLUSIVE, FORCE;') FROM information_schema.tables \
+  WHERE table_schema = 'dbame' AND engine = 'InnoDB'" | mysql
 Problem is that future ALTERs (while the bug is not solved), will again render the schema bugged for the "IMPORT TABLESPACE", so you can:
   * Enforce the use of '', ALGORITHM=copy'' on each dangerous ALTER statement.
   * or ''SET SESSION alter_algorithm=copy'' (see [[https://mariadb.com/kb/en/library/server-system-variables/#alter_algorithm|alter_algorithm]] system variable) before an ALTER that could cause this "schema mismatch" issue with MariaDB 10.4
-  * or just setting ''alter_algorithm=copy'' on a [Mariadb-10.4] section of your ''server.cnf'' configuration (check that it survives a mariadb restart using ''SELECT @@alter_algorithm;''
+  * or just setting ''alter_algorithm=copy'' on your ''server.cnf'' configuration (check that it survives a mariadb restart using ''SELECT @@alter_algorithm;''
+  * <wrap tip>2020 - Also you can use</wrap> ''innodb_instant_alter_column_allowed=add_last'' in server config. as seen [[https://jira.mariadb.org/browse/MDEV-20590?focusedCommentId=148275&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-148275|here]] since an INSTANT ''add_last'' column works, the problematic ALTER changes are column ''drop'' and ''reorder'' (reorder being applied when adding a column which will not be the last). This new server variable is only valid starting with MariaDB 10.4.14, 10.5.3, and later versions
+The COPY algorithm is going to slow things down when doing ALTER on big tables, but you know...
 Hope that helps.
 ~~DISCUSSION|Comentarios~~

JuangaCovas.info

Herramientas de usuario

Herramientas del sitio

Diferencias

Herramientas de la página