Safely alter a column in MySQL 5.5

Question

I have found myself looking after an old testlink installation, all the people responsible have left and it is years since I did any serious SQL work.

The underlying database is version 5.5.24-0ubuntu0.12.04.1

I do not have all the passwords, but I have enough rights to do a backup without locks;

  mysqldump --all-databases --single-transaction -u testlink -p --result-file=dump2.sql

I really do not want to a attempt to restore the data!

We need to increase the length of the name field in testlink, various pages lead me to increasing the length of a field in the nodes_hierarchy table.

The backup yielded this;

CREATE TABLE `nodes_hierarchy` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `node_type_id` int(10) unsigned NOT NULL DEFAULT '1',
  `node_order` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid_m_nodeorder` (`parent_id`,`node_order`)
) ENGINE=MyISAM AUTO_INCREMENT=184284 DEFAULT CHARSET=utf8;

I have only really one chance to get this right and cannot lose any data. Does this look exactly right?

ALTER TABLE nodes_hierarchy MODIFY name VARCHAR(150) DEFAULT NULL;

Answer 1

That is the correct syntax.

Backup

You should backup the database regardless how safe this operation is. It seems like you are already planning on it. It is unlikely you will have problems. Backup is just an insurance policy to account for unlikely occurrences.

Test table

You seem to have ~200K records. I'd recommend you make a copy of this table by just doing:

CREATE TABLE `test_nodes_hierarchy` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `node_type_id` int(10) unsigned NOT NULL DEFAULT '1',
  `node_order` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test_pid_m_nodeorder` (`parent_id`,`node_order`)
) ENGINE=MyISAM AUTO_INCREMENT=184284  DEFAULT CHARSET=utf8;

Populate test table

Populate the test table with:

insert into test_nodes_hierarchy
select *
from nodes_hierarchy;

Run alter state on this test table

Find how long the alter statement will take on the test table.

ALTER TABLE test_nodes_hierarchy
MODIFY name VARCHAR(150) DEFAULT NULL;

Rename test table

Practice renaming the test table using:

RENAME TABLE test_nodes_hierarchy TO test2_nodes_hierarchy;

Once you know the time it takes, you know what to expect on the main table. If something goes awry, you can replace the drop the nodes_hierarchy table and just rename test_nodes_hierarchy table.

That'll just build confidence around the operation.