4

I have no experience with MySQL. My boss ran an optimize against a MySQL database. Against a bunch of views we got the message

"tablexyz" is not BASE TABLE

and the next has message is

Corrupt

It seems to be only against views, we didn't get any of these error messages against the base tables.

Does this look like an incorrect error message, or do we have issues with our tables?

2 Answers 2

5

You cannot run OPTIMIZE TABLE against a View. I can see why the error message would be Corrupt

In mysql, a view is represented as a table in INFORMATION_SCHEMA.TABLES.

I have discussed views in this light before

However, a View in INFORMATION_SCHEMA.TABLES has NULL as a Storage Engine.

Consequently, there must be a storage engine to optimize on. Since the Storage Engine is NULL, Corrupt error message is understandable. Therefore, don't be concerned with the error message. Just run OPTIMIZE TABLE on the underlying base tables.

1

I experienced this so started to look into it

SHOW CREATE TABLE tablexyz;

This produced something like

HEADER RESULT NAMES
View, Create View, character_set_client, collation_connection

And in the second column contained:

CREATE ALGORITHM=UNDEFINED DEFINER=`%someone%`@`%some_ol_host_name%` 
SQL SECURITY DEFINER 
VIEW `tablexyz`.`view_tablexyz` AS 
SELECT `tablexyz`.`some_column_name`,
       `tablexyz`.`some_more_column_names`
FROM `tablexyz`
JOIN `tableabc` ON `tablexyz`.`id` = `tableabc`.`id`
WHERE `tablexyz`.`some_column_names` = 'somevalues'
GROUP BY  `tablexyz`.`some_column_names`;

For me... Well Turned out to be nothing but a view.

One last thing to note:

USE tablexyz;

ANALYZE TABLE tablexyz; -- Corrupt
CHECK TABLE tablexyz; -- OK
REPAIR TABLE tablexyz; -- Corrupt

Check table will provide a false positive as messages are inconsistent.

ANALYZE TABLE & REPAIR TABLE state that the table is corrupted even though it is a view.

And CHECK TABLE says the table is OK

# Table, Op, Msg_type, Msg_text
'tablexyz.tablexyz_view', 'check', 'status', 'OK'

Thought it odd but seems ok per the documentation

Cheers, Jay

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.