Create index causes 'Lost connection to MySQL'

Question

I have a table which is defined like this. Where ProviderId is the primary key, and StateID is the foreign key, that comes from another table. It is running in my laptop, therefore, it is a local database. In my table I have around 9 million entries. I ran the following command to create a new index for the table:

CREATE INDEX name_addr ON providers (ProviderName, Address, StateID, Zip);

It ran around 10 minutes, and I got this error message:

Error Code: 2013. Lost connection to MySQL server during query - 600.547 sec

I checked my log, and the result was this:

150226 13:07:31    12 Query CREATE INDEX name_addr ON providers (ProviderName, Address, StateID, Zip)
150226 13:17:32    13 Connect   root@localhost on 
           13 Query set autocommit=1
           13 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
           13 Query SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
           13 Query SELECT current_user()
           13 Query SET CHARACTER SET utf8
           13 Query SET NAMES utf8
           13 Query SET SQL_SAFE_UPDATES=1
           13 Query SELECT CONNECTION_ID()
           13 Query SHOW SESSION STATUS LIKE 'Ssl_cipher'
           13 Query USE `workflydb`
           13 Query set autocommit=1

Although, I got an error message, when I checked my indexes, the result was this. I don't know what the problem can be, and how to solve it, or whether my created index actually works. Any ideas?

Answer 1

look for a property like 'connection keep alive' in your MySql Workbench. See this answer: MySQL Workbench: How to keep the connection alive

Answer 2

This may because of timeout problem. Change time out to a larger value. You can do it in command line interface or in mysql workbench.