Diferencias

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

--- linux:howtos:mariadb-104-mariabackup-partial-backup [13/02/2021 02:25] – 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...

JuangaCovas.info

Herramientas de usuario

Herramientas del sitio

Diferencias

Herramientas de la página