- Neueste
- Die meisten Stimmen
- Die meisten Kommentare
resolved after connect with Support.
general baseline:
1. check old date-format tables (and alter it). Docs: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html#USER_UpgradeDBInstance.MySQL.DateTime57
use query:
SELECT DISTINCT CONCAT('ALTER TABLE ', REPLACE(is_tables.TABLE_SCHEMA, '
', ''), '`.`', REPLACE(is_tables.TABLE_NAME, '`', '
'), '` FORCE;')
FROM information_schema.TABLES is_tables
INNER JOIN information_schema.COLUMNS col ON col.TABLE_SCHEMA = is_tables.TABLE_SCHEMA
AND col.TABLE_NAME = is_tables.TABLE_NAME
LEFT OUTER JOIN information_schema.INNODB_SYS_TABLES systables ON
SUBSTRING_INDEX(systables.NAME, '#', 1) = CONCAT(is_tables.TABLE_SCHEMA,'/',is_tables.TABLE_NAME)
LEFT OUTER JOIN information_schema.INNODB_SYS_COLUMNS syscolumns ON
syscolumns.TABLE_ID = systables.TABLE_ID AND syscolumns.NAME = col.COLUMN_NAME
WHERE col.COLUMN_TYPE IN ('time','timestamp','datetime')
AND is_tables.TABLE_TYPE = 'BASE TABLE'
AND is_tables.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema')
AND (is_tables.ENGINE = 'InnoDB' AND syscolumns.MTYPE = 6);
2. run mysqlcheck. Docs: https://dev.mysql.com/doc/refman/8.0/en/mysqlcheck.html#option_mysqlcheck_check
mysqlcheck --host=<scheme>.<region>.rds.amazonaws.com --user=<user> --password=<password> --databases <database_name> --check-upgrade
3. check orphaned tables, which can exists after some failed ALTER-statements in the past. Docs: https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
and remove them using template:
DROP TABLE #mysql50#<table name from previous output>
;
for example:
DROP TABLE #mysql50##sql-993_15a14a
;
after this steps upgrade was made successfully
This doesn't work as described here: https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/ as there is a situation where a file is missing and the DROP TABLE just errors out
The suggested fix would be something like that:
cp t1.frm "#sql-f3db_2.frm"
and then do the DROP TABLE which I obviously cannot do on the managed RDS MySQL instance. What would be the suggested solution to that one then?
mysql> select * from information_schema.innodb_sys_tables where name like '%#sql%';
+----------+----------------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+----------------------------+------+--------+-------+-------------+------------+---------------+------------+
| 315 | some_db/#sql2-11d5-12 | 33 | 15 | 291 | Barracuda | Dynamic | 0 | Single |
+----------+----------------------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.00 sec)
mysql> use some_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> DROP TABLE `#mysql50##sql2-11d5-12`;
ERROR 1051 (42S02): Unknown table 'some_db.#mysql50##sql2-11d5-12'
Relevanter Inhalt
- AWS OFFICIALAktualisiert vor 10 Monaten
- AWS OFFICIALAktualisiert vor 3 Jahren
- AWS OFFICIALAktualisiert vor 2 Jahren
- AWS OFFICIALAktualisiert vor 3 Jahren