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

alter-tableMySQLmysql-workbench

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.

Best Answer

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.

Related Solutions

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Mysql query not working

Your problem is the quotes you are using for the default of NAME.

Here is your original create table from the question

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’, <<--- LOOK HERE
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

You gave default ‘’ and the mysql client does not understand it.

You need to express it with standard single quotes like this : default ''

I just noticed that a_horse_with_no_name's comment already says this.

Don't change anything else. Leave all other quotes as is.

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Adding index to large thesql tables

Mysql query not working

Related Question