[MDEV-9020] Connect issues ALTER TABLE DISABLE KEYS when inserting data Created: 2015-10-27  Updated: 2022-05-28  Resolved: 2022-05-27

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.1.8
Fix Version/s: 10.3.36, 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2

Type: Bug Priority: Major
Reporter: Mohit Chandel Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: connect-engine
Environment:

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.

 Comments   
Comment by Mohit Chandel [ 2015-12-05 ]

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-934
 
       sprintf(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-1208
 
      dup->Step = "Enabling indexes";
 
      sprintf(cmd, "ALTER TABLE `%s` ENABLE KEYS", Tabname);
 
      Myc.m_Rows = -1;      // To execute the query
 
      m_Rc = Myc.ExecSQL(g, cmd, &w);  // May fail for some engines

Connect 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} KEYS

command while connecting/disconnecting.

Comment by Olivier Bertrand [ 2015-12-27 ]

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.

Comment by Mohit Chandel [ 2016-06-24 ]

Olivier Bertrand,

Is there any progress on the issue?

Comment by Mathew [ 2022-02-03 ]

Heres a fixing PR https://github.com/MariaDB/server/pull/2002

Comment by Daniel Black [ 2022-05-27 ]

Per splitice comment on PR - "ENABLE KEYS on a 50GB+ database for every insert was never going to work."

Comment by Daniel Black [ 2022-05-27 ]

Thanks splitice

Generated at Thu Feb 08 07:31:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.