MySQL database freezing after 'use'

Question

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.

score 36 · Accepted Answer · edited Sep 09 '15 at 12:08

36

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).

edited Sep 09 '15 at 12:08

Paul White

83,961
28
402
634

answered Mar 30 '14 at 20:34

Maxime Fouilleul

3,525
22
21

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

score 3 · Answer 2 · answered Sep 26 '18 at 05:02

3

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.)

answered Sep 26 '18 at 05:02

wwkudu

131
3

I faced a similar issue.. what does Waiting for table metadata lock means? – Mr. Hobo Jul 28 '21 at 08:04
1

I'm not a MySQL internal guru, but looks like a reasonable explanation here. Basically it wants to alter meta data about a table, i.e. catalog information (not content) but that meta data is locked because of some other process. – wwkudu Jul 29 '21 at 11:40

score 1 · Answer 3 · answered Jan 14 '19 at 00:46

1

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

answered Jan 14 '19 at 00:46

Rafael Lemos

111
1

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 08:31

MySQL database freezing after 'use'

3 Answers3