Ambos lados, revisión anteriorRevisión previa | |
linux:howtos:mariadb-104-mariabackup-partial-backup [16/03/2021 04:17] – Juanga Covas | linux:howtos:mariadb-104-mariabackup-partial-backup [09/09/2021 06:07] (actual) – Juanga Covas |
---|
| |
Getting a list of InnoDB tables on a given "dbname", with the commands to "FORCE" (fix them) | 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'" | 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: | 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 | 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: | 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. | * 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 ''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... | The COPY algorithm is going to slow things down when doing ALTER on big tables, but you know... |