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

Question

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_schema and 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@localhost 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.

Answer 1

I copied the code for each view by hand.

I deleted the old views and then created views again with the same names. That worked fine. Took a while to think of the obvious solution.

If you hit the same problem and find this useful please give me an up vote.

I will be uploading the DB to the production server a table at a time and carefully checking the SQL. If you hit this problem suggest vigilance as I suspect problems can easily reappear.

Answer 2

1.my.ini or my.cnf

$ mysqld --skip-grant-tables

2.sh mysql

$mysql -u root mysql
$mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';
$mysql> FLUSH PRIVILEGES;

3.Finally, restart the instance/daemon without the --skip-grant-tables option.

$ /etc/init.d/mysql restart

4.You should be able to connect with your new password.

$ mysql -u root -p

Enter password: my_password