0

I've upgraded from Mysql 5.7 to 8 on Ubuntu 18.04

Made it by guide, very accurate, on a new empty server, checked all error/warning logs and fixed it. Everything works just fine with it.

I have 2 same Innodb tables, 1 was created and filled before updating and has right numbers of Rows / Size when querying:

SHOW TABLE STATUS FROM dbname;

Another table shows me 16Kb size and no rows, But its now ~8GB and 4.5 millions rows there. I tried to make Analyze Tablename / Update in Client (Heidisql).

I can't find anything in Google about problem like this. I choose table manually and check rows count, but when I'm using mysql visual clients like Phpmyadmin / HeidiSql it shows me no data in this table.

4
  • What does I've updated Mysql 5.7 > 8 mean? Have you migratred from 5.7 to 8? In this case, something like 5.7 --> 8 might be clearer? If not, please edit your question to make this clear. p.s. welcome to the forum! :-)
    – Vérace
    Apr 28, 2019 at 15:21
  • How do SELECTs go with your new table? Are they returning the correct results?
    – Vérace
    Apr 28, 2019 at 15:22
  • I made clean 5.7 installation , created DB and inserted data here, than Updated 5.7 to 8. Yes, they returning correct results.
    – wtfowned
    Apr 28, 2019 at 15:47
  • If your tables/queries are returning the correct data, I wouldn't worry too much. The MVCC architecture means that it's not always easy to know (or even define) a record count for a given table. I'm not sure where the information in SHOW TABLE STATUS FROM dbname; but my guess would be that it's not continually updated. You should always use the mysql CLI client when asking questions here-if that shows a correct rowcount and or query results, then you have no problem. The issue could be with the HeidiSQL client?
    – Vérace
    Apr 28, 2019 at 15:54

4 Answers 4

1

Check your settings for information_schema_stats_expiry. The default cache for schema stats is 86400s (1 day) so it's likely that you are just seeing cached information here. It's a session variable though, so you can just set it to zero for any situation where you want up-to-date information.

1

I've got suggestion to make Check and after it Analyze, and it worked for me! Now size and other table service information updated. I will try to find why its not updated automatically and post here.

1

I had the same issue when updating MariaDB from version 10.2 to 10.4 (analogical to MySQL 5.7 to 8). For me the rows and size of table shown incorrect, when inspect database in HeidiSQL, also the results were returning actual. But the most important was that the indexes were not used during SELECT queries and we faced serious performance issues.

To resolve the issue, we run ANALYZE TABLE table_name, which performs a key distribution analysis and stores the distribution for the table or tables.

0

For the size of your database, it would be easier to dump your database into a sql file with mysqldump and let the mysql client do the upload.

First go to your MySQL 5.7 instalation and execute the following command:

[root@host ~]# mysqldump -u user -p selected_database > selected_database.sql

Where it will be prompted a request for your password for the selected user, dumping the selected_database.

After that change to your MySQL 8 instalation, create the database (in your client write CREATE DATABASE selected_database) and then in terminal execute:

[root@host ~]# mysql -u user -p selected_database < selected_database.sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.