Hi all ! Sorry for the long subject line, but I couldn't find a short one...
Here is the problem : My database (mysql 4.1.1) consists mostly for compressed MyISAM tables. I wrote a script, relying on a temporary memory-based table. This table is, of course, writable. If try to do an update or a delete on this table (and only on this table), but with a join on a compressed table in the where clause, I get an error : 'table [mycompressedtable] is read only'. The problem is that I am not trying to write to this table ! For example, let's say I have a memory based table 'tmpTable', and a compressed table 'storageTable'. All these queries give me errors : UPDATE tmpTable, storageTable SET tmpTable.Field1 = 'whatever' WHERE (tmpTAble.Field2 = storageTable.Field2) AND (storageTable.Field3 = 'something') or DELETE tmpTAble FROM tmpTable INNER JOIN storageTable ON (tmpTAble.Field2 = storageTable.Field2) WHERE (storageTable = 'something') or DELETE FROM tmpTAble USING tmpTable, storageTable WHERE (tmpTAble.Field2 = storageTable.Field2) AND (storageTable = 'something') So, it looks like the query analyzer does not even check if the table being updated or deleted is writable, it only checks if there is a read only table in the query, and rejects it in this case... I have seen no mention of this in the documentation, should I report this as a bug ? I can work around this using a subquery, but it is a lot less optimized than using joins, I think... Thanks for helping ! Arnaud Lesauvage -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
