我正在使用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-tables2.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 restart4.您应该能够使用新密码进行连接。
$ mysql -u root -p Enter password: my_passwordI 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.
更多推荐
发布评论