Mysql – Optimize command issuing “’View is not base table” and “Corrupt” error messages

Paolo, can you please provide the mysql error log? If one isn't available, you can add one by adding this line to your my.cnf file

[mysqld]

log-error=/var/log/mysql/mysql.err

One possible idea to narrow down your research.

You already know how to remove the ibdata and iblog files to create new ones. If you just want to test your mysql installation.

1. Remove the Innodb_force_recovery or comment it out
2. Backup your databases : mysqldump --all-databases -u root -p | gzip -9 > all_databases.sql.gz
3. Move your MySQL data directory somewhere else: mv /var/lib/mysql{,.bak}
4. Create a new MySQL data directory: mkdir /var/lib/mysql
5. Give rights to the MySQL user: chown -R mysql:mysql /var/lib/mysql
6. Start MySQL: /etc/init.d/mysql start
7. Run a tail -f on the mysql log you added to my.cnf: tail -f /var/log/mysql/mysql.err

If MySQL isn't running then something else on the system level isn't right. As Gopinath's link mentionned. Is there enough disk space: df -h and inodes: df -i? Is you file system healthy? A system reboot or using fsck on the disk partition where MySQL is installed, would detect and possibly fix file system problems.

If MySQL comes back, then you can restore your databases:

zcat all_databases.sql.gz | mysql -u root -p

Send us the logs after completing the steps I sent, and we'll take it from there.

For a view to be updatable without using INSTEAD OF triggers "SQL Server must be able to unambiguously trace modifications from the view definition to one base table.".

There is no performance disadvantage to Updating these Views as SQL Server will just generate a query plan for the base table affected. One possible disadvantage might be that it adds a layer of obfuscation so unless you are using Views as a security layer it is clearer to just write code that updates the base table directly.

Another one might be if the View contains a one to many JOIN and you update the "one" side with a value from the "many" side it is undeterministic what result you end up with but the same applies to SQL Server's proprietary UPDATE ... FROM syntax. You would need to use MERGE or a scalar correlated sub query to avoid this possible issue.

For Views that are not updatable and require an INSTEAD OF trigger there are performance implications as the inserted and deleted pseudo tables need to be generated from the base table so if possible updating the base tables directly will likely be more efficient.