Mysql

無法將列添加到 Mariadb 中的 InnoDB 表

  • January 28, 2015

我在 Linux (x86_64) 上的 mysql 5.5.40-MariaDB 中有這個少於 10 行的 InnoDB 表。

+-----------------+--------------+------+-----+-------------------+----------------+
| 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              |                |
+-----------------+--------------+------+-----+-------------------+----------------+

當我嘗試添加一列時,客戶端掛起如下:

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

伺服器繼續響應其他查詢,但客戶端什麼也不做。我可以用 ctrl-C 終止查詢。alter table 命令在我的開發環境中有效,但在這裡似乎失敗了。

我該如何解決這個問題,並添加列?

我做了什麼

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

你用的是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 
);

看來問題部分與使用“鍵”作為欄位名稱有關。

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)

然後我添加了列

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

然後再次選擇

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]>

引用自:https://dba.stackexchange.com/questions/90459