1

I am trying to run an alter query on a table, and its giving me a

Lost connection to MySQL server during query
MySQL server has gone away.

The query that I am running is

 alter table table_name modify column_name DOUBLE ( 16, 2) default 8.5;

I am guessing that the table contains a lot of data, and hence the alter is taking a lot of time, and the server connection is lost.

My query is,

Firstly,

How do I find out the amount of data present in the table ? ( in MB/GB i suppose).

Secondly, In case the table does contain a huge amount of data, then how do I perform the alter on this table ?

migrated from stackoverflow.com Jan 3 '14 at 15:33

This question came from our site for professional and enthusiast programmers.

  • 2
    You'd probably do better asking this on Database Administrators – Mike W Jan 3 '14 at 14:04
  • You need to check the MySQL's error log. If you're executing the statement from some web frontend (like PHPMyAdmin), try to execute the statement directly from mysql cli (mysql). – Dimitre Radoulov Jan 3 '14 at 15:38
  • If its innodb, try the Percona Tools for online table changes – Alain Craven Jun 8 '14 at 12:52
  • I'd like to add that pt-online-schema-change is storage engine agnostic – akuzminsky Jun 8 '14 at 14:38
  • This is tangential to your question, but in most cases a FLOAT or DOUBLE column should be DECIMAL instead. – Bill Karwin Jun 8 '14 at 17:30
2

You need to check the server's error log(s). There are too many situations that could cause this problem. For example:

  • The table is corrupt, and InnoDB detects this while reading an invalid page checksum. The default behavior here is to shutdown to force an administrator to intervene.

  • Your server is over configured to use way too much memory. The OOM killer terminates mysqld.

  • There is no problem at all. The client disconnects, but the server keeps running the ALTER statement, and it eventually finishes.

0
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Will give you the database size in MB.

Sorry, I can't help you with your second question.

0

For the second part of your question, you can:

  • Create a new table with the new column structure
  • Copy the data from the old table into the new table INSERT INTO ... SELECT ...
  • DROP the old table
  • RENAME TABLE the new table to the old table name.
0

There is a tool, oak-online-alter-table, written by Shlomi Noach which might be right up your alley. It is designed to "perform a non-blocking ALTER TABLE operation". Shlomi Noach is a big hitter in the MySQL world and the tool is available (with other stuff) here. Written in Python. Disclaimer - I haven't used it. As for size, a popular post is available here. p.s. always put your MySQL version in your posts.

Your Answer

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