Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.8
-
Debian 8 x86_64
Description
Connect is issuing "ALTER TABLE DISABLE KEYS" when inserting data to a connect table pointing to a remote table. I think Connect disables keys for speedy insertion of data and then creates key.
Most of the time users connecting remotely are not having ALTER TABLE privilege. So it becomes impossible for them to insert data using connect.
I suggest that a flag should be added to connect connection parameters to disable/enable connect issuing ALTER TABLE DISABLE KEYS command. I could not find something similar on the connect documentation page.
This way all user can insert data to remote connect table, even if they are not having ALTER TABLE privilege.
Above can be tested using the following steps:
|
Server 1 |
CREATE DATABASE test;
|
USE test;
|
CREATE TABLE t1 (id INT) ENGINE=InnoDB;
|
INSERT INTO `t1` (`id`) VALUES (1);
|
INSERT INTO `t1` (`id`) VALUES (2);
|
GRANT SELECT, INSERT, UPDATE, DELETE ON test.t1 TO 'user1'@'server2' IDENTIFIED BY PASSWORD 'XXX';
|
|
Server 2 |
CREATE DATABASE test;
|
USE test;
|
CREATE TABLE t2 ENGINE=CONNECT DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC CONNECTION='mysql://user1:XXX@server1'
|
`table_type`=MYSQL `dbname`=test `tabname`=t1;
|
SELECT id FROM t2;
|
INSERT INTO t2 (id) VALUES(6);
|
Error Code: 1296
|
Got error 174 '(1142) ALTER command denied to user 'user1'@'server2' for table 't1' [ALTER TABLE `t1` DISABLE KEYS]' from CONNECT
|
See the above error given by connect.
I have tested it on Debian 8 64bit only.
Attachments
Activity
On Windows I could not reproduce this error. Besides, a comment in the mysql_alter_table says:
When the ALTER TABLE statement just does a RENAME or ENABLE|DISABLE KEYS, or both, then this function short cuts its operation by renaming the table and/or enabling/disabling the keys. In this case, the FRM is not changed, directly by mysql_alter_table.
Looks like the ALTER TABLE privilege may not be always required for doing these operations.
Leaving it to the user is more complicated. The:
ALTER TABLE `%s` {DISABLE|ENABLE} KEYS
|
must be executed on the remote server to be operational, which implies to have access to it (then why using a connect table) or to create a special table enabling to execute remote commands.
It is easy to comment out these two lines fixing this problem for you. However, before doing this, I'd like to have more information about why this error occured, in what case and on what platform and to understand why this error did not occur on my machine.
Per splitice comment on PR - "ENABLE KEYS on a 50GB+ database for every insert was never going to work."
There was no activity on this, so I explored the connect code source code and found the following:
Branch: 10.1, File : storage/connect/tabmysql.cpp, Revision: fdba672
...bool TDBMYSQL::OpenDB(PGLOBAL g)...// line nos. 932-934sprintf(cmd, "ALTER TABLE `%s` DISABLE KEYS", Tabname);m_Rc = Myc.ExecSQL(g, cmd, &w); // may fail for some engines...void TDBMYSQL::CloseDB(PGLOBAL g)...// line nos. 1205-1208dup->Step = "Enabling indexes";sprintf(cmd, "ALTER TABLE `%s` ENABLE KEYS", Tabname);Myc.m_Rows = -1; // To execute the querym_Rc = Myc.ExecSQL(g, cmd, &w); // May fail for some enginesConnect issues "ALTER TABLE `%s` DISABLE KEYS" while opening connection to the DB and then issues "ALTER TABLE `%s` ENABLE KEYS" while closing DB connection.
In my opinion connect should not issue ALTER TABLE while connecting to Remote Database because most of the remote users do not have ALTER TABLE privilege.
I think we should leave this to user, whether to use
ALTER TABLE `%s` {DISABLE|ENABLE} KEYScommand while connecting/disconnecting.