MySQL: SELECT...GROUP BY causes "Table is read only " error

Question

I am working as admin on a Windows 7 PC with XAMPP Server and Mysql QueryBrowser. The database is storage only, nobody writes in it or reads from it except me.

These statements work as expected:

Select Col1, Col2, Col3 from Table1 
Select Col1, Col2, Col3 from Table1 Group by Col1

Each of the following statements

Select Col1, Col2, Col3 from Table1 Group by Col2
Select Col1, Col2, Col3 from Table1 Group by Col3
Select Col1, Col2, Col3 from Table1 Group by Col1,Col2,Col3

gives the following output

#1036 - Table 'Table1' is read only

There is an index on Col1 and Col2 but not on Col3. How can that be?

EDIT: I found out that it has nothing to do with GROUP BY. It is even more strange as can be seen from these simple queries:

SELECT count(*) FROM Table1 where Col2 > 3000;


EXPLAIN SELECT count(*) FROM Table1 where Col2 > 3000;
+----+-------------+----------------+-------+-----------------+--------+-----    +------+----------+--------------------------+
| id | select_type | table          | type  | possible_keys   | key    | key_len | ref  | rows     | Extra                    |
+----+-------------+----------------+-------+-----------------+--------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | Table1         | range | Col2,Col7       | Col2   | 4       | NULL | 22315581 | Using where; Using index |
+----+-------------+----------------+-------+-----------------+--------+---------+------+----------+--------------------------+

works fine. However,

SELECT count(*) FROM Table1 where Col4 > '2009-01-01';

EXPLAIN SELECT count(*) FROM Table1 where Col4 > '2009-01-01';

+----+-------------+----------------+------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+----+-------------+----------------+------+---------------+------+---------+------+-----------+-------------+
|  1 | SIMPLE      | Table1         | ALL  | NULL          | NULL | NULL    | NULL | 237089257 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+-----------+-------------+

gives the error

ERROR 1036 (HY000): Table 'Table1' is read only

I am thankful for @Michael - sqlbot's input and also added the EXPLAIN ... output. However, there how can one column cause potential problems with the filesystem permissions and the other column does not? I checked the mysql_error file in the XAMPP directory but I found nothing that helped me.

`Group by` is generally when aggregating data, you're not doing that here. Perhaps you mean `ORDER BY` instead of `group by`? — xQbert, Jul 02 '15 at 19:50
Unless you're aggregating Col2 and Col3, the output of that query will give you unreliable results. Does this link answer your question? http://stackoverflow.com/questions/9575914/table-is-read-only — ash, Jul 02 '15 at 19:52
Ok, but the above syntax would be invalid in any database except mySQL as it [extends the group by clause](https://dev.mysql.com/doc/refman/5.1/en/group-by-handling.html) so you can have one without aggregation in mySQL but no where else. As to why this isn't working. I couldn't sa. — xQbert, Jul 02 '15 at 19:53
@doublesidedstickytape I have checked the link before but I do not see the reason for the problem because I can perform a SELECT ...GROUP BY on Col1. I do not see the option of passing rights to columns of a table? — HOSS_JFL, Jul 02 '15 at 20:07
@xQbert I edited the post. It does not work even if I inlcude all three Columns that I select. — HOSS_JFL, Jul 02 '15 at 20:09
@hoss_JFL what about `SELECT count(col1), col1,col2, col3 from table1 Group by col1,col2,col3`? again operating a group by without an aggregate is not standard use of a group by. I would need to understand what you're trying to do with sample data and expected results. to figure out how to write a query to give you desired results. As it stands group by without an aggregate makes no sense to me. — xQbert, Jul 02 '15 at 20:12
It is more severe than what you suggested me to test. `SELECT count(*) FROM Table1 where Col4 > '2009-01-01';` gives me `ERROR 1036 (HY000): Table 'Table1' is read only` although I am in the commandline with all the privilges. How can that be? This is crazy... — HOSS_JFL, Jul 03 '15 at 14:01
*However, there how can one column cause potential problems with the filesystem permissions and the other column does not?* Simply enough, if the server needs one or more temporary tables to resolve some queries, but not others, as I explained. I am not saying it's directly related to the group by, but the symptoms there seem to point to tmpdir permissions issues. You have also not followed through with my `EXPLAIN SELECT` reasoning. — Michael - sqlbot, Jul 03 '15 at 21:01
Thanks, I tested the `EXPLAIN SELECT` statement yesterday but did not find anything problematic. As you can see in my edit the second line that causes the problems is even more simple than the first line that does nto cause any issues. I need also to add that I worked before on the database and used the table extensively. I have these problems for the first time. — HOSS_JFL, Jul 04 '15 at 05:01

score 1 · Answer 1 · answered Jul 03 '15 at 19:35

You say:

There is an index on Col1 and Col2 but not on Col3. How can that be?

What I assume you mean is that there is a single index on (col1,col2). This index could be used for GROUP BY col1 but not for any other grouping.

EXPLAIN SELECT should reveal that the failing queries are doing something along the lines of using temporary, using filesort for the grouping, with the implication being that it isn't the base table itself that is read only, but rather what's happening is an OS permissions problem that's being encountered with the temporary table necessary for the grouping that can't use an index.

So it isn't MySQL preventing you from running the queries, it's Windows preventing MySQL from doing what it needs with temporary tables. Presumably filesystem permissions.

There may also be useful messages in the MySQL error log.

https://dev.mysql.com/doc/refman/5.6/en/temporary-files.html

Thanks, I updated my posting. It has nothing to do with the `GROPU BY` statement, it is more general. Maybe the table is damaged? — HOSS_JFL, Jul 03 '15 at 20:53

MySQL: SELECT...GROUP BY causes "Table is read only " error

1 Answers1