Description:
I am using MySQL 5.5.24, it is a slave server with read only enabled.
I am using user with the following privileges.
select, insert, update, delete, create temporary tables, create, drop, alter
I am able to create temporary table but not able to insert data either with normal insert statement or create temporary table tablename select * tom table.
mysql> create temporary table test1 (id int(5), name varchar(10 ));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into test1 select * from t1;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
where as i was able to insert data into temporary table using load data local infile command:
mysql> load data local infile '/home/mysql/text' into table test1 fields terminated by ',' lines terminated by '\n';
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
How come we are not able insert data with normal insert but with load data command we are able to insert.
i am not able to create base table, i can understand it cannot be done.
But in one of the bug i saw the below information posted:
According to http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_only: "When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. " This definition says nothing about DDL statements.
Even DDL command doesn't work with read only enabled for non super user.
mysql> create table t2 (id int(2));
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
Does create temporary table doesn't classify into DDL as similar to the create table statement.
How to repeat:
mysql> create temporary table test1 (id int(5), name varchar(10 ));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into test1 select * from t1;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
where as i was able to insert data into temporary table using load data local infile command:
mysql> load data local infile '/home/mysql/text' into table test1 fields terminated by ',' lines terminated by '\n';
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
How come we are not able insert data with normal insert but with load data command we are able to insert.
