| Bug #13108 | ALTER TABLE does not work on FEDERATED tables | ||
|---|---|---|---|
| Submitted: | 10 Sep 2005 21:43 | Modified: | 18 Dec 2005 5:20 |
| Reporter: | Patrick Galbraith | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.12/BK source 5.0 | OS: | MacOS (Mac OSX 10.4.2, Darwin 8.2.0) |
| Assigned to: | Eric Herman | CPU Architecture: | Any |
[12 Sep 2005 6:03]
Patrick Galbraith
More thoughts on this: What an alter does is tries to 'back up' the table into a temp table and then drops the old table and then recreates it, copying back over the data (my understanding). This might explain the insert statement we see in any of the failed alter statements. What we really need is to simply modify the frm file (this is automatic with alter tables). So, in the end, we need to avoid trying to do anything to the table other than allowing the alter table to occur, hence the frm file will be changed. Also, if the comment is changed (connect string), then that connection needs to be checked just as it is in 'create'.
[12 Sep 2005 13:11]
MySQL Verification Team
This also happens with Windows server.
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.13-beta-nt-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table t1 (id int(32), name varchar(32)) ENGINE="FEDERATED"
-> COMMENT="mysql://miguel:testuser@192.168.0.119/test/t1";
Query OK, 0 rows affected (0.13 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | name |
+------+------+
1 row in set (0.06 sec)
mysql> alter table t1 modify column name varchar(64);
ERROR 1430 (HY000): There was a problem processing the query on the foreign data source. Data source error: ': 1062 : Duplicate entry '1' for key 1'
mysql> alter table t1 modify column id int(16);
ERROR 1430 (HY000): There was a problem processing the query on the foreign data source. Data source error: ': 1062 : Duplicate entry '1' for key 1'
mysql> alter table t1 rename to t2;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(32) default NULL,
`name` varchar(32) default NULL
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COMMENT='mysql://miguel:testuser@192.168.0.119/test/t1' |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
mysql> alter table t2 add primary key (id);
ERROR 1121 (42000): Column 'id' is used with UNIQUE or INDEX but is not defined as NOT NULL
mysql>
[30 Sep 2005 23:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/30592
[19 Oct 2005 17:26]
Eric Herman
"ALTER" is now not supported for federated tables.
[20 Oct 2005 19:58]
Paul DuBois
We still need the three-part number of the version into which the fix was pushed.
[16 Dec 2005 20:17]
Eric Herman
oops. this was *fixed* in 5.0.13, not discovered in 5.0.13
[16 Dec 2005 20:18]
Eric Herman
sorry, disregard previos comment, updated wrong page.
[16 Dec 2005 20:22]
Eric Herman
No. It's obviously late. I was correct the first time: bk c2r -rmysql-5.0.12 federated_archive.test returns no data bk c2r -rmysql-5.0.13 federated_archive.test return 5.0.13 This was indeed fixed in 5.0.13
[18 Dec 2005 5:20]
Paul DuBois
Noted in 5.0.13 changelog.
Description: ALTER TABLE is not fully functional for FEDERATED. Summary In testing, I found this: supported: rename of table, change of comment field not supported: modify column (see log output), index modification, change of comment to a user that doesn't have priveleges, then try to change comment back to one who does (odd!) Table on foreign server: mysql> show create table t1; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(32) NOT NULL auto_increment, `name` varchar(32) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Federated table: mysql> create table t1 (id int(32), name varchar(32)) ENGINE="FEDERATED" COMMENT="mysql://root@localhost/test/t1"; Query OK, 0 rows affected (0.15 sec) mysql> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | 1st name | +------+----------+ 1 row in set (0.13 sec) mysql> alter table t1 modify column name varchar(64); ERROR 1430 (HY000): There was a problem processing the query on the foreign data source. Data source error: ': 1062 : Duplicate entry '1' for key 1' mysql> alter table t1 modify column id int(16); ERROR 1430 (HY000): There was a problem processing the query on the foreign data source. Data source error: ': 1062 : Duplicate entry '1' for key 1' mysql> alter table t1 rename to t2; Query OK, 0 rows affected (0.00 sec) mysql> show create table t2; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `id` int(32) default NULL, `name` varchar(32) default NULL ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COMMENT='mysql://root@localhost/test/t1' | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> alter table t2 add primary key (id); ERROR 1121 (42000): Column 'id' is used with UNIQUE or INDEX but is not defined as NOT NULL mysql> alter table t2 modify column t1 int(32) NOT NULL; ERROR 1054 (42S22): Unknown column 't1' in 't2' mysql> alter table t2 add index name(name); ERROR 1121 (42000): Column 'name' is used with UNIQUE or INDEX but is not defined as NOT NULL mysql> alter table t2 COMMENT="mysql://patg@localhost/test/t2"; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t2; ERROR 1429 (HY000): Unable to connect to foreign data source - database ': 1045 : Access denied for user 'patg'@'localhost' (using password: NO )'! mysql> alter table t2 COMMENT="mysql://root@localhost/test/t2"; ERROR 1429 (HY000): Unable to connect to foreign data source - database ': 1045 : Access denied for user 'patg'@'localhost' (using password: NO )'! mysql> drop table t2; Query OK, 0 rows affected (0.01 sec) mysql> create table t1 (id int(32), name varchar(32)) ENGINE="FEDERATED" COMMENT="mysql://root@localhost/test/t1"; Query OK, 0 rows affected (0.01 sec) mysql> alter table t2 COMMENT="mysql://root@localhost/test/t2"; ERROR 1146 (42S02): Table 'test.t2' doesn't exist mysql> alter table t1 rename to t2; Query OK, 0 rows affected (0.00 sec) mysql> alter table t2 COMMENT="mysql://root@localhost/test/t2"; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t2; +------+----------+ | id | name | +------+----------+ | 1 | 1st name | +------+----------+ 1 row in set (0.00 sec) mysql> show create table t2; +-------+-------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------+ | t2 | CREATE TABLE `t2` ( `id` int(32) default NULL, `name` varchar(32) default NULL ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COMMENT='mysql://root@localhost/test/t2' | +-------+-------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------+ 1 row in set (0.00 sec) mysql> create table t1 (id int(32) NOT NULL, name varchar(32)) ENGINE="FEDERATED" COMMENT="mysql://root@localhost/test/t1"; Query OK, 0 rows affected (0.12 sec) mysql> alter table t1 add primary key (id); ERROR 1430 (HY000): There was a problem processing the query on the foreign data source. Data source error: ': 1062 : Duplicate entry '1' for key 1 LOG: (alter table t1 add primary key (id) 8 Connect root@localhost on test 2 Query SHOW TABLE STATUS LIKE 't1' 2 Query SELECT `id`, `name` FROM `t1` 8 Query INSERT INTO `t1` (id, name) VALUES (1, 'Kumar') (alter table t1 modify column name varchar(64) ) 10 Connect root@localhost on test 2 Query SHOW TABLE STATUS LIKE 't1' 2 Query SELECT `id`, `name` FROM `t1` 10 Query INSERT INTO `t1` (id, name) VALUES (1, 'Kumar') 10 Quit How to repeat: Create a federated table that references another table (foreign table, any type), and run the various alter table commands as above.