18

I recently made a rather stupid mistake and corrupt my Ubuntu installation. To resolve it, I booted with a live cd and copied the database files over. However, now I have copied the folders from the old installation into the new one, whenever I type "use database_name" it just freezes on this:

mysql> use my_database_name;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

And then freezes without giving the terminal.

29

On the first "use" command after login, MySQL scans database, tables and columns name for auto completion. If you have many db, tables it could take a while.

To avoid that, launch your client with the -A option (or --no-auto-rehash)

mysql -uroot -p -A

You could also add the disable_auto_rehash variable in your my.cnf (in the [mysql] section) if you want to disable it completely. This change does not require a reboot (it is a client, not server, variable).

  • can "a while" be more than a day for a ~2.5GiB database, or is that unreasonably long? – lucidbrot Jul 31 '19 at 6:55
2

In my case, the "Reading table information" was taking an unreasonable amount of time. When I ran mysql -e 'show processlist' I discovered that the process that was "hanging" was "Waiting for table metadata lock". This made some sort of sense as I had a long-running query of the form create table from select ... running elsewhere, so on one level I could understand that until that new table was finished being created, the metadata about tables would not be available. (I guess it would be preferable if auto-complete scan just omitted any "incomplete" tables from the meta data query if this were possible.)

0

📌

When you copy entire database from other system:

/var/lib/mysql/*

You may chown -R mysql:mysql /var/lib/mysql/* and start service again.

☝ this solved my problem 👊

  • 2
    Welcome to DBA Stack Exchange! You've formatted this as a quote; if you really got this solution from elsewhere, you must provide the source. If not, please remove the formatting. – Glorfindel Jan 14 '19 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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