MySQL - Alter Table Fails As Workbench Tries To Create Existing Table

Question

I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as it fails attempting a 'CREATE TABLE, which it should not be doing as the table already exists! The output is below:-

ERROR 1114: The table 'Analysis' is full
SQL Statement:
ALTER TABLE `Patient`.`Analysis` ADD COLUMN `TestDateTime` DATETIME
NULL AFTER `idResults`

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'Patient' already exists
SQL Statement:
CREATE TABLE `Patient` (
`idAnalysis` int(11) NOT NULL,
`idResults` int(11) DEFAULT NULL,
`ResultPDF` mediumblob,
`ResultCSV` mediumblob
) ENGINE=InnoDB DEFAULT CHARSET=latin1

How can I add this extra column as this database will evolve over time as user requirements expand?

Thank you.

Answer 1

While the CREATE TABLE is a strange error, and should be investigated further and/or reported to see if the failback operation should be done for Workbench, your real problem is the first one:

ERROR 1114: The table 'Analysis' is full

This suggests that the engine cannot alter the table because it has run out of space. This doesn't necessarily mean that the database has run out of disk space, but InnoDB does for that particular table. For example, if you have a fixed size for the main tablespace and not using innodb_file_per_table=1 and using the COPY algorithm for the alter, it will require as much space as the size of the whole table for the operation.

Check the output of the following command:

SHOW GLOBAL VARIABLES like 'innodb_data_file_path';

And then check the size and free space of the ibdata1 files on the data dir of the MySQL server. You may need to increase the available size for the tablespaces, make it autoincremental or use innodb_file_per_table=1.

Answer 2

If you have a lot of records in your table, take a look at my answer to a related question here.

With respect to MySQL Workbench, I have always found the tool to be excellent.

Having said that, what MySQL Workbench 'may' be doing in the background is creating an empty temporary table with the extra field, copying over all the records, the deleting your old Patient table and renaming the temp table to Patient and then having problems creating the "new" Patient table. You'd have to check the source code to find out for sure what's going on - or maybe run SHOW TABLES; continuously against your system while the update was being performed.

Where the message "The table "Analysis' is full" is coming from, I don't have a clue - does it exist in your schema? Do you have spare disk space for your MySQL datadir, or is it getting tight?

In any case, if a GUI tool fails, the first thing to do is to is to run the command from the mysql command line tool (another reason to prefer the command line), just to ensure that you're really dealing with a MySQL problem and not an issue with Workbench. Do you have the output of the error log? That might also be helpful.

Answer 3

ALTER TABLE .. ADD COLUMN must copy the table over and rebuild the indexes. This means that it needs about as much extra space as the table requires. It may not seem like the disk ever got full, but it may have, then got freed up when the error occurred.

Also, depending on the setting of 'tmpdir', it may be running out of room in the temp directory. (Hence, I argue against carving up disk into filesystems.)

Answer 4

Answer originally left by MichaelJohn (the question author) in a comment:

In the end I tracked down the issue to MySQL having installed the DataDirectory on /dev/sda2 (/root) and not /dev/sda3 (/home).

The process of adding the new column caused /root to fill completely (potentially the reason for the muddled messages). I have now copied the data to /home but am having other issues that I may post separately. Thank you for all answers and comments.