MariaDB – Cannot Add Column to InnoDB Table

alter-tableinnodbmariadbMySQL

I have this InnoDB table with less than 10 rows in mysql 5.5.40-MariaDB on Linux (x86_64).

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| current_version | varchar(20)  | NO   |     | NULL              |                |
| last_updated    | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| key             | varchar(256) | NO   |     | NULL              |                |
| language        | varchar(20)  | NO   |     | NULL              |                |
| text            | text         | NO   |     | NULL              |                |
+-----------------+--------------+------+-----+-------------------+----------------+

When I try to add a column, the client hangs like so:

MariaDB [pkppln]> alter table terms_of_use add column `weight` int(11) not null default 0;
Stage: 2 of 2 'Enabling keys'      0% of stage done

The server continues to respond to other queries, but the client does nothing. I can kill the query with ctrl-C. The alter table command works in my dev environment, but seems to be failing here.

How can I fix this, and add the column?

Best Answer

What I did

MariaDB [test]> select version();
+---------------------+
| version()           |
+---------------------+
| 10.0.15-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

Are you using 5.5?

CREATE TABLE terms_of_use
( 

  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  current_version  varchar(20),  
  last_updated     timestamp,    
  keyx              varchar(256),  <== note keyx - fails if I use 'key'
  language         varchar(20),
  text 
);

It appears that the problem is partly to do with the use of 'key' as a field name.

INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');


MariaDB [test]> select * from terms_of_use;
+----+-----------------+---------------------+----------+----------+--------+
| id | current_version | last_updated        | keyx     | language | text   |
+----+-----------------+---------------------+----------+----------+--------+
|  1 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  2 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  3 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  4 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  5 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  6 | afsdf           | 2015-01-28 20:45:06 | asdfadsf | adfs     | terewe |
+----+-----------------+---------------------+----------+----------+--------+
6 rows in set (0.00 sec)

Then I added the column

alter table terms_of_use add column `weight` int(11) not null default 0;

Then SELECT again

MariaDB [test]> select * from terms_of_use;
+----+-----------------+---------------------+----------+----------+--------+--------+
| id | current_version | last_updated        | keyx     | language | text   | weight |
+----+-----------------+---------------------+----------+----------+--------+--------+
|  1 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  2 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  3 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  4 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  5 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  6 | afsdf           | 2015-01-28 20:45:06 | asdfadsf | adfs     | terewe |      0 |
+----+-----------------+---------------------+----------+----------+--------+--------+
6 rows in set (0.00 sec)

MariaDB [test]>
Related Question