MySQL create table issue with --read-only error

Question

I'd like to create a user who has all privileges with his own database in MySQL.

When I use this user to create a table, MySQL returns that the SQL server is running with read-only option.

However when I changed to an another existing user with all privileges on *.*, I can create table without error.

I'm wondering if the read-only option is global or what?

The following is my MySQL commands using MySQL root:

mysql> create user 'demo'@'localhost' identified by 'demo';
mysql> create database demo;
mysql> grant all privileges on demo.* to demo@localhost;
mysql> show grants for demo@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for demo@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'demo'@'localhost' IDENTIFIED BY PASSWORD '*demo-hashed*' |
| GRANT ALL PRIVILEGES ON `demo`.* TO 'demo'@'localhost'                                                |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Then I switched to user "demo":

mysql> use demo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t(t1 int);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

So I checked the read-only option, and it seems to be on.

However then I tried using another user with privileges on *.* and I can create tables successfully.

The another user grant setting:

mysql> show grants for demo2@'%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for demo2@%                                                                                            |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'demo2'@'%' IDENTIFIED BY PASSWORD '*demo2-hased*' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL version:

mysql> select version();
+------------------------+
| version()              |
+------------------------+
| 5.1.68.0 |
+------------------------+
1 row in set (0.00 sec)

BTW, after I set read_only = 0 I can use demo to create table. I just don't know why the demo2 can create table while read-only is on.

Thanks!

Can you please add Select current_user() in both places. Also one of the possible reason I found was promoting slave to master. Hope that is not your case — georgecj11

Ahmad Abuhasna Ahmad Abuhasna · Accepted Answer · 2014-04-16T09:24:35

Please check the My.cnf for Linux or My.ini for windows under [mysqld] remove read only parameters then restart the service and try again that will solve the read only problem, but if you create table in read only that will be a temp table.

MySQL create table issue with --read-only error

1 Answers