MySQL VIEWS访问旧数据库。(MySQL VIEWS accessing old DB. Cannot edit `information

MySQL VIEWS访问旧数据库。(MySQL VIEWS accessing old DB. Cannot edit `information_schema'. Cannot add new rows. Repair advice please)

我正在使用HeidiSQL。 已移至我的数据库的新版本。 我现在似乎有两个问题。 关于如何修理的任何建议。

1）我有几个视图，大大简化了查询，但现在引用旧数据库。

我检查了information_schema ，当然在那里的VIEWS表中，我的视图引用了旧数据库。 但是当我尝试编辑时，我得到： SQL Error (1044): Access denied for use root@localhost to database information_schema 。 （我以root用户身份登录。）

并非所有视图都是硬编码的。 有些是select table1, table2 etc但有些是select database.table1, database.table2 etc 。 当我创建这个新版本（导出旧的SQL然后导入到一个新的空数据库）时，我有错误显示它何时到达视图。 有一些关于“算法”然后它有第二次去看似乎创建它们。

2）通常在HeidiSQL中，您只需在表格中添加一个空行然后进行编辑即可。 我现在可以编辑名称，但是我不能指定DATATYPE（在information_schema编辑甚至odder（和我的一些旧DB）如果我尝试添加一行然后显示默认的DATATYPE VARCHAR但是我的两个较新的数据库DATATYPE字段是空白，我不能输入一个。）

我在HeidiSQL中使用过Maintenance ，但它似乎没有解决这个问题。 我重新启动了MySQL（但还没有重启计算机）。

有什么想法吗？ 我只是移动到Heidi，因为phpMyAdmin设法在不久前完全扰乱了数据库。

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.

最满意答案

1.my.ini或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.最后，在没有--skip-grant-tables选项的情况下重新启动实例/守护程序。

$ /etc/init.d/mysql restart

4.您应该能够使用新密码进行连接。

$ mysql -u root -p Enter password: my_password

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.