2

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?

3

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]>
  • 1
    Good work, +1 !!! – RolandoMySQLDBA Jan 28 '15 at 21:04
  • 1
    I'm currently compiling a MariaDB 5.5 instance to see what happens then. – Vérace Jan 28 '15 at 21:05
  • Wow, thanks for doing all that. Yes, it's 5.5. One of the sysadmins here restarted mariadb, which removed a bunch of sleeping and killed processes. After that, the alter table worked without problem. – Michael Jan 28 '15 at 21:07
  • 1
    @Michael: if this worked after a restart my guess would be that one of those "bunch of sleeping and killed processes" still kept some kind of lock on that table that prevented DDL but not DML. – a_horse_with_no_name Jan 28 '15 at 22:31
  • 2
    @Michael. I tried with MariaDB5.5 and it works fine. It looks like a_horse_with_no_name's explanation is the correct one. May I STRONGLY recommend that you don't use SQL keywords for table or field names. It makes tracking down bugs/typos difficult and will totally mess you up if you want to port to another db. – Vérace Jan 29 '15 at 0:21

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.