0

I have the same problem as this when I want to index a very large table on one of its non-unique columns which is an integer, and I tried all the solutions that are proposed in that post that has at least one vote up. I still couldn't fix it. Any other ideas?

I have enough memory:

max_allowed_packet: 2G, innodb_buffer_pool_size: 9G

All the time out settings mentioned in this post and here are set to much higher numbers than the default.

Community
  • 1
  • 1
Andi Keikha
  • 1,246
  • 2
  • 16
  • 37
  • The size of an index shouldn't be limited by available memory, but allocating more memory should make indexing more efficient. If an index fits in the buffer pool (InnoDB) then it will run considerably faster. What errors are you getting? – tadman Mar 16 '15 at 20:30
  • error code: 2013 Lost connection to mysql during query – Andi Keikha Mar 16 '15 at 20:31
  • `SHOW PROCESSLIST` should show that the query continues to run even if you lose your connection. Is it actually doing anything? I've run `ALTER TABLE` type queries that take several hours to run and it always worked out. – tadman Mar 16 '15 at 20:33
  • I started create index again, and the memory that is consumed by mysql is increasing gradually. It takes some time to give the error, but the error comes in a separate message box, and I think the process drops the memory again. Let me update you for sure when it gives me the error again. – Andi Keikha Mar 16 '15 at 20:35
  • 2 possible issues: 1) The tmp location is out of disk space while creating the index; 2) The system is out of memory and swap while creating the index. 1 is really tricky and annoying to find because once it happens, the disk space is released again making you search for a needle in a haystack. – Norbert Mar 16 '15 at 20:37
  • It's normal for the buffer to fill up. That's what it's for. When the memory is no longer used, it might be released. – tadman Mar 16 '15 at 20:38
  • I myself have run the sql queries that are Wikipeida's dumps that took one day successfuly, with the same settings (even with the default time_out settings, just increasing the memory for everything). and now I just want to index a column of one of those tables! It's weird! – Andi Keikha Mar 16 '15 at 20:39
  • Update: at the time I got the error 71% of my memory was being used, and 6G of memory was being used by mysql. – Andi Keikha Mar 16 '15 at 20:43
  • @NorbertvanNobelen: I have 147G free disk space on the drive that mysql is installed! So that should be enough for the temp file I suppose. – Andi Keikha Mar 16 '15 at 20:45
  • Do you also have this much disk space on /tmp? – Norbert Mar 16 '15 at 20:50
  • @NorbertvanNobelen: I only have one drive on my system. – Andi Keikha Mar 16 '15 at 20:52
  • run the query from mysql command line and see if it works..not from workbench...if it works just running from the command line, you know its just a mysql workbench issue and not something else. – BK435 Mar 16 '15 at 20:55
  • Okay, thanks, I am trying that, I am not very familiar with command line, it takes a little time. – Andi Keikha Mar 16 '15 at 20:59
  • @BK435: Thank you, it seems that it is working, no errors so far and it took 10G of RAM! amazing. I will update you once it is done. I think you should post it as an answer then. Thank you again. – Andi Keikha Mar 16 '15 at 21:30
  • 1
    @tadman: I think you were right about the process was running even after being disconnected, because when I ran it from command line, after one hour it said that the key name for the index is duplicate and the index was there. – Andi Keikha Mar 16 '15 at 22:52

1 Answers1

1

While this is not necessary an answer for loosing connection in mysql workbench it is a workaround. When it comes to long running queries in mysql workbench, even if one changes the mysql workbench parameters, there seems to be a connection time_out issue still occuring. So, run the query from mysql command line and see if it works. If it works when you run from the mysql command line and not from workbench, you know its just a mysql workbench issue and not some other issue.

BK435
  • 3,076
  • 3
  • 19
  • 27
  • Thank you for the point, your way didn't throw any errors during the process. However at the end it said that the key for index is duplicate. It seems that in spite of showing a connection error by workbench it was working and making the index but it didn't show any progress to me to know that it was actually doing it. – Andi Keikha Mar 16 '15 at 22:55
  • 1
    workbench is know to have issues...Its not perfect but HEY, its FREE!!! for now....... – BK435 Mar 16 '15 at 22:59