2.11.5 Preparing Your Installation for Upgrade

Inhoudsopgave

Before upgrading to the latest MySQL 8.0 release, ensure the upgrade readiness of your current MySQL 5.7 or MySQL 8.0 server instance by performing the preliminary checks described below. The upgrade process may fail otherwise.

The same checks and others can be performed using the MySQL Shell upgrade checker utility. For more information, see Upgrade Checker Utility.

Preliminary checks:

The following issues must not be present:
- There must be no tables that use obsolete data types or functions.
  In-place upgrade to MySQL 8.0 is not supported if tables contain old temporal columns in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision). If your tables still use the old temporal column format, upgrade them before attempting an in-place upgrade to MySQL 8.0. For more information, see Server Changes.
- There must be no orphan .frm files.
- Triggers must not have a missing or empty definer or an invalid creation context (indicated by the character_set_client, collation_connection, Database Collation attributes displayed by SHOW TRIGGERS or the INFORMATION_SCHEMA TRIGGERS table).
To check for these issues, execute this command:
```
mysqlcheck -u root -p --all-databases --check-upgrade
```
If mysqlcheck reports any errors, correct the issues.
There must be no partitioned tables that use a storage engine that does not have native partitioning support. To identify such tables, execute this query:
1. SELECT TABLE_SCHEMA, TABLE_NAME
2. FROM INFORMATION_SCHEMA.TABLES
3. WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
4. AND CREATE_OPTIONS LIKE '%partitioned%';
Any table reported by the query must be altered to use InnoDB or be made nonpartitioned. To change a table storage engine to InnoDB, execute this statement:
1. ALTER TABLE table_name ENGINE = INNODB;
For information about converting MyISAM tables to InnoDB, see Section 15.6.1.3, “Converting Tables from MyISAM to InnoDB”.
To make a partitioned table nonpartitioned, execute this statement:
1. ALTER TABLE table_name REMOVE PARTITIONING;
Some keywords may be reserved in MySQL 8.0 that were not reserved previously. See Section 9.3, “Keywords and Reserved Words”. This can cause words previously used as identifiers to become illegal. To fix affected statements, use identifier quoting. See Section 9.2, “Schema Object Names”.
There must be no tables in the MySQL 5.7 mysql system database that have the same name as a table used by the MySQL 8.0 data dictionary. To identify tables with those names, execute this query:
1. SELECT TABLE_SCHEMA, TABLE_NAME
2. FROM INFORMATION_SCHEMA.TABLES
3. WHERE LOWER(TABLE_SCHEMA) = 'mysql'
4. and LOWER(TABLE_NAME) IN
5. (
6. 'catalogs',
7. 'character_sets',
8. 'check_constraints',
9. 'collations',
10. 'column_statistics',
11. 'column_type_elements',
12. 'columns',
13. 'dd_properties',
14. 'events',
15. 'foreign_key_column_usage',
16. 'foreign_keys',
17. 'index_column_usage',
18. 'index_partitions',
19. 'index_stats',
20. 'indexes',
21. 'parameter_type_elements',
22. 'parameters',
23. 'resource_groups',
24. 'routines',
25. 'schemata',
26. 'st_spatial_reference_systems',
27. 'table_partition_values',
28. 'table_partitions',
29. 'table_stats',
30. 'tables',
31. 'tablespace_files',
32. 'tablespaces',
33. 'triggers',
34. 'view_routine_usage',
35. 'view_table_usage'
36. );
Any tables reported by the query must be renamed (use RENAME TABLE). This may also entail changes to applications that use the affected tables.
There must be no tables that have foreign key constraint names longer than 64 characters. To identify tables with too-long constraint names, execute this query:
1. SELECT TABLE_SCHEMA, TABLE_NAME
2. FROM INFORMATION_SCHEMA.TABLES
3. WHERE TABLE_NAME IN
4. (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
5. INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
6. FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
7. WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);
Any tables reported by the query must be altered to have constraint names no longer than 64 characters (use ALTER TABLE).
The must be no obsolete SQL modes defined in your sql_mode system variable setting. Attempting to use an obsolete SQL mode will cause a startup failure on MySQL 8.0. Applications that use obsolete SQL modes should also be revised to avoid them. For information about SQL modes removed in MySQL 8.0, see Server Changes.
There must be no tables or stored procedures with individual ENUM or SET column elements that exceed 255 characters or 1020 bytes in length. Prior to MySQL 8.0, the maximum combined length of ENUM or SET column elements was 64K. In MySQL 8.0, the maximum character length of an individual ENUM or SET column element is 255 characters, and the maximum byte length is 1020 bytes. (The 1020 byte limit supports multitibyte character sets). Before upgrading to MySQL 8.0, modify any ENUM or SET column elements that exceed the new limits. Failing to do so causes the upgrade to fail with an error.
Before upgrading to MySQL 8.0.13 or higher, there must be no table partitions that reside in shared InnoDB tablespaces, which include the system tablespace and general tablespaces. Identify table partitions in shared tablespaces by querying INFORMATION_SCHEMA:
If upgrading from MySQL 5.7, run this query:
1. SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
2. WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';
If upgrading from an earlier MySQL 8.0 release, run this query:
1. SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLES
2. WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';
Move table partitions from shared tablespaces to file-per-table tablespaces using ALTER TABLE ... REORGANIZE PARTITION:
1. ALTER TABLE table_name REORGANIZE PARTITION partition_name
2. INTO (partition_definition TABLESPACE=innodb_file_per_table);
There must be no queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses. Otherwise, upgrading to MySQL 8.0.13 or higher may fail, as may replicating to MySQL 8.0.13 or higher slave servers. For additional details, see SQL Changes.
Your MySQL 5.7 installation must not use features that are not supported by MySQL 8.0. Any changes here are necessarily installation specific, but the following example illustrates the kind of thing to look for:
Some server startup options and system variables have been removed in MySQL 8.0. See Features Removed in MySQL 8.0, and Section 1.5, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0”. If you use any of these, an upgrade requires configuration changes.
Example: Because the data dictionary provides information about database objects, the server no longer checks directory names in the data directory to find databases. Consequently, the --ignore-db-dir option is extraneous and has been removed. To handle this, remove any instances of --ignore-db-dir from your startup configuration. In addition, remove or move the named data directory subdirectories before upgrading to MySQL 8.0. (Alternatively, let the 8.0 server add those directories to the data dictionary as databases, then remove each of those databases using DROP DATABASE.)

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-upgrade-prerequisites.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut