MySQL VIEWS accessing old DB. Cannot edit `information_schema'. Cannot add new rows. Repair advice please

stevehomer's profile image stevehomer posted 9 years ago in General Permalink
I am using HeidiSQL. Moved to a new version of my DB. I now seem to have two problems. Any advice on how to repair.

1) I have several views which have greatly simplified queries BUT the now refer to the old DB.

I checked in information_schemaand sure enough in the VIEWS table there, my views are referencing the old DB. But when I try to edit I get: SQL Error (1044): Access denied for use root@localhos to database information_schema. (I am logged in as root.)

Not all views are hard coded. Some are select table1, table2 etc but others are select database.table1, database.table2 etc. When I created this new version (exported old SQL then imported into a new empty DB) I had errors showing when it got to the views. There was something about "algorithm" and then it had a second go and appeared to create them.

2) Normally in HeidiSQL you can just add a blank row to a table and then edit it. I can now edit the name but I cannot specify theDATATYPE (EDIT even odder in information_schema (and some of my older DBs) if I try to add a row then the default DATATYPE VARCHAR is shown but with my two newer DBs the DATATYPE field is blank and I cannot enter one.)

I have used Maintenance in HeidiSQL but it does not seem to fix this problem. I have restarted MySQL (but not the computer yet).

Any thoughts? I only moved to Heidi as phpMyAdmin managed to completely scramble a DB some time ago.

Please login to leave a reply, or register at first.