Mysql
無法將列添加到 Mariadb 中的 InnoDB 表
我在 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]>